Know you JDBC!

Posted on September 28, 2015
Tags: java, JDBC

Please, for your own good and for the poor soul who will have to debug and maintain your code: if you use bare bones JDBC, learn how it works!

I’ve recently spent an entire day debugging a whole application searching for connection leaks that regularly took down an application in production more or less every hour… an experience that I don’t recomment to anyone. Investing some time on learning how JDBC works will save you and your team a lot of time and stress.

The basic workflow is something like:

  1. Acquire a Connection.
  2. Create a PreparedStatement.
  3. If there are any, bind the query parameters.
  4. Execute the PreparedStatement.
  5. If the query is a SELECT, process the ResultSet.
  6. Release all resources calling #close() on them in reverse order.

The last step is especially important, since JDBC won’t automatically release the acquired resources (Statement and Connection). Even if you force the GC.

The bad news is that in every step along the way JDBC can throw a SQLException, which is checked. It doesn’t matter how far you got in the execution/release process, you should always release every statement and connection claimed. This gets especially ugly in Java 6-, where you need a lot of error-prone try-catch-fu. Here is a simple example:

Connection        conn  = null;
PreparedStatement s     = null;
List<String>      names = new ArrayList<String>();
try {
  conn = DriverManager.getConnection("jdbc:h2:mem:exercise_db;DB_CLOSE_DELAY=-1");
  s = conn.prepareStatement("SELECT name FROM people");
  ResultSet rs = s.executeQuery();
  while (rs.next()) {
    names.add(rs.getString(1));
  }
} catch (SQLException ex1) {
  ex1.printStackTrace();
} finally {
  if (s != null) {
    try {
      s.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
  if (conn != null) {
    try {
      conn.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}

You need to close not only the Connection, but also the PreparedStatement. ResultSet is automatically closed when a PreparedStatement is closed or used to execute another query.

This is much better in Java 7+, where you can use the try-with-resource statement:

List<String> names = new ArrayList<>();
try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:exercise_db;DB_CLOSE_DELAY=-1");
     PreparedStatement s = conn.prepareStatement("SELECT name FROM people")) {
  ResultSet rs = s.executeQuery();
  while (rs.next()) {
    names.add(rs.getString(1));
  }
} catch (SQLException ex1) {
  ex1.printStackTrace();
}

Since both Connection and PreparedStatement are AutoCloseable, they are automatically managed by the VM and you don’t need to explicitly close them.

Even if you use a connection pool, you should always close Connections and PreparedStatements: since Connection is an interface, a connection pool will usually return an implementation than on #close() will do the right thing (either actually close it, or return it to the pool).

Resources

Take a look at this resources to correctly use SQL and JDBC and you won’t suffer needlessly: