A Java JDBC exception example showing try, catch, and finally

Java/JDBC try/catch/finally exception FAQ: Can you show me a decent example of how to catch a JDBC exception in a try/catch/finally block?

Beauty is in the eye of the beholder, but here's a JDBC example showing some Java code that uses a try/catch/finally block with a Java JDBC query:

    public String getContactEmail(int contactNumber)
    throws SQLException
    {
      Connection connection = null;
      PreparedStatement doSelect = null;
      try
      {
        String contactEmail = null;
        String selectStatement = "SELECT contact_email FROM store WHERE contact_id = ?";
        connection = ConnectionPool.getConnection();
        doSelect = connection.prepareStatement(selectStatement);
        doSelect.setInt(1,contactNumber);
        ResultSet rs = doSelect.executeQuery();
        if ( rs.next() )
        {
          contactEmail = rs.getString(1);
          return contactEmail;
        }
        else
        {
          return null;
        }
      }
      catch (SQLException se)
      {
        // log the exception
        log.error(se);
        // re-throw the exception
        throw se;
      }
      finally
      {
        try
        {
          doSelect.close();
          ConnectionPool.freeConnection(connection);
        }
        catch (Exception e)
        {
        }
      }
    }

The query occurs in the try block. The catch block catches any SQLException's that occur, print an error message, then throw the exception. The finally clause makes sure all the necessary resources are cleaned up in the end. In this clause we catch but discard any exceptions that may occur as we attempt to clean up the resources we've used.

It looks like you get your database connection from an imported class named ConnectionPool?

Yes.

It looks like the pool lets you get a connection, and then later it looks like you "free" the connection?

Yes again. "Freeing" a connection typically means it is returned to the pool.

Should I always close() my resources and free my connection in the finally clause?

How you close a connection -- and whether you close a connection -- depends a great deal on your connection pooling software, but yes, if you are supposed to call close() on a connection, this is a good place to do this, because you are always guaranteed that the finally clause will run, even if the code in your try block throws an exception.