37

How to integrate the common JDBC idiom of creating/receiving a connection, querying the database and possibly processing the results with Java 7's automatic resource management, the try-with-resources statement? (Tutorial)

Before Java 7, the usual pattern was something like this:

Connection con = null;
PreparedStatement prep = null;

try{
    con = getConnection();
    prep = prep.prepareStatement("Update ...");
    ...
    con.commit();
}
catch (SQLException e){
    con.rollback(); 
    throw e;
}
finally{
    if (prep != null)
        prep.close();
    if (con != null)
        con.close();
}

With Java 7 you can go for:

try(Connection con = getConnection(); PreparedStatement prep = con.prepareConnection("Update ..."){

   ...
   con.commit();
}

This will close the Connection and the PreparedStatement, but what about the rollback? I cannot add a catch clause containing the rollback, because the connection is only available within the try block.

Do you still define the connection outside of the try block? What is the best practice here, especially if connection pooling is used?

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
TPete
  • 2,049
  • 4
  • 24
  • 26
  • 1
    I just won't use auto-close in those situations. As the term already states it is just for closing resources. Btw: placing the connection outside the `try...` block won't help as you cannot rollback after the try block as the connection is already closed. – home Feb 13 '12 at 12:06
  • Possible duplicate of http://stackoverflow.com/questions/8066501/how-should-i-use-try-with-resources-with-jdbc – Raedwald Feb 13 '12 at 12:30
  • 3
    @Raedwald: No this is not a duplicate. This here is about con.rollback(). – Bijan Jun 28 '14 at 07:46

3 Answers3

40
try(Connection con = getConnection()) {
   try (PreparedStatement prep = con.prepareConnection("Update ...")) {
       //prep.doSomething();
       //...
       //etc
       con.commit();
   } catch (SQLException e) {
       //any other actions necessary on failure
       con.rollback();
       //consider a re-throw, throwing a wrapping exception, etc
   }
}

According to the oracle documentation, you can combine a try-with-resources block with a regular try block. IMO, the above example captures the correct logic, which is:

  • Attempt to close the PreparedStatement if nothing goes wrong
  • If something goes wrong in the inner block, (no matter what is is) roll back the current transaction
  • Attempt to close the connection no matter what
  • If something goes wrong closing the connection, you can't rollback the transaction (as that's a method on the connection, which is now in indeterminate state), so don't try

In java 6 and earlier, I would do this with a triply nested set of try blocks (outer try-finally, middle try-catch, inner try-finally). ARM syntax does make this terser.

Sean Reilly
  • 21,526
  • 4
  • 48
  • 62
  • 1
    Your solution rolls back on `SQLException`, but does not on `RuntimeException`, `Error` (etc.), which is kinda a problem. `finally`-based code would do that. The rule "you shall not catch `Error`" does not make rollback an optional operation in case of errors. – Piotr Findeisen Oct 22 '14 at 15:03
  • @PiotrFindeisen: if you want that, then catch `Throwable` instead of `SQLException`. Finally is not the correct place to rollback a transaction, as the finally block is triggered whether or not something goes wrong. You definitely don't want to rollback when everything worked! – Sean Reilly Oct 22 '14 at 15:45
  • yes, unless you commit before leaving the try block (in successful case). Of course, in that case you have commit-then-rollback-nothing and this is not ideal, but it's definitely better then missing rollback in some cases. BTW i suggest edit the answer, since it's so nice copy-paste code that it should handle all exceptions. – Piotr Findeisen Oct 23 '14 at 10:15
  • @PiotrFindeisen Committing in the try block and rolling back in a finally block is not correct. Even When everything works it will cause an extra round trip, which is a significant inefficiency. Finally blocks are for ensuring object graphs are in a consistent state (closing connections, statements, streams, etc) **not** taking actions that should only happen when an error occurs (rolling back). – Sean Reilly Oct 23 '14 at 12:53
  • @SeanReilly What if the automatic closing of the PreparedStatement throws an exception? Wouldn't that trigger an unnecessary rollback? – Enrique Jan 15 '16 at 00:23
  • @Enrique: A call to `close()` throws a `SQLException` "if a database access error occurs" (according to the documentation). In this case, the transaction is indeed in a unknown state, and a rollback is appropriate. – Sean Reilly Jan 17 '16 at 18:18
4

IMO, declaring Connection and PreparedStatement outside try-catch is the best way available in this case.

Garbage
  • 1,490
  • 11
  • 23
2

If you want to use pooled connection in transaction, you should use it in this way:

try (Connection conn = source.getConnection()) {
        conn.setAutoCommit(false);
        SQLException savedException = null;
        try {
            // Do things with connection in transaction here...
            conn.commit();
        } catch (SQLException ex) {
            savedException = ex;
            conn.rollback();
        } finally {
            conn.setAutoCommit(true);
            if(savedException != null) {
                throw savedException;
            }
        }
    } catch (SQLException ex1) {
        throw new DataManagerException(ex1);
    }

This sample code handles setting autocommit values.

NOTE, that using savedException does save exception in case that conn.rollback() throws another. This way, finally block will throw "right" exception.

user3698328
  • 71
  • 1
  • 7
  • Looks like, we could simplify things a bit. Why do we need such a verbose exception handling? We can just use: `try (Connection conn = source.getConnection()) { conn.setAutoCommit(false); try {...; conn.commit(); } catch (SQLException ex) { conn.rollback(); throw ex;} finally { conn.setAutoCommit(true);}}` – Igor Lytvynenko Dec 24 '18 at 16:55
  • Sometimes you want specific DataManagerException (or one of its subclasses) exception to be thrown in different cases. Also note `savedException` variable. – user3698328 Apr 17 '20 at 06:42