Chapter 11. Data access using JDBC
来源:百度文库 编辑:神马文学网 时间:2024/04/26 07:30:59
Chapter 11. Data access using JDBC
11.1. Introduction
The value-add provided by the Spring Framework‘s JDBC abstractionframework is perhaps best shown by the following list (note that only theitalicized lines need to be coded by an application developer):
Define connection parameters
Open the connection
Specify the statement
Prepare and execute the statement
Set up the loop to iterate through the results (if any)
Do the work for each iteration
Process any exception
Handle transactions
Close the connection
The Spring Framework takes care of all the grungy, low-level detailsthat can make JDBC such a tedious API to develop with.
11.1.1. Choosing a style
There are a number of options for selecting an approach to formthe basis for your JDBC database access. There are three flavors of theJdbcTemplate, a new "SimpleJdbc" approach taking advantage of databasemetadata, and there is also the "RDBMS Object" style for a more objectoriented approach similar in style to the JDO Query design. We‘llbriefly list the primary reasons why you would pick one of theseapproaches. Keep in mind that even if you start using one of theseapproaches, you can still mix and match if there is a feature in adifferent approach that you would like to take advantage of. Allapproaches requires a JDBC 2.0 compliant driver and some advancedfeatures require a JDBC 3.0 driver.
JdbcTemplate - this is the classic Spring JDBC approach and the most widely used. This is the "lowest level" approach and all other approaches use a JdbcTemplate under the covers. Works well in a JDK 1.4 and higher environment.
NamedParameterJdbcTemplate - wraps a JdbcTemplate to provide more convenient usage with named parameters instead of the traditional JDBC "?" place holders. This provides better documentation and ease of use when you have multiple parameters for an SQL statement. Works with JDK 1.4 and up.
SimpleJdbcTemplate - this class combines the most frequently used features of both JdbcTemplate and NamedParameterJdbcTemplate plus it adds additional convenience by taking advantage of some Java 5 features like varargs, autoboxing and generics to provide an easier to use API. Requires JDK 5 or higher.
SimpleJdbcInsert and SimpleJdbcCall - designed to take advantage of database metadata to limit the amount of configuration needed. This will simplify the coding to a point where you only need to provide the name of the table or procedure and provide a Map of parameters matching the column names. Designed to work together with the SimpleJdbcTemplate. Requires JDK 5 or higher and a database that provides adequate metadata.
RDBMS Objects including MappingSqlQuery, SqlUpdate and StoredProcedure - an approach where you create reusable and thread safe objects during initialization of your data access layer. This approach is modeled after JDO Query where you define your query string, declare parameters and compile the query. Once that is done any execute methods can be called multiple times with various parameter values passed in. Works with JDK 1.4 and higher.
11.1.2. The package hierarchy
The Spring Framework‘s JDBC abstraction framework consists of fourdifferent packages, namely core,datasource, object, andsupport.
The org.springframework.jdbc.core packagecontains the JdbcTemplate class and its variouscallback interfaces, plus a variety of related classes. A sub-packagenamed org.springframework.jdbc.core.simple containsthe SimpleJdbcTemplate class and the relatedSimpleJdbcInsert andSimpleJdbcCall classes. Another sub-package namedorg.springframework.jdbc.core.namedparam contains theNamedParameterJdbcTemplate class and the relatedsupport classes.
The org.springframework.jdbc.datasource packagecontains a utility class for easyDataSource access, and various simpleDataSource implementations that can beused for testing and running unmodified JDBC code outside of a J2EEcontainer. The utility class provides static methods to obtainconnections from JNDI and to close connections if necessary. It hassupport for thread-bound connections, e.g. for use withDataSourceTransactionManager.
Next, the org.springframework.jdbc.objectpackage contains classes that represent RDBMS queries, updates, andstored procedures as thread safe, reusable objects. This approach ismodeled by JDO, although of course objects returned by queries are“disconnected” from the database. This higher level of JDBCabstraction depends on the lower-level abstraction in theorg.springframework.jdbc.core package.
Finally the org.springframework.jdbc.supportpackage is where you find the SQLExceptiontranslation functionality and some utility classes.
Exceptions thrown during JDBC processing are translated toexceptions defined in the org.springframework.daopackage. This means that code using the Spring JDBC abstraction layerdoes not need to implement JDBC or RDBMS-specific error handling. Alltranslated exceptions are unchecked giving you the option of catchingthe exceptions that you can recover from while allowing other exceptionsto be propagated to the caller.
11.2. Using the JDBC Core classes to control basic JDBC processing anderror handling
11.2.1. JdbcTemplate
The JdbcTemplate class is the central classin the JDBC core package. It simplifies the use of JDBC since it handlesthe creation and release of resources. This helps to avoid common errorssuch as forgetting to always close the connection. It executes the coreJDBC workflow like statement creation and execution, leaving applicationcode to provide SQL and extract results. This class executes SQLqueries, update statements or stored procedure calls, imitatingiteration over ResultSets and extractionof returned parameter values. It also catches JDBC exceptions andtranslates them to the generic, more informative, exception hierarchydefined in the org.springframework.daopackage.
Code using the JdbcTemplate only need toimplement callback interfaces, giving them a clearly defined contract.The PreparedStatementCreator callbackinterface creates a prepared statement given aConnection provided by this class,providing SQL and any necessary parameters. The same is true for theCallableStatementCreator interface whichcreates callable statement. TheRowCallbackHandler interface extractsvalues from each row of aResultSet.
The JdbcTemplate can be used within a DAOimplementation via direct instantiation with aDataSource reference, or be configured ina Spring IOC container and given to DAOs as a bean reference. Note: theDataSource should always be configured asa bean in the Spring IoC container, in the first case given to theservice directly, in the second case to the prepared template.
Finally, all of the SQL issued by this class is logged at the‘DEBUG‘ level under the category corresponding to thefully qualified class name of the template instance (typicallyJdbcTemplate, but it may be different if a customsubclass of the JdbcTemplate class is beingused).
11.2.1.1. Examples
Find below some examples of using theJdbcTemplate class. (These examples are not anexhaustive list of all of the functionality exposed by theJdbcTemplate; see the attendant Javadocs forthat).
11.2.1.1.1. Querying (SELECT)
A simple query for getting the number of rows in arelation.
int rowCount = this.jdbcTemplate.queryForInt("select count(0) from t_accrual");
A simple query using a bind variable.
int countOfActorsNamedJoe = this.jdbcTemplate.queryForInt(
"select count(0) from t_actors where first_name = ?", new Object[]{"Joe"});
Querying for a String.
String surname = (String) this.jdbcTemplate.queryForObject(
"select surname from t_actor where id = ?",
new Object[]{new Long(1212)}, String.class);
Querying and populating a single domainobject.
Actor actor = (Actor) this.jdbcTemplate.queryForObject(
"select first_name, surname from t_actor where id = ?",
new Object[]{new Long(1212)},
new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
Actor actor = new Actor();
actor.setFirstName(rs.getString("first_name"));
actor.setSurname(rs.getString("surname"));
return actor;
}
});
Querying and populating a number of domain objects.
Collection actors = this.jdbcTemplate.query(
"select first_name, surname from t_actor",
new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
Actor actor = new Actor();
actor.setFirstName(rs.getString("first_name"));
actor.setSurname(rs.getString("surname"));
return actor;
}
});
If the last two snippets of code actually existed in the sameapplication, it would make sense to remove the duplication presentin the two RowMapper anonymous innerclasses, and extract them out into a single class (typically astatic inner class) that can then be referencedby DAO methods as needed. For example, the last code snippet mightbe better off written like so:
public Collection findAllActors() {
return this.jdbcTemplate.query( "select first_name, surname from t_actor", new ActorMapper());
}
private static final class ActorMapper implements RowMapper {
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
Actor actor = new Actor();
actor.setFirstName(rs.getString("first_name"));
actor.setSurname(rs.getString("surname"));
return actor;
}
}
11.2.1.1.2. Updating (INSERT/UPDATE/DELETE)
this.jdbcTemplate.update(
"insert into t_actor (first_name, surname) values (?, ?)",
new Object[] {"Leonor", "Watling"});this.jdbcTemplate.update(
"update t_actor set weapon = ? where id = ?",
new Object[] {"Banjo", new Long(5276)});this.jdbcTemplate.update(
"delete from actor where id = ?",
new Object[] {new Long.valueOf(actorId)});
11.2.1.1.3. Other operations
The execute(..) method can be used toexecute any arbitrary SQL, and as such is often used for DDLstatements. It is heavily overloaded with variants taking callbackinterfaces, binding variable arrays, and suchlike.
this.jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");
Invoking a simple stored procedure (more sophisticated storedprocedure support iscoveredlater).
this.jdbcTemplate.update(
"call SUPPORT.REFRESH_ACTORS_SUMMARY(?)",
new Object[]{Long.valueOf(unionId)});
11.2.1.2. JdbcTemplate idioms (bestpractices)
Instances of the JdbcTemplate class arethreadsafe once configured. This is importantbecause it means that you can configure a single instance of aJdbcTemplate and then safely inject thisshared reference into multiple DAOs (orrepositories). To be clear, the JdbcTemplate isstateful, in that it maintains a reference to aDataSource, but this state isnot conversational state.
A common idiom when using theJdbcTemplate class (and the associatedSimpleJdbcTemplateandNamedParameterJdbcTemplateclasses) is to configure a DataSourcein your Spring configuration file, and then dependency inject thatshared DataSource bean into your DAOclasses; the JdbcTemplate is created in thesetter for the DataSource. This leadsto DAOs that look in part like this:
public class JdbcCorporateEventDao implements CorporateEventDao {
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
// JDBC-backed implementations of the methods on the CorporateEventDao follow...
}
The attendant configuration might look like this.
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">
If you are using Spring‘s JdbcDaoSupportclass, and your various JDBC-backed DAO classes extend from it, thenyou inherit a setDataSource(..) method forfree from said superclass. It is totally up to you as to whether ornot you inherit from said class, you certainly are not forced to. Ifyou look at the source for the JdbcDaoSupportclass you will see that there is not a whole lot to it... it isprovided as a convenience only.
Regardless of which of the above template initialization stylesyou choose to use (or not), there is (almost) certainly no need tocreate a brand new instance of a JdbcTemplateclass each and every time you wish to execute some SQL... remember,once configured, a JdbcTemplate instance isthreadsafe. A reason for wanting multipleJdbcTemplate instances would be when you havean application that accesses multiple databases, which requiresmultiple DataSources, and subsequentlymultiple differently configuredJdbcTemplates.
11.2.2. NamedParameterJdbcTemplate
The NamedParameterJdbcTemplate class addssupport for programming JDBC statements using named parameters (asopposed to programming JDBC statements using only classic placeholder(‘?‘) arguments. TheNamedParameterJdbcTemplate class wraps aJdbcTemplate, and delegates to the wrappedJdbcTemplate to do much of its work. This sectionwill describe only those areas of theNamedParameterJdbcTemplate class that differ fromthe JdbcTemplate itself; namely, programming JDBCstatements using named parameters.
// some JDBC-backed DAO class...
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}
public int countOfActorsByFirstName(String firstName) {
String sql = "select count(0) from T_ACTOR where first_name = :first_name";
SqlParameterSource namedParameters = new MapSqlParameterSource("first_name", firstName);
return namedParameterJdbcTemplate.queryForInt(sql, namedParameters);
}
Notice the use of the named parameter notation in the valueassigned to the ‘sql‘ variable, and the correspondingvalue that is plugged into the ‘namedParameters‘variable (of type MapSqlParameterSource).
If you like, you can also pass along named parameters (and theircorresponding values) to aNamedParameterJdbcTemplate instance using the(perhaps more familiar) Map-based style.(The rest of the methods exposed by theNamedParameterJdbcOperations - andimplemented by the NamedParameterJdbcTemplateclass) follow a similar pattern and will not be covered here.)
// some JDBC-backed DAO class...
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}
public int countOfActorsByFirstName(String firstName) {
String sql = "select count(0) from T_ACTOR where first_name = :first_name";
Map namedParameters = Collections.singletonMap("first_name", firstName);
return this.namedParameterJdbcTemplate.queryForInt(sql, namedParameters);
}
Another nice feature related to theNamedParameterJdbcTemplate (and existing in thesame Java package) is theSqlParameterSource interface. You havealready seen an example of an implementation of this interface in one ofthe preceding code snippets (theMapSqlParameterSource class). The entire point ofthe SqlParameterSource is to serve as asource of named parameter values to aNamedParameterJdbcTemplate. TheMapSqlParameterSource class is a very simpleimplementation, that is simply an adapter around ajava.util.Map, where the keys are theparameter names and the values are the parameter values.
Another SqlParameterSourceimplementation is theBeanPropertySqlParameterSource class. This classwraps an arbitrary JavaBean (that is, an instance of a class thatadheres tothe JavaBeanconventions), and uses the properties of the wrapped JavaBean asthe source of named parameter values.
public class Actor {
private Long id;
private String firstName;
private String lastName;
public String getFirstName() {
return this.firstName;
}
public String getLastName() {
return this.lastName;
}
public Long getId() {
return this.id;
}
// setters omitted...
}// some JDBC-backed DAO class...
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}
public int countOfActors(Actor exampleActor) {
// notice how the named parameters match the properties of the above ‘Actor‘ class
String sql = "select count(0) from T_ACTOR where first_name = :firstName and last_name = :lastName";
SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(exampleActor);
return this.namedParameterJdbcTemplate.queryForInt(sql, namedParameters);
}
Remember that theNamedParameterJdbcTemplate classwraps a classic JdbcTemplatetemplate; if you need access to the wrappedJdbcTemplate instance (to access some of thefunctionality only present in the JdbcTemplateclass), then you can use thegetJdbcOperations() method to access thewrapped JdbcTemplate via theJdbcOperationsinterface.
See also the section entitledSection 11.2.1.2, “JdbcTemplate idioms (bestpractices)” for some advice on how to best usethe NamedParameterJdbcTemplate class in thecontext of an application.
11.2.3. SimpleJdbcTemplate
Note
The functionality offered by the SimpleJdbcTemplate is only available to you if you are using Java 5 or later.
The SimpleJdbcTemplate class is a wrapperaround the classic JdbcTemplate that takesadvantage of Java 5 language features such as varargs and autoboxing.The SimpleJdbcTemplate class is somewhat of a sopto the syntactic-sugar-like features of Java 5, but as anyone who hasdeveloped on Java 5 and then had to move back to developing on aprevious version of the JDK will know, those syntactic-sugar-likefeatures sure are nice.
The value-add of the SimpleJdbcTemplateclass in the area of syntactic-sugar is best illustrated with a‘before and after‘ example. The following codesnippet shows first some data access code using the classicJdbcTemplate, followed immediately thereafter bya code snippet that does the same job, only this time using theSimpleJdbcTemplate.
// classic JdbcTemplate-style...
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public Actor findActor(long id) {
String sql = "select id, first_name, last_name from T_ACTOR where id = ?";
RowMapper mapper = new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
Actor actor = new Actor();
actor.setId(rs.getLong("id"));
actor.setFirstName(rs.getString("first_name"));
actor.setLastName(rs.getString("last_name"));
return actor;
}
};
// notice the cast, the wrapping up of the ‘id‘ argument
// in an array, and the boxing of the ‘id‘ argument as a reference type
return (Actor) jdbcTemplate.queryForObject(sql, mapper, new Object[] {Long.valueOf(id)});
}
Here is the same method, only this time using theSimpleJdbcTemplate; notice how much ‘cleaner‘ thecode is.
// SimpleJdbcTemplate-style...
private SimpleJdbcTemplate simpleJdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
}
public Actor findActor(long id) {
String sql = "select id, first_name, last_name from T_ACTOR where id = ?";
ParameterizedRowMapper mapper = new ParameterizedRowMapper() {
// notice the return type with respect to Java 5 covariant return types
public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
Actor actor = new Actor();
actor.setId(rs.getLong("id"));
actor.setFirstName(rs.getString("first_name"));
actor.setLastName(rs.getString("last_name"));
return actor;
}
};
return this.simpleJdbcTemplate.queryForObject(sql, mapper, id);
}
See also the section entitledSection 11.2.1.2, “JdbcTemplate idioms (bestpractices)” for some advice on how to best usethe SimpleJdbcTemplate class in the context of anapplication.
Note
The SimpleJdbcTemplate class only offers a subset of the methods exposed on the JdbcTemplate class. If you need to use a method from the JdbcTemplate that is not defined on the SimpleJdbcTemplate, you can always access the underlying JdbcTemplate by calling the getJdbcOperations() method on the SimpleJdbcTemplate, which will then allow you to invoke the method that you want. The only downside is that the methods on the JdbcOperations interface are not generified, so you are back to casting and such again.
11.2.4. DataSource
In order to work with data from a database, one needs to obtain aconnection to the database. The way Spring does this is through aDataSource. ADataSource is part of the JDBCspecification and can be seen as a generalized connection factory. Itallows a container or a framework to hide connection pooling andtransaction management issues from the application code. As a developer,you don not need to know any details about how to connect to thedatabase, that is the responsibility for the administrator that sets upthe datasource. You will most likely have to fulfill both roles whileyou are developing and testing you code though, but you will notnecessarily have to know how the production data source isconfigured.
When using Spring‘s JDBC layer, you can either obtain a datasource from JNDI or you can configure your own, using an implementationthat is provided in the Spring distribution. The latter comes in handyfor unit testing outside of a web container. We will use theDriverManagerDataSource implementation for thissection but there are several additional implementations that will becovered later on. The DriverManagerDataSourceworks the same way that you probably are used to work when you obtain aJDBC connection. You have to specify the fully qualified class name ofthe JDBC driver that you are using so that theDriverManager can load the driver class. Then youhave to provide a URL that varies between JDBC drivers. You have toconsult the documentation for your driver for the correct value to usehere. Finally you must provide a username and a password that will beused to connect to the database. Here is an example of how to configurea DriverManagerDataSource:
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("org.hsqldb.jdbcDriver");
dataSource.setUrl("jdbc:hsqldb:hsql://localhost:");
dataSource.setUsername("sa");
dataSource.setPassword("");
11.2.5. SQLExceptionTranslator
SQLExceptionTranslator is aninterface to be implemented by classes that can translate betweenSQLExceptions and Spring‘s owndata-access-strategy-agnosticorg.springframework.dao.DataAccessException.Implementations can be generic (for example, using SQLState codes forJDBC) or proprietary (for example, using Oracle error codes) for greaterprecision.
SQLErrorCodeSQLExceptionTranslator is theimplementation of SQLExceptionTranslatorthat is used by default. This implementation uses specific vendor codes.More precise than SQLState implementation, but vendorspecific. The error code translations are based on codes held in aJavaBean type class named SQLErrorCodes. Thisclass is created and populated by anSQLErrorCodesFactory which as the name suggestsis a factory for creating SQLErrorCodes based onthe contents of a configuration file named ‘sql-error-codes.xml‘. This file ispopulated with vendor codes and based on the DatabaseProductName takenfrom the DatabaseMetaData, the codes forthe current database are used.
The SQLErrorCodeSQLExceptionTranslatorapplies the following matching rules:
Try custom translation implemented by any subclass. Note that this class is concrete and is typically used itself, in which case this rule does not apply.
Apply error code matching. Error codes are obtained from the SQLErrorCodesFactory by default. This looks up error codes from the classpath and keys into them from the database name from the database metadata.
Use the fallback translator. SQLStateSQLExceptionTranslator is the default fallback translator.
SQLErrorCodeSQLExceptionTranslator can beextended the following way:
public class MySQLErrorCodesTranslator extends SQLErrorCodeSQLExceptionTranslator {
protected DataAccessException customTranslate(String task, String sql, SQLException sqlex) {
if (sqlex.getErrorCode() == -12345) {
return new DeadlockLoserDataAccessException(task, sqlex);
}
return null;
}
}
In this example the specific error code‘-12345‘ is translated and any other errors aresimply left to be translated by the default translator implementation.To use this custom translator, it is necessary to pass it to theJdbcTemplate using the methodsetExceptionTranslator and to use thisJdbcTemplate for all of the data accessprocessing where this translator is needed. Here is an example of howthis custom translator can be used:
// create a JdbcTemplate and set data source
JdbcTemplate jt = new JdbcTemplate();
jt.setDataSource(dataSource);
// create a custom translator and set the DataSource for the default translation lookup
MySQLErrorCodesTransalator tr = new MySQLErrorCodesTransalator();
tr.setDataSource(dataSource);
jt.setExceptionTranslator(tr);
// use the JdbcTemplate for this SqlUpdate
SqlUpdate su = new SqlUpdate();
su.setJdbcTemplate(jt);
su.setSql("update orders set shipping_charge = shipping_charge * 1.05");
su.compile();
su.update();
The custom translator is passed a data source because we stillwant the default translation to look up the error codes insql-error-codes.xml.
11.2.6. Executing statements
To execute an SQL statement, there is very little code needed. Allyou need is a DataSource and aJdbcTemplate. Once you have that, you can use anumber of convenience methods that are provided with theJdbcTemplate. Here is a short example showingwhat you need to include for a minimal but fully functional class thatcreates a new table.
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
public class ExecuteAStatement {
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public void doExecute() {
this.jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");
}
}
11.2.7. Running Queries
In addition to the execute methods, there is a large number ofquery methods. Some of these methods are intended to be used for queriesthat return a single value. Maybe you want to retrieve a count or aspecific value from one row. If that is the case then you can usequeryForInt(..),queryForLong(..) orqueryForObject(..). The latter will convert thereturned JDBC Type to the Java class that ispassed in as an argument. If the type conversion is invalid, then anInvalidDataAccessApiUsageException willbe thrown. Here is an example that contains two query methods, one foran int and one that queries for aString.
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
public class RunAQuery {
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public int getCount() {
return this.jdbcTemplate.queryForInt("select count(*) from mytable");
}
public String getName() {
return (String) this.jdbcTemplate.queryForObject("select name from mytable", String.class);
}
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
}
In addition to the single results query methods there are severalmethods that return a List with an entry for each row that the queryreturned. The most generic method isqueryForList(..) which returns aList where each entry is aMap with each entry in the maprepresenting the column value for that row. If we add a method to theabove example to retrieve a list of all the rows, it would look likethis:
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public List getList() {
return this.jdbcTemplate.queryForList("select * from mytable");
}
The list returned would look something like this:
[{name=Bob, id=1}, {name=Mary, id=2}]
11.2.8. Updating the database
There are also a number of update methods that you can use. Findbelow an example where a column is updated for a certain primary key. Inthis example an SQL statement is used that has place holders for rowparameters. Note that the parameter values are passed in as an array ofobjects (and thus primitives have to be wrapped in the primitive wrapperclasses).
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
public class ExecuteAnUpdate {
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public void setName(int id, String name) {
this.jdbcTemplate.update(
"update mytable set name = ? where id = ?",
new Object[] {name, new Integer(id)});
}
}
11.2.9. Retrieving auto-generated keys
One of the update convenience methodsprovides support for acquiring the primary keys generated by thedatabase (part of the JDBC 3.0 standard - see chapter 13.6 of thespecification for details). The method takes aPreparedStatementCreator as its first argument,and this is the way the required insert statement is specified. Theother argument is a KeyHolder, which will containthe generated key on successful return from the update. There is not astandard single way to create an appropriatePreparedStatement (which explains why the methodsignature is the way it is). An example that works on Oracle and may notwork on other platforms is:
final String INSERT_SQL = "insert into my_test (name) values(?)";
final String name = "Rob";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement ps =
connection.prepareStatement(INSERT_SQL, new String[] {"id"});
ps.setString(1, name);
return ps;
}
},
keyHolder);
// keyHolder.getKey() now contains the generated key
11.3. Controlling database connections
11.3.1. DataSourceUtils
The DataSourceUtils class is a convenientand powerful helper class that provides staticmethods to obtain connections from JNDI and close connections ifnecessary. It has support for thread-bound connections, for example foruse with DataSourceTransactionManager.
11.3.2. SmartDataSource
The SmartDataSource interface is tobe implemented by classes that can provide a connection to a relationaldatabase. Extends the DataSourceinterface to allow classes using it to query whether or not theconnection should be closed after a given operation. This can sometimesbe useful for efficiency, in the cases where one knows that one wants toreuse a connection.
11.3.3. AbstractDataSource
This is an abstract base class for Spring‘sDataSource implementations, that takescare of the "uninteresting" glue. This is the class one would extend ifone was writing one‘s own DataSourceimplementation.
11.3.4. SingleConnectionDataSource
The SingleConnectionDataSource class is animplementation of the SmartDataSourceinterface that wraps a singleConnection that isnot closed after use. Obviously, this is notmulti-threading capable.
If client code will call close in the assumption of a pooledconnection, like when using persistence tools, setsuppressClose to true. This willreturn a close-suppressing proxy instead of the physical connection. Beaware that you will not be able to cast this to a native OracleConnection or the like anymore.
This is primarily a test class. For example, it enables easytesting of code outside an application server, in conjunction with asimple JNDI environment. In contrast toDriverManagerDataSource, it reuses the sameconnection all the time, avoiding excessive creation of physicalconnections.
11.3.5. DriverManagerDataSource
The DriverManagerDataSource class is animplementation of the standard DataSourceinterface that configures a plain old JDBC Driver via bean properties, andreturns a new Connection every time.
This is potentially useful for test or standalone environmentsoutside of a J2EE container, either as aDataSource bean in a Spring IoCcontainer, or in conjunction with a simple JNDI environment.Pool-assuming Connection.close() calls will simplyclose the connection, so anyDataSource-aware persistence code shouldwork. However, using JavaBean style connection pools such ascommons-dbcp is so easy, even in a test environment, that it is almostalways preferable to use such a connection pool overDriverManagerDataSource.
11.3.6. TransactionAwareDataSourceProxy
TransactionAwareDataSourceProxy is a proxyfor a target DataSource, which wraps thattarget DataSource to add awareness ofSpring-managed transactions. In this respect it is similar to atransactional JNDI DataSource as providedby a J2EE server.
Note
It should almost never be necessary or desirable to use this class, except when existing code exists which must be called and passed a standard JDBC DataSource interface implementation. In this case, it‘s possible to still have this code be usable, but participating in Spring managed transactions. It is generally preferable to write your own new code using the higher level abstractions for resource management, such as JdbcTemplate or DataSourceUtils.
(See theTransactionAwareDataSourceProxy Javadocs for moredetails.)
11.3.7. DataSourceTransactionManager
The DataSourceTransactionManager class is aPlatformTransactionManager implementationfor single JDBC datasources. It binds a JDBC connection from thespecified data source to the currently executing thread, potentiallyallowing for one thread connection per data source.
Application code is required to retrieve the JDBC connection viaDataSourceUtils.getConnection(DataSource) instead ofJ2EE‘s standard DataSource.getConnection. This isrecommended anyway, as it throws uncheckedorg.springframework.dao exceptions instead of checkedSQLExceptions. All framework classes likeJdbcTemplate use this strategy implicitly. If notused with this transaction manager, the lookup strategy behaves exactlylike the common one - it can thus be used in any case.
The DataSourceTransactionManager classsupports custom isolation levels, and timeouts that get applied asappropriate JDBC statement query timeouts. To support the latter,application code must either use JdbcTemplate orcall DataSourceUtils.applyTransactionTimeout(..)method for each created statement.
This implementation can be used instead ofJtaTransactionManager in the single resourcecase, as it does not require the container to support JTA. Switchingbetween both is just a matter of configuration, if you stick to therequired connection lookup pattern. Note that JTA does not supportcustom isolation levels!
11.3.8. NativeJdbcExtractor
There are times when we need to access vendor specific JDBCmethods that differ from the standard JDBC API. This can be problematicif we are running in an application server or with aDataSource that wraps theConnection, Statement andResultSet objects with its own wrapper objects.To gain access to the native objects you can configure yourJdbcTemplate orOracleLobHandler with aNativeJdbcExtractor.
The NativeJdbcExtractor comes in a variety of flavors to matchyour execution environment:
SimpleNativeJdbcExtractor
C3P0NativeJdbcExtractor
CommonsDbcpNativeJdbcExtractor
JBossNativeJdbcExtractor
WebLogicNativeJdbcExtractor
WebSphereNativeJdbcExtractor
XAPoolNativeJdbcExtractor
Usually the SimpleNativeJdbcExtractor issufficient for unwrapping a Connection object inmost environments. See the Java Docs for more details.
11.4. JDBC batch operations
Most JDBC drivers provide improved performance if you batch multiplecalls to the same prepared statement. By grouping updates into batches youlimit the number of round trips to the database. This section will coverbatch processing using both the JdbcTemplate and theSimpleJdbcTemplate.
11.4.1. Batch operations with the JdbcTemplate
Using the JdbcTemplate batch processing is accomplished byimplementing a special interface,BatchPreparedStatementSetter, and passing that inas the second parameter in your batchUpdatemethod call. This interface has two methods you must implement. One isnamed getBatchSize and here you provide the sizeof the current batch. The other method issetValues and it allows you to set the values forthe parameters of the prepared statement and. This method will getcalled the number of times that you specified in thegetBatchSize call. Here is an example of thiswhere we update the actor table based on entries in a list. The entirelist is used as the batch in his example.
public class JdbcActorDao implements ActorDao {
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public int[] batchUpdate(final List actors) {
int[] updateCounts = jdbcTemplate.batchUpdate(
"update t_actor set first_name = ?, last_name = ? where id = ?",
new BatchPreparedStatementSetter() {
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setString(1, ((Actor)actors.get(i)).getFirstName());
ps.setString(2, ((Actor)actors.get(i)).getLastName());
ps.setLong(3, ((Actor)actors.get(i)).getId().longValue());
}
public int getBatchSize() {
return actors.size();
}
} );
return updateCounts;
}
// ... additional methods
}
If you are processing stream of updates or reading from afile then you might have a preferred batch size, but the last batchmight not have that number of entries. In this case you can use theInterruptibleBatchPreparedStatementSetterinterface which allows you to interrupt a batch once the input source isexhausted. The isBatchExhausted method allows youto signal the end of the batch.
11.4.2. Batch operations with the SimpleJdbcTemplate
The SimpleJdbcTemplate provides analternate way of providing the batch update. Instead of implementing aspecial batch interface, you simply provide all parameter values in thecall and the framework will loop over these values and use an internalprepared statement setter. The API varies depending on whether you usenamed parameters or not. For the named parameters you provide an arrayof SqlParameterSource, one entry for each memberof the batch. You can use theSqlParameterSource.createBatch method to createthis array, passing in either an array of JavaBeans or an array of Mapscontaining the parameter values.
This example shows a batch update using named parameters:
public class JdbcActorDao implements ActorDao {
private SimpleJdbcTemplate simpleJdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
}
public int[] batchUpdate(final List actors) {
SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(actors.toArray());
int[] updateCounts = simpleJdbcTemplate.batchUpdate(
"update t_actor set first_name = :firstName, last_name = :lastName where id = :id",
batch);
return updateCounts;
}
// ... additional methods
}
For an SQL statement using the classic "?" place holders youpass in a List containing an object array with the update values. Thisobject array must have one entry for each placeholder in the SQLstatement and they must be in the same order as they are defined in theSQL statement.
The same example using classic JDBC "?" place holders:
public class JdbcActorDao implements ActorDao {
private SimpleJdbcTemplate simpleJdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
}
public int[] batchUpdate(final List actors) {
List
11.1. Introduction
The value-add provided by the Spring Framework‘s JDBC abstractionframework is perhaps best shown by the following list (note that only theitalicized lines need to be coded by an application developer):
Define connection parameters
Open the connection
Specify the statement
Prepare and execute the statement
Set up the loop to iterate through the results (if any)
Do the work for each iteration
Process any exception
Handle transactions
Close the connection
The Spring Framework takes care of all the grungy, low-level detailsthat can make JDBC such a tedious API to develop with.
11.1.1. Choosing a style
There are a number of options for selecting an approach to formthe basis for your JDBC database access. There are three flavors of theJdbcTemplate, a new "SimpleJdbc" approach taking advantage of databasemetadata, and there is also the "RDBMS Object" style for a more objectoriented approach similar in style to the JDO Query design. We‘llbriefly list the primary reasons why you would pick one of theseapproaches. Keep in mind that even if you start using one of theseapproaches, you can still mix and match if there is a feature in adifferent approach that you would like to take advantage of. Allapproaches requires a JDBC 2.0 compliant driver and some advancedfeatures require a JDBC 3.0 driver.
JdbcTemplate - this is the classic Spring JDBC approach and the most widely used. This is the "lowest level" approach and all other approaches use a JdbcTemplate under the covers. Works well in a JDK 1.4 and higher environment.
NamedParameterJdbcTemplate - wraps a JdbcTemplate to provide more convenient usage with named parameters instead of the traditional JDBC "?" place holders. This provides better documentation and ease of use when you have multiple parameters for an SQL statement. Works with JDK 1.4 and up.
SimpleJdbcTemplate - this class combines the most frequently used features of both JdbcTemplate and NamedParameterJdbcTemplate plus it adds additional convenience by taking advantage of some Java 5 features like varargs, autoboxing and generics to provide an easier to use API. Requires JDK 5 or higher.
SimpleJdbcInsert and SimpleJdbcCall - designed to take advantage of database metadata to limit the amount of configuration needed. This will simplify the coding to a point where you only need to provide the name of the table or procedure and provide a Map of parameters matching the column names. Designed to work together with the SimpleJdbcTemplate. Requires JDK 5 or higher and a database that provides adequate metadata.
RDBMS Objects including MappingSqlQuery, SqlUpdate and StoredProcedure - an approach where you create reusable and thread safe objects during initialization of your data access layer. This approach is modeled after JDO Query where you define your query string, declare parameters and compile the query. Once that is done any execute methods can be called multiple times with various parameter values passed in. Works with JDK 1.4 and higher.
11.1.2. The package hierarchy
The Spring Framework‘s JDBC abstraction framework consists of fourdifferent packages, namely core,datasource, object, andsupport.
The org.springframework.jdbc.core packagecontains the JdbcTemplate class and its variouscallback interfaces, plus a variety of related classes. A sub-packagenamed org.springframework.jdbc.core.simple containsthe SimpleJdbcTemplate class and the relatedSimpleJdbcInsert andSimpleJdbcCall classes. Another sub-package namedorg.springframework.jdbc.core.namedparam contains theNamedParameterJdbcTemplate class and the relatedsupport classes.
The org.springframework.jdbc.datasource packagecontains a utility class for easyDataSource access, and various simpleDataSource implementations that can beused for testing and running unmodified JDBC code outside of a J2EEcontainer. The utility class provides static methods to obtainconnections from JNDI and to close connections if necessary. It hassupport for thread-bound connections, e.g. for use withDataSourceTransactionManager.
Next, the org.springframework.jdbc.objectpackage contains classes that represent RDBMS queries, updates, andstored procedures as thread safe, reusable objects. This approach ismodeled by JDO, although of course objects returned by queries are“disconnected” from the database. This higher level of JDBCabstraction depends on the lower-level abstraction in theorg.springframework.jdbc.core package.
Finally the org.springframework.jdbc.supportpackage is where you find the SQLExceptiontranslation functionality and some utility classes.
Exceptions thrown during JDBC processing are translated toexceptions defined in the org.springframework.daopackage. This means that code using the Spring JDBC abstraction layerdoes not need to implement JDBC or RDBMS-specific error handling. Alltranslated exceptions are unchecked giving you the option of catchingthe exceptions that you can recover from while allowing other exceptionsto be propagated to the caller.
11.2. Using the JDBC Core classes to control basic JDBC processing anderror handling
11.2.1. JdbcTemplate
The JdbcTemplate class is the central classin the JDBC core package. It simplifies the use of JDBC since it handlesthe creation and release of resources. This helps to avoid common errorssuch as forgetting to always close the connection. It executes the coreJDBC workflow like statement creation and execution, leaving applicationcode to provide SQL and extract results. This class executes SQLqueries, update statements or stored procedure calls, imitatingiteration over ResultSets and extractionof returned parameter values. It also catches JDBC exceptions andtranslates them to the generic, more informative, exception hierarchydefined in the org.springframework.daopackage.
Code using the JdbcTemplate only need toimplement callback interfaces, giving them a clearly defined contract.The PreparedStatementCreator callbackinterface creates a prepared statement given aConnection provided by this class,providing SQL and any necessary parameters. The same is true for theCallableStatementCreator interface whichcreates callable statement. TheRowCallbackHandler interface extractsvalues from each row of aResultSet.
The JdbcTemplate can be used within a DAOimplementation via direct instantiation with aDataSource reference, or be configured ina Spring IOC container and given to DAOs as a bean reference. Note: theDataSource should always be configured asa bean in the Spring IoC container, in the first case given to theservice directly, in the second case to the prepared template.
Finally, all of the SQL issued by this class is logged at the‘DEBUG‘ level under the category corresponding to thefully qualified class name of the template instance (typicallyJdbcTemplate, but it may be different if a customsubclass of the JdbcTemplate class is beingused).
11.2.1.1. Examples
Find below some examples of using theJdbcTemplate class. (These examples are not anexhaustive list of all of the functionality exposed by theJdbcTemplate; see the attendant Javadocs forthat).
11.2.1.1.1. Querying (SELECT)
A simple query for getting the number of rows in arelation.
int rowCount = this.jdbcTemplate.queryForInt("select count(0) from t_accrual");
A simple query using a bind variable.
int countOfActorsNamedJoe = this.jdbcTemplate.queryForInt(
"select count(0) from t_actors where first_name = ?", new Object[]{"Joe"});
Querying for a String.
String surname = (String) this.jdbcTemplate.queryForObject(
"select surname from t_actor where id = ?",
new Object[]{new Long(1212)}, String.class);
Querying and populating a single domainobject.
Actor actor = (Actor) this.jdbcTemplate.queryForObject(
"select first_name, surname from t_actor where id = ?",
new Object[]{new Long(1212)},
new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
Actor actor = new Actor();
actor.setFirstName(rs.getString("first_name"));
actor.setSurname(rs.getString("surname"));
return actor;
}
});
Querying and populating a number of domain objects.
Collection actors = this.jdbcTemplate.query(
"select first_name, surname from t_actor",
new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
Actor actor = new Actor();
actor.setFirstName(rs.getString("first_name"));
actor.setSurname(rs.getString("surname"));
return actor;
}
});
If the last two snippets of code actually existed in the sameapplication, it would make sense to remove the duplication presentin the two RowMapper anonymous innerclasses, and extract them out into a single class (typically astatic inner class) that can then be referencedby DAO methods as needed. For example, the last code snippet mightbe better off written like so:
public Collection findAllActors() {
return this.jdbcTemplate.query( "select first_name, surname from t_actor", new ActorMapper());
}
private static final class ActorMapper implements RowMapper {
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
Actor actor = new Actor();
actor.setFirstName(rs.getString("first_name"));
actor.setSurname(rs.getString("surname"));
return actor;
}
}
11.2.1.1.2. Updating (INSERT/UPDATE/DELETE)
this.jdbcTemplate.update(
"insert into t_actor (first_name, surname) values (?, ?)",
new Object[] {"Leonor", "Watling"});this.jdbcTemplate.update(
"update t_actor set weapon = ? where id = ?",
new Object[] {"Banjo", new Long(5276)});this.jdbcTemplate.update(
"delete from actor where id = ?",
new Object[] {new Long.valueOf(actorId)});
11.2.1.1.3. Other operations
The execute(..) method can be used toexecute any arbitrary SQL, and as such is often used for DDLstatements. It is heavily overloaded with variants taking callbackinterfaces, binding variable arrays, and suchlike.
this.jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");
Invoking a simple stored procedure (more sophisticated storedprocedure support iscoveredlater).
this.jdbcTemplate.update(
"call SUPPORT.REFRESH_ACTORS_SUMMARY(?)",
new Object[]{Long.valueOf(unionId)});
11.2.1.2. JdbcTemplate idioms (bestpractices)
Instances of the JdbcTemplate class arethreadsafe once configured. This is importantbecause it means that you can configure a single instance of aJdbcTemplate and then safely inject thisshared reference into multiple DAOs (orrepositories). To be clear, the JdbcTemplate isstateful, in that it maintains a reference to aDataSource, but this state isnot conversational state.
A common idiom when using theJdbcTemplate class (and the associatedSimpleJdbcTemplateandNamedParameterJdbcTemplateclasses) is to configure a DataSourcein your Spring configuration file, and then dependency inject thatshared DataSource bean into your DAOclasses; the JdbcTemplate is created in thesetter for the DataSource. This leadsto DAOs that look in part like this:
public class JdbcCorporateEventDao implements CorporateEventDao {
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
// JDBC-backed implementations of the methods on the CorporateEventDao follow...
}
The attendant configuration might look like this.
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">
If you are using Spring‘s JdbcDaoSupportclass, and your various JDBC-backed DAO classes extend from it, thenyou inherit a setDataSource(..) method forfree from said superclass. It is totally up to you as to whether ornot you inherit from said class, you certainly are not forced to. Ifyou look at the source for the JdbcDaoSupportclass you will see that there is not a whole lot to it... it isprovided as a convenience only.
Regardless of which of the above template initialization stylesyou choose to use (or not), there is (almost) certainly no need tocreate a brand new instance of a JdbcTemplateclass each and every time you wish to execute some SQL... remember,once configured, a JdbcTemplate instance isthreadsafe. A reason for wanting multipleJdbcTemplate instances would be when you havean application that accesses multiple databases, which requiresmultiple DataSources, and subsequentlymultiple differently configuredJdbcTemplates.
11.2.2. NamedParameterJdbcTemplate
The NamedParameterJdbcTemplate class addssupport for programming JDBC statements using named parameters (asopposed to programming JDBC statements using only classic placeholder(‘?‘) arguments. TheNamedParameterJdbcTemplate class wraps aJdbcTemplate, and delegates to the wrappedJdbcTemplate to do much of its work. This sectionwill describe only those areas of theNamedParameterJdbcTemplate class that differ fromthe JdbcTemplate itself; namely, programming JDBCstatements using named parameters.
// some JDBC-backed DAO class...
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}
public int countOfActorsByFirstName(String firstName) {
String sql = "select count(0) from T_ACTOR where first_name = :first_name";
SqlParameterSource namedParameters = new MapSqlParameterSource("first_name", firstName);
return namedParameterJdbcTemplate.queryForInt(sql, namedParameters);
}
Notice the use of the named parameter notation in the valueassigned to the ‘sql‘ variable, and the correspondingvalue that is plugged into the ‘namedParameters‘variable (of type MapSqlParameterSource).
If you like, you can also pass along named parameters (and theircorresponding values) to aNamedParameterJdbcTemplate instance using the(perhaps more familiar) Map-based style.(The rest of the methods exposed by theNamedParameterJdbcOperations - andimplemented by the NamedParameterJdbcTemplateclass) follow a similar pattern and will not be covered here.)
// some JDBC-backed DAO class...
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}
public int countOfActorsByFirstName(String firstName) {
String sql = "select count(0) from T_ACTOR where first_name = :first_name";
Map namedParameters = Collections.singletonMap("first_name", firstName);
return this.namedParameterJdbcTemplate.queryForInt(sql, namedParameters);
}
Another nice feature related to theNamedParameterJdbcTemplate (and existing in thesame Java package) is theSqlParameterSource interface. You havealready seen an example of an implementation of this interface in one ofthe preceding code snippets (theMapSqlParameterSource class). The entire point ofthe SqlParameterSource is to serve as asource of named parameter values to aNamedParameterJdbcTemplate. TheMapSqlParameterSource class is a very simpleimplementation, that is simply an adapter around ajava.util.Map, where the keys are theparameter names and the values are the parameter values.
Another SqlParameterSourceimplementation is theBeanPropertySqlParameterSource class. This classwraps an arbitrary JavaBean (that is, an instance of a class thatadheres tothe JavaBeanconventions), and uses the properties of the wrapped JavaBean asthe source of named parameter values.
public class Actor {
private Long id;
private String firstName;
private String lastName;
public String getFirstName() {
return this.firstName;
}
public String getLastName() {
return this.lastName;
}
public Long getId() {
return this.id;
}
// setters omitted...
}// some JDBC-backed DAO class...
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}
public int countOfActors(Actor exampleActor) {
// notice how the named parameters match the properties of the above ‘Actor‘ class
String sql = "select count(0) from T_ACTOR where first_name = :firstName and last_name = :lastName";
SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(exampleActor);
return this.namedParameterJdbcTemplate.queryForInt(sql, namedParameters);
}
Remember that theNamedParameterJdbcTemplate classwraps a classic JdbcTemplatetemplate; if you need access to the wrappedJdbcTemplate instance (to access some of thefunctionality only present in the JdbcTemplateclass), then you can use thegetJdbcOperations() method to access thewrapped JdbcTemplate via theJdbcOperationsinterface.
See also the section entitledSection 11.2.1.2, “JdbcTemplate idioms (bestpractices)” for some advice on how to best usethe NamedParameterJdbcTemplate class in thecontext of an application.
11.2.3. SimpleJdbcTemplate
Note
The functionality offered by the SimpleJdbcTemplate is only available to you if you are using Java 5 or later.
The SimpleJdbcTemplate class is a wrapperaround the classic JdbcTemplate that takesadvantage of Java 5 language features such as varargs and autoboxing.The SimpleJdbcTemplate class is somewhat of a sopto the syntactic-sugar-like features of Java 5, but as anyone who hasdeveloped on Java 5 and then had to move back to developing on aprevious version of the JDK will know, those syntactic-sugar-likefeatures sure are nice.
The value-add of the SimpleJdbcTemplateclass in the area of syntactic-sugar is best illustrated with a‘before and after‘ example. The following codesnippet shows first some data access code using the classicJdbcTemplate, followed immediately thereafter bya code snippet that does the same job, only this time using theSimpleJdbcTemplate.
// classic JdbcTemplate-style...
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public Actor findActor(long id) {
String sql = "select id, first_name, last_name from T_ACTOR where id = ?";
RowMapper mapper = new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
Actor actor = new Actor();
actor.setId(rs.getLong("id"));
actor.setFirstName(rs.getString("first_name"));
actor.setLastName(rs.getString("last_name"));
return actor;
}
};
// notice the cast, the wrapping up of the ‘id‘ argument
// in an array, and the boxing of the ‘id‘ argument as a reference type
return (Actor) jdbcTemplate.queryForObject(sql, mapper, new Object[] {Long.valueOf(id)});
}
Here is the same method, only this time using theSimpleJdbcTemplate; notice how much ‘cleaner‘ thecode is.
// SimpleJdbcTemplate-style...
private SimpleJdbcTemplate simpleJdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
}
public Actor findActor(long id) {
String sql = "select id, first_name, last_name from T_ACTOR where id = ?";
ParameterizedRowMapper
// notice the return type with respect to Java 5 covariant return types
public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
Actor actor = new Actor();
actor.setId(rs.getLong("id"));
actor.setFirstName(rs.getString("first_name"));
actor.setLastName(rs.getString("last_name"));
return actor;
}
};
return this.simpleJdbcTemplate.queryForObject(sql, mapper, id);
}
See also the section entitledSection 11.2.1.2, “JdbcTemplate idioms (bestpractices)” for some advice on how to best usethe SimpleJdbcTemplate class in the context of anapplication.
Note
The SimpleJdbcTemplate class only offers a subset of the methods exposed on the JdbcTemplate class. If you need to use a method from the JdbcTemplate that is not defined on the SimpleJdbcTemplate, you can always access the underlying JdbcTemplate by calling the getJdbcOperations() method on the SimpleJdbcTemplate, which will then allow you to invoke the method that you want. The only downside is that the methods on the JdbcOperations interface are not generified, so you are back to casting and such again.
11.2.4. DataSource
In order to work with data from a database, one needs to obtain aconnection to the database. The way Spring does this is through aDataSource. ADataSource is part of the JDBCspecification and can be seen as a generalized connection factory. Itallows a container or a framework to hide connection pooling andtransaction management issues from the application code. As a developer,you don not need to know any details about how to connect to thedatabase, that is the responsibility for the administrator that sets upthe datasource. You will most likely have to fulfill both roles whileyou are developing and testing you code though, but you will notnecessarily have to know how the production data source isconfigured.
When using Spring‘s JDBC layer, you can either obtain a datasource from JNDI or you can configure your own, using an implementationthat is provided in the Spring distribution. The latter comes in handyfor unit testing outside of a web container. We will use theDriverManagerDataSource implementation for thissection but there are several additional implementations that will becovered later on. The DriverManagerDataSourceworks the same way that you probably are used to work when you obtain aJDBC connection. You have to specify the fully qualified class name ofthe JDBC driver that you are using so that theDriverManager can load the driver class. Then youhave to provide a URL that varies between JDBC drivers. You have toconsult the documentation for your driver for the correct value to usehere. Finally you must provide a username and a password that will beused to connect to the database. Here is an example of how to configurea DriverManagerDataSource:
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("org.hsqldb.jdbcDriver");
dataSource.setUrl("jdbc:hsqldb:hsql://localhost:");
dataSource.setUsername("sa");
dataSource.setPassword("");
11.2.5. SQLExceptionTranslator
SQLExceptionTranslator is aninterface to be implemented by classes that can translate betweenSQLExceptions and Spring‘s owndata-access-strategy-agnosticorg.springframework.dao.DataAccessException.Implementations can be generic (for example, using SQLState codes forJDBC) or proprietary (for example, using Oracle error codes) for greaterprecision.
SQLErrorCodeSQLExceptionTranslator is theimplementation of SQLExceptionTranslatorthat is used by default. This implementation uses specific vendor codes.More precise than SQLState implementation, but vendorspecific. The error code translations are based on codes held in aJavaBean type class named SQLErrorCodes. Thisclass is created and populated by anSQLErrorCodesFactory which as the name suggestsis a factory for creating SQLErrorCodes based onthe contents of a configuration file named ‘sql-error-codes.xml‘. This file ispopulated with vendor codes and based on the DatabaseProductName takenfrom the DatabaseMetaData, the codes forthe current database are used.
The SQLErrorCodeSQLExceptionTranslatorapplies the following matching rules:
Try custom translation implemented by any subclass. Note that this class is concrete and is typically used itself, in which case this rule does not apply.
Apply error code matching. Error codes are obtained from the SQLErrorCodesFactory by default. This looks up error codes from the classpath and keys into them from the database name from the database metadata.
Use the fallback translator. SQLStateSQLExceptionTranslator is the default fallback translator.
SQLErrorCodeSQLExceptionTranslator can beextended the following way:
public class MySQLErrorCodesTranslator extends SQLErrorCodeSQLExceptionTranslator {
protected DataAccessException customTranslate(String task, String sql, SQLException sqlex) {
if (sqlex.getErrorCode() == -12345) {
return new DeadlockLoserDataAccessException(task, sqlex);
}
return null;
}
}
In this example the specific error code‘-12345‘ is translated and any other errors aresimply left to be translated by the default translator implementation.To use this custom translator, it is necessary to pass it to theJdbcTemplate using the methodsetExceptionTranslator and to use thisJdbcTemplate for all of the data accessprocessing where this translator is needed. Here is an example of howthis custom translator can be used:
// create a JdbcTemplate and set data source
JdbcTemplate jt = new JdbcTemplate();
jt.setDataSource(dataSource);
// create a custom translator and set the DataSource for the default translation lookup
MySQLErrorCodesTransalator tr = new MySQLErrorCodesTransalator();
tr.setDataSource(dataSource);
jt.setExceptionTranslator(tr);
// use the JdbcTemplate for this SqlUpdate
SqlUpdate su = new SqlUpdate();
su.setJdbcTemplate(jt);
su.setSql("update orders set shipping_charge = shipping_charge * 1.05");
su.compile();
su.update();
The custom translator is passed a data source because we stillwant the default translation to look up the error codes insql-error-codes.xml.
11.2.6. Executing statements
To execute an SQL statement, there is very little code needed. Allyou need is a DataSource and aJdbcTemplate. Once you have that, you can use anumber of convenience methods that are provided with theJdbcTemplate. Here is a short example showingwhat you need to include for a minimal but fully functional class thatcreates a new table.
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
public class ExecuteAStatement {
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public void doExecute() {
this.jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");
}
}
11.2.7. Running Queries
In addition to the execute methods, there is a large number ofquery methods. Some of these methods are intended to be used for queriesthat return a single value. Maybe you want to retrieve a count or aspecific value from one row. If that is the case then you can usequeryForInt(..),queryForLong(..) orqueryForObject(..). The latter will convert thereturned JDBC Type to the Java class that ispassed in as an argument. If the type conversion is invalid, then anInvalidDataAccessApiUsageException willbe thrown. Here is an example that contains two query methods, one foran int and one that queries for aString.
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
public class RunAQuery {
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public int getCount() {
return this.jdbcTemplate.queryForInt("select count(*) from mytable");
}
public String getName() {
return (String) this.jdbcTemplate.queryForObject("select name from mytable", String.class);
}
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
}
In addition to the single results query methods there are severalmethods that return a List with an entry for each row that the queryreturned. The most generic method isqueryForList(..) which returns aList where each entry is aMap with each entry in the maprepresenting the column value for that row. If we add a method to theabove example to retrieve a list of all the rows, it would look likethis:
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public List getList() {
return this.jdbcTemplate.queryForList("select * from mytable");
}
The list returned would look something like this:
[{name=Bob, id=1}, {name=Mary, id=2}]
11.2.8. Updating the database
There are also a number of update methods that you can use. Findbelow an example where a column is updated for a certain primary key. Inthis example an SQL statement is used that has place holders for rowparameters. Note that the parameter values are passed in as an array ofobjects (and thus primitives have to be wrapped in the primitive wrapperclasses).
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
public class ExecuteAnUpdate {
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public void setName(int id, String name) {
this.jdbcTemplate.update(
"update mytable set name = ? where id = ?",
new Object[] {name, new Integer(id)});
}
}
11.2.9. Retrieving auto-generated keys
One of the update convenience methodsprovides support for acquiring the primary keys generated by thedatabase (part of the JDBC 3.0 standard - see chapter 13.6 of thespecification for details). The method takes aPreparedStatementCreator as its first argument,and this is the way the required insert statement is specified. Theother argument is a KeyHolder, which will containthe generated key on successful return from the update. There is not astandard single way to create an appropriatePreparedStatement (which explains why the methodsignature is the way it is). An example that works on Oracle and may notwork on other platforms is:
final String INSERT_SQL = "insert into my_test (name) values(?)";
final String name = "Rob";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement ps =
connection.prepareStatement(INSERT_SQL, new String[] {"id"});
ps.setString(1, name);
return ps;
}
},
keyHolder);
// keyHolder.getKey() now contains the generated key
11.3. Controlling database connections
11.3.1. DataSourceUtils
The DataSourceUtils class is a convenientand powerful helper class that provides staticmethods to obtain connections from JNDI and close connections ifnecessary. It has support for thread-bound connections, for example foruse with DataSourceTransactionManager.
11.3.2. SmartDataSource
The SmartDataSource interface is tobe implemented by classes that can provide a connection to a relationaldatabase. Extends the DataSourceinterface to allow classes using it to query whether or not theconnection should be closed after a given operation. This can sometimesbe useful for efficiency, in the cases where one knows that one wants toreuse a connection.
11.3.3. AbstractDataSource
This is an abstract base class for Spring‘sDataSource implementations, that takescare of the "uninteresting" glue. This is the class one would extend ifone was writing one‘s own DataSourceimplementation.
11.3.4. SingleConnectionDataSource
The SingleConnectionDataSource class is animplementation of the SmartDataSourceinterface that wraps a singleConnection that isnot closed after use. Obviously, this is notmulti-threading capable.
If client code will call close in the assumption of a pooledconnection, like when using persistence tools, setsuppressClose to true. This willreturn a close-suppressing proxy instead of the physical connection. Beaware that you will not be able to cast this to a native OracleConnection or the like anymore.
This is primarily a test class. For example, it enables easytesting of code outside an application server, in conjunction with asimple JNDI environment. In contrast toDriverManagerDataSource, it reuses the sameconnection all the time, avoiding excessive creation of physicalconnections.
11.3.5. DriverManagerDataSource
The DriverManagerDataSource class is animplementation of the standard DataSourceinterface that configures a plain old JDBC Driver via bean properties, andreturns a new Connection every time.
This is potentially useful for test or standalone environmentsoutside of a J2EE container, either as aDataSource bean in a Spring IoCcontainer, or in conjunction with a simple JNDI environment.Pool-assuming Connection.close() calls will simplyclose the connection, so anyDataSource-aware persistence code shouldwork. However, using JavaBean style connection pools such ascommons-dbcp is so easy, even in a test environment, that it is almostalways preferable to use such a connection pool overDriverManagerDataSource.
11.3.6. TransactionAwareDataSourceProxy
TransactionAwareDataSourceProxy is a proxyfor a target DataSource, which wraps thattarget DataSource to add awareness ofSpring-managed transactions. In this respect it is similar to atransactional JNDI DataSource as providedby a J2EE server.
Note
It should almost never be necessary or desirable to use this class, except when existing code exists which must be called and passed a standard JDBC DataSource interface implementation. In this case, it‘s possible to still have this code be usable, but participating in Spring managed transactions. It is generally preferable to write your own new code using the higher level abstractions for resource management, such as JdbcTemplate or DataSourceUtils.
(See theTransactionAwareDataSourceProxy Javadocs for moredetails.)
11.3.7. DataSourceTransactionManager
The DataSourceTransactionManager class is aPlatformTransactionManager implementationfor single JDBC datasources. It binds a JDBC connection from thespecified data source to the currently executing thread, potentiallyallowing for one thread connection per data source.
Application code is required to retrieve the JDBC connection viaDataSourceUtils.getConnection(DataSource) instead ofJ2EE‘s standard DataSource.getConnection. This isrecommended anyway, as it throws uncheckedorg.springframework.dao exceptions instead of checkedSQLExceptions. All framework classes likeJdbcTemplate use this strategy implicitly. If notused with this transaction manager, the lookup strategy behaves exactlylike the common one - it can thus be used in any case.
The DataSourceTransactionManager classsupports custom isolation levels, and timeouts that get applied asappropriate JDBC statement query timeouts. To support the latter,application code must either use JdbcTemplate orcall DataSourceUtils.applyTransactionTimeout(..)method for each created statement.
This implementation can be used instead ofJtaTransactionManager in the single resourcecase, as it does not require the container to support JTA. Switchingbetween both is just a matter of configuration, if you stick to therequired connection lookup pattern. Note that JTA does not supportcustom isolation levels!
11.3.8. NativeJdbcExtractor
There are times when we need to access vendor specific JDBCmethods that differ from the standard JDBC API. This can be problematicif we are running in an application server or with aDataSource that wraps theConnection, Statement andResultSet objects with its own wrapper objects.To gain access to the native objects you can configure yourJdbcTemplate orOracleLobHandler with aNativeJdbcExtractor.
The NativeJdbcExtractor comes in a variety of flavors to matchyour execution environment:
SimpleNativeJdbcExtractor
C3P0NativeJdbcExtractor
CommonsDbcpNativeJdbcExtractor
JBossNativeJdbcExtractor
WebLogicNativeJdbcExtractor
WebSphereNativeJdbcExtractor
XAPoolNativeJdbcExtractor
Usually the SimpleNativeJdbcExtractor issufficient for unwrapping a Connection object inmost environments. See the Java Docs for more details.
11.4. JDBC batch operations
Most JDBC drivers provide improved performance if you batch multiplecalls to the same prepared statement. By grouping updates into batches youlimit the number of round trips to the database. This section will coverbatch processing using both the JdbcTemplate and theSimpleJdbcTemplate.
11.4.1. Batch operations with the JdbcTemplate
Using the JdbcTemplate batch processing is accomplished byimplementing a special interface,BatchPreparedStatementSetter, and passing that inas the second parameter in your batchUpdatemethod call. This interface has two methods you must implement. One isnamed getBatchSize and here you provide the sizeof the current batch. The other method issetValues and it allows you to set the values forthe parameters of the prepared statement and. This method will getcalled the number of times that you specified in thegetBatchSize call. Here is an example of thiswhere we update the actor table based on entries in a list. The entirelist is used as the batch in his example.
public class JdbcActorDao implements ActorDao {
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public int[] batchUpdate(final List actors) {
int[] updateCounts = jdbcTemplate.batchUpdate(
"update t_actor set first_name = ?, last_name = ? where id = ?",
new BatchPreparedStatementSetter() {
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setString(1, ((Actor)actors.get(i)).getFirstName());
ps.setString(2, ((Actor)actors.get(i)).getLastName());
ps.setLong(3, ((Actor)actors.get(i)).getId().longValue());
}
public int getBatchSize() {
return actors.size();
}
} );
return updateCounts;
}
// ... additional methods
}
If you are processing stream of updates or reading from afile then you might have a preferred batch size, but the last batchmight not have that number of entries. In this case you can use theInterruptibleBatchPreparedStatementSetterinterface which allows you to interrupt a batch once the input source isexhausted. The isBatchExhausted method allows youto signal the end of the batch.
11.4.2. Batch operations with the SimpleJdbcTemplate
The SimpleJdbcTemplate provides analternate way of providing the batch update. Instead of implementing aspecial batch interface, you simply provide all parameter values in thecall and the framework will loop over these values and use an internalprepared statement setter. The API varies depending on whether you usenamed parameters or not. For the named parameters you provide an arrayof SqlParameterSource, one entry for each memberof the batch. You can use theSqlParameterSource.createBatch method to createthis array, passing in either an array of JavaBeans or an array of Mapscontaining the parameter values.
This example shows a batch update using named parameters:
public class JdbcActorDao implements ActorDao {
private SimpleJdbcTemplate simpleJdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
}
public int[] batchUpdate(final List
SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(actors.toArray());
int[] updateCounts = simpleJdbcTemplate.batchUpdate(
"update t_actor set first_name = :firstName, last_name = :lastName where id = :id",
batch);
return updateCounts;
}
// ... additional methods
}
For an SQL statement using the classic "?" place holders youpass in a List containing an object array with the update values. Thisobject array must have one entry for each placeholder in the SQLstatement and they must be in the same order as they are defined in theSQL statement.
The same example using classic JDBC "?" place holders:
public class JdbcActorDao implements ActorDao {
private SimpleJdbcTemplate simpleJdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
}
public int[] batchUpdate(final List
List
Chapter 11. Data access using JDBC
使用 SDO 和 JDBC Data Access Service 启用面向服务的体系结构
使用 SDO 和 JDBC Data Access Service 启用面向服务的体系结构
JDBC连接Access
[Loney05] Chapter 9. Using STATSPACK
Chapter 5. Dimension 4: Using Knowledge Meani...
Chapter 5. Dimension 4: Using Knowledge Meani...
[Laskey99] Chapter 4. Preventing Data Loss
Using the SDO Data Graph Editor
WPF Data Binding Using LINQ to...
Access your Outlook application using PHP
SOA Web Services - Data Access Service
Textual Searches on File Data Using Microsoft SQL Server 7.0
Using Service Data Objects with Enterprise Information Integration technology
Using Java to Handle Custom WSDL Data Types
Using SPGridView to Display Your Data in WSS 3.0
Access the Linux kernel using the /proc files...
.Net Framework 数据提供程序要求 Microsoft Data Access...
CHAPTER
博客园 - Terrylee‘s Tech Space - Enterprise Library2.0(1):Data Access Application Block学习
Enterprise .NET Community: Build a Data Access Layer with the Visual Studio 2005 DataSet Designer
ACCESS
[Cockcroft98] Chapter 11. System Architectures
10 Things You Shouldn‘t Do with SQL Server (Data Access Developer "Don‘ts") - goody9807 - 博客园