com.interface21.jdbc.core
Class JdbcTemplate

java.lang.Object
  |
  +--com.interface21.jdbc.core.JdbcTemplate
All Implemented Interfaces:
InitializingBean

public class JdbcTemplate
extends java.lang.Object
implements InitializingBean

This is the central class in this package. It simplifies the use of JDBC and helps to avoid common errors. It executes core JDBC workflow, leaving application code to provide SQL and extract results. This class executes SQL queries or updates, initating iteration over ResultSets and catching JDBC exceptions and translating them to the generic, more informative, exception hierarchy defined in the com.interface21.dao package.

Code using this class need only implement callback interfaces, giving them a clearly defined contract. The PreparedStatementCreator callback interface creates a prepared statement given a Connection provided by this class, providing SQL and any necessary parameters. The RowCallbackHandler interface extracts values from each row of a ResultSet.

Can be used within a service implementation via direct instantiation with a DataSource reference, or get prepared in an application context and given to services as bean reference. Note: The DataSource should always be configured as bean in the application context, in the first case given to the service directly, in the second case to the prepared template.

The motivation and design of this class is discussed in detail in Expert One-On-One J2EE Design and Development by Rod Johnson (Wrox, 2002).

Because this class is parameterizable by the callback interfaces and the SQLExceptionTranslater interface, it isn't necessary to subclass it. All SQL issued by this class is logged.

Since:
May 3, 2001
Version:
$Id: JdbcTemplate.java,v 1.20 2003/06/06 14:34:43 jhoeller Exp $
Author:
Rod Johnson, Juergen Hoeller, Yann Caroff, Thomas Risberg, Isabelle Muszynski
See Also:
com.interface21.dao, JndiObjectFactoryBean

Field Summary
protected  org.apache.commons.logging.Log logger
           
static PreparedStatementSetter PREPARE_STATEMENT
          Constant for use as a parameter to query methods to force use of a PreparedStatement rather than a Statement, even when there are no bind parameters.
 
Constructor Summary
JdbcTemplate()
          Construct a new JdbcTemplate.
JdbcTemplate(javax.sql.DataSource dataSource)
          Construct a new JdbcTemplate, given a DataSource to obtain connections from.
 
Method Summary
 void afterPropertiesSet()
          Invoked by a BeanFactory after it has set all bean properties supplied.
 int[] batchUpdate(java.lang.String sql, BatchPreparedStatementSetter setter)
          Issue multiple updates using JDBC 2.0 batch updates and PreparedStatementSetters to set values on a PreparedStatement created by this method
 void doWithResultSetFromPreparedQuery(PreparedStatementCreator psc, ResultSetExtracter rse)
          Query using a prepared statement.
 void doWithResultSetFromStaticQuery(java.lang.String sql, ResultSetExtracter rse)
          Execute a query given static SQL.
 javax.sql.DataSource getDataSource()
          Return the DataSource used by this template.
protected  SQLExceptionTranslater getExceptionTranslater()
          Return the exception translater for this instance.
 boolean getIgnoreWarnings()
          Return whether or not we ignore SQLWarnings.
 void query(PreparedStatementCreator psc, RowCallbackHandler callbackHandler)
          Query using a prepared statement.
 void query(java.lang.String sql, PreparedStatementSetter pss, RowCallbackHandler callbackHandler)
          Query given SQL to create a prepared statement from SQL and a PreparedStatementSetter implementation that knows how to bind values to the query.
 void query(java.lang.String sql, RowCallbackHandler callbackHandler)
          Execute a query given static SQL.
 void setDataSource(javax.sql.DataSource dataSource)
          Set the J2EE DataSource to obtain connections from.
 void setExceptionTranslater(SQLExceptionTranslater exceptionTranslater)
          Set the exception translater used in this class.
 void setIgnoreWarnings(boolean ignoreWarnings)
          Set whether or not we want to ignore SQLWarnings.
 int update(PreparedStatementCreator psc)
          Issue an update using a PreparedStatementCreator to provide SQL and any required parameters
 int[] update(PreparedStatementCreator[] pscs)
          Issue multiple updates using multiple PreparedStatementCreators to provide SQL and any required parameters
 int update(java.lang.String sql)
          Issue a single SQL update.
 int update(java.lang.String sql, PreparedStatementSetter pss)
          Issue an update using a PreparedStatementSetter to set bind parameters, with given SQL.
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Field Detail

PREPARE_STATEMENT

public static final PreparedStatementSetter PREPARE_STATEMENT
Constant for use as a parameter to query methods to force use of a PreparedStatement rather than a Statement, even when there are no bind parameters. For example, query(sql, JdbcTemplate.PREPARE_STATEMENT, callbackHandler) will force the use of a JDBC PreparedStatement even if the SQL passed in has no bind parameters.

logger

protected final org.apache.commons.logging.Log logger
Constructor Detail

JdbcTemplate

public JdbcTemplate()
Construct a new JdbcTemplate. Note: The DataSource has to be set before using the instance. This constructor can be used to prepare a JdbcTemplate via a BeanFactory, typically setting the DataSource via setDataSourceName.
See Also:
setDataSource(javax.sql.DataSource)

JdbcTemplate

public JdbcTemplate(javax.sql.DataSource dataSource)
             throws InvalidParameterException
Construct a new JdbcTemplate, given a DataSource to obtain connections from.
Parameters:
dataSource - J2EE DataSource to obtain connections from
Throws:
InvalidParameterException - when dataSource is null
Method Detail

setDataSource

public void setDataSource(javax.sql.DataSource dataSource)
                   throws InvalidParameterException
Set the J2EE DataSource to obtain connections from.

getDataSource

public javax.sql.DataSource getDataSource()
Return the DataSource used by this template.

setIgnoreWarnings

public void setIgnoreWarnings(boolean ignoreWarnings)
Set whether or not we want to ignore SQLWarnings. Default is true.

getIgnoreWarnings

public boolean getIgnoreWarnings()
Return whether or not we ignore SQLWarnings. Default is true.

setExceptionTranslater

public void setExceptionTranslater(SQLExceptionTranslater exceptionTranslater)
Set the exception translater used in this class. If no custom translater is provided, a default is used which examines the SQLException's SQLState code.
Parameters:
exceptionTranslater - custom exception translator

getExceptionTranslater

protected SQLExceptionTranslater getExceptionTranslater()
Return the exception translater for this instance. Create a default one for the specified DataSource if none set.

afterPropertiesSet

public void afterPropertiesSet()
Description copied from interface: InitializingBean
Invoked by a BeanFactory after it has set all bean properties supplied.
This method allows the bean instance to perform initialization only possible when all bean properties have been set and to throw an exception in the event of misconfiguration.
Specified by:
afterPropertiesSet in interface InitializingBean
Following copied from interface: com.interface21.beans.factory.InitializingBean
Throws:
java.lang.Exception - in the event of misconfiguration (such as failure to set an essential property) or if initialization fails.

query

public void query(java.lang.String sql,
                  RowCallbackHandler callbackHandler)
           throws DataAccessException
Execute a query given static SQL. Uses a JDBC Statement, not a PreparedStatement. If you want to execute a static query with a PreparedStatement, use the overloaded query method with a the PREPARE_STATEMENT PreparedStatementSetter constant as a parameter.
In most cases the query() method should be preferred to the parallel doWithResultSetXXXX() method. The doWithResultSetXXXX() methods are included to allow full control over the extraction of data from ResultSets and to facilitate integration with third-party software.
Parameters:
sql - SQL query to execute
callbackHandler - object that will extract results
Throws:
DataAccessException - if there is any problem executing the query

doWithResultSetFromStaticQuery

public void doWithResultSetFromStaticQuery(java.lang.String sql,
                                           ResultSetExtracter rse)
                                    throws DataAccessException
Execute a query given static SQL. Uses a JDBC Statement, not a PreparedStatement. If you want to execute a static query with a PreparedStatement, use the overloaded query method with a NOP PreparedStatement setter as a parameter.
Parameters:
sql - SQL query to execute
rse - object that will extract all rows of results
Throws:
DataAccessException - if there is any problem executing the query

query

public void query(PreparedStatementCreator psc,
                  RowCallbackHandler callbackHandler)
           throws DataAccessException
Query using a prepared statement.
Parameters:
psc - Callback handler that can create a PreparedStatement given a Connection
callbackHandler - object that will extract results, one row at a time
Throws:
DataAccessException - if there is any problem

doWithResultSetFromPreparedQuery

public void doWithResultSetFromPreparedQuery(PreparedStatementCreator psc,
                                             ResultSetExtracter rse)
                                      throws DataAccessException
Query using a prepared statement. Most other query methods use this method.
Parameters:
psc - Callback handler that can create a PreparedStatement given a Connection
rse - object that will extract results.
Throws:
DataAccessException - if there is any problem

query

public void query(java.lang.String sql,
                  PreparedStatementSetter pss,
                  RowCallbackHandler callbackHandler)
           throws DataAccessException
Query given SQL to create a prepared statement from SQL and a PreparedStatementSetter implementation that knows how to bind values to the query.
Parameters:
sql - SQL to execute
pss - object that knows how to set values on the prepared statement. If this is null, the SQL will be assumed to contain no bind parameters. Even if there are no bind parameters, this object may be used to set fetch size and other performance options.
callbackHandler - object that will extract results
Throws:
DataAccessException - if the query fails

update

public int update(java.lang.String sql)
           throws DataAccessException
Issue a single SQL update.
Parameters:
sql - static SQL to execute
Returns:
the number of rows affected
Throws:
DataAccessException - if there is any problem.

update

public int update(PreparedStatementCreator psc)
           throws DataAccessException
Issue an update using a PreparedStatementCreator to provide SQL and any required parameters
Parameters:
psc - helper: callback object that provides SQL and any necessary parameters
Returns:
the number of rows affected
Throws:
DataAccessException - if there is any problem issuing the update

update

public int[] update(PreparedStatementCreator[] pscs)
             throws DataAccessException
Issue multiple updates using multiple PreparedStatementCreators to provide SQL and any required parameters
Parameters:
pscs - array of helpers: callback object that provides SQL and any necessary parameters
Returns:
an array of the number of rows affected by each statement
Throws:
DataAccessException - if there is any problem issuing the update

update

public int update(java.lang.String sql,
                  PreparedStatementSetter pss)
           throws DataAccessException
Issue an update using a PreparedStatementSetter to set bind parameters, with given SQL. Simpler than using a PreparedStatementCreator as this method will create the PreparedStatement: the PreparedStatementSetter has only to set parameters.
Parameters:
sql - SQL, containing bind parameters
pss - helper that sets bind parameters. If this is null we run an update with static SQL
Returns:
the number of rows affected
Throws:
DataAccessException - if there is any problem issuing the update TODO add a similar query method

batchUpdate

public int[] batchUpdate(java.lang.String sql,
                         BatchPreparedStatementSetter setter)
                  throws DataAccessException
Issue multiple updates using JDBC 2.0 batch updates and PreparedStatementSetters to set values on a PreparedStatement created by this method
Parameters:
sql - defining PreparedStatement that will be reused. All statements in the batch will use the same SQL.
setter - object to set parameters on the PreparedStatement created by this method
Returns:
an array of the number of rows affected by each statement
Throws:
DataAccessException - if there is any problem issuing the update


Rod Johnson and Spring contributors 2001-2003.