Using transactions with JDBC

SQL statements can be grouped together in a single statement to ensure the ACID (Atomicity, Consistency, Isolation, Durability) characteristics. By default, each SQL statement is auto-commited. In other words, it is treated as a transaction by itself and will be committed right away. This may not be desirable in cases where all the SQL statements need to be executed or none of them, if one fails. The way this can be done with JDBC is by turning off auto-commit mode before the group of statements and turn auto-commit back on after.

   conn.setAutoCommit(false);
   Statement stmt = conn.createStatement();
   stmt.executeUpdate("UPDATE accounts SET balance = 100 where accountid = 1");
   stmt.executeUpdate("UPDATE products SET itemsleft = 0 where prodid = 4");
   stmt.rollback();
   stmt.executeUpdate("UPDATE products SET itemsleft = 0 where prodid = 3");
   stmt.commit();
   conn.setAutoCommit(true);  

In this example, the first two updates will not affect the database table because of rollback. The last update is explicitely committed.

Typically, you would use commit and rollback in conjunction with Exception handling. eg.

   try {
      conn.setAutoCommit(false);
 
      updateInventory();
      updateBalance();
 
      conn.commit();
      conn.setAutoCommit(true);
   }
   catch(ShoppingException e) {
      e.printStackTrace();
      conn.rollback();
      conn.setAutoCommit(true);
   }