12. Data access using JDBC

12.1 Introduction

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):

  1. Define connection parameters

  2. Open the connection

  3. Specify the statement

  4. Prepare and execute the statement

  5. Set up the loop to iterate through the results (if any)

  6. Do the work for each iteration

  7. Process any exception

  8. Handle transactions

  9. 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.

12.1.1 Choosing a style

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. We'll briefly list the primary reasons why you would pick one of these approaches. Keep in mind that even if you start using one of these approaches, you can still mix and match if there is a feature in a different approach that you would like to take advantage of. 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. In Spring 3.0 it has been updated with Java 5 support like generics and varargs.

  • 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. It has also been updated with Java 5 support like generics and varargs for Spring 3.0.

  • SimpleJdbcTemplate - this class combines the most frequently used operations across JdbcTemplate and NamedParameterJdbcTemplate. It also adds some additional convenience around support for Java 5 varargs where this was not possible in the JdbcTemplate due to backwards compatibility reasons.

  • 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 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. It has also been updated with Java 5 support like generics and vararg support for Spring 3.0.

12.1.2 The package hierarchy

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 Java EE container. A sub-package named org.springfamework.jdbc.datasource.embedded provides support for creating in-memory database instances using Java database engines such as HSQL and H2.

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.