Eliminate JDBC overhead
A reusable, flexible SQL architecture tackles JDBC programming inefficiencies
While developing a small suite of J2EE (Java 2 Platform, Enterprise Edition) applications for my company recently, I grew frustrated with the bloated process of executing and handling SQL calls. I was sure that somebody in the Java developer family must have developed a framework to clean up the process. Yet searches on such terms as “Java SQL framework” and “JDBC [Java Database Connectivity] framework” yielded no satisfactory results.
The article “Clever Facade Makes JDBC Look Easy” by Thomas Davis (JavaWorld, May 1999), addressed some, but not all, my needs. Specifically, the JDBC Facade he described fails to handle transactions, factor out the building of prepared statements, and cleanly handle the result set. Therefore, I took it upon myself to develop a simple architecture that included those functionalities and eliminated all that displeased me. This article outlines that architecture.
What’s the problem?
Before we outline a solution, let’s outline the problem. What hoops do you normally have to jump through to execute a SQL query against a JDBC data source?
- Form a SQL string
- Get a connection
- Get a prepared statement
- Populate the prepared statement with values
- Execute the statement
- Iterate through the result set and form the result objects
Throughout it all, you must worry about those incessant SQLExceptions; if any of those steps exist in different places, the SQLExecptions’ overhead compounds because you must employ multiple try/catch blocks.
Yet, when we look closely at our steps, we see that several parts of this process don’t vary between executions: You always use the same mechanism to get a connection and a prepared statement. Populating the prepared statement always happens in the same manner, and executing and processing the query is a given. You can factor out three of the six steps. Even within the varied steps, areas exist where we can factor common functionality. So how can we automate and simplify this process?
Query framework
To start, let’s define the method signature that we will use to execute a SQL statement. Keeping it simple and passing in only the needed variables, we could concoct a signature similar to the following:
public Object[] executeQuery(String sql, Object[] pStmntValues,
ResultProcessor processor);
We know that the only variances between executions are the SQL statement, the prepared statement value, and how the result set is parsed. The sql argument is obviously the SQL statement. The pStmntValues object array contains the values that must be inserted into the resulting prepared statement. And the processor argument is an object that processes the result set and returns the resulting objects; I’ll discuss that object in more detail later.
So in this one method signature, we have isolated the three constant parts of every JDBC database interaction. Now let’s examine the guts of exeuteQuery() and its other supporting methods, all of which are part of a SQLProcessor class:
public class SQLProcessor {
public Object[] executeQuery(String sql, Object[] pStmntValues,
ResultProcessor processor) {
//Get a connection (assume it's part of a ConnectionManager class)
Connection conn = ConnectionManager.getConnection();
//Hand off our connection to the method that will actually execute
//the call
Object[] results = handleQuery(sql, pStmntValues, processor, conn);
//Close the connection
closeConn(conn);
//And return its results
return results;
}
protected Object[] handleQuery(String sql, Object[] pStmntValues,
ResultProcessor processor, Connection conn) {
//Get a prepared statement to use
PreparedStatement stmnt = null;
try {
//Get an actual prepared statement
stmnt = conn.prepareStatement(sql);
//Attempt to stuff this statement with the given values. If
//no values were given, then we can skip this step.
if(pStmntValues != null) {
PreparedStatementFactory.buildStatement(stmnt, pStmntValues);
}
//Attempt to execute the statement
ResultSet rs = stmnt.executeQuery();
//Get the results from this query
Object[] results = processor.process(rs);
//Close out the statement only. The connection will be closed by the
//caller.
closeStmnt(stmnt);
//Return the results
return results;
//Any SQL exceptions that occur should be recast to our runtime query
//exception and thrown from here
} catch(SQLException e) {
String message = "Could not perform the query for " + sql;
//Close out all resources on an exception
closeConn(conn);
closeStmnt(stmnt);
//And rethrow as our runtime exception
throw new DatabaseQueryException(message);
}
}
}
...
}
Two parts of these methods might prove unclear: the PreparedStatementFactory.buildStatement() and handleQuery()‘s processor.process() method calls. buildStatement() simply places each object of the argument object array into its indexed place in the given prepared statement. For instance:
...
//Loop through all objects of the values array, and set the value
//of the prepared statement using the value array index
for(int i = 0; i < values.length; i++) {
//If the object is our representation of a null value, then handle it separately
if(value instanceof NullSQLType) {
stmnt.setNull(i + 1, ((NullSQLType) value).getFieldType());
} else {
stmnt.setObject(i + 1, value);
}
}
Because no stmnt.setObject(int index, Object value) method can take a null object value, to add this functionality, we must use our own special construct: the NullSQLType class. NullSQLType denotes a null statement placeholder and also contains that field’s JDBC type. When a NullSQLType object instantiates, it takes the SQL type of the field in which it will be placed. As we can see above, when the prepared statement is populated with a NullSQLType, you can use the NullSQLType‘s field type to tell the prepared statement the field’s JDBC type. So, in summary, you use the NullSQLType both to alert when a null value is being used to populate a prepared statement and to store the JDBC type of the field it represents. (You can download the source to this simple class from Resources.)
Now that I have explained the PreparedStatementFactory.buildStatement() logic, I’ll explain the other missing piece: processor.process(). The processor is of type ResultProcessor, an interface that represents the class that builds the expected domain objects from the query’s result set. ResultProcessor contains one simple method that returns an array of resulting objects:
public interface ResultProcessor {
public Object[] process(ResultSet rs) throws SQLException;
}
A typical result processor loops through the given result set and forms domain objects/object structures from the result set’s rows. I now present a real-world example to pull all these pieces together.
Example query
Often, you need to retrieve a User object from the database based on a user information table. Let’s assume the following USERS table:
|
And let’s assume a fairly simple User object with the following constructor:
public User(int id, String userName, String firstName,
String lastName, String email)
If we weren’t using this article’s framework, we would have one large method that handles all aspects of retrieving the user information from the database and forming that User object. Now what needs to happen with our framework?
First, we form our SQL statement:
private static final String SQL_GET_USER = "SELECT * FROM USERS WHERE ID = ?";
Next, we form the ResultProcessor, which we use to take the result set and form a User object:
public class UserResultProcessor implements ResultProcessor {
//Column definitions here (i.e., COLUMN_USERNAME, etc...)
..
public Object[] process(ResultSet rs) throws SQLException {
//Where we will collect all returned users
List users = new ArrayList();
User user = null;
//If there were results returned, then process them
while(rs.next()) {
user = new User(rs.getInt(COLUMN_ID), rs.getString(COLUMN_USERNAME),
rs.getString(COLUMN_FIRST_NAME), rs.getString(COLUMN_LAST_NAME),
rs.getString(COLUMN_EMAIL));
users.add(user);
}
return users.toArray(new User[users.size()]);
Finally, we write the single method that executes the query and returns our User object in one step:
public User getUser(int userId) {
//Get a SQL processor and execute the query
SQLProcessor processor = new SQLProcessor();
Object[] users = processor.executeQuery(SQL_GET_USER_BY_ID,
new Object[] {new Integer(userId)},
new UserResultProcessor());
//And just return the first User object
return (User) users[0];
}
That’s all there is to it. With one processor class and one simple method, we have eliminated the need for direct connection manipulation, statements, and exceptions. In addition, if we have another query that gets a row from the user table through a different query, such as by username and password, we can reuse the UserResultProcessor. We just need to insert a different SQL statement and reuse the previous method’s user processor. Since the returned row’s metadata doesn’t depend on the query, we can heavily reuse result processors.
Update framework
But what about updates? We handle them similarly, with just a few tweaks. First, we must add two new methods to our SQLProcessor class. They resemble the executeQuery() and handleQuery() methods, except you don’t need to fool with a result set, only the number of rows updated as a result of the call:
public void executeUpdate(String sql, Object[] pStmntValues,
UpdateProcessor processor) {
//Get a connection
Connection conn = ConnectionManager.getConnection();
//Send it off to be executed
handleUpdate(sql, pStmntValues, processor, conn);
//Close the connection
closeConn(conn);
}
protected void handleUpdate(String sql, Object[] pStmntValues,
UpdateProcessor processor, Connection conn) {
//Get a prepared statement to use
PreparedStatement stmnt = null;
try {
//Get an actual prepared statement
stmnt = conn.prepareStatement(sql);
//Attempt to stuff this statement with the given values. If
//no values were given, then we can skip this step.
if(pStmntValues != null) {
PreparedStatementFactory.buildStatement(stmnt, pStmntValues);
}
//Attempt to execute the statement
int rows = stmnt.executeUpdate();
//Now hand off the number of rows updated to the processor
processor.process(rows);
//Close out the statement only. The connection will be closed by the
//caller.
closeStmnt(stmnt);
//Any SQL exceptions that occur should be recast to our runtime query
//exception and thrown from here
} catch(SQLException e) {
String message = "Could not perform the update for " + sql;
//Close out all resources on an exception
closeConn(conn);
closeStmnt(stmnt);
//And rethrow as our exception
throw new DatabaseUpdateException(message);
}
}
These methods and the query-handling methods differ only in how they handle the call’s result: Since an update returns only the number of rows updated, we no longer need a result processor. We could easily ignore the number of updated rows, but cases might arise where we want to ensure an update occurred. UpdateProcessor takes the number of rows that were updated and can perform any type of validation or logging on the number of rows:
public interface UpdateProcessor {
public void process(int rows);
}
If an update call must update at least one row, then an object implementing UpdateProcessor can check the number of rows updated and throw a domain-specific exception if no rows were updated. Or, possibly, all that’s needed is to log the number of rows updated, initiate a resulting process, or fire an update event. You can code these cases into your custom UpdateProcessor. You get the idea: the possibilities are unlimited and can be easily integrated with the framework.
Update example
I’ll reuse our User model explained above to show how an update of a user’s information would occur:
First, form the SQL statement:
private static final String SQL_UPDATE_USER = "UPDATE USERS SET USERNAME = ?, " +
"F_NAME = ?, " +
"L_NAME = ?, " +
"EMAIL = ? " +
"WHERE ID = ?";
Next, form the UpdateProcessor, which we use to examine the number of updated rows and throw an exception if no rows were updated:
public class MandatoryUpdateProcessor implements UpdateProcessor {
public void process(int rows) {
if(rows < 1) {
String message = "There were no rows updated as a result of this operation.";
throw new IllegalStateException(message);
}
}
}
Finally, write the single method that executes the update in one step:
public static void updateUser(User user) {
SQLProcessor sqlProcessor = new SQLProcessor();
//Use our get user SQL statement
sqlProcessor.executeUpdate(SQL_UPDATE_USER,
new Object[] {user.getUserName(),
user.getFirstName(),
user.getLastName(),
user.getEmail(),
new Integer(user.getId())},
new MandatoryUpdateProcessor());
}
And just like that, we’ve executed an update without ever directly handling SQLExceptions or Connections.
Transactions
Earlier, I said that I found other SQL framework implementations lacking because they didn’t populate prepared statements, consolidate and separate result set processing, or handle transactions. We’ve resolved populating prepared statements with the buildStatement() utility method, and the various processors have factored out the results’ processing. However, how does our framework handle transactions?
A transaction differs from an independent SQL call only in that it uses the same connection throughout its life—and, of course, the auto commit flag must be off. So we need a way to specify that a transaction has started and when it has ended. Throughout that transaction lifecycle, it uses the same connection, and the commit initiates when the transaction ends.
To handle transactions, we can reuse much of SQLProcessor. You might wonder why I separated that class’s executeUpdate() and handleUpdate() methods when I could have easily combined them into one method. I did that to separate connection management from the actual SQL execution. That proves handy when building a transaction system that must retain control of the connection across several SQL executions.
For a transaction to work, we must maintain state, specifically the connection state. Until this point, the SQLProcessor has been a stateless class. It lacks member variables. To reuse everything we’ve done with SQLProcessor, we create a transaction wrapper that takes a SQLProcessor and transparently handles the transaction lifecycle.
Let’s see what that looks like:
public class SQLTransaction {
private SQLProcessor sqlProcessor;
private Connection conn;
//Assume constructor that initializes the connection and sets auto commit to false
...
public void executeUpdate(String sql, Object[] pStmntValues,
UpdateProcessor processor) {
//Try and get the results. If an update fails, then rollback
//the transaction and rethrow the exception.
try {
sqlProcessor.handleUpdate(sql, pStmntValues, processor, conn);
} catch(DatabaseUpdateException e) {
rollbackTransaction();
throw e;
}
}
public void commitTransaction() {
//Try to commit and release all resources
try {
conn.commit();
sqlProcessor.closeConn(conn);
//If something happens, then attempt a rollback and release resources
} catch(Exception e) {
rollbackTransaction();
throw new DatabaseUpdateException("Could not commit the current transaction.");
}
}
private void rollbackTransaction() {
//Try to rollback and release all resources
try {
conn.rollback();
conn.setAutoCommit(true);
sqlProcessor.closeConn(conn);
//If something happens, then just swallow it
} catch(SQLException e) {
sqlProcessor.closeConn(conn);
}
}
}
SQLTransaction has many new methods, but most of them are simple and handle only mundane aspects of connection and transaction handling. Throughout the transaction’s life, this transaction wrapper adds to the SQLProcessor only a simple connection management. When a transaction starts, it retrieves a new connection, and its auto commit property sets to false. Every execution after that uses that same connection (passing into our SQLProcessor‘s handleUpdate() method), so the transaction remains intact.
Only when your persistence object or method calls commitTransaction() will the transaction commit and the connection close. If, at any point, something happens with one of the executions, the SQLTransaction catches the offending exception, automatically orders a rollback, and rethrows the exception.
Example transaction
Let’s demonstrate a simple transaction:
//Reuse the SQL_UPDATE_USER statement defined above
public static void updateUsers(User[] users) {
//Get our transaction
SQLTransaction trans = sqlProcessor.startTransaction();
//For each user, update it
User user = null;
for(int i = 0; i < users.length; i++) {
user = users[i];
trans.executeUpdate(SQL_UPDATE_USER,
new Object[] {user.getUserName(),
user.getFirstName(),
user.getLastName(),
user.getEmail(),
new Integer(user.getId())},
new MandatoryUpdateProcessor());
}
//Now commit the transaction
trans.commitTransaction();
}
Granted, the above illustrates a trivial transaction example, but it shows how one would work. If anything fails within each of the executeUpdate() method calls, an exception is thrown after the transaction rolls back. This calling method’s developer never needs to worry about handling rollbacks or whether the connection closes. That’s all handled behind the scenes. The developer needs to focus only on the business logic.
The transaction could have easily handled a query piece, but I left it out since transactions usually consist of a sequence of updates.
Issues
As I wrote this article, I stumbled upon several areas where I questioned the framework. I outline those issues here in anticipation that some of you will have the same questions.
Custom connections
What if a connection retrieval is not the same for every transaction? What if the ConnectionManager needs certain variables to tell it which connection pool to retrieve from? You can easily incorporate these valid scenarios into this framework. The executeQuery() and executeUpdate() methods (of both the SQLProcessor and SQLTransaction classes) would need to take in these custom connection arguments and pass them on the ConnectionManager. Remember that all the connection management should occur within the execute methods.
Alternatively, and in a much more object-oriented fashion, a connection factory could pass into the SQLProcessor on instantiation. You would then need a SQLProcessor instance for every different connection factory type. Depending on your connections’ variability, this might not prove ideal.
ResultProcessor return type
Why does the ResultProcessor interface specify that the process() method should return an array of objects? Why not use a List? In most of the applications I developed with this framework, the SQL queries returned only one object. It seemed too much overhead to form a List and then add that one object to it. Returning an array of one object was just simpler and easier to return. However, if object collections apply to your scenario, then returning a List over an array makes perfect sense.
SQLProcessor instance management
Most of this articles’ examples instantiate a SQLProcessor for each method that must execute a SQL call. Since SQLProcessors are completely stateless, it makes sense for the processor to be a singleton shared among the calling methods.
The SQLTransaction class, however, lacks state, so it must not be used as a singleton. Rather than learning how to instantiate a SQLTransaction, I suggest adding a simple method to the SQLProcessor class that looks like this:
public SQLTransaction startTransaction() {
return new SQLTransaction(this);
}
This keeps all transaction functionality accessible within the SQLProcessor class and limits the method calls you must know.
Database exceptions
I use several different types of database exceptions to wrap any SQLExceptions that occur at runtime. In the applications to which I applied this framework, I found it most convenient to make those exceptions runtime exceptions so they bubble up to an exception handler. You could argue that these exceptions should be declarative so they can be handled as close to the point of error as possible. However, that would make the SQL execution process almost as cumbersome as the original SQLExceptions, something we try explicitly to avoid.
Hassle-free JDBC programming
The framework outlined in this article allows queries, updates, and transactions to execute without the hassle and redundancy typically experienced with such operations. The only variance between similar SQL calls is now whittled down to one method with simple, reusable supporting classes. My hope is that this framework addresses many of the inefficiencies you have encountered in your JDBC programming.
