ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.

advertisement

AddThis Social Bookmark Button

Tuning JDBC: Measuring JDBC performance
Pages: 1, 2, 3

Wrapping the Connection class

We'll start by wrapping the Connection class. The following ConnectionWrapper class implements Connection. The class has one instance variable of Connection type, and a constructor that initializes that instance variable with the constructor parameter. Most of the Connection methods are simply defined to delegate the call to the instance variable:



package tuning.jdbc;

import java.sql.*;
import java.util.Map;

public class ConnectionWrapper implements Connection
{
  protected Connection realConnection;

  public Connection realConnection () {
    return realConnection;
  }

  public ConnectionWrapper (Connection Connection) {
    realConnection = Connection;
  }

  public void clearWarnings() throws SQLException {
    realConnection.clearWarnings();
  }

  public void close() throws SQLException {
    realConnection.close();
  }

  public boolean isClosed() throws SQLException {
    return realConnection.isClosed();
  }

   public void commit() throws SQLException {
    realConnection.commit();
  }
  
  ...

Related Reading

Java Performance TuningJava Performance Tuning
By Jack Shirazi
Table of Contents
Index
Sample Chapter
Full Description
Read Online -- Safari

I have left out most of the methods, but they follow the template of the ones shown here. Where previously you would have used a Connection object obtained from the database driver, you instead simply wrap that Connection object with the ConnectionWrapper, and use the ConnectionWrapper object. Wherever you obtain your Connection object, you simply need to add one extra line like the one shown here:

Connection dbConnection = getConnectionFromDriver();
  dbConnection = new ConnectionWrapper(dbConnection);

Obtaining connections is really the only part of the application that needs changing. This will require all the calls that obtain a Connection to be found and edited; however, most applications use a central factory class to provide their Connection objects, and in these cases it is very simple to add the ConnectionWrapper. The factory class frequently fronts a pool of connections, and there may be a little extra work to release the Connection back into the pool, since it will first need to be unwrapped, for example:

public static void releaseConnection(Connection conn)
{
  if (conn instanceof ConnectionWrapper)
    conn = ( (ConnectionWrapper) conn).realConnection();
  //carry on with original release code
  ...
}

We havent actually finished with the ConnectionWrapper class yet. There are some methods of the ConnectionWrapper class that are not simple delegations. These are the methods that provide various types of Statement objects:

public Statement createStatement() throws SQLException {
    return new StatementWrapper(realConnection.createStatement(), this);
  }

  public Statement createStatement(int resultSetType,
              int resultSetConcurrency) throws SQLException {
    return new StatementWrapper(
          realConnection.createStatement(resultSetType,
             resultSetConcurrency), this);
  }

  public CallableStatement prepareCall(String sql) throws SQLException {
    return new CallableStatementWrapper(
          realConnection.prepareCall(sql), this, sql);
  }

  public CallableStatement prepareCall(String sql, int resultSetType,
             int resultSetConcurrency) throws SQLException {
    return new CallableStatementWrapper(
          realConnection.prepareCall(sql, resultSetType,
                resultSetConcurrency), this, sql);
  }

  public PreparedStatement prepareStatement(String sql)
        throws SQLException {
    return new PreparedStatementWrapper(
          realConnection.prepareStatement(sql), this, sql);
  }

  public PreparedStatement prepareStatement(String sql, int resultSetType,
        int resultSetConcurrency) throws SQLException {
    return new PreparedStatementWrapper(
          realConnection.prepareStatement(sql, resultSetType,
               resultSetConcurrency), this, sql);
  }

As you can see, we have three types of Statement wrapper classes we need to define. In addition, there is one other wrapper class we need, for the DatabaseMetaData. This wrapper class is required for completeness, because DatabaseMetaData can return the Connection object used to create the DatabaseMetaData, so we need to make sure that the Connection object is our wrapped one, not the original unwrapped Connection.

  public DatabaseMetaData getMetaData() throws SQLException {
    return new DatabaseMetaDataWrapper(
          realConnection.getMetaData(), this);
  }

Wrapping the statement classes

The three statement classes, Statement, PreparedStatement, and CallableStatement, have similar simple wrappers that forward all of the calls:

public class StatementWrapper implements Statement
{
  protected Statement realStatement;
  protected ConnectionWrapper connectionParent;

  public StatementWrapper(Statement statement, ConnectionWrapper parent)
  {
    realStatement = statement;
    connectionParent = parent;
  }

  public void cancel() throws SQLException {
    realStatement.cancel();
  }

  ...

I've chosen to implement the PreparedStatementWrapper as a subclass of StatementWrapper, but that isn't a requrement. You could have PreparedStatement as a subclass of Object and implement all of the required methods rather than inherit the Statement methods:

public class PreparedStatementWrapper extends StatementWrapper implements PreparedStatement
{
  PreparedStatement realPreparedStatement;
  String sql;
  public PreparedStatementWrapper(PreparedStatement statement, ConnectionWrapper parent, String sql)
  {
    super(statement, parent);
    realPreparedStatement = statement;
    this.sql = sql;
  }

  public void addBatch() throws SQLException {
    realPreparedStatement.addBatch();
  }

Similarly, I've chosen to implement the CallableStatementWrapper as a subclass of PreparedStatementWrapper:

public class CallableStatementWrapper extends PreparedStatementWrapper implements CallableStatement
{
  CallableStatement realCallableStatement;
  public CallableStatementWrapper(CallableStatement statement, ConnectionWrapper parent, String sql)
  {
    super(statement, parent, sql);
    realCallableStatement = statement;
  }

  public Array getArray(int i) throws SQLException {
    return new SQLArrayWrapper(realCallableStatement.getArray(i), this, sql);
  }

Once again, we haven't quite finished. There are several kinds of methods in these Statement wrapper classes which should not be simple delegations. First, there is a method that returns the Connection object. We want to return the ConnectionWrapper instead, but that is easy enough. Here is the method from StatementWrapper:

  public Connection getConnection() throws SQLException {
    return connectionParent;
  }

Second, we have methods that return ResultSets. These methods need to return ResultSet wrappers. In order to keep the ResultSetWrapper consistent, I've added a lastSqlString instance variable to StatementWrapper, which is passed to the ResultSetWrapper constructor. This instance variable will become useful when we come to assigning measurements to particular SQL statements. The methods that return ResultsSets are:

//StatementWrapper method
  public ResultSet getResultSet() throws SQLException {
    return new ResultSetWrapper(realStatement.getResultSet(), this, lastSql);
  }

  public ResultSet executeQuery(String sql) throws SQLException {
    return new ResultSetWrapper(realStatement.executeQuery(sql), this, sql);
  }

//PreparedStatementWrapper method
  public ResultSet executeQuery() throws SQLException {
    return new ResultSetWrapper(realPreparedStatement.executeQuery(), this, sql);
  }

Third, some methods use java.sql.Array objects. Because these Array objects can return a ResultSet, we again need to provide an Array wrapper so that ResultSetWrapper objects will be returned rather than plain ResultSets. We also need to handle the case where an Array object is passed in to the setArray() method: if it is an Array wrapper, the object needs to be unwrapped before being passed to the underlying PreparedStatement:

public void setArray(int i, Array x) throws SQLException {
    if (x instanceof SQLArrayWrapper)
      realPreparedStatement.setArray(i, ((SQLArrayWrapper) x).realArray);
    else
      realPreparedStatement.setArray(i, x);
  }

public Array getArray(int i) throws SQLException {
    return new SQLArrayWrapper(realCallableStatement.getArray(i), this, sql);
  }

Finally, the reason we are creating all these wrapper classes is to enable measurements to be taken. The methods that execute the SQL statements are, reasonably enough, called execute-something. We need to add logging into these methods. Note that I delegate responsibility for logging to a JDBCLogger class in the following methods. Essentially, each method has a call to the real execute method wrapped with a logging call. I pass the sql string and the current thread to the logging call because these are both very useful parameters for any type of logging, especially measuring the time taken for the procedure to run. Note also that I've redefined the executeQuery() methods that return ResultSets (which were first defined a couple of code fragments back) so that they now have logging inserted:

//StatementWrapper methods
  public void addBatch(String sql) throws SQLException {
    realStatement.addBatch(sql);
    lastSql = sql;
  }

  public boolean execute(String sql) throws SQLException {
    Thread t = Thread.currentThread();
    JDBCLogger.startLogSqlQuery(t, sql);
    boolean b = realStatement.execute(sql);
    JDBCLogger.endLogSqlQuery(t, sql);
    lastSql = sql;
    return b;
  }

  public int[] executeBatch() throws SQLException {
    Thread t = Thread.currentThread();
    JDBCLogger.startLogSqlQuery(t, "batch");
    int[] i = realStatement.executeBatch();
    JDBCLogger.endLogSqlQuery(t, "batch");
    return i;
  }

  public ResultSet executeQuery(String sql) throws SQLException {
    Thread t = Thread.currentThread();
    JDBCLogger.startLogSqlQuery(t, sql);
    ResultSet r = realStatement.executeQuery(sql);
    JDBCLogger.endLogSqlQuery(t, sql);
    lastSql = sql;
    return new ResultSetWrapper(r, this, sql);
  }

  public int executeUpdate(String sql) throws SQLException {
    Thread t = Thread.currentThread();
    JDBCLogger.startLogSqlQuery(t, sql);
    int i = realStatement.executeUpdate(sql);
    JDBCLogger.endLogSqlQuery(t, sql);
    lastSql = sql;
    return i;
  }


//PreparedStatementWrapper methods
  public boolean execute() throws SQLException {
    Thread t = Thread.currentThread();
    JDBCLogger.startLogSqlQuery(t, sql);
    boolean b = realPreparedStatement.execute();
    JDBCLogger.endLogSqlQuery(t, sql);
    return b;
  }

  public ResultSet executeQuery() throws SQLException {
    Thread t = Thread.currentThread();
    JDBCLogger.startLogSqlQuery(t, sql);
    ResultSet r = realPreparedStatement.executeQuery();
    JDBCLogger.endLogSqlQuery(t, sql);
    return new ResultSetWrapper(r, this, sql);
  }

  public int executeUpdate() throws SQLException {
    Thread t = Thread.currentThread();
    JDBCLogger.startLogSqlQuery(t, sql);
    int i = realPreparedStatement.executeUpdate();
    JDBCLogger.endLogSqlQuery(t, sql);
    return i;
  }

Pages: 1, 2, 3

Next Pagearrow