Database exception handling

When working with databases (Connection, Statement, ResultSets) you might have run into problems because of wrong excpetion handling. If done like in the following example, you will most definitly run into problems as soon as an Exception is thrown:

Connection dbConnection = DbConnectionManager.getInstance().getConn();

if (dbConnection != null) {
  String sqlStatement = "...";

  try{
    Statement stmt = dbConnection.createStatement();
    // do something (which might also throw an SQLException)

    try{ 
      ResultSet rs = stmt.executeQuery(sqlStatement);
      // do something (which might also throw an SQLException)
      rs.close();
    } catch (SQLException e2) {
      // handle exception
    }
    stmt.close();
  }catch(SQLException e1){
    // handle exception
  }
}

Just imagine an exception being thrown after executing a query and successfully creating a ResultSet. Due to the exception the ResultSet will not be closed. This may very well result in an ORA-01000: maximum open cursors exceeded Exception. That is why it is always a good idea to close your resources in a finally block. The finally block ensure, that code is processed even if an unexpected exception occurs. (It is always executed when the try block exits).

Connection dbConnection = DbConnectionManager.getInstance().getConn();

if (dbConnection != null) {
  String sqlStatement = "...";

  try{
    Statement stmt = dbConnection.createStatement();
    // do something (which might also throw an SQLException)

    ResultSet rs = stmt.executeQuery(sqlStatement); 
    try { 
      // do something (which might also throw an SQLException)
    } catch (SQLException e2) {
      // handle exception
    } finally {
      rs.close(); // is closed even if an exception is thrown 
                  // while processing the ResultSet
    }
  } catch(SQLException e1) {
    // handle exception
  } finally {
    stmt.close(); // is closed even if an exception is thrown 
                  // when calling executeQuery
  } 
}