Re: Best way to work around SQLException

From:

Simon Brooke <simon@jasmine.org.uk>

Newsgroups:

comp.lang.java.programmer

Date:

Thu, 30 Nov 2006 17:22:54 +0000

Message-ID:

<ekg344-53t.ln1@gododdin.internal.jasmine.org.uk>

in message <1164836036.774955.65450@n67g2000cwd.googlegroups.com>, Pablo
('gomesp@gmail.com') wrote:

Hi all,

Let's say I have the following code:

try {
PreparedStatement insInvoice = conn.prepareStatement( "INSERT INTO
...");

insInvoice.executeUpdate();
conn.commit();
insInvoice.close();
conn.close();
}
catch(SQLException ex)
{
//...
}

I just would like to know the following: If I have an exception before
the "close()" method calls, is it going to leave the connection opened?
I could put the "close()" calls in a "finally" block, but I would have
to, again make another try-catch block somewhere (either inside the
finally or in the caller method).

What is the best approach?

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.

--
simon@jasmine.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/
  Anagram: I'm soon broke.