SQLExecutor: A Simple JDBC Framework  (by Jeff S Smith, jeff@SoftTechDesign.com)

Update: SQLExecutor now supports Oracle, MySQL, PostgreSQL, and HSQL (Hypersonic)
 

While designing an SQL intensive J2EE application for a client, I decided that I needed to streamline the bloated JDBC code required to execute SQL statements. While JDBC is a powerful, low level API for accessing and manipulating a myriad of SQL databases . (reference 1), it is so low level that it requires:

1)      writing verbose finally blocks to close database connections

2)      writing catch blocks for all checked exceptions, even if they can't be reasonably recovered from.  For example, programs typically can't recover from bad SQL grammar or a non-existent view errors-nor should they. So why should programmers be forced to write exception blocks to catch them?

3)      Adding clumsy if else or switch statements that examine SQLState or error code values to determine the type of exception generated

4)      Writing custom connection or connection pooling code

 

For example, here is some standard JDBC code that selects records from a table and displays them via System.out:

 

public static void testStandardJDBC()

{

    String sql = "SELECT * FROM JDBC_TEST WHERE TEST_ID < ? AND TEST_DT < ?";

 

    Connection con = null;

    try

    {

        Class.forName(driverName).newInstance();

        con = DriverManager.getConnection(connURL, username, password);

 

        PreparedStatement ps = con.prepareStatement(sql);

        ps.setInt(1, 8);

        ps.setDate(2, Date.valueOf("2003-05-10"));

        ResultSet rs = ps.executeQuery();

 

        String out = "SQL RESULTS:\n";

        while (rs.next())  //still have records...

          out += rs.getLong("TEST_ID") + " " + rs.getString("NOTES") + " " +

                 rs.getDate("TEST_DT") + " " + rs.getDouble("AMOUNT") + " " +

                 rs.getString("CODE") + "\n";

        System.out.println(out);

    }

    catch (SQLException sqle)

    {

        sqle.printStackTrace();

    }

    catch (ClassNotFoundException cnfe)

    {

        cnfe.printStackTrace();

    }

    catch (Exception e)

    {

        e.printStackTrace();

    }

    finally

    {

        try

        {

            if (con != null)

                con.close();

        }

        catch (SQLException sqle)

        {

            sqle.printStackTrace();

        }

    }

}

 

The only code in this method that is really doing anything useful is the highlighted code. The rest is just JDBC overhead. Even if you can place the code that creates the database connection in another method (or class), you will still be left with bloated code that catches exceptions for which you don't want to write recovery code.

 

Programmers can live with this awkward limitation of JDBC when they only have one or two sections of JDBC code. But if your JDBC application has code like this in 100 places, you're going to have a lot of superfluous code that can be breeding ground of unnecessary bugs. A good JDBC framework could eliminate these problems.

 

I looked at some already existing JDBC frameworks (references 2-4), but they either didn't provide the exception handling flexibility I wanted or they were overly complicated to use. Rod Johnson's JDBC framework fell into the latter category-excellent in many respects, but it required creating a subclass for each query, learning a somewhat complicated exception hierarchy, and requiring framework users to implement anonymous inner classes. I wanted to create a useful framework for both advanced Java programmers who only have 5 minutes to learn a new framework, and for beginner/intermediate Java programmers who may find it difficult to understand a more complicated framework.

 

When I designed the SQLExecutor framework, my primary focus was on creating a very simple interface to the JDBC API. I wanted to minimize the number of classes and methods the framework user needed to learn as well as reduce the number of lines of code required to execute SQL statements. I did not try to complicate the framework by supporting every arcane use of JDBC-there may be instances when you will need to write low level JDBC code in order to accomplish an unusual task. That said, the framework should successfully execute the vast majority of SQL you throw at it.

 

Here is the same SELECT statement code using my simple JDBC framework:

 

public static void testSimpleSelectWithParams()

{

    String sql = "SELECT * FROM JDBC_TEST WHERE TEST_ID < ? AND TEST_DT < ?";

    ConnectionPool conPool = new ConnectionPool(1, driverName, conURL, username, password);

    SQLExecutor sqlExec = new SQLExecutor(conPool);

    sqlExec.addParam(8);                            //add int param with value = 8

    sqlExec.addParam(Date.valueOf("2003-05-10"));   //add date param with value 5/10/03

    SQLResults res = sqlExec.runQueryCloseCon(sql); //runs the query, closes connection

    System.out.println(res.toString());  //display entire result set in tabular form

}

Note that every line of code is doing something useful. The database connection is abstracted away in the new ConnectionPool() call. There is no code for closing the connection as it is closed automatically in the runQueryCloseCon() method. The programmer isn't required to catch exceptions he doesn't want to handle. Since the framework generates runtime exceptions, the programmer is empowered to either catch recoverable exceptions, or just let the exceptions bubble up to the calling code block. He is not forced to write unnecessary catch blocks.

 

Another problem inherent in standard JDBC is the difficulty in determining the exact cause of a database exception without resorting to messy if else or switch statements to decipher the database error code. My exception classes provide simple methods such as isNonExistentTableOrView() and isDataIntegrityViolation() to enable the framework user to determine the exact cause of the JDBC failure so he can implement the appropriate recovery strategy. To this end, the abstract exception superclass, DatabaseException, extends the RuntimeException class.

 

Modifying The Framework To Support A New Database

 

For each new database you want the framework to support, you simply extend  DatabaseException with a concrete exception class and implement the abstract methods which decipher the database error codes. The framework currently provides an OracleException class and a MySQLException class. There is no need to edit any of the primary framework classes to support the new database type because all DatabaseExceptions are created by an intermediary ExceptionFactory class which automatically translates the generic DatabaseException to a specific subclass (such as an OracleException). Hence the main framework classes are database agnostic. For example, the runQuery() method in the SQLExecutor class throws an exception like so:

 

catch (SQLException e)

{ 

    throw ExceptionFactory.getException(dbType, e.getMessage() + "SQL Failed: " + sql, e);

}

 

At runtime, the stack trace on this exception looks like an OracleException:

com.cexp.wms.jdbc.OracleException: ORA-00936: missing expression

SQL Failed: select dd * from JDBC_TEST where CODE < 'E'

       at com.cexp.wms.jdbc.ExceptionFactory.getException(ExceptionFactory.java:23)

       at com.cexp.wms.jdbc.SQLExecutor.runQuery(SQLExecutor.java:217)

       at com.cexp.wms.jdbc.SampleCodeUsingFramework.test(SampleCodeUsingFramework.java:377)

       at com.cexp.wms.jdbc.SampleCodeUsingFramework.main(SampleCodeUsingFramework.java:422)

Exception in thread "main"

 

The steps involved in extending the framework to support a new database are:

1)      Extend DatabaseException with the new database class (e.g. PostgresException) and implement the abstract methods

2)      Add new constants for the database in interface DatabaseType

3)      Add a new entry to the switch statements in ExceptionFactory

There is no need to rewrite any other framework code to support the new database type.

 

DatabaseException defines the following abstract methods:

 

public abstract boolean isDataIntegrityViolation();

public abstract boolean isBadSQLGrammar();

public abstract boolean isNonExistentTableOrView();

public abstract boolean isInvalidBindVariableName();

public abstract boolean isDatabaseUnavailable();

public abstract boolean isDeadlockOrTimedOut();

 

To use these exception methods, your client code might implement a catch block like:

 

catch (DatabaseException e)

{

    if (e.isDataIntegrityViolation())

        applyDataIntegrityViolationRecovery();

    else if (e.isDeadlockOrTimedOut())   

        applyDeadlockRecovery();

}

 

Note that this code is database independent and succinct. Without the framework, your program would be hardcoded to work with a specific database and might look something like:

 

catch (SQLException e)

{

    int oracleErrorCode = e.getErrorCode();

    switch (oracleErrorCode)

    {

        case 1:

        case 1407: applyDataIntegrityViolationRecovery();

                   break;

 

        case 104:

        case 1013: 

        case 2087:

        case 60:   applyDeadlockRecovery();

                   break;

    }

}

 

Besides being database independent, the code using the JDBC framework is much simpler and readable.

 

Instead of using methods to determine the exact cause of a database exception, I thought about creating a separate exception class for every type of exception and leaving it to the framework user to catch the desired exceptions. But I felt that solution didn't offer any additional value and required adding many additional classes to the package. Implementing lots of exception classes would complicate supporting additional databases and require framework users to learn the names of many exceptions. I felt it was simpler to allow programmers to catch a single exception (DatabaseException), and then use the code completion feature of an IDE like Eclipse to discover the methods available to find out more specific information about an exception. Hence I implemented this simpler exception handling paradigm.

 

An Overview Of The Framework Classes

 

In many database applications, the most time intensive program code is the code that establishes database connections. For example, it might take one second to establish a database connection, but only fifty milliseconds to execute the query. By creating a pool of connections at application startup and then reusing them throughout the program, you can drastically improve application performance. The framework class, ConnectionPool, manages a pool of JDBC database connections for you. You specify the number of connections to open (and add to the pool), with the first parameter you pass into the constructor. You can also resize the connection pool at runtime via the resizeConnectionPool() method. If your code attempts to get a connection and none are available, a new connection is automatically created and added to the pool. By abstracting away this low level connection process from the client code, framework users don't need to clutter their application with complex and redundant connection code.

 

If you are using a J2EE application server (like JBOSS or Weblogic), you are probably using the intrinsic connection pool  and don't need the one provided in the ConnectionPool class. If this is the case and you don't want the framework to implement connection pooling, you can pass in numPooledCon = 1 to the ConnectionPool constructor to disable the feature. Alternatively, you can simply pass in your own custom created java.sql.Connection object to an overloaded version of the constructor. (Note that I did not implement the ConnectionPool class using a javax.sql.Datasource because Datasource wasn't added until JDK 1.4 and I didn't want my framework to be incompatible with the millions of programmers still using JDK 1.3).

 

The main framework class is SQLExecutor; it contains methods for adding parameters to a query, executing queries with a variety of options, returning SQL result sets, and implementing transaction management. The SQLExecutor constructor takes a single parameter: the ConnectionPool object to use for its connection. Instead of throwing checked exceptions (exceptions you are forced to catch), SQLExecutor throws exceptions that are a subclass of DatabaseException,such as OracleException or MySQLException. These exceptions are created by an intermediary ExceptionFactory class which generates the appropriate exception depending on the current database type. If the database type is ORACLE, then an OracleException is created. If the database type is MYSQL, then a MySQLException is created. But regardless of how many databases are supported by the framework, the framework user need only worry about catching a single exception, DatabaseException, in his code.

 

Parameters are passed into SQLExecutor as objects via the addParam(Object param) method and stored internally in an ArrayList. You can pass in Integer, String, Double, Float, Long, Boolean, and Date objects as parameters. For example:

  sqlExecutor.addParam(10);                                                     //adds the integer value 10 as a parameter

  sqlExecutor.addParam(Date.valueOf("2003-05-10")); //adds date as a parameter

  sqlExecutor.addParam("JOHN");                     //adds String as a parameter

 

Queries that return data (i.e. SELECT statements) return a SQLResults object. The SQLResults object maps the fields from the query to simple Java objects and provides access methods such as getInt(), getString(), getBoolean(), getTime(), getTimestamp(), getDouble(), getFloat(), and getDate(). These methods should look familiar to JDBC programmers because they mirror the same methods in the ResultSet class. The difference is that any exceptions generated from accessing the SQLResults fields are descendants of DatabaseException and hence are runtime exceptions. Thus the framework user is not required to write meaningless catch blocks each time he accesses a field in the result set. Note: the SQLResults class has a toList() method to return the SQLResults as a List.

 

Here is a query example illustrating how to pass parameters into a query and how to loop through the result set:

 

public static void testSimpleSelectWithParams()

{

    SQLExecutor sqlExec = new SQLExecutor(getConnectionPool());

    sqlExec.addParam(8);

    sqlExec.addParam(Date.valueOf("2003-05-10"));

    SQLResults res = sqlExec.runQueryCloseCon("SELECT * FROM JDBC_TEST WHERE TEST_ID = ? " +

                                              "AND TEST_DT < ?");

    String out = "SQL Results:\n";

    for (int row=0; row < res.getRowCount(); row++)

        out += res.getLong(row, "TEST_ID") + " " + res.getString(row, "NOTES") + " " +

               res.getDate(row, "TEST_DT") + " " + res.getDouble(row, "AMOUNT") + " " +

               res.getString(row, "CODE") + "\n";

    System.out.println(out);

}

 

This code gets a connection by calling getConnectionPool(), creates a SQLExecutor object, adds a couple of parameters to it, then runs the select statement via runQueryCloseCon(). The results of the query are passed back as a SQLResults object, which is iterated through in a simple for loop. Each field is accessed by calling the appropriate getter method and passing in the column name--for example, getDate(row, "TEST_DT"). Alternatively, the framework provides overloaded versions of each getter method that take a column index as a parameter instead of a column name. And for debugging convenience, I've also provided an overridden toString() method in SQLResults which formats the entire result set as a text table. For example, a single call to SQLResults.toString() returns something like:

 

TEST_ID     NOTES       TEST_DT     AMOUNT      CODE      

----------------------------------------------------------

2.0         two         2003-03-10  99.8        X         

6.0         six         2003-03-13  54.3501     X          

 

Transactions

 

The SQLExecutor class also provides an interface for JDBC transactions. For greater simplicity, I designed the framework to use the same methods, runQuery() and runQueryCloseCon(), for both SELECTS and UPDATES, INSERTS, and DELETES. There is no need to call different methods for different operations. Here is a code sample that does a couple of updates within the context of a transaction:

 

public static void testMultipleUpdatesAndTrans()

{

    SQLExecutor sqlExec = new SQLExecutor(getConnectionPool());

    try

    {

        sqlExec.setAutoCommit(false);

        sqlExec.addParam(7);

        sqlExec.runQuery("UPDATE JDBC_TEST SET CODE = 'Z' WHERE TEST_ID = ?");

        sqlExec.addParam(6);

        sqlExec.runQuery("UPDATE JDBC_TEST SET CODE = 'E' WHERE TEST_ID = ?");

       

        sqlExec.commitTrans();

    }

    catch (DatabaseException e)

    {

        sqlExec.rollbackTrans();

    }

    finally

    {           

        sqlExec.closeConnection();

    }

}

 

The first line of this method creates a SQLExecutor object. Next it disables auto commit, so our updates run within a transaction. Next it adds the parameters and executes the first SQL update statement, using the runQuery () method. This method runs the first query but does not commit the transaction or close the connection. Since we are using the runQuery() method (which doesn't automatically close the connection), we must close the connection ourselves in a try finally block. Note that both the runQuery() and runQueryCloseCon() methods automatically clear out the parameter list after executing, so there is no need to do that manually with an extra line of code.

 

Next, the code adds a parameter to the sqlExec object and runs the second query. If no exception occurs, the transaction is committed. If a DatabaseException is thrown, the transaction is rolled back in the catch block. Note that the framework user need only catch DatabaseException-he doesn't need to catch one of the database specific subclasses (such as OracleException). In the finally block, the connection is closed regardless of whether or not an exception was thrown.

 

Note that the framework only re-prepares the SQL statement (inside the second runQuery() call) if the SQL statement has changed. If the SQL statement hasn't changed, then the statement prepared in the first call to runQuery() is re-used.

 

As an alternative to JDBC transactions, programmers using an app server like JBOSS or Weblogic could ignore the framework's JDBC transaction support and use the standard J2EE JTA API or EJB CMT to manage transactions in a J2EE container. By implementing transaction support at the J2EE container level, the J2EE container can marshal multiple resources in the same transaction and roll back all operations if necessary.

 

Stored Procedures and Functions

 

The framework also simplifies calling stored procedures and functions. You use the same SQLExecutor object, add any parameters by calling the addParam() method, and then call the runStoredProc() method. A simple example might look like:

 

SQLExecutor sqlExec = new SQLExecutor(getConnectionPool());

sqlExec.addParam(8);

sqlExec.addParam("DOCUMENT");

sqlExec.runStoredProc("StoredProcName");

 

The framework also supports calling stored procedures with output (OUT) parameters. To add an output parameter, you call the addStoredProcOutParam() method instead of calling addParam(). Here is an example of calling a stored procedure which takes two input parameters and one output parameter:

 

SQLExecutor sqlExec = new SQLExecutor(getConnectionPool());

sqlExec.addParam("Jeff");

sqlExec.addParam("Smith");

//it doesn't matter what you put here as long as it is of type String

//assuming this stored proc has this OUT parameter defined as VARCHAR2)

sqlExec.addStoredProcOutParam("fullname"); //dummy param of type String  sqlExec.runStoredProc("jdbc_proc_test2");

System.out.println("Your full name is " + res.getString(0, 2));

 

If you want to automatically close the connection after executing the stored procedure, you use the runStoredProcCloseCon() method instead of runStoredProc(). You can also use the runQuery() method to execute stored procedures and functions that don't use output parameters by imbedding the call in a SQL statement. Here is an example that illustrates calling an Oracle stored function:

sqlExec.runQuery("SELECT myStoredFunction() AS MyCount FROM DUAL");

 

Streaming Large Result Sets

 

SQLExecutor also supports streaming large result sets (so you don't have to store them in RAM via a SQLResults object). To this end, SQLExecutor includes a runQueryStreamResults() method. You can use it like so:


String sql = "select CARRIER_ID, NAME from carrier";
SQLExecutor sqlExec = new SQLExecutor(getConnectionPool());
ResultSet rs = sqlExec.runQueryStreamResults(sql);
try
{
  while (rs.next()) //still have records...
    System.out.println(rs.getString("CARRIER_ID") + " " + rs.getString("NAME"));
}
catch (SQLException sqle) { }

 

The Source Code

 

The source code includes an Eclipse project as well as an Ant build.xml file with tasks for building, cleaning, and generating the javadocs for the framework. The source code package also includes a class called  SampleCodeUsingFramework that includes many static methods illustrating the various ways the framework can be used.

 

 

Summary

 

Using the SQLExecutor JDBC framework will result in concise, readable, and maintainable Java code, with fewer database connection cleanup errors. Moreover, with the database independent exception code, it will be easy to port your applications to additional databases and to determine the root cause of database exceptions without resorting to ugly if else or switch statements in a catch block.

 

References

 

1) "Sun Microsystems JDBC website", http://java.sun.com/products/jdbc/

2) "Clever Facade Makes JDBC Look Easy" by Thomas Davis, Java World, May 1999

3) "Eliminate JDBC Overhead" by Ryan Daigle, Java World, May 2002

4) "A Generic JDBC Abstraction Framework" by Rod Johnson in his book J2EE Design and Development, Wrox Press, 2002

UML Class Diagram