Spring Framework : A Framework from SpringSource :: Spring with JDBC

• Spring JDBC Intro
• Spring JDBC Package Hierarchy
• JdbcTemplate
• NamedParameterJdbcTemplate
• SimpleJdbcTemplate
• DataSource
• Donwload Examples

Spring JDBC

The value-add provided by the Spring Framework’s JDBC abstraction framework is perhaps best shown by the following list (note that only the italicized 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 details that can make JDBC such a tedious API to develop with.

JDBC Selection Approach

• There are a number of options for selecting an approach to form the basis for your JDBC database access.
• There are three flavors of the JdbcTemplate, a new “SimpleJdbc” approach taking advantage of database metadata, and
• there is also the “RDBMS Object” style for a more object oriented approach similar in style to the JDO Query design.
• All approaches requires a JDBC 2.0 compliant driver and some advanced features 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.

The Spring Framework’s JDBC abstraction framework consists of four different packages, namely

• core,
• datasource,
• object, and
• support.

• The org.springframework.jdbc.core package contains the JdbcTemplate class and its various callback interfaces, plus a variety of related classes.
  • A sub-package named org.springframework.jdbc.core.simple contains the SimpleJdbcTemplate class and the related SimpleJdbcInsert and SimpleJdbcCall classes.
  • Another sub-package named org.springframework.jdbc.core.namedparam contains the NamedParameterJdbcTemplate class and the related support classes.
• The org.springframework.jdbc.datasource package contains a utility class for easy DataSource access, and various simple DataSource implementations that can be used for testing and running unmodified JDBC code outside of a J2EE container. The utility class provides static methods to obtain connections from JNDI and to close connections if necessary. It has support for thread-bound connections, e.g. for use with DataSourceTransactionManager.
• Next, the org.springframework.jdbc.object package contains classes that represent RDBMS queries, updates, and stored procedures as thread safe, reusable objects. This approach is modeled by JDO, although of course objects returned by queries are “disconnected” from the database. This higher level of JDBC abstraction depends on the lower-level abstraction in the org.springframework.jdbc.core package.
• Finally the org.springframework.jdbc.support package is where you find the SQLException translation functionality and some utility classes.
• Exceptions thrown during JDBC processing are translated to exceptions defined in the org.springframework.dao package. This means that code using the Spring JDBC abstraction layer does not need to implement JDBC or RDBMS-specific error handling. All translated exceptions are unchecked giving you the option of catching the exceptions that you can recover from while allowing other exceptions to be propagated to the caller.

Using the JDBC Core classes to control basic JDBC processing and error handling

• The JdbcTemplate class is the central class in the JDBC core package.
• It simplifies the use of JDBC since it handles the creation and release of resources.
• This helps to avoid common errors such as forgetting to always close the connection.
• It executes the core JDBC workflow like statement creation and execution, leaving application code to provide SQL and extract results.
• This class executes SQL queries, update statements or stored procedure calls, imitating iteration over ResultSets and extraction of returned parameter values.
• It also catches JDBC exceptions and translates them to the generic, more informative, exception hierarchy defined in the org.springframework.dao package.
• Code using the JdbcTemplate only need to implement callback interfaces, giving them a clearly defined contract.
• The JdbcTemplate can be used within a DAO implementation via direct instantiation with a DataSource reference, or be configured in a Spring IOC container and given to DAOs as a bean reference.

Note: the DataSource should always be configured as a bean in the Spring IoC container, in the first case given to the service directly, in the second case to the prepared template.

• The NamedParameterJdbcTemplate class adds support for programming JDBC statements using named parameters (as opposed to programming JDBC statements using only classic placeholder (‘?’) arguments.
• The NamedParameterJdbcTemplate class wraps a JdbcTemplate, and delegates to the wrapped JdbcTemplate to do much of its work.
• This section will describe only those areas of the NamedParameterJdbcTemplate class that differ from the JdbcTemplate itself; namely, programming JDBC statements 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); 
}

• The functionality offered by the SimpleJdbcTemplate is only available to you if you are using Java 5 or later.
• The SimpleJdbcTemplate class is a wrapper around the classic JdbcTemplate that takes advantage of Java 5 language features such as varargs and autoboxing. The SimpleJdbcTemplate class is somewhat of a sop to the syntactic-sugar-like features of Java 5, but as anyone who has developed on Java 5 and then had to move back to developing on a previous version of the JDK will know, those syntactic-sugar-like features sure are nice.
• The value-add of the SimpleJdbcTemplate class in the area of syntactic-sugar is best illustrated with a ‘before and after’ example. The following code snippet shows first some data access code using the classic JdbcTemplate, followed immediately thereafter by a code snippet that does the same job, only this time using the SimpleJdbcTemplate.

 // 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)});
}

• In order to work with data from a database, one needs to obtain a connection to the database.
• The way Spring does this is through a DataSource.
• A DataSource is part of the JDBC specification and can be seen as a generalized connection factory.
• It allows a container or a framework to hide connection pooling and transaction management issues from the application code.
• As a developer, you don not need to know any details about how to connect to the database, that is the responsibility for the administrator that sets up the datasource.
• You will most likely have to fulfill both roles while you are developing and testing you code though, but you will not necessarily have to know how the production data source is configured.
• When using Spring’s JDBC layer, you can either obtain a data source from JNDI or you can configure your own, using an implementation that is provided in the Spring distribution. The latter comes in handy for unit testing outside of a web container.

We will use the DriverManagerDataSource implementation for this

• The DriverManagerDataSource works the same way that you probably are used to work when you obtain a JDBC connection.
• You have to specify the fully qualified class name of the JDBC driver that you are using so that the DriverManager can load the driver class.
• Then you have to provide a URL that varies between JDBC drivers.
• You have to consult the documentation for your driver for the correct value to use here.
• Finally you must provide a username and a password that will be used to connect to the database.

Here is an example of how to configure a DriverManagerDataSource:

DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("org.hsqldb.jdbcDriver");
dataSource.setUrl("jdbc:hsqldb:hsql://localhost:"); 
dataSource.setUsername("sa"); 
dataSource.setPassword("");

Download Examples

Click Below to download the Examples

• Spring with JdbcTemplate Example (3KB)
• Spring with JdbcTemplate Using DMDS(DriverManagerDataSource) Example (3KB)
• Click Here to Download Spring Jar Files (5.3MB)