Java Database Connectivity (JDBC) - An Introduction to Network Programming with Java - page 194

7.7 Transactions

Industrial-strength databases (so not MS Access!) will normally incorporate
transaction processing . A transaction is one or more SQL statements that may be
grouped together as a single processing entity. This feature caters for situations in
which a group of related statements needs to be carried out at the same time. If only
some of the statements are executed, then the database is likely to be left in an
inconsistent state. For example, an online ordering system may update the Orders
table when a customer places an order and may also need to update the Stock table
at the same time (in order to refl ect the fact that stock has been set aside for the
customer and cannot be ordered by another customer). In such a situation, we want
either both statements or neither to be executed. Unfortunately, network problems
may cause one of these statements to fail after the other has been executed. If this
happens, then we want to undo the statement that has been executed.
The SQL statements used to implement transaction processing are COMMIT
and ROLLBACK, which are mirrored in Java by the Connection interface methods
commit and rollback . As their names imply, commit is used at the end of a transac-
tion to commit/fi nalise the database changes, while rollback is used (in an error
situation) to restore the database to the state it was in prior to the current transaction
(by undoing any statements that may have been executed). By default, however,
JDBC automatically commits each individual SQL statement that is applied to a
database. In order to change this default behaviour so that transaction processing
may be carried out, we must fi rst execute Connection method setAutoCommit
with an argument of false (to switch off auto-commit). We can then use methods
commit and rollback to effect transaction processing.

Example

..............................
connection.setAutoCommit(false);
..............................
try
{
//Assumes existence of 3 SQL update strings
//called update1, update2 and update3.
statement.executeUpdate(update1);
statement.executeUpdate(update2);
statement.executeUpdate(update3);
connection.commit();
}
catch(SQLException sqlEx)
{
connection.rollback();
System.out.println(
"* SQL error! Changes aborted… *");
}