Transaction rollback on SQLException using new try-with-resources block

Question

I have a problem with try-with-resources and I am asking just to be sure. Can I use it, if I need to react on exception, and I still need the resource in catch block? Example given is this:

try (java.sql.Connection con = createConnection())
{
    con.setAutoCommit(false);
    Statement stm = con.createStatement();
    stm.execute(someQuery); // causes SQLException
}
catch(SQLException ex)
{
    con.rollback();
    // do other stuff
}

I fear that I am still doomed to use the old try-catch-finally in this case, even according to oracle documentation - "catch and finally blocks in a try-with-resources statement, any catch or finally block is run after the resources declared have been closed."

Answer 1

According to the language spec, the connection will be closed before the catch clause is executed (http://docs.oracle.com/javase/specs/jls/se7/html/jls-14.html#jls-14.20.3.2).

A possible solution is to nest try-with-resources statements:

try (java.sql.Connection con = createConnection())
{
    con.setAutoCommit(false);
    try (Statement stm = con.createStatement())
    {
        stm.execute(someQuery); // causes SQLException
    }
    catch(SQLException ex)
    {
        con.rollback();
        con.setAutoCommit(true);
        throw ex;
    }
    con.commit();
    con.setAutoCommit(true);
}

Hopefully that illustrates the point. This should be improved quite a bit if you plan on using it in production code.

For instance, if you are using a connection pool, then you must return the connection as you got it, so con.setAutoCommit(true); should be done in a finally clause. This would mean that the outer try-with-resources should be a traditional try-catch-finally.

Edit (2018)

I see people commenting on this still, so I thought I'd give it a 2018 reply. I am not working in Java anymore, mainly been working in Scala, Clojure and Kotlin, and this code has not been tested, so please treat this as just another example. However since Java has lambdas, I think the following approach is a lot better. And I've done similar things in production code in these other languages.

In this approach there is a inTransaction function handling all the nasty transaction stuff. But usage is pretty simple.

public class Foo {

    interface ConnectionProvider {
        Connection get() throws SQLException;
    }

    public static <A> A doInTransation(ConnectionProvider connectionProvider, Function<Connection, A> f) throws SQLException {
        Connection connection = null;
        A returnValue;
        boolean initialAutocommit = false;
        try {
            connection = connectionProvider.get();
            initialAutocommit = connection.getAutoCommit();
            connection.setAutoCommit(false);
            returnValue = f.apply(connection);
            connection.commit();
            return returnValue;
        } catch (Throwable throwable) {
            // You may not want to handle all throwables, but you should with most, e.g.
            // Scala has examples: https://github.com/scala/scala/blob/v2.9.3/src/library/scala/util/control/NonFatal.scala#L1
            if (connection != null) {
                connection.rollback();
            }
            throw throwable;
        } finally {
            if (connection != null) {
                try {
                    if(initialAutocommit){
                        connection.setAutoCommit(true);
                    }
                    connection.close();
                } catch (Throwable e) {
                    // Use your own logger here. And again, maybe not catch throwable,
                    // but then again, you should never throw from a finally ;)
                    StringWriter out = new StringWriter();
                    e.printStackTrace(new PrintWriter(out));
                    System.err.println("Could not close connection " + out.toString());
                }
            }
        }
    }

    public static void main(String[] args) throws SQLException {
        DataSource ds = null;

        // Usage example:
        doInTransation(ds::getConnection, (Connection c) -> {
            // Do whatever you want in a transaction
            return 1;
        });
    }
}

I would hope there is some battle tested libraries out there doing this stuff for you, there is at least in these other languages.

I see there are several comments regarding autocommit and connection pools. The above examples should be agnostic to where the connection came from, a pool or not, i.e. only setting it back to true if that was it's initial value. So if from a pool it is false, it should not be touched.

A final word on try-with-resources. I don't think it is a very good abstraction, so I'd be careful using it in more complex scenarios.