Java 7 Automatic Resource Management JDBC (try-with-resources statement)

Solution 1:

try(Connection con = getConnection()) {
   try (PreparedStatement prep = con.prepareConnection("Update ...")) {
       //prep.doSomething();
       //...
       //etc
       con.commit();
   } catch (SQLException e) {
       //any other actions necessary on failure
       con.rollback();
       //consider a re-throw, throwing a wrapping exception, etc
   }
}

According to the oracle documentation, you can combine a try-with-resources block with a regular try block. IMO, the above example captures the correct logic, which is:

• Attempt to close the PreparedStatement if nothing goes wrong
• If something goes wrong in the inner block, (no matter what is is) roll back the current transaction
• Attempt to close the connection no matter what
• If something goes wrong closing the connection, you can't rollback the transaction (as that's a method on the connection, which is now in indeterminate state), so don't try

In java 6 and earlier, I would do this with a triply nested set of try blocks (outer try-finally, middle try-catch, inner try-finally). ARM syntax does make this terser.

Solution 2:

IMO, declaring Connection and PreparedStatement outside try-catch is the best way available in this case.

Solution 3:

If you want to use pooled connection in transaction, you should use it in this way:

try (Connection conn = source.getConnection()) {
        conn.setAutoCommit(false);
        SQLException savedException = null;
        try {
            // Do things with connection in transaction here...
            conn.commit();
        } catch (SQLException ex) {
            savedException = ex;
            conn.rollback();
        } finally {
            conn.setAutoCommit(true);
            if(savedException != null) {
                throw savedException;
            }
        }
    } catch (SQLException ex1) {
        throw new DataManagerException(ex1);
    }

This sample code handles setting autocommit values.

NOTE, that using savedException does save exception in case that conn.rollback() throws another. This way, finally block will throw "right" exception.