What is the best way to rollback() a statement execution failure in Java 8?

Question

I'm writing a transaction with Java 8. First, my code was like this.

try (Connection conn = DAOUtil.getConnection();
     PreparedStatement ps = conn.prepareStatement(addSubscriptionSql)) {
    conn.setAutoCommit(false);
    //do work
    conn.commit();
} catch (SQLException e) {
    e.printStackTrace(); //handle error
}

But since I should rollback in case of transaction failure, I had to change the code like this. Note the two try blocks.

try (Connection conn = DAOUtil.getConnection()) {
    try (PreparedStatement ps = conn.prepareStatement(addSubscriptionSql)) {
        conn.setAutoCommit(false);
        //do work
        conn.commit();
    } catch (SQLException e) {
        conn.rollback();
        e.printStackTrace(); //handle error
    }
} catch (SQLException e) {
    e.printStackTrace(); //handle error
}

My question is, is there a better (I mean simpler) way of doing this? Can I achieve this with a single try block?

Answer 1

You can use

try(Connection conn = DAOUtil.getConnection();
    PreparedStatement ps = conn.prepareStatement(addSubscriptionSql);
    AutoCloseable finish = conn::rollback) {

    conn.setAutoCommit(false);
    //do work
    conn.commit();
}

This will always call rollback(), but after a successful completion of commit(), the rollback will become a no-op as it resets the state to that after the last successful completion of commit()…

Since AutoCloseable declares to throw Exception that will require to handle this broad exception type. It can be fixed with a custom type that might be useful in other cases as well:

interface SQLCloseable extends AutoCloseable {
    @Override public void close() throws SQLException;
}

…

try(Connection conn = DAOUtil.getConnection();
    PreparedStatement ps = conn.prepareStatement(addSubscriptionSql);
    SQLCloseable finish = conn::rollback) {

    conn.setAutoCommit(false);
    //do work
    conn.commit();
}

Now, only the handling of the exception type SQLException is enforced.

If you don’t like the idea of rollback() being called unconditionally, the solution becomes less elegant:

boolean[] success = { false };
try(Connection conn = DAOUtil.getConnection();
    PreparedStatement ps = conn.prepareStatement(addSubscriptionSql);
    SQLCloseable finish = () -> { if(!success[0]) conn.rollback(); }) {

    conn.setAutoCommit(false);
    //do work
    conn.commit();
    success[0] = true;
}

If you reset the auto-commit state at the end, you could use that as an indicator for the necessity of a roll-back:

try(Connection conn = DAOUtil.getConnection();
    PreparedStatement ps = conn.prepareStatement(addSubscriptionSql);
    SQLCloseable finish = () -> { if(!conn.getAutoCommit()) conn.rollback(); }) {

    conn.setAutoCommit(false);
    //do work
    conn.commit();
    conn.setAutoCommit(true);
}