17.6 Using Database Transactions

When a database is updated, by default the changes are permanently written (or committed ) to the database. However, this default behavior can be programmatically turned off. If autocommitting is turned off and a problem occurs with the updates, then each change to the database can be backed out (or rolled back to the original values). If the updates execute successfully, then the changes can later be permanently committed to the database. This approach is known as transaction management .

Transaction management helps to ensure the integrity of your database tables. For example, suppose you are transferring funds from a savings account to a checking account. If you first withdraw from the savings account and then deposit into the checking account, what happens if there is an error after the withdrawal but before the deposit? The customer's accounts will have too little money, and the banking regulators will levy stiff fines . On the other hand, what if you first deposit into the checking account and then withdraw from the savings account, and there is an error after the deposit but before the withdrawal? The customer's accounts will have too much money, and the bank's board of directors will fire the entire IT staff. The point is, no matter how you order the operations, the accounts will be left in an inconsistent state if one operation is committed and the other is not. You need to guarantee that either both operations occur or that neither does. That's what transaction management is all about.

The default for a database connection is autocommit; that is, each executed statement is automatically committed to the database. Thus, for transaction management you first need to turn off autocommit for the connection by calling setAutoCommit(false) .

Typically, you use a try / catch / finally block to properly handle the transaction management. First, you should record the autocommit status. Then, in the try block, you should call setAutoCommit(false) and execute a set of queries or updates. If a failure occurs, you call rollback in the catch block; if the transactions are successful, you call commit at the end of the try block. Either way, you reset the autocommit status in the finally block.

Following is a template for this transaction management approach.

 Connection connection =   DriverManager.getConnection(url, username, password); boolean autoCommit = connection.getAutoCommit(); Statement statement; try {   connection.setAutoCommit(false);   statement = connection.createStatement();   statement.execute(...);   statement.execute(...);   ...   connection.commit(); } catch(SQLException sqle) {   connection.rollback(); } finally {   statement.close();   connection.setAutoCommit(autoCommit); } 

Here, the statement for obtaining a connection from the DriverManager is outside the try / catch block. That way, rollback is not called unless a connection is successfully obtained. However, the getConnection method can still throw an SQLException and must be thrown by the enclosing method or be caught in a separate try / catch block.

In Listing 17.13, we add new recordings to the music table as a transaction block (see Section 18.5 to create the music table). To generalize the task, we create a TransactionBean , Listing 17.14, in which we specify the connection to the database and submit a block of SQL statements as an array of strings. The bean then loops through the array of SQL statements, executes each one of them, and if an SQLException is thrown, performs a rollback and rethrows the exception.

Listing 17.13 Transactions.java

 package coreservlets; import java.sql.*; import coreservlets.beans.*; /** An example to demonstrate submission of a block of  *  SQL statements as a single transaction. Specifically,  *  four new records are inserted into the music table.  *  Performed as a transaction block so that if a problem  *  occurs, a rollback is performed and no changes are  *  committed to the database.  */ public class Transactions {   public static void main(String[] args) {     if (args.length < 5) {       printUsage();       return;     }     String vendor = args[4];     // Change to DriverUtilities2.loadDrivers() to load     // vendor drivers from an XML file instead of loading     // hard-coded vendor drivers in DriverUtilities.     DriverUtilities.loadDrivers();     if (!DriverUtilities.isValidVendor(vendor)) {       printUsage();       return;     }     String driver = DriverUtilities.getDriver(vendor);     String host = args[0];     String dbName = args[1];     String url =       DriverUtilities.makeURL(host, dbName, vendor);     String username = args[2];     String password = args[3];     doTransactions(driver, url, username, password);   }   private static void doTransactions(String driver,                                      String url,                                      String username,                                      String password) {     String[] transaction =     { "INSERT INTO music VALUES " +       " ( 9, 'Chopin',       'No. 2 in F minor',  100, 17.99)",       "INSERT INTO music VALUES " +       " (10, 'Tchaikovsky',   'No. 1 in Bb minor', 100, 24.99)",       "INSERT INTO music VALUES " +       " (11, 'Ravel',        'No. 2 in D major',  100, 14.99)",       "INSERT INTO music VALUES " +       " (12, 'Schumann',     'No. 1 in A minor',  100, 14.99)"};  TransactionBean bean = new TransactionBean();  try {  bean.setConnection(driver, url, username, password);   bean.execute(transaction);  } catch (SQLException sqle) {       System.err.println("Transaction failure: " + sqle);     } finally {  bean.close();  }   }   private static void printUsage() {     System.out.println("Usage: Transactions host " +                        "dbName username password " +                        "vendor.");   } } 

Listing 17.14 TransactionBean.java

 package coreservlets.beans; import java.io.*; import java.sql.*; import java.util.*; import coreservlets.*; /** Bean for performing JDBC transactions. After specifying  *  the connection, submit a block of SQL statements as a  *  single transaction by calling execute. If an SQLException  *  occurs, any prior statements are automatically rolled back.  */ public class TransactionBean {   private Connection connection;   public void setConnection(Connection connection) {     this.connection = connection;   }   public void setConnection(String driver, String url,                             String username, String password) {     setConnection(ConnectionInfoBean.getConnection(                      driver, url, username, password));   }   public Connection getConnection() {     return(connection);   }   public void execute(List list) throws SQLException {      execute((String[])list.toArray(new String[list.size()]));   }   public void execute(String transaction)       throws SQLException {     execute(new String[] { transaction });   }   /** Execute a block of SQL statements as a single    *  transaction.  If an SQLException occurs, a rollback    *  is attempted and the exception is thrown.    */     public void execute(String[] transaction)       throws SQLException {     if (connection == null) {       throw new SQLException("No connection available.");     }  boolean autoCommit = connection.getAutoCommit();  try {  connection.setAutoCommit(false);  Statement statement = connection.createStatement();       for(int i=0; i<transaction.length; i++) {         statement.execute(transaction[i]);       }       statement.close();     } catch(SQLException sqle) {  connection.rollback();  throw sqle;     } finally {  connection.commit();   connection.setAutoCommit(autoCommit);  }   }   public void close() {     if (connection != null) {       try {         connection.close();       } catch(SQLException sqle) {         System.err.println(           "Failed to close connection: " + sqle);       } finally {         connection = null;       }     }   } } 

The preceding example demonstrates the use of a bean for submitting transactions to a database. This approach is excellent for a servlet; however, in a JSP page, you may want to use the sql:transaction action available in the JSP Standard Tag Library (JSTL). See Volume 2 of this book for details on JSTL.