JDBC - setSavepoint Examples

---

---

Following is the example, which makes use of setSavepoint and rollback described in the Transaction tutorial.

This sample code has been written based on the environment and database setup done in the previous chapters.

Using SavePoint to rollback to Previous Stable Change Example

In this example, we've six static strings containing a dababase connection url, username, password, SELECT Query and two DELETE queries. Delete Queries are used to delete two records in the Employees table with id 8 and 9. Now using DriverManager.getConnection() method, we've prepared a database connection. Using setAutoCommit(false), we've set the auto commit as false which is by default true. Once connection is prepared, we've created a Statement object using connection.createStatement() method. While creating Statement Object, we've used ResultSet types as TYPE_SCROLL_INSENSITIVE and CONCUR_READ_ONLY.

Using setSavepoint() method, we've set a save point and then record with id 8 is deleted. As next step, changes are reverted using rollback to earlier savepoint. This will revert the delete row change. Then we've deleted a record with id 9 and records are printed using printResultSet() method which prints all the records of a resultset.

Copy and paste the following example in JDBCExample.java, compile and run as follows −

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;

public class JDBCExample {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = "SELECT id, first, last, age FROM Employees";
   static final String DELETE_QUERY = "DELETE FROM Employees WHERE ID = 8";
   static final String DELETE_QUERY_1 = "DELETE FROM Employees WHERE ID = 9";

   public static void printResultSet(ResultSet rs) throws SQLException{
      // Ensure we start with first row
      rs.beforeFirst();
      while(rs.next()){
         // Display values
         System.out.print("ID: " + rs.getInt("id"));
         System.out.print(", Age: " + rs.getInt("age"));
         System.out.print(", First: " + rs.getString("first"));
         System.out.println(", Last: " + rs.getString("last"));
      }
      System.out.println();
   }

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement(
            ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_UPDATABLE);				
      ) {		

         conn.setAutoCommit(false);
         ResultSet rs = stmt.executeQuery(QUERY);
         System.out.println("List result set for reference....");
         printResultSet(rs);

         // delete row having ID = 8
         // But save point before doing so.
         Savepoint savepoint1 = conn.setSavepoint("ROWS_DELETED_1");
         System.out.println("Deleting row....");
         stmt.executeUpdate(DELETE_QUERY);  
         // Rollback the changes after save point 1.
         conn.rollback(savepoint1);

         // delete rows having ID = 9
         // But save point before doing so.
         conn.setSavepoint("ROWS_DELETED_2");
         System.out.println("Deleting row....");

         stmt.executeUpdate(DELETE_QUERY_1);  

         rs = stmt.executeQuery(QUERY);
         System.out.println("List result set for reference....");
         printResultSet(rs);

         // Clean-up environment
         rs.close();

      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

Output

Now, let us compile the above example as follows −

C:\>javac JDBCExample.java
C:\>

When you run JDBCExample, it produces the following result −

C:\>java JDBCExample
List result set for reference....
ID: 1, Age: 23, First: Zara, Last: Ali
ID: 2, Age: 30, First: Mahnaz, Last: Fatma
ID: 3, Age: 35, First: Zaid, Last: Khan
ID: 4, Age: 33, First: Sumit, Last: Mittal
ID: 5, Age: 40, First: John, Last: Paul
ID: 7, Age: 20, First: Sita, Last: Singh
ID: 8, Age: 20, First: Rita, Last: Tez
ID: 9, Age: 20, First: Sita, Last: Singh

Deleting row....
Deleting row....
List result set for reference....
ID: 1, Age: 23, First: Zara, Last: Ali
ID: 2, Age: 30, First: Mahnaz, Last: Fatma
ID: 3, Age: 35, First: Zaid, Last: Khan
ID: 4, Age: 33, First: Sumit, Last: Mittal
ID: 5, Age: 40, First: John, Last: Paul
ID: 7, Age: 20, First: Sita, Last: Singh
ID: 8, Age: 20, First: Rita, Last: Tez
C:\>

Using SavePoint to rollback to Previous Change Example

In this example, we've six static strings containing a dababase connection url, username, password, SELECT Query and two DELETE queries. Delete Queries are used to delete two records in the Employees table with id 8. Now using DriverManager.getConnection() method, we've prepared a database connection. Using setAutoCommit(false), we've set the auto commit as false which is by default true. Once connection is prepared, we've created a Statement object using connection.createStatement() method. While creating Statement Object, we've used ResultSet types as TYPE_SCROLL_INSENSITIVE and CONCUR_READ_ONLY.

Using setSavepoint() method, we've set a save point and then record with id 8 is deleted. As next step, changes are reverted using rollback to earlier savepoint. This will revert the delete row change. Then records are printed using printResultSet() method which prints all the records of a resultset.

Copy and paste the following example in JDBCExample.java, compile and run as follows −

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;

public class JDBCExample {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = "SELECT id, first, last, age FROM Employees";
   static final String DELETE_QUERY = "DELETE FROM Employees WHERE ID = 8";

   public static void printResultSet(ResultSet rs) throws SQLException{
      // Ensure we start with first row
      rs.beforeFirst();
      while(rs.next()){
         // Display values
         System.out.print("ID: " + rs.getInt("id"));
         System.out.print(", Age: " + rs.getInt("age"));
         System.out.print(", First: " + rs.getString("first"));
         System.out.println(", Last: " + rs.getString("last"));
      }
      System.out.println();
   }

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement(
            ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_UPDATABLE);				
      ) {		

         conn.setAutoCommit(false);

         // Create a savepoint object before executing the 
         // deleteQuery
         Savepoint beforeDeleteSavepoint = conn.setSavepoint();
         stmt.executeUpdate(DELETE_QUERY);  
		 
		 System.out.println("Table EMPLOYEES after executing deleteQuery");
		 ResultSet rs = stmt.executeQuery(QUERY);
		 printResultSet(rs);
         
		 // Rollback the changes after delete save point.
         conn.rollback(beforeDeleteSavepoint);
         
         System.out.println("Table Employees after rollback");
         rs = stmt.executeQuery(QUERY);
         printResultSet(rs);

         // Clean-up environment
         rs.close();

      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

Output

Now, let us compile the above example as follows −

C:\>javac JDBCExample.java
C:\>

When you run JDBCExample, it produces the following result −

C:\>java JDBCExample
Table EMPLOYEES after executing deleteQuery
ID: 1, Age: 18, First: Zara, Last: Ali
ID: 2, Age: 25, First: Mahnaz, Last: Fatma
ID: 3, Age: 30, First: Zaid, Last: Khan
ID: 4, Age: 28, First: Sumit, Last: Mittal
ID: 7, Age: 20, First: Rita, Last: Tez
Table Employees after rollback
ID: 1, Age: 18, First: Zara, Last: Ali
ID: 2, Age: 25, First: Mahnaz, Last: Fatma
ID: 3, Age: 30, First: Zaid, Last: Khan
ID: 4, Age: 28, First: Sumit, Last: Mittal
ID: 7, Age: 20, First: Rita, Last: Tez
ID: 8, Age: 20, First: Sita, Last: Singh

C:\>