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

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."

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.

In your code you are catching "SQLException" to perform the autoCommit reset. Any kind of runtime exception (like a null pointer exception) will bubble from your code without resetting the auto-commit.

The try-with-resource syntax causes the compiler to generate some wonderful code to cover all execution paths and to keep up with all suppressed exceptions through the closings. With a couple of helper classes you can insert commit/rollback and reset-auto-commit into the code generation process:

import java.sql.SQLException;
import java.sql.Connection;

public class AutoRollback implements AutoCloseable {
    
    private Connection conn;
    private boolean committed;
    
    public AutoRollback(Connection conn) throws SQLException {
        this.conn = conn;        
    }
    
    public void commit() throws SQLException {
        conn.commit();
        committed = true;
    }

    @Override
    public void close() throws SQLException {
        if(!committed) {
            conn.rollback();
        }
    }

}

public class AutoSetAutoCommit implements AutoCloseable {
    
    private Connection conn;
    private boolean originalAutoCommit;
    
    public AutoSetAutoCommit(Connection conn, boolean autoCommit) throws SQLException {
        this.conn = conn;
        originalAutoCommit = conn.getAutoCommit();
        conn.setAutoCommit(autoCommit);
    }

    @Override
    public void close() throws SQLException {
        conn.setAutoCommit(originalAutoCommit);
    }

}

Now you can control rollback and autocommit with the "try with resource" syntax like this:

    try(Connection conn = getConnection(),
        AutoSetAutoCommit a = new AutoSetAutoCommit(conn,false),
        AutoRollback tm = new AutoRollback(conn)) 
    {
                
        // Do stuff
                                    
        tm.commit();
    } 
    
    

    //try with resources
    try(Connection conn = this.connectionProvider.getConnection()){//auto close BEFORE reach this , catch block, so we need a inner try block for statement
        boolean oldAutoCommit=conn.getAutoCommit();
        conn.setAutoCommit(false);//auto commit to false
        try(
            Statement stm = con.createStatement()
        ){
            stm.execute(someQuery); // causes SQLException
            conn.commit();//commit
        }
        catch (SQLException ex){
            conn.rollback();//error, rollback
            throw ex;//If you need to throw the exception to the caller
        }
        finally {
            conn.setAutoCommit(oldAutoCommit);//reset auto commit
        }
    }

In the example above I think it's better to put con.commit() inside nested try-catch because it also can throw SQLException.

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

We had such problem in our production environment with unclosed sessions.