Transaction Management in Spring - Spring Enterprise Recipes: A Problem Solution Approach - page 164

As you can see, the lack of transaction management causes your data to be left in an inconsistent
state. To avoid this inconsistency, your three SQL statements for the purchase() operation should be
executed within a single transaction. Once any of the actions in a transaction fail, the entire transaction
should be rolled back to undo all changes made by the executed actions.
Managing Transactions with JDBC Commit and Rollback
When using JDBC to update a database, by default each SQL statement will be committed immediately
after its execution. This behavior is known as auto-commit . However, it does not allow you to manage
transactions for your operations.
JDBC supports the primitive transaction management strategy of explicitly calling the commit() and
rollback() methods on a connection. But before you can do that, you must turn off auto-commit, which
is turned on by default.
package com.apress.springenterpriserecipes.bookshop.spring;
...
public class JdbcBookShop implements BookShop {
...
public void purchase(String isbn, String username) {
Connection conn = null;
try {
conn = dataSource.getConnection();
conn.setAutoCommit(false);
...
conn.commit();
} catch (SQLException e) {
if (conn != null) {
try {
conn.rollback();
} catch (SQLException e1) {}
}
throw new RuntimeException(e);
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {}
}
}
}
}
The auto-commit behavior of a database connection can be altered by calling the setAutoCommit()
method. By default, auto-commit is turned on to commit each SQL statement immediately after its
execution. To enable transaction management, you must turn off this default behavior and commit the
connection only when all the SQL statements have been executed successfully. If any of the statements
go wrong, you must roll back all changes made by this connection.