Method to set savepoints for transaction rollback in Java JDBC usage

The interface to the new JDBC3.0 savepoint provides additional transaction control. Most modern DBMSS in their environment, such as Oracle's PL/SQL, support savepoints.

Defines a logical rollback point in a transaction when setting a savepoint. If an error occurs and a savepoint is past, the rollback method can be used to undo either all changes or only changes made after the savepoint.

The Connection object has two new methods to help manage savepoints:

SetSavepoint (String savepointName): a new savepoint is defined. It also returns a Savepoint object.

ReleaseSavepoint (Savepoint savepointName): removes a Savepoint. Note that it requires a Savepoint object as an argument. This object is typically a savepoint generated by the setSavepoint() method.

There is a rollback (String savepointName) method that rolls back work to the specified savepoint.

The following example demonstrates how to use the Savepoint object:

try{
  //Assume a valid connection object conn
  conn.setAutoCommit(false);
  Statement stmt = conn.createStatement();
  
  //set a Savepoint
  Savepoint savepoint1 = conn.setSavepoint("Savepoint1");
  String SQL = "INSERT INTO Employees " +
        "VALUES (106, 20, 'Rita', 'Tez')";
  stmt.executeUpdate(SQL); 
  //Submit a malformed SQL statement that breaks
  String SQL = "INSERTED IN Employees " +
        "VALUES (107, 22, 'Sita', 'Tez')";
  stmt.executeUpdate(SQL);
  // If there is no error, commit the changes.
  conn.commit();

}catch(SQLException se){
  // If there is any error.
  conn.rollback(savepoint1);
}

In this case, no INSERT statement above will succeed and everything will be rolled back.
The following is an example of using setSavepoint and the transaction tutorial to describe a rollback.

Doing this sample code based on the environment and database installation in the previous section has been explained.

Copy the following example jdbcexample.java, compile and run as follows:

//STEP 1. Import required packages
import java.sql.*;

public class JDBCExample {
  // JDBC driver name and database URL
  static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; 
  static final String DB_URL = "jdbc:mysql://localhost/EMP";

  // Database credentials
  static final String USER = "username";
  static final String PASS = "password";
  
public static void main(String[] args) {
  Connection conn = null;
  Statement stmt = null;
  try{
   //STEP 2: Register JDBC driver
   Class.forName("com.mysql.jdbc.Driver");

   //STEP 3: Open a connection
   System.out.println("Connecting to database...");
   conn = DriverManager.getConnection(DB_URL,USER,PASS);

   //STEP 4: Set auto commit as false.
   conn.setAutoCommit(false);

   //STEP 5: Execute a query to delete statment with
   // required arguments for RS example.
   System.out.println("Creating statement...");
   stmt = conn.createStatement();

  //STEP 6: Now list all the available records.
   String sql = "SELECT id, first, last, age FROM Employees";
   ResultSet rs = stmt.executeQuery(sql);
   System.out.println("List result set for reference....");
   printRs(rs);

   // STEP 7: delete rows having ID grater than 104
   // But save point before doing so.
   Savepoint savepoint1 = conn.setSavepoint("ROWS_DELETED_1");
   System.out.println("Deleting row....");
   String SQL = "DELETE FROM Employees " +
          "WHERE ID = 110";
   stmt.executeUpdate(SQL); 
   // oops... we deleted too wrong employees!
   //STEP 8: Rollback the changes afetr save point 2.
   conn.rollback(savepoint1);

  // STEP 9: delete rows having ID grater than 104
   // But save point before doing so.
   Savepoint savepoint2 = conn.setSavepoint("ROWS_DELETED_2");
   System.out.println("Deleting row....");
   SQL = "DELETE FROM Employees " +
          "WHERE ID = 95";
   stmt.executeUpdate(SQL); 

  //STEP 10: Now list all the available records.
   sql = "SELECT id, first, last, age FROM Employees";
   rs = stmt.executeQuery(sql);
   System.out.println("List result set for reference....");
   printRs(rs);

   //STEP 10: Clean-up environment
   rs.close();
   stmt.close();
   conn.close();
  }catch(SQLException se){
   //Handle errors for JDBC
   se.printStackTrace();
   // If there is an error then rollback the changes.
   System.out.println("Rolling back data here....");
  try{
  if(conn!=null)
      conn.rollback();
   }catch(SQLException se2){
     se2.printStackTrace();
   }//end try

  }catch(Exception e){
   //Handle errors for Class.forName
   e.printStackTrace();
  }finally{
   //finally block used to close resources
   try{
     if(stmt!=null)
      stmt.close();
   }catch(SQLException se2){
   }// nothing we can do
   try{
     if(conn!=null)
      conn.close();
   }catch(SQLException se){
     se.printStackTrace();
   }//end finally try
  }//end try
  System.out.println("Goodbye!");
}//end main

  public static void printRs(ResultSet rs) throws SQLException{
   //Ensure we start with first row
   rs.beforeFirst();
   while(rs.next()){
     //Retrieve by column name
     int id = rs.getInt("id");
     int age = rs.getInt("age");
     String first = rs.getString("first");
     String last = rs.getString("last");

     //Display values
     System.out.print("ID: " + id);
     System.out.print(", Age: " + age);
     System.out.print(", First: " + first);
     System.out.println(", Last: " + last);
   }
   System.out.println();
  }//end printRs()
}//end JDBCExample

Now let's compile the above example:

C:>javac JDBCExample.java

When running JDBCExample, it produces the following results:

C:>java JDBCExample

Connecting to database...
Creating statement...
List result set for reference....
ID: 95, Age: 20, First: Sima, Last: Chug
ID: 100, Age: 18, First: Zara, Last: Ali
ID: 101, Age: 25, First: Mahnaz, Last: Fatma
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 30, First: Sumit, Last: Mittal
ID: 110, Age: 20, First: Sima, Last: Chug

Deleting row....
Deleting row....
List result set for reference....
ID: 100, Age: 18, First: Zara, Last: Ali
ID: 101, Age: 25, First: Mahnaz, Last: Fatma
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 30, First: Sumit, Last: Mittal
ID: 110, Age: 20, First: Sima, Last: Chug

Goodbye!