Eliminate JDBC overhead

A reusable, flexible SQL architecture tackles JDBC programming inefficiencies

By Ryan Daigle, JavaWorld.com, 05/24/02

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?

1. Form a SQL string
2. Get a connection
3. Get a prepared statement
4. Populate the prepared statement with values
5. Execute the statement
6. 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:

Free Download - 5 Minute Product Review. When slow equals Off: Manage the complexity of Web applications - Symphoniq

Free Download - 5 Minute Product Review. Realize the benefits of real user monitoring in less than an hour. - Symphoniq