Why Spring JDBC?
Why Spring JDBC?
by Vikram Veeravelu
05/09/2006
Spring is a lightweight application framework. In many cases, Spring can elegantly replace services traditionally provided by Java EE application servers. Spring is both comprehensive and modular. With its layered architecture, it gives the developer flexibility to use any part of it individually. Spring consists of a number of modules, such as the IoC container, AOP, MVC, persistence, DAO, and remoting. These modules are fairly loosely coupled: some can be used without using the others. There is nothing as such as a Spring application: you can opt to use some, most, or all of the components supported by Spring framework to build your application. And, since Spring is open source, it can be used in discovering concrete examples of good application design principles.
The easiest way to get going with Spring, in many instances, is through Spring JDBC. The JDBC support provided by the Spring framework is not tightly coupled with other parts of Spring, which boosts the maintainability of code. This article shows how any application that uses JDBC directly (i.e., not through some O/R mapping framework that itself uses JDBC) can easily benefit from Spring.
Traditional JDBC
There are many positive aspects of traditional JDBC that have allowed it to play a significant role in many J2SE and J2EE applications. However, there are some characteristics that make it unfortunately difficult to use:
- The developer needs to deal with lot of plumbing and infrastructure, such as endless
try-catch-finally-try-catch blocks.
- Applications need complex error handling to ensure that connections are properly closed after they're used, which makes the code verbose, bloated, and repetitive.
- JDBC uses the rather uninformative
SQLException.
- JDBC has no exception hierarchy.
SQLException is thrown in response to any error, whether it originates in the JDBC driver or in the database, which makes it difficult to understand what actually went wrong. For example, if the SQL object is invalid or has been locked, an SQLException is thrown. Debugging such exceptions involves examining the SQL state value and error code, which requires more time. Worse, the meaning of SQL state value and error code varies between databases.
Writing JDBC code is arguably not a easy job because of this repetitive overhead. To illustrate this, here's an example of traditional JDBC used to get a list of available tasks from the database.
package com.spring.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
public class TraditionalJDBC {
public Vector getTasksNames() {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Vector task = new Vector();
try {
con = getConnection();
pstmt = con.prepareStatement(
"select TASKNAME from tasks");
rs = pstmt.executeQuery();
while (rs.next()) {
task.add(rs.getString(1));
}
} catch (SQLException e) {
System.out.println(e);
} finally {
try {
rs.close();
pstmt.close();
con.close();
} catch (SQLException e1) {
System.out.println(e1);
}
}
return task;
}
private Connection getConnection()
throws SQLException {
try {
DriverManager.registerDriver(
new oracle.jdbc.driver.OracleDriver());
return DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl",
"scott",
"tiger");
} catch (SQLException sqle) {
System.out.println(sqle);
return null;
}
}
public static void main(String[] args) {
TraditionalJDBC obj = new TraditionalJDBC();
Vector task = obj.getTasksNames();
for (int i = 0; i < task.size(); i++) {
System.out.println(task.elementAt(i));
}
}
}
A tremendous amount of routine code is necessary in the above example, in addition to the SQL code that actually queries the database. The getConnection() method has nothing to do with our tasks, and even the getTasksNames() method contains just two lines of code that are specific to the task at hand. The rest is generic plumbing code.
The many positive aspects of JDBC have allowed it to play a significant role in many J2SE and J2EE applications. However, as you've seen, there are some characteristics that make it more difficult to use than we might desire. These tedious and sometimes frustrating characteristics of JDBC have led to the creation of publicly available JDBC abstraction frameworks (such as SQLExecutor and Apache Jakarta Commons DBUtils), as well as an innumerable amount of homegrown JDBC application frameworks. One publicly available JDBC abstraction framework is Spring framework's JDBC abstraction.
Introduction to Spring JDBC
The JDBC abstraction framework provided by Spring consists of four different packages:
- The
core package contains the JdbcTemplate. This class is one of the fundamental classes provided by and used by the Spring framework's JDBC support.
- The
datasource package is a great fit for unit testing database access code. Its DriverManagerDataSource can be used in a way that is similar to what you are already used to from JDBC: just create a new DriverManagerDataSource and call the setter methods to set DriverClassName, Url, Username, and Password.
- The
object package contains classes that represent RDBMS queries, updates, and stored procedures as thread safe, reusable objects.
- The
support package is where you can find the SQLException translation functionality and utility classes.
The Template Design Pattern
Spring JDBC implements the Template design pattern, meaning the repetitive plumbing parts of the code are implemented in template classes. This approach simplifies the use of JDBC, since it handles the creation and release of resources. This helps to avoid common errors like forgetting to close the connection. It executes the core JDBC workflow tasks like statement creation and execution, leaving application code to provide SQL and extract results.
Spring JDBC Exception Handling
The Spring framework addresses the problems faced in traditional JDBC programming with the following solutions:
- Spring provides an abstract exception layer, moving verbose 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 extracting results by using appropriate SQL.
- Spring provides a significant exception hierarchy for your application code to work with in place of
SQLException.
With the help of an abstract exception layer, we achieve database independence without having to change the exception handling. For example, if you change your database from PostgreSQL to Oracle, you do not have to change the exception handling from OracleDataException to PostgresDataException. Spring catches the application-server-specific Exception and throws a Spring data exception.
When dealing with exceptions, Spring examines the metadata available from a database connection to determine the database product. It uses this knowledge to map SQLException to the correct exception in its own hierarchy. So, we need not worry about proprietary SQL state 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.
Spring JDBC Example
In the next two listings, we'll take the business logic we previously implemented in traditional JDBC and show how much simpler the Spring JDBC versions are. We begin with a simple interface.
package com.spring.jdbc;
import java.util.List;
public interface TasksDAO {
public List getTasksNames();
}
Next, we provide an implementation for the TasksDAO interface.
package com.spring.jdbc;
import java.util.Iterator;
import java.util.List;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultReader;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
public class TasksJdbcDAO extends JdbcDaoSupport
implements TasksDAO {
public List getTasksNames() {
JdbcTemplate jt = getJdbcTemplate();
return jt.query("select TASKNAME from tasks",
new RowMapperResultReader(new TasksRowMapper()));
}
class TasksRowMapper implements RowMapper {
public Object mapRow(ResultSet rs, int index)
throws SQLException {
return rs.getString(1);
}
}
public static void main(String[] args)
throws Exception {
ApplicationContext ctx =
new ClassPathXmlApplicationContext(
"SpringConfig.xml");
DataSource ds =
(DataSource) ctx.getBean("dataSourceDBDirect");
TasksJdbcDAO taskDao = new TasksJdbcDAO();
taskDao.setDataSource(ds);
Iterator tskIter = taskDao.getTasksNames().iterator();
while (tskIter.hasNext()) {
System.out.println(tskIter.next().toString());
}
}
}
In the above example, generic and plumbing code has been moved to the framework. Also note how along with Spring JDBC, we make use of the Inversion of Control (IoC) container to provide a DataSource that we can inject into the TasksJdbcDAO object.
The concept behind Inversion of Control is often expressed as "Don't call me, I'll call you." IoC moves the responsibility for making things happen into the framework, and away from application code. Instead of your code calling a traditional class library, an IoC framework calls your code. Lifecycle callbacks in many APIs, such as the setSessionContext() method for session EJBs, demonstrate this approach.
The DataSource has to be injected into this class (or rather its superclass) via the setDataSource() method. All configuration details stay out of the business logic or client code; this increases the decoupling of your application and thereby the testability and maintainability. Alternatively, we can set up a DataSource in the JNDI or servlet container, retrieve it programmatically, and inject it into the DAO object. Here's a sample Spring bean configuration file, SpringConfig.xml, that you could use:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
"http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
<bean id="dataSourceDBDirect"
class="org.springframework.jdbc.datasource.DriverManagerDataSource"
destroy-method="close">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
<property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl"/>
<property name="username" value="scott"/>
<property name="password" value="tiger"/>
</bean>
</beans>
This file instructs the Spring bean container to instantiate a dataSourceDBDirect bean that is created based on the org.springframework.jdbc.datasource.DriverManagerDataSource class.
Implementing a Business Layer over Spring JDBC
We have already seen a simple example of using Spring JDBC and in that case, it had very little help from the Spring BeanFactory (the Inversion of Control container). Now we will go beyond the simple example. We are going to see how we implement the business services on top of Spring JDBC. First, let us create a Client, an application that produces output for end users. The Client makes use of a Service, a business service that conforms to the following Service interface:
package com.spring.jdbc;
import java.util.List;
public interface Service {
public List getTasksNames();
public void setTasksDao(TasksDAO taskDAO);
}
The client needs to access a business service object. It will use the Spring BeanContainer to get hold of such a service object. The client can just program against the interface and rely on the container to provide an actual implementation. Also, the ServiceImpl class has to implement all of the methods present in business service interface. The code will look as follows:
package com.spring.jdbc;
import java.util.List;
public class ServiceImpl implements Service{
TasksDAO taskDAO;
public void setTasksDao(TasksDAO taskDAO)
{
this.taskDAO=taskDAO;
}
public List getTasksNames()
{
List tasks = taskDAO.getTasksNames();
return tasks;
}
}
You should have already noticed that the Service requires a TasksJdbcDAO. This object, in turn, implements the TasksDAO interface. So we will inject DAO to the service through the BeanFactory. We happen to have the TasksJdbcDAO class that the bean factory can use for this purpose. However, since this class extends from JdbcDaoSupport, we know we need to inject a DataSource, or have the bean factory inject that DataSource for us. The bean configuration file now looks like this:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
"http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
<bean id="dataSourceDBDirect"
class="org.springframework.jdbc.datasource.DriverManagerDataSource"
destroy-method="close">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
<property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl"/>
<property name="username" value="scott"/>
<property name="password" value="tiger"/>
</bean>
<bean id="tasksDAO" class="com.spring.jdbc.TasksJdbcDAO">
<property name="dataSource">
<ref local="dataSourceDBDirect"/>
</property>
</bean>
<bean id="service" class="com.spring.jdbc.ServiceImpl">
<property name="tasksDao">
<ref local="tasksDAO"/>
</property>
</bean>
</beans>
We see the Service bean gets the tasksDao bean injected, which in turn gets the dataSourceDBDirect object injected. When we ask for the Service bean, we get it with a DAO that is ready to go with a DataSource. Everything's ready to roll. So what happens when the Client accesses the bean container to get the Service object? The bean container instantiates and injects a DataSource and a TasksDAO before returning the Service to Client. Our Clientbecomes quite simple now. It needs to communicate with the BeanFactory, get hold of a Service object, and process it:
package com.spring.jdbc;
import java.util.Iterator;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class Client extends RuntimeException {
public static void main(String[] args) throws Exception {
ApplicationContext ctx = new ClassPathXmlApplicationContext(
"SpringConfig.xml");
Service service = (Service)ctx.getBean("service");
Iterator tskIter = service.getTasksNames().iterator();
while (tskIter.hasNext()) {
System.out.println(tskIter.next().toString());
}
}
}
You must have noticed Client extending RuntimeException. Spring throws RuntimeExceptions instead of checked Exceptions. RuntimeExceptions should not be caught. As it is a complex task to catch all of the exceptions in your code, the Spring developers decided to throw RuntimeExceptions so that if you do not catch an exception, your application will break and the user will get the application exception. Their second reasoning is that most exceptions are unrecoverable, so your application logic cannot deal with them anyway.
Other Advantages
Besides the advantages the Spring framework brings to JDBC, described in some detail above, there are several other advantages to using the Spring framework with your JDBC applications. These include the following:
- The Spring framework provides the
org.springframework.jdbc.support.nativejdbc.NativeJdbcExtractor interface and some implementations (such as SimpleNativeJdbcExtractor) of this interface. These are useful for accessing Oracle features via an Oracle connection or ResultSet when the connection is "wrapped" by another DataSource (such as that used with some application servers) or obtained through certain connection pools.
- For creating instances of
oracle.sql.BLOB (binary large object) and oracle.sql.CLOB(character large object), Spring provides the class org.springframework.jdbc.support.lob.OracleLobHandler.
- The Spring-provided
OracleSequenceMaxValueIncrementer class provides the next value of an Oracle sequence. It effectively provides the same information that would be provided if you used the following command directly: select someSequence.nextval from dual (where someSequence is the name of your sequence in the Oracle database). An advantage of this approach is that the DataFieldMaxValueIncrementer interface can be used in a DAO hierarchy without tight coupling of the Oracle-specific implementation.
Conclusion
This article has focused on the use of Spring to write JDBC code that is more maintainable and less error-prone. Spring JDBC provides benefits such as cleaner code, better exception and resource handling (at least than what I usually write using traditional JDBC), and the ability to really focus on the business problem instead of on plumbing code. It is noteworthy how much less code is required with the Spring framework to implement essentially the same functionality as with traditional JDBC.
Further Reading
Vikram Veeravelu is working with a Software company in Chennai.