CODEWITHSUNDEEP
javajdbctransactionsjava-8
Bee
Bee

Reputation: 12512

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

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?

Upvotes: 7

Views: 5686

Answers (2)

Holger
Holger

Reputation: 298283

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

Upvotes: 16

Gord Thompson
Gord Thompson

Reputation: 123689

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

Upvotes: 7

Related Questions