JDBC: Patterns and Anti-Patterns

Because of the nature of JDBC, the code for executing any given query is strikingly similar. There is a certain pattern that cannot be avoided when using this API directly. The driving factor is that the developer is directly manipulating, at least to some degree, the state of a remote resource and the state of the connection to that resource. Fortunately, the template is fairly straightforward.

A method to select information will look substantially like this:

public String getFooNameById(long id)
{
  String fooName = null;
  Connection connection = null;
  PreparedStatement preparedStatement = null; // PreparedStatements are compiled and cached and sometimes pooled.
                                           // Always favor them over Statements.
  ResultSet results = null;

  try
  {
     connection = _poolManager.getConnection(); // Always get the connection from some managed resource pool
     preparedStatement = connection.prepareStatement("select name from foo where id = ?");
     preparedStatement.setLong(1, new Long(id)); // JDBC is 1-indexed
     results = preparedStatement.executeQuery();

     if (results.next()) // Cursor always starts before the first row
        fooName = results.getString(1);
  }
  catch (SQLException ex)
  {
     _log.error("getFooNameById() caught an SQLException while attempting to retrieve data", ex);
  }
  finally // Resources must always be freed so execute in a finally block
  {
     try
     {
        // close entities in reverse order
        if (null != results)
           results.close();
        if (null != preparedStatement)
           preparedStatement.close();
        if (null != connection)
           connection.close(); // With a pool manager, this just returns the connection to the pool
     }
     catch (SQLException ex)
     {
        _log.warn("getFooNameById() caught an SQLException while freeing resources", ex);
     }
  }

  return fooName;
}

If you are slightly more paranoid, you would wrap each of the three closes in independent try-catch blocks. This method eats the data retrieval SQLException if it occurs and simply returns null. This is a policy decision. The method could just as easily allow the SQLException to propagate up the stack, or it could wrap it in another Exception subclass. You generally would not want to propagate the SQLException related to freeing resources. Regardless, the pattern remains largely the same. Inserts, updates and deletes are similar but would not involve a ResultSet.

Consider the following anti-patterns:

{
  Connection connection = ...;
  PreparedStatement preparedStatement = ...;
  ResultSet results = ...;
  return results.getString(1);
}

The above example always leaks a connection from its pool (or a socket connection if there is no pool, yet another anti-pattern) and a prepared statement from its pool (if there is one). It also leaves the cursor open on the database side because the PreparedStatement is never closed. This would usually be recognized very quickly, because the application would only be able to execute maxPoolSize queries, say 30 to 50, before running out of resources.

Consider something more subtle:

{
  Connection connection = ...;
  PreparedStatement preparedStatement = ...;
  ResultSet results = ...;
  String fooName = results.getString(1);

  results.close();
  preparedStatement.close();
  connection.close();

  return fooName;
}

This example again leaks everything, but only if an exception is thrown past the close() statements. This could slowly leak resources depending on usage.

Last one:

{
  ...
  try
  {
     connection = ...;
     preparedStatement = ...;
     results = ...;
     fooName = results.getString(1);
  }
  catch (...) {}
  finally
  {
     connection.close();
  }

  return fooName;
}

This example is almost correct, and it may work with some JDBC drivers. The vendor may opt for Connection.close() to propagate down to subordinate resources. However, this is not required, and it's a bad habit to start. I believe this works fine with MySQL. With Oracle it leaks memory. I'm not sure if it leaks cursors. This could be really interesting to find if the application supports multiple database platforms, but the one that doesn't leak is more favored in development.

Hopefully this will save someone a couple of hours of frustration. If you have a fair amount of database access code to write, you might consider using an OR mapper such as Hibernate. This approach hides some of the repititive, error-prone tasks associated with JDBC.