Recipe 21.10 Executing Several SQL Statements Within a Single Transaction

Problem

You want to execute more than one SQL statement within a single transaction.

Solution

Use the java.sql.Connection API and the setAutoCommit( ) , commit( ) , and rollback( ) methods to create a transaction.

Discussion

Some SQL statements, such as those that update customer information in two different database tables, are meant to be executed only as a group . If one of them does not succeed, the database is returned to its previous state. This is the purpose of using a transaction in your Java code. A transaction is a logical unit of database operations that can be "rolled back" or canceled as a group if something goes wrong with one of the operations.

Once you have a database connection (an instance of java.sql.Connection ), you can call various Connection methods to create a transaction. Here are the steps for executing a transaction:

Call the Connection object's setAutoCommit( ) method with false as the parameter. This turns off the default behavior for JDBC code, which is to commit each separate SQL statement instead of automatically grouping sequential statements as a single transaction.
Follow the setAutoCommit( ) method call with the database code that you want to treat as a single transaction.
Call the Connection's commit( ) method to commit the SQL statements, which writes any database changes associated with the SQL (such as a DELETE or UPDATE statement) to the underlying database file.
In the area of Java code reserved for dealing with errors or unexpected conditions, such as a catch block, call the Connection's rollback( ) method, which rolls back the SQL that was included in the transaction.

Example 21-14 is a servlet that illustrates this process.

Example 21-14. A servlet that uses a SQL transaction

 package com.jspservletcookbook;  import java.sql.*; import javax.naming.Context; import javax.naming.InitialContext; import javax.naming.NamingException; import javax.sql.*;  import javax.servlet.*; import javax.servlet.http.*; public class DbServletTrans extends HttpServlet {  DataSource pool;  /*Initialize the DataSource in the servlet's init( ) method    which the servlet container calls once when it creates an instance of    the servlet */  public void init( ) throws ServletException {              Context env = null;              try{                     env = (Context) new InitialContext( ).lookup("java:comp/env");         pool  = (DataSource) env.lookup("jdbc/oracle-8i-athletes");         if (pool == null)             throw new ServletException(               "'oracle-8i-athletes' is an unknown DataSource");                   } catch (NamingException ne) {                   throw new ServletException(ne);     }          }//init   public void doGet(HttpServletRequest request,      HttpServletResponse response) throws ServletException,       java.io.IOException {            Connection conn = null;       Statement stmt = null;               response.setContentType("text/html");       java.io.PrintWriter out = response.getWriter( );       out.println(       "<html><head><title>Using transactions</title></head><body>");       out.println(       "<h2>These SQL statements are part of a transaction</h2>");       out.println("CallableStatement.executeUpdate( )");       out.println("<br><br>");       out.println("Statement.executeUpdate( )");       out.println("<br><br>");                try{                     //Get a connection from the pool           conn = pool.getConnection( );  //Display the default values for setAutoCommit( )           //and the isolation level           out.println("AutoCommit before setAutoCommit( ): " +             conn.getAutoCommit( ) + "<br><br>");  out.println("Transaction isolation level: ");  //just out of curiosity, display the existing transaction           // isolation level            witch(conn.getTransactionIsolation( )){                           case 0 : out.println("TRANSACTION_NONE<br><br>"); break;               case 1 : out.println(               "TRANSACTION_READ_UNCOMMITTED<br><br>"); break;               case 2 : out.println(               "TRANSACTION_READ_COMMITTED<br><br>"); break;               case 4 : out.println(               "TRANSACTION_REPEATABLE_READ<br><br>"); break;               case 8 : out.println(               "TRANSACTION_SERIALIZABLE<br><br>"); break;               default: out.println("UNKNOWN<br><br>");                }//switch         //set Autocommit to false so that individual SQL statements will         //not be committed until Connection.commit( ) is called         conn.setAutoCommit(false);  //Transaction-related SQL begins...            CallableStatement cs = null;                  //Create an instance of the CallableStatement         cs = conn.prepareCall( "{call addEvent (?,?,?)}" );                                      cs.setString(1,"Salisbury Beach 5-Miler");             cs.setString(2,"Salisbury MA");             cs.setString(3,"14-Aug-2003");                  //Call the inherited PreparedStatement.executeUpdate( ) method         cs.executeUpdate( );                  String sql = "update raceevent set racedate='13-Aug-2003' "+             "where name='Salisbury Beach 5-Miler'";                  int res = 0;                  stmt = conn.createStatement( );                res = stmt.executeUpdate(sql);  //commit the two SQL statements         conn.commit( );  } catch (Exception e){                          try{                            //rollback the transaction in case of a problem                 conn.rollback( );                         } catch (SQLException sqle){ }                            throw new ServletException(e.getMessage( ));                      } finally {                          try{                                  if (stmt != null)                     stmt.close( );                                      if (conn != null)                      conn.close( );             } catch (SQLException sqle){ }                  }         out.println("</table></body></html>");          } //doGet }

The doGet( ) method in Example 21-14 displays the default values for "auto committing" SQL statements and the transaction isolation level (the level of database-locking that occurs as the transactions within your Java code are initiated). For example, if your SQL statements include the updating of database fields, can other users of the database view the new column values before your transaction is committed? If allowed, this type of behavior is called a dirty read .

Table 21-2 shows the different types of transaction isolation levels, from the least to most restrictive level. Two other terms need addressing before you inspect this table:

A non-repeatable read occurs when one transaction reads a row, another transaction changes the same row, and the first transaction reads the same row and receives the different value.
A phantom read happens when one transaction obtains a result set based on a WHERE condition and a second transaction inserts a new row that satisfies this WHERE condition. The first transaction then evaluates the same database table again with the same WHERE condition and retrieves the new "phantom" row.

Table 21-2. Transaction isolation levels

Transaction isolation level	Return value of java.sql.Connection. getTransactionIsolation( )	Definition
`TRANSACTION_NONE`		The database driver does not support transactions.
`TRANSACTION_READ_` `UNCOMMITTED`	1	Another transaction can see uncommitted changes; "dirty reads" are allowed.
`TRANSACTION_READ_` `COMMITTED`	2	Uncommitted changes are not visible to other transactions.
`TRANSACTION_` `REPEATABLE_READ`	4	Uncommitted changes are not visible to other transactions; nonrepeatable reads are also disallowed .
`TRANSACTION_` `SERIALIZABLE`	8	Uncommitted changes are not visible to other transactions; nonrepeatable reads and phantom reads are also disallowed.

Check your database vendor's specifications or literature for how the database system you use handles transaction isolation. Use the Connection object's getTransactionIsolation( ) method to find out the value associated with a particular database driver that JDBC-related code is using. This method returns an int . For example, a "2" return value means that the Connection is associated with a TRANSACTION_READ_COMMITTED transaction isolation level.

Example 21-14 runs two SQL statements within a transaction: it executes a stored procedure and initiates an UPDATE statement. Then the code calls commit( ) on the Connection object to commit any database changes to the underlying data store. If this SQL code throws an exception, the transaction is rolled back with a call to Connection's rollback( ) method. This method call prevents the prior SQL statements from having any effect on the underlying database.

Figure 21-7 shows the output of the servlet in Example 21-14, as it would appear in a web browser.

Figure 21-7. A servlet with a database transaction provides browser output