Using Transactions in Java Programs

15.8.1 Problem

You want to perform a transaction in a JDBC script.

15.8.2 Solution

Use the standard JDBC transaction support mechanism.

15.8.3 Discussion

To perform transactions in Java, use your Connection object to turn off auto-commit mode. Then, after issuing your queries, use the object's commit( ) method to commit the transaction or rollback( ) to cancel it. Typically, you execute the statements for the transaction in a try block, with commit( ) at the end of the block. To handle failures, invoke rollback( ) in the corresponding exception handler:

try
{
 conn.setAutoCommit (false);
 Statement s = conn.createStatement ( );
 // move some money from one person to the other
 s.executeUpdate ("UPDATE money SET amt = amt - 6 WHERE name = 'Eve'");
 s.executeUpdate ("UPDATE money SET amt = amt + 6 WHERE name = 'Ida'");
 s.close ( );
 conn.commit ( );
 conn.setAutoCommit (true);
}
catch (SQLException e)
{
 System.err.println ("Transaction failed, rolling back.");
 Cookbook.printErrorMessage (e);
 // empty exception handler in case rollback fails
 try
 {
 conn.rollback ( );
 conn.setAutoCommit (true);
 }
 catch (Exception e2) { }
}