The normal idiom is the following:
public void executeSomeQuery() throws SQLException {
try (Connection connection = dataSource.getConnection()) {
connection.setAutoCommit(false);
try (PreparedStatement statement = connection.prepareStatement(SOME_SQL)) {
// Fire transactional queries here.
connection.commit();
} catch (SQLException e) {
connection.rollback();
throw e;
}
}
}
Note that Java 7’s try-with-resources statement always implicitly calls close()
on the resource when the try
block finishes, as if it happens in finally
.
Calling rollback()
is also mandatory when it concerns a pooled connection. Namely, it will reset the transactional state of the connection. The close()
of a pooled connection won’t do that, only the commit()
and rollback()
will do that. Not calling rollback()
may lead that the next lease of the pooled connection will still have the (successful) queries of the previous transaction in its memory.
See also javadoc of Connection#close()
(emphasis not mine):
It is strongly recommended that an application explicitly commits or rolls back an active transaction prior to calling the
close
method. If theclose
method is called and there is an active transaction, the results are implementation-defined.