MySQL Cookbook, 2nd Edition

Using Transactions in Java Programs

Problem

You want to perform a transaction in a JDBC application.

Solution

Use the standard JDBC transaction support mechanism.

Discussion

To perform transactions in Java, use your Connection object to turn off auto-commit mode. Then, after issuing your statements, 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) { }
}