Introducing Spring s JDBC Framework

Introducing Spring's JDBC Framework

Before going into detail about the various parts of Spring's JDBC support, let's look at some common issues encountered while writing code using the JDBC API directly. After that we'll see how Spring can help and we'll take a quick look at a brief example of using Spring for solving one specific problem — how to populate a table with test data using Java and SQL.

Motivation: Issues with Direct Use of JDBC

JDBC has been available since Java 1.1 and it was one of the most important early APIs that made Java's advances in server-side development possible. Today it is hard to imagine accessing a database from Java without using JDBC either directly or indirectly. Without it we would have to use proprietary APIs specific to a certain database, and these APIs from different vendors would all be significantly different from each other. The JDBC API consists of a number of interfaces that must be implemented by each provider of a JDBC driver. This is how the usage across various databases is unified into a single API. There are some differences between the various JDBC implementations, but overall they are not too difficult to work around.

Managing the Connection, Statements, and ResultSets

The primary JDBC interfaces include Connection, DataSource, Statement, PreparedStatement, CallableStatement, and ResultSet. To go along with these interfaces we have the class SQLException. The major obstacles you encounter working with JDBC are usually related to the exception and connection management. These are areas, as we will see later, where Spring's JDBC framework shines.

When you open a connection to the database, you must also close it. This forces you to always provide a try-finally set of blocks where the closing of the ResultSets, the Statements, and the Connection must take place in the finally block. On top of this, each call to the close() method can itself throw an SQLException, forcing you to nest additional try-catch blocks within the finally block. If you miss this, which is easy when you are new to JDBC, the connection will remain open. Leaving connections open means that you will sooner or later run out of connections for your application to use — a common cause of operational problems.

Handling SQLExceptions

If there is a problem accessing the database, all JDBC implementations throw an SQLException. This is the only exception used and it has proven difficult to manage exceptions in a way that works well across a variety of database products. To begin with, you end up writing lots of error-prone code in try-catch blocks. The exception itself is not very informative, even when we consider the error codes provided as part of the exception. There are two sets of codes provided — an error code and an SQL state code for each exception.

The error code is vendor-specific: for example, in the case of Oracle it will correspond to an ORA code, such as ORA-942 (table or view does not exist). This is informative but not portable. We need to write database-specific code to check it — although conceptually, of course, errors (like our example) are not usually database-specific.

The SQL state code is theoretically more portable, as it is supposed to be a generic code defined in the Open Group's XOPEN SQL specification. In reality, however, you will receive different SQL state codes from different implementations for seemingly similar errors. As an example, reading the X/Open specification for SQL version 2.0, it appears that the code for "table not found" should be 42S02. However, running a query selecting from a nonexistent table produced the following SQL state codes:

Oracle

DB2

HSQLDB

MySQL

PostgreSQL

42000

42704

S0002

S1000

42P01

Beginning with JDBC 3.0, the provider of the JDBC implementation can now follow either the XOPEN SQLState conventions or the SQL 99 conventions for the SQL state code. All of this clearly indicates that we need some kind of error code mapping to find the true cause of the problem.

Thus writing portable, self-documenting code that needs to react to specific errors — rather than merely know that "something went wrong" — is problematic using raw JDBC.

Lookup of Resources

The javax.sql.DataSource interface is part of JDBC 2.0 API and defines a standard way for interacting with a connection factory without having to provide database-specific parameters such as JDBC driver class name or connection URL. All of these configuration details are managed through some other means of configuration, allowing the code that uses the connection to remain unaware of this detail. Normally you would need to look up the DataSource using a naming service such as JNDI or use a local implementation such as Jakarta Common's DBCP from Apache. This leads to a different approach compared to the traditional way of using the JDBC DriverManager. You would have to maintain multiple sets of code depending on how the runtime environment was configured.

Important 

Spring's JDBC framework is designed to obtain connections using the standard JDBC 2.0 DataSource interface. This is particularly appropriate in J2EE environments, but works well in any environment.

JDBC Strengths

There are a number of features that work well in JDBC. Let's examine some of them.

Implementations Provided by Vendors

The JDBC specification largely specifies interfaces, rather than classes. These interfaces define the core functionality, leaving the actual implementation to JDBC driver vendors. This makes it possible to switch from one database or JDBC driver to another without having to modify your code, in most cases.

Using SQL

With JDBC, we can use regular SQL for querying and modifying the database. SQL is proven to work well for manipulating relational data. Many developers already know it and it is fairly easy to learn the basics if you don't. There are many resources available for learning SQL. The availability of JDBC drivers for almost any relational database is another strength for JDBC. This allows us to write code that can be used against a variety of databases as long as we don't use any esoteric features that may not be portable.

Cross Database Escape Syntax

To make cross-product coding easier, JDBC provides an SQL escape syntax consisting of keywords specified within curly braces. An example of this is { fn user()}, which will retrieve the current user name, and {d '2004-01-28'} for a cross-database date format. The JDBC driver is supposed to either map these expressions to the appropriate database feature or provide its own implementation. You can read up on this feature at http://java.sun.com/j2se/1.3/docs/guide/jdbc/spec/jdbc-spec.frame11.html.

How Spring Can Help

The main aim of Spring's JDBC framework is to leverage the features of JDBC that work well and to abstract away problem areas. This focuses primarily around connection and exception handling problems we mentioned earlier. This is done by providing an abstraction layer on top of JDBC. The abstraction layer manages connections as well as translates SQLExceptions into more meaningful exceptions.

Spring provides its own exception hierarchy for data access errors and the same hierarchy is used both when you use JDBC and when you use an O/R mapping tool such as Hibernate or JDO. This means that all your Data Access Objects (DAO) will throw the same type of errors regardless of the underlying data access implementation. All of this makes your data access code much easier to write and use. An added benefit is that it makes your database code more portable as well.

A Simple Example

We want to be able to populate a database table with some test data that we will be using for some tests. This brief example will set the stage for the more detailed discussion of each concept that follows. The main class used in this example is the JdbcTemplate. This is the central framework class that manages all the database communication and exception handling. Under the covers, it uses a java.sql.Connection that is obtained from a java.sql.DataSource. The JdbcTemplate provides some convenience methods for running simple SQL statements. One of these convenience methods is execute(), which just takes an SQL statement as the only parameter. Let's say you already have a table that was created using the following SQL:

create table mytable (id integer, name varchar(100)) 

Now you want to add some test data that can be used for one of your unit tests. Here is a minimal test case that you would write to accomplish this:

package org.springframework.prospring.ticket.db;     import junit.framework.TestCase;     import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.datasource.DriverManagerDataSource;     public class MinimalTest extends TestCase {   private DriverManagerDataSource dataSource;       public void setUp() {         dataSource = new DriverManagerDataSource();     dataSource.setDriverClassName( "org.hsqldb.jdbcDriver");     dataSource.setUrl( "jdbc:hsqldb:hsql://localhost:");     dataSource.setUsername( "sa");     dataSource.setPassword( "");         JdbcTemplate jt = new JdbcTemplate(dataSource);     jt.execute("delete from mytable");      jt.execute("insert into mytable (id, name) values(1, 'John’)");     jt.execute("insert into mytable (id, name) values(2, 'Jane’)");       }       public void testSomething() {         // the actual test code goes here       } } 

In this example we first set up one of Spring's DataSource implementations that are designed for use in a test environment. Normally we would use Dependency Injection for the DataSource, but to show everything in one code snippet, we create the DataSource programmatically here. Next, we create a JdbcTemplate, passing in the DataSource as the only parameter for the constructor. Next, we call the JdbcTemplate's execute() method with the SQL insert statements we need.

As you can see, there is no exception handling in this code. The JdbcTemplate will catch any SQLExceptions and translate them to a subclass of DataAccessException, which is the top-level class in Spring's data access exception hierarchy. These exceptions are unchecked, and because we assume that for this program there is nothing we can do about any database errors, we just let the runtime exception get propagated to the top of the call stack and the whole program execution will fail.

You can also see that there is no need for a try-finally block for closing the connection. This is unnecessary because the JdbcTemplate will manage the connection for us and this makes the code we write much more concise and easy to follow. All the unwieldy "plumbing" code has been relegated to the framework classes.