Best way to work around SQLException

P

Pablo

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?

Regards,
P.
 
M

Manish Pandit

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
 
W

Wesley Hall

Pablo said:
Hi all,

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 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.
 
S

Simon Brooke

Pablo said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top