Olaf Heimburger's Blog

Although this should be common sense, it is worth to remind you on this again...

To catch or not to catch?

Working with exceptions is a common task for Java developers. It is so common that even the experienced ones make errors. Let's explore the possible pitfalls with JDBC samples.

First Try: Exception Propagation

A method which uses JDBC methods but don't like to handle the exception is the first code to look for. Here is an example:

public doIt(DataSource ds) throws SQLException {
  Connection conn = ds.getConnection();
  Statement stmt = conn.createStatement();
  // ... do some work
  stmt.close();
  conn.close();
}

Compiles & works. And has possible resource leaks. Yes, every line might throw an SQLException and the JDBC resources are not properly closed.

Next Try: Finally block

To get around we can put the code in question into a finally block to be sure that the close() methods are called in any case.

public doIt(DataSource ds) throws SQLException {
  Connection conn = null;
  Statement stmt = null;
  try {
    conn = ds.getConnection();
    stmt = conn.createStatement();
    // ... do some work
  } finally  {
    try {
      stmt.close();
      conn.close();
    } catch (SQLException ignore) {
      // gracefully ignored
    }
  }
}

Much better, but resource leaks are still there. stmt.close() can throw an exception and will leave the connection open. Worse, stmt and conn could be null pointers...

Final Approach: Prevent NullPointerException also.

public doIt(DataSource ds) throws SQLException {
  Connection conn = null;
  Statement stmt = null;
  try {
    conn = ds.getConnection();
    stmt = conn.createStatement();
    // ... do some work
  } finally  {
    try {
      if (stmt != null) {
        stmt.close();
      }
    } catch (SQLException ignore) {
      // gracefully ignored
    }
    try {
      if (conn != null) {
        conn.close();
      }
    } catch (SQLException ignore) {
      // gracefully ignored
    }
  }
}

Wow, this is a lot of typing but correct. But, be aware of copy & past programming here. Every time you need this you just do the same.

Final Final Approach: Use Helper Methods

To reduce code size and avoid DRY (Don't Repeat Yourself) you can use a helper method. Luckily this was done by someone else already. here is the same example with Apache Commons DbUtils.

public doIt(DataSource ds) throws SQLException {
  Connection conn = null;
  Statement stmt = null;
  try {
    conn = ds.getConnection();
    stmt = conn.createStatement();
    // ... do some work
  } finally  {
    DbUtils.closeQuietly(conn, stmt, null);
  }
}

There is also the Spring JDBC Template you can use to avoid resource leaks. Either way helps you to save time and avoid resource leaks.