java - Try-catch-finally and then again a try catch

Question

I have often come across situations like :-

try{ 
     ...
     stmts
     ...
} 
catch(Exception ex) {
     ... 
     stmts
     ... 
} finally {
     connection.close // throws an exception
}

which still needs a try - catch block inside finally.

What is the best practice to overcome this?

Answer 1

Write a SQLUtils class that contains static closeQuietly methods that catch and log such exceptions, then use as appropriate.

You'll end up with something that reads like this:

public class SQLUtils 
{
  private static Log log = LogFactory.getLog(SQLUtils.class);

  public static void closeQuietly(Connection connection)
  {
    try
    {
      if (connection != null)
      {
        connection.close();
      }
    }
    catch (SQLExcetpion e)
    {
      log.error("An error occurred closing connection.", e);
    }
  }

  public static void closeQuietly(Statement statement)
  {
    try
    {
      if (statement!= null)
      {
        statement.close();
      }
    }
    catch (SQLExcetpion e)
    {
      log.error("An error occurred closing statement.", e);
    }
  }

  public static void closeQuietly(ResultSet resultSet)
  {
    try
    {
      if (resultSet!= null)
      {
        resultSet.close();
      }
    }
    catch (SQLExcetpion e)
    {
      log.error("An error occurred closing result set.", e);
    }
  }
}

And your client code will be something like:

Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try 
{
  connection = getConnection();
  statement = connection.prepareStatement(...);
  resultSet = statement.executeQuery();

  ...
}
finally
{
  SQLUtils.closeQuietly(resultSet);
  SQLUtils.closeQuietly(statment);
  SQLUtils.closeQuietly(connection);
}

Update: since Java 7, the various JDBC interfaces extend java.lang.AutoCloseable and while the above code answers the original question, if you're writing code directly against the JDBC API, it can now be structured:

try (
  Connection connection = getConnection();
  PreparedStatement statement = connection.prepareStatement(...);
  ResultSet resultSet = statement.executeQuery()
)
{
  ...
}