October 22, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

SQLExecutor: A Simple JDBC Framework

  • April 11, 2003
  • By Jeff Smith
  • Send Email »
  • More Articles »

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:

  1. Extend DatabaseException with the new database class (for example 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 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.





Page 2 of 3



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel