Creating JDBC Templates With Java 5

Note: please excuse the code formatting. Apparently by beautifully formatted code, which looks great in Writely, looks pretty bad when I got it on to Blogger. I will try to clean it up in the next few days, but for now it will need to stay as it is.

In a recent Java project I was writing my Data Access Object which uses JDBC, and I wanted to simplify the code and promote code reuse. JDBC code typically contains a lot of duplicate code, which causes the following problems:

1. Duplicate code means a lot of copy and paste.
2. Duplicate code means more code to read and maintain.
3. Duplicate code means that if you need to make a change to it, you need to change it in multiple places.
   

This results in a lot of code that is difficult to maintain and difficult to keep consistent. Here is an example of some JDBC code with the associated error handling.

01 Connection conn = null;
02 PreparedStatement ps = null;
03 ResultSet rs = null;
04 Account result = null;
05
06 try {
07 conn = dataAccess.getConnection();
08 ps = conn.prepareStatement("select * from accounts where accountid=?");
09 ps.setLong(2353676);
10 rs = ps.executeQuery();
11
12 if (rs.next()) {
13 result = new Account();
14 result.setAccountId(rs.getLong("accountid"));
15 result.setAccountName(rs.getString("accountname"));
16 }
17 }
18 catch (Exception e) {
19 log.error(e);
20 }
21 finally {
22 try {
23 if (rs != null) rs.close;
24 }
25 catch (Exception e) {
26 log.warn(e);
27 }
28 try {
29 if (ps != null) ps.close;
30 }
31 catch (Exception e) {
32 log.warn(e);
33 }
34 try {
35 if (conn != null) conn.close;
36 }
37 catch (Exception e) {
38 log.warn(e);
39 }
40 }
41
42 return result;

Out of the 42 lines of code in the example above, only 7 lines (those in purple) aren't the usual boilerplate code. So the focus of this article is how to allow for reuse of the 35 lines of code that are required each time we want to access the database. We can do this by taking advantage of Anonymous Classes and a new Java 5 feature that allows for variable length argument lists.

What we want to build is something called a template. This template is just an abstract class that will have several methods, one for each step of performing a JDBC call. Lets take a look at what the template looks like, then discuss it.

import java.sql.ResultSet;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public abstract class JDBCTemplate
{
private static Log log = LogFactory.getLog(JDBCTemplate.class);

public void receiveResults (ResultSet rs)
{
return;
}

public void handleError (Exception ex)
{
log.error(ex);
}
}

We could have defined more steps, but in most cases this is probably all we need. We have a receiveResults() method that takes the results of a query and builds objects from the results. We also have a second method handleError() which we can override if we want to add some special error handling other than the default.

We also need to build a utility class to handle opening the connection, creating the prepared statement, and all of the duplicated code. The utility class will take as an argument a class that extends the abstract JDBCTemplate class, and use the template as part of the routine. Lets first take a look at how we might call the utility method, passing it an anonymous class.

01 final Account result = new Account();
02
03 DBUtils.executeQuery(dataSource, new JDBCTemplate()
04 {
05 public void receiveResults (ResultSet rs) throws Exception
06 {
07 if (rs.next()) {
08 result.setAccountId(rs.getLong("accountid"));
09 result.setAccountName(rs.getString("accountid"));
10 }
11 }
12 }, "select * from accounts where accountid=?", 2353676L);
13
14 return (result.getAccountId()==0 ? null : result);

These 14 lines replace the 42 lines from the first example. Line 1 creates our result object. We must declare this variable as final, otherwise Java will not allow us to access this object within our anonymous class. Because of this the return statement on line 14 differs from the first example, because we know that result will never be null, so we must rely on the key value of the property instead. Lines 3 through 12 are where we are calling our utility method with 4 arguments.

The first argument is our data source which is where the executeQuery() method will fetch a database connection from. The dataSource variable is a standard JDBC javax.sql.DataSource object, which in most cases will be a connection pool. The second argument is the anonymous class, high-lighted in pink, which overrides the receiveResults() method of our JDBCTemplate class. We don't override the handleError() method since it already does what we want. The syntax for creating an anonymous class instance is similar to creating a regular object instance except that you add a block of code following the constructor arguments. So this code is really creating an unnamed subclass of JDBCTemplate and creating an instance of it.

The 3rd argument to the utility method is the SQL statement to execute, and the last argument is the value to be passed in the placeholder of the SQL statement. There is a really good reason why we made this the last argument. Java 5 introduced variable length argument lists. Below is a short example of a method that takes any number of String arguments.

public void doStuff (String ... args) {
// do stuff
}

The ellipses (...) tells Java that this method takes any number of String arguments, and rolls them into an array variable named args. This means that this method can be called with no arguments (i.e. doStuff()), or one argument (i.e. doStuff("foo")), or even five arguments (i.e. doStuff("a", "b", "c", "d", "e")). The only special rule is that the variable length argument list must be the last parameter of the method. We will use this feature to allow for any number of arguments to be passed to our method.

Now, lets take a look at the DBUtils.executeQuery() method, the last step of our JDBC framework.

01 import java.sql.Connection;
02 import java.sql.PreparedStatement;
03 import java.sql.ResultSet;
04 import org.apache.commons.logging.Log;
05 import org.apache.commons.logging.LogFactory;
06 import javax.sql.DataAccess;
07
08 public class DBUtils
09 {
10 private static Log log = LogFactory.getLog(DBUtils.class);
11
12 public static void executeQuery (
13 DataAccess dataAccess,
14 JDBCTemplate tmpl,
15 String sql,
16 Object ... args)
17 {
18 Connection conn = null;
19 PreparedStatement ps = null;
20 ResultSet rs = null;
21
22 try {
23 conn = dataAccess.getConnection();
24 ps = conn.prepareStatement(sql);
25
26 for (int i = 0; i < args.length; i++) {
27 Object arg = args[i];
28
29 if (arg instanceof String) {
30 ps.setString(i+1, (String)arg);
31 }
32 if (arg instanceof Long) {
33 ps.setLong(i+1, (Long)arg);
34 }
35 if (arg instanceof Integer) {
36 ps.setInt(i+1, (Integer)arg);
37 }
38 }
39
40 rs = ps.executeQuery();
41
42 tmpl.receiveResults(rs);
43 }
44 catch (Exception e) {
45 log.error(e);
46 tmpl.fail(e);
47 }
48 finally {
49 try {
50 if (rs != null) {
51 rs.close();
52 }
53 } catch (Exception e) {
54 log.warn(e);
55 }
56 try {
57 if (ps != null) {
58 ps.close();
59 }
60 } catch (Exception e) {
61 log.warn(e);
62 }
63 try {
64 if (conn != null) {
65 conn.close();
66 }
67 } catch (Exception e) {
68 log.warn(e);
69 }
70 }
71 }
72
73 }

I hope that most of this will make sense, but there are a few key areas to point out.

1. Line 42 is where we call out template method receiveResults(). On line 46 we call handleError() in case an error occurs.
   
   
2. On Line we see that the last parameter of our method is Object ... args. We talked about variable length arguments above, but it might not be obvious why we made this an array of type Object, and why Java allows us to pass value of the primitive type long as a value. Parameters may be numbers or strings, so we chose type Object as the parent of the type of value that may be passed. In Java 5 we can now use primitive values where in Java 4 it was required to encapsulate it in an object. Java 5 allows this via a procedure called auto-boxing and auto-unboxing. Auto-boxing means that when you use a primitive where an object is required, Java will automatically convert the primitive into an object. For example the primitive value 3 will be converted to new Integer(3). Auto-unboxing is the exact opposite, where Java 5 will convert an object into a primitive, for example an Integer becomes an int.
   
   
3. On lines 26-38 we set the parameters of the prepared statement. We inspect each argument to determine the type of object, then use the appropriate set statement.
   

Although it took us quite a bit to build the framework, we can now reap the benefits. For example, if you wanted to added additional debugging to print out the SQL being executed, we now only need to do this in one place.

I hope that this article give you some ideas on how you can clean up not only your JDBC code, but other duplicated boilerplate code in your projects. For more ideas you might also want to take a look at the Template Method design pattern, which offers a similar mechanism to clean up your code.