Savepoints in JDBC applications

An SQL savepoint represents the state of data and schemas at a particular point in time within a unit of work. You can use SQL statements to set a savepoint, release a savepoint, and restore data and schemas to the state that the savepoint represents.

The IBM® Data Server Driver for JDBC and SQLJ supports the following methods for using savepoints:

Connection.setSavepoint() or Connection.setSavepoint(String name): Sets a savepoint. These methods return a Savepoint object that is used in later releaseSavepoint or rollback operations.
When you execute either of these methods, the data server executes the form of the SAVEPOINT statement that includes ON ROLLBACK RETAIN CURSORS.
Connection.releaseSavepoint(Savepoint savepoint): Releases the specified savepoint, and all subsequently established savepoints.
Connection.rollback(Savepoint savepoint): Rolls back work to the specified savepoint.
DatabaseMetaData.supportsSavepoints(): Indicates whether a data source supports savepoints.

You can indicate whether savepoints are unique by calling the method DB2Connection.setSavePointUniqueOption. If you call this method with a value of true, the application cannot set more than one savepoint with the same name within the same unit of recovery. If you call this method with a value of false (the default), multiple savepoints with the same name can be created within the same unit of recovery, but creation of a savepoint destroys a previously created savepoint with the same name.

The following example demonstrates how to set a savepoint, roll back to the savepoint, and release the savepoint.

Figure 1. Setting, rolling back to, and releasing a savepoint in a JDBC application

Connection con;
Statement stmt;
ResultSet rs;
String empNumVar;
int shoeSizeVar;
…
con.setAutoCommit(false);                    // set autocommit OFF
stmt = con.createStatement();                // Create a Statement object
…                                            // Perform some SQL
con.commit();                                // Commit the transaction
stmt.executeUpdate("INSERT INTO EMP_SHOE " +
  "VALUES ('000010', 6)");                   // Insert a row
((com.ibm.db2.jcc.DB2Connection)con).setSavePointUniqueOption(true);
                                             // Indicate that savepoints
                                             // are unique within a unit
                                             // of recovery
Savepoint savept = con.setSavepoint("savepoint1"); 
                                             // Create a savepoint
…
stmt.executeUpdate("INSERT INTO EMP_SHOE " +
  "VALUES ('000020', 10)");                  // Insert another row
conn.rollback(savept);                       // Roll back work to the point
                                             //  after the first insert
…
con.releaseSavepoint(savept);                // Release the savepoint
stmt.close();                                // Close the Statement
conn.commit();                               // Commit the transaction