SQLExecutor - A Simple, Open Source JDBC Framework

Introduction

Writing succinct and elegant JDBC code can be difficult. You have to create a few different objects and catch numerous checked exceptions, even if you can't reasonably recover from them. Closing a connection requires writing a nested try-catch block. And you have to write ugly if-else or switch statements in your catch blocks just to examine the SQL error codes. Moreover, since these SQL error codes are database specific, examining them in your Java code destroys the very platform independence that defines Java. You can live with this awkward limitation of JDBC when you 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 the breeding ground of unnecessary bugs.

Existing Frameworks

I looked at some already existing JDBC frameworks ^{2 - 4}, but they either didn't provide the exception handling flexibility I wanted or they were overly complicated to use. The JDBC Spring 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.

SQLExecutor

SQLExecutor is a light weight, open source JDBC framework that makes it easier to write concise, readable, and database independent JDBC code. SQLExecutor automatically translates checked exceptions such as SQLException into unchecked (runtime) exceptions, enabling you to catch only those exceptions you want to catch. And instead of littering your program with database specific code that checks for arcane SQL error codes, you can call methods that abstract away the specifics of a particular database. For example, instead of checking to see if the SQL error code is 1407, you can call a method like isDataIntegrityViolation(). SQLExecutor also provides a simplified interface to transaction management, connection pooling, and stored procedure/function calls.

Installing and Using SQLExecutor

Installing SQLExecutor is simple: just download the JAR file from sourceforge.net and add it to your classpath. The complete source code, including an Eclipse project and Ant build.xml file (which generates copious javadocs), is also available for download. This source code includes a class called SampleCodeUsingFramework that includes many static methods illustrating the various ways the framework can be used. SQLExecutor currently supports Oracle and MySQL. To use the framework with a different database, you simply extend DatabaseException with a concrete exception class and implement the abstract methods that decipher the database error codes.

Example One: JDBC code vs. SQLExecutor Code

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 the code that creates the database connection is moved into another method (or class), the program will still be bloated with unnecessary catch blocks and a superfluous PreparedStatement object.

Here is the same SELECT statement code using SQLExecutor:

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

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

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

  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. You aren't required to catch exceptions you don't want to handle. There is no need to create an intermediate, PreparedStatement, object. And since the framework generates runtime exceptions, you are empowered to either catch recoverable exceptions, or just let the exceptions bubble up to the calling code block.

Example Two: JDBC code vs. SQLExecutor Code

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. SQLExecutor abstracts away this complexity by providing simple methods such as isNonExistentTableOrView() and isDataIntegrityViolation() to determine the root cause of a JDBC error. In contrast, standard JDBC code is 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;
    }
}

The same code using SQLExecutor will look like:

catch (DatabaseException e)
{
    if (e.isDataIntegrityViolation())
        applyDataIntegrityViolationRecovery();
    else if (e.isDeadlockOrTimedOut())
        applyDeadlockRecovery();
}

Note that this code is database independent and much more readable.

An Overview Of The Framework Classes

The main framework class, SQLExecutor, 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.

Parameters are passed into SQLExecutor as objects via an overloaded method, addParam(Object param), which accepts Integer, String, Double, Float, Long, Boolean, and Date objects as parameters as well as their simple type equivalents. 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 unchecked (runtime) exceptions. Thus the framework user is not required to write meaningless catch blocks each time he accesses a field in the result set.

Here is an example illustrating how to pass parameters into a query and 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 an 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, the framework also provides an overridden toString() method in SQLResults that 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

Examples illustrating other framework features can be found in the documentation on sourceforge.net (search keyword: SQLExecutor).

Summary

Since developers can learn to use this simple framework in about five to ten minutes, it is worth their investment in time to reap the rewards of concise and readable code. Moreover, with the database independent exception code, it is easy to port 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.

Author Information

Jeff Smith is the lead Java architect and president of SoftTech Design, Inc, a web and software development company located near Denver, Colorado. Jeff has authored articles for Java World, Developer's Network Journal, and Developer.com. He can be reached at [email protected] or through his company website at www.SoftTechDesign.com.

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