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 < ?"; |
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 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.
IE 7
Firefox 2.0
