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: