Using Transactions

There are times when you have multiple statements that depend on one another successfully completing before another statement should execute. For example, in the employee database that you are working with, if an employee's salary is updated in the EMPLOYEE table, you also need to update that in the SALARYHISTORY table. If the update to the EMPLOYEE table fails, you don't want to go through with the update to the SALARYHISTORY table or you will have invalid data in the database. This is known as a data integrity problem.

The best way to ensure that this happens in the correct manner is to wrap these two updates in a transaction. A transaction is a set of one or more statements that work together as a unit. Either all the statements complete or they all fail. This is typical behavior in most real-world applications and especially in Internet applications. If a user 's online order isn't saved to the database, you don't want to bill the users credit card.

To begin a transaction, you need to turn off the auto-commit mode on the connection instance. For example, you would need to do this:

 connection.setAutoCommit(false); 

Assuming that the connection instance was a live instance, then any statements executed after this method call would not be automatically committed to the database. Only after the commit was called would the statements be executed against the database. You can also cause the statements to be committed by setting the auto commit back to true like this:

 connection.setAutoCommit(true); 

Note

Note that when the auto commit mode is true, which is the connection default, that every statement is a transaction with only the statement in the transaction.

If an SQLException is thrown during execution of the commit of a transaction, the rollback method should be called on the transaction. The rollback() method ensures that all data that was updated by the statements in the transaction will be undone. The SQLException might or might not give you enough information about why the execution failed, but it's a good idea to call the rollback and start the transaction all over again if possible. Otherwise , you might just provide a message back to the client explaining what happened . One possible explanation is that someone else acquired a lock on the table or record that the transaction tried to update. In this case, you will have to rollback your transaction and get updated data and start the transaction over again.

Look at an example using the EMP and SALARYHISTORY explanation from before. In Listing 26.13, you will cause the update to intentionally fail. There has been a constraint put on the column SAL in the EMP table. It must be greater than 0. In this example, two statements are part of the transaction. The first one is valid, but the second one will cause the transaction to fail because the check constraint rule on the SAL column is not met. So when the transaction is committed, the database will rollback the transaction and neither statement will be committed to the database.

Note

By default, the constraint on the SAL column is not in the database already. You will need to add a constraint on this column to ensure that all values in this column are greater than zero. View the database documentation on how to setup this constraint.

Listing 26.13 EmployeeSalaryTransactionExample.java

 import java.sql.*; class EmployeeSalaryTransactionExample {   // Private instance variables for the connection and the prepared statement   private Connection connection = null;   // Default Constructor   public EmployeeSalaryTransactionExample( Connection conn )   {     super();     connection = conn;   }   // Public Accessor for the Connection   public Connection getConnection()   {     return connection;   }   // Method to update the EMPLOYEE and SALARY_HISTORY tables   public void updateEmployeeSalary( int employeeId, double oldSalary, double newSalary )   {     try     {       // Start the transaction by turning off auto commit       getConnection().setAutoCommit( false );       // Set up the first statement, which is valid       String sqlString2 = "INSERT INTO SALARYHISTORY (EMPID, DATE, SALARY )";       sqlString2 = sqlString2 + " VALUES( ?, ?, ? )";       PreparedStatement stmt2 = getConnection().prepareStatement( sqlString2 );       stmt2.setInt( 1, employeeId );       Timestamp ts = new Timestamp( System.currentTimeMillis() );       stmt2.setTimestamp( 2, ts );       stmt2.setDouble( 3, newSalary );       // This statement should not really execute until    // the commit of the transaction       stmt2.executeUpdate();       // Set up the second statement which should fail because of database       // constraints on the salary value. The salary must be greater than 0       String sqlString1 = "UPDATE EMP SET SAL = ? WHERE EMPNO = ?";       PreparedStatement stmt1 = getConnection().prepareStatement( sqlString1 );       stmt1.setDouble( 1, newSalary );       stmt1.setInt( 2, employeeId );       stmt1.executeUpdate();       // Attempt to commit the transaction       getConnection().commit();     }     catch( SQLException sqlException )     {       try       {         // There was a problem, so roll back the changes         System.out.println(  "Database Transaction Failed...Rolling back the changes" );         sqlException.printStackTrace();         getConnection().rollback();       }       catch( Exception ex )       {         ex.printStackTrace();       }     }   }   // Main method to test this class   public static void main(String[] args)   {     try     {       // Use the previous DatabaseManager class to acquire a connection       Connection conn = DatabaseManager.getConnection();       // Create an instance of the example class       EmployeeSalaryTransactionExample example = new EmployeeSalaryTransactionExample( conn );       // Get the first employee record and use it for this example       Statement stmt = conn.createStatement();       ResultSet rs = stmt.executeQuery( "SELECT EMPNO, SAL FROM EMP" );       // Just interested in the first record for this example.    // Make sure there is at least one record       // at least one record       if ( rs.next() )       {         int employeeId = rs.getInt( 1 );         double oldSalary = rs.getDouble( 2 );         // Close the result set since you don't need it anymore         rs.close();         // This is being set to a negative number so that      // it will fail because the database column has a constraint      // that it has to be greater than zero.         double newSalary = -10.00;         example.updateEmployeeSalary( employeeId, oldSalary, newSalary );       }       else       {         System.out.println( "There were 0 employee records in the database" );       }       // Always be sure to close the connection when you are finished       conn.close();     }     catch( SQLException ex )     {       ex.printStackTrace();     }   } } 

Listing 26.14 can be used to test the transaction rollback example from Listing 26.13.

Listing 26.14 Exception Stacktrace from Listing 26.13

 C:\jdk1.3se_book\classes>java EmployeeSalaryTransactionExample Database Transaction Failed...Rolling back the changes java.sql.SQLException: [POL-4317] Check constraint is violated         at oracle.lite.poljdbc.POLJDBCPreparedStatement.jniExecute(Native Method )         at oracle.lite.poljdbc.POLJDBCPreparedStatement.executeInt(Unknown Sourc e)         at oracle.lite.poljdbc.POLJDBCPreparedStatement.executeUpdate(Unknown So urce)         at EmployeeSalaryTransactionExample.updateEmployeeSalary(EmployeeSalaryT ransactionExample.java:46)         at EmployeeSalaryTransactionExample.main(EmployeeSalaryTransactionExampl e.java:96) C:\jdk1.3se_book\classes> 

As expected, the transaction fails because of the constraint on the SAL column value. Remember to always rollback the changes when a transaction fails or otherwise you might leave the database integrity in an unknown condition.