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?

score 16 · Accepted Answer · answered Nov 24 '16 at 14:02

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);
}

It is a clever solution, so +1 for that, but I wonder how many people will understand this when they come across it in a codebase. — Mark Rotteveel, Nov 25 '16 at 11:51
The SQLCloseable is a nice workaround. Consider making it generic, such as `public interface TypedAutoCloseable extends AutoCloseable` so it could be used in any try-with-resources block. — splungebob, Oct 26 '18 at 16:20
@splungebob yes, but such a generic type develops its usefulness best when being part of a general-purpose library. It's a pity that it doesn't exist in `java.util.functions`. — Holger, Oct 27 '18 at 08:37

score 7 · Answer 2 · answered Nov 24 '16 at 12:48

Can I achieve this with a single try block?

No, because your conn object will not be available in the catch block of your try-with-resources. If you want to catch an exception while executing the PreparedStatement and explicitly do a conn.rollback() then the rollback will have to happen within the try of the try-with-resources that creates the conn object (i.e., using a second nested try block for the PreparedStatement call).

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

2 Answers2