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?
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:
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.