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 databases1, it is so low level that it requires:
- Writing verbose finally blocks to close database connections
- 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?
- Adding clumsy if else or switch statements that examine SQLState or error code values to determine the type of exception generated
- 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 < ?"; |
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 have only 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 a breeding ground for unnecessary bugs. A good JDBC framework could eliminate these problems.
I looked at some already existing JDBC frameworks2, 3, 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 five 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(new Integer(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 because it is closed automatically in the runQueryCloseCon() method. The programmer isn't required to catch exceptions he doesn't want to handle. Because 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 that 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 that 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:
- Extend DatabaseException with the new database class (for example PostgresException) and implement the abstract methods
- Add new constants for the database in interface DatabaseType
- 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 this:
...
catch (DatabaseException e)
{
if (e.isDataIntegrityViolation())
applyDataIntegrityViolationRecovery();
else if (e.isDeadlockOrTimedOut())
applyDeadlockRecovery();
}
Note: 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 this:
... 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 such as 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 (such as 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: 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 that generates the appropriate exception depending on the current database type. If the database type is ORACLE, an OracleException is created. If the database type is MYSQL, 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:
//adds the integer value 10 as a parameter
sqlExecutor.addParam(new Integer(10));
//adds date as a parameter
sqlExecutor.addParam(Date.valueOf("2003-05-10"));
//adds String as a parameter
sqlExecutor.addParam("JOHN");
Queries that return data (such as 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 therefore 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.
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(new Integer(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, and 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 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
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(new Integer(7));
sqlExec.runQuery("UPDATE JDBC_TEST SET CODE = 'Z'
WHERE TEST_ID = ?");
sqlExec.addParam(new Integer(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 an SQLExecutor object. Next, it disables auto commit, so our updates run within a transaction. Then, 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. Because 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: 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: The framework user needs only to 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.
As an alternative to JDBC transactions, programmers using an app server such as 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(new Integer(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 that 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");
Download 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.
You can download the source code from here.
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
- Sun Microsystems JDBC web site, http://java.sun.com/products/jdbc/
- "Clever Façade Makes JDBC Look Easy" by Thomas Davis, Java World, May 1999
- "Eliminate JDBC Overhead" by Ryan Daigle, Java World, May 2002
- "A Generic JDBC Abstraction Framework" by Rod Johnson in his book J2EE Design and Development, Wrox Press, 2002
About the Author
Jeff Smith is the President of SoftTech Design, Inc., a web and software development company located near Denver, Colorado. Jeff has a BA in Physics from the University of Virginia and has fifteen years of software/database/Web development experience as well as six years of experience as a Delphi and Java instructor. He has written numerous games and home entertainment programs, as well as authored articles on genetic algorithms. He can be reached at jeff@SoftTechDesign.com or through his Web site at www.SoftTechDesign.com.
UML Class Diagram
