Try-catch-finally and then again a try catch

Commons-io also has closeQuietly() for in and output streams. I'm using it all the time. It makes your code much more readable.

I usually did it this way:

try {
    try {
        ..
        stmts
        ...
    }
    finally {
       connection.close():
    }
} catch (Exception ex) {
     ..
     stmts
     ..    
}

I usually only used this when I wasn't using a library that took care of this plumbing for me.

As Imagist points out, this isn't technically the same as the finally will run before the catch but I think it solves the problem you were trying to solve.

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()
)
{
  ...
}

As others have mentioned, a static closeQuietly utility is the way to go. One thing to add - if you are in the world of java.io rather than java.sql then there is a useful interface for exactly this purpose - java.io.Closeable

All the data sources and sinks in java.io implement this interface - all streams, channels, writers and readers. That way you can create a single utility to cope with the same "exception on close()" issue without requiring many overloaded versions.

e.g.

public class IoUtils {

  public static closeQuietly (Closeable closeable) {
    try {
      closeable.close();
    } catch (IOException logAndContinue) {
      ...
    }
  }

}