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