Hi,

The way I've been doing and seeing this done is via the finally block.
You are right about putting a try/catch in finally as well, but that is
the way it is - if you cannot even close a connection in finally, there
are bigger problems to worry about (from a practical standpoint). The
system could be in 'unrecoverable' state if that is the case. Here is a
snippet:

Connection conn = null;
try{
 conn = createConnection( );
 //do your thing
}catch(SQLException e){
  //log the error
  //wrap as custom exception if needed...
}finally{
 try{
   if (conn != null) conn.close( );
  }catch(SQLException e){
   //log this error - this could be fatal

  }
}

-cheers,
Manish

The accepted practice is to use a finally block. You are right about the
additional try/catch and this bothers me too. JDBC exception handling is
poorly thought out and this leads to messy calling code. There are
solutions to this (for example, the spring framework provides a JDBC
abstraction layer that tidys exception handling dramatically). You also
might want to consider having your data access methods declare 'throws
SQLException' to rethrow the exception (rather than the try/catch, in
each method). This would require the calling code to handle the
exception but this may not be advisable because the SQL exception may
represent a failed SQL command or failure to close the connection (more
poor design in the JDBC library, these should be distinct exception
types), the calling code would not be able to determine if the SQL was
successful or not (unless you use JTA which is a whole other subject).

For now, go with the finally block solution.

The general pattern is:

   Connection db = null;
   Statement state = null;

   try
   {
     db = ConnectionPool.getConnection( /* stuff */ );
     db.setAutoCommit( false);
     state = db.createStatement(  );

     /* do stuff */

     db.commit();
   }
   catch ( SQLException sex )
   {
     db.rollback();

     /* do what needs to be done to clean up, alert the user, etc */
   }
   finally
   {
     try
     {
       if ( state != null )
       {
         /* close down the statement */
         state.close(  );
       }

       if ( db != null )
       {
         /* release the database connection */
         db.close(  );
       }

     }
     catch ( Exception eek ) // really should not happen
     {
       /* panic */
     }
   }

It's /really/ important to clean up your connections, whether or not you're
using a connection pool (which, incidentally, you really should be).
Excess open connections use up a lot of resource server-side.