Correct Exception Handling

The main challenge in writing robust JDBC code is ensuring correct exception handling. Most JDBC API methods can throw the checked java.sql.SQLException. Not only must we ensure that we catch this exception, we must ensure that cleanup occurs even if an exception is thrown.

The following code listing, which performs a trivial SELECT, shows a common JDBC error, with serious consequences:

public String getPassword(String forename) throws ApplicationException {

 String sql = "SELECT password FROM customer WHERE forename= ' " +
               forename + " ' ";

 String password = null;
 Connection con = null;
 Statement s = null;
 ResultSet rs = null;
 try {
     con = ;
   s = con.createStatement();
   rs = s.executeQuery (sql);

   while (rs.next()) {
     password = rs.getString(1);
   }
   rs. close();
   s. close();
   con.close(); 

 } catch (SQLException ex) {
   throw new ApplicationException ("Couldn't run query [" + sql + "]", ex);
 }
 return password;
}
    

Code like this can crash a whole application, and even bring down a database. The problem is that each of the highlighted lines can throw a SQLException. If this happens, we will fail to close the Connection, as the connection is closed only if we reach the last line of the try block. The result will be a "leaked" connection, stolen from the application server's connection pool. Although the server may eventually detect the problem and release the stolen connection, the impact on other users of the connection pool may be severe. Each connection costs the RDBMS resources to maintain, and pooled connections are a valuable resource. Eventually all connections in the pool may become unavailable, bringing the application server to a halt, and the application server may be unable to increase the size of the pool because the database can support no more concurrent connections.

ref : Expert One-on-One J2EE Design and Development, by Rod Johnson