JDBC abstraction and data access exception hierarchy

Data access is another area in which Spring shines.

JDBC offers fairly good abstraction from the underlying database, but is a painful API to use. Some of the problems include:

• The need for verbose error handling to ensure that ResultSets, Statements and (most importantly) Connections are closed after use. This means that correct use of JDBC can quickly result in a lot of code. It's also a common source of errors. Connection leaks can quickly bring applications down under load.
• The relatively uninformative SQLException. JDBC does not offer an exception hierarchy, but throws SQLException in response to all errors. Finding out what actually went wrong - for example, was the problem a deadlock or invalid SQL? - involves examining the SQLState value and error code. The meaning of these values varies between databases.

Spring addresses these problems in two ways:

• By providing APIs that move tedious and error-prone exception handling out of application code into the framework. The framework takes care of all exception handling; application code can concentrate on issuing the appropriate SQL and extracting results.
• By providing a meaningful exception hierarchy for your application code to work with in place of SQLException. When Spring first obtains a connection from a DataSource it examines the metadata to determine the database product. It uses this knowledge to map SQLExceptions to the correct exception in its own hierarchy descended from org.springframework.dao.DataAccessException. Thus your code can work with meaningful exceptions, and need not worry about proprietary SQLState or error codes. Spring's data access exceptions are not JDBC-specific, so your DAOs are not necessarily tied to JDBC because of the exceptions they may throw.

The following UML class diagram illustrates a part of this data access exception hierarchy, indicating its sophistication. Note that none of the exceptions shown here is JDBC-specific. There are JDBC-specific subclasses of some of these exceptions, but calling code is generally abstracted wholly away from dependence on JDBC: an essential if you wish to use truly API-agnostic DAO interfaces to hide your persistence strategy.

Spring provides two levels of JDBC abstraction API. The first, in the org.springframework.jdbc.core package, uses callbacks to move control - and hence error handling and connection acquisition and release - from application code inside the framework. This is a different type of Inversion of Control, but equally valuable to that used for configuration management.

Spring uses a similar callback approach to address several other APIs that involve special steps to acquire and cleanup resources, such as JDO (acquiring and relinquishing a PersistenceManager), transaction management (using JTA) and JNDI. Spring classes that perform such callbacks are called templates.

For example, the Spring JdbcTemplate object can be used to perform a SQL query and save the results in a list as follows:

JdbcTemplate template = new JdbcTemplate(dataSource);
List names = template.query("SELECT USER.NAME FROM USER",
	new RowMapper() {
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException;
			return rs.getString(1);
		}
	});

The mapRow callback method will be invoked for each row of the ResultSet.

Note that application code within the callback is free to throw SQLException: Spring will catch any exceptions and rethrow them in its own hierarchy. The application developer can choose which exceptions, if any, to catch and handle.

The JdbcTemplate provides many methods to support different scenarios including prepared statements and batch updates. Simple tasks like running SQL functions can be accomplished without a callback, as follows. The example also illustrates the use of bind variables:

int youngUserCount = template.queryForInt("SELECT COUNT(0) FROM USER WHERE USER.AGE < ?",
	new Object[] { new Integer(25) });

The Spring JDBC abstraction has a very low performance overhead beyond standard JDBC, even when working with huge result sets. (In one project in 2004, we profiled the performance of a financial application performing up to 1.2 million inserts per transaction. The overhead of Spring JDBC was minimal, and the use of Spring facilitated the tuning of batch sizes and other parameters.)

The higher level JDBC abstraction is in the org.springframework.jdbc.object package. This is built on the core JDBC callback functionality, but provides an API in which an RDBMS operation - whether query, update or stored procedure - is modelled as a Java object. This API was partly inspired by the JDO query API, which I found intuitive and highly usable.

A query object to return User objects might look like this:

class UserQuery extends MappingSqlQuery {

	public UserQuery(DataSource datasource) {
		super(datasource, "SELECT * FROM PUB_USER_ADDRESS WHERE USER_ID = ?");
		declareParameter(new SqlParameter(Types.NUMERIC));
		compile();
	}

	// Map a result set row to a Java object
	protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
		User user = new User();
		user.setId(rs.getLong("USER_ID"));
		user.setForename(rs.getString("FORENAME"));
		return user;
	}

	public User findUser(long id) {
		// Use superclass convenience method to provide strong typing
		return (User) findObject(id);
	}
}

This class can be used as follows:

User user = userQuery.findUser(25);

Such objects are often inner classes inside DAOs. They are threadsafe, unless the subclass does something unusual.

Another important class in the org.springframework.jdbc.object package is the StoredProcedure class. Spring enables a stored procedure to be proxied by a Java class with a single business method. If you like, you can define an interface that the stored procedure implements, meaning that you can free your application code from depending on the use of a stored procedure at all.

The Spring data access exception hierarchy is based on unchecked (runtime) exceptions. Having worked with Spring on several projects I'm more and more convinced that this was the right decision.

Data access exceptions not usually recoverable. For example, if we can't connect to the database, a particular business object is unlikely to be able to work around the problem. One potential exception is optimistic locking violations, but not all applications use optimistic locking. It's usually bad to be forced to write code to catch fatal exceptions that can't be sensibly handled. Letting them propagate to top-level handlers like the servlet or EJB container is usually more appropriate. All Spring data access exceptions are subclasses of DataAccessException, so if we do choose to catch all Spring data access exceptions, we can easily do so.

Note that if we do want to recover from an unchecked data access exception, we can still do so. We can write code to handle only the recoverable condition. For example, if we consider that only an optimistic locking violation is recoverable, we can write code in a Spring DAO as follows:

try {
	// do work
}
catch (OptimisticLockingFailureException ex) {
	// I'm interested in this
}

If Spring data access exceptions were checked, we'd need to write the following code. Note that we could choose to write this anyway:

try {
	// do work
}
catch (OptimisticLockingFailureException ex) {
	// I'm interested in this
}
catch (DataAccessException ex) {
	// Fatal; just rethrow it
}

One potential objection to the first example - that the compiler can't enforce handling the potentially recoverable exception - applies also to the second. Because we're forced to catch the base exception (DataAccessException), the compiler won't enforce a check for a subclass (OptimisticLockingFailureException). So the compiler would force us to write code to handle an unrecoverable problem, but provide no help in forcing us to deal with the recoverable problem.

Spring's use of unchecked data access exceptions is consistent with that of many - probably most - successful persistence frameworks. (Indeed, it was partly inspired by JDO.) JDBC is one of the few data access APIs to use checked exceptions. TopLink and JDO, for example, use unchecked exceptions exclusively. Hibernate switched from checked to unchecked exceptions in version 3.

Spring JDBC can help you in several ways:

• You'll never need to write a finally block again to use JDBC
• Connection leaks will be a thing of the past
• You'll need to write less code overall, and that code will be clearly focused on the necessary SQL
• You'll never need to dig through your RDBMS documentation to work out what obscure error code it returns for a bad column name. Your application won't be dependent on RDBMS-specific error handling code.
• Whatever persistence technology use, you'll find it easy to implement the DAO pattern without business logic depending on any particular data access API.
• You'll benefit from improved portability (compared to raw JDBC) in advanced areas such as BLOB handling and invoking stored procedures that return result sets.

In practice we find that all this amounts to substantial productivity gains and fewer bugs. I used to loathe writing JDBC code; now I find that I can focus on the SQL I want to execute, rather than the incidentals of JDBC resource management.

Spring's JDBC abstraction can be used standalone if desired - you are not forced to use the other parts of Spring.