How to create and release a save point in JDBC?

When you set a save point you define a logical rollback point within a transaction. If an error occurs past a save point, you can use the rollback method to undo either all the changes or only the changes made after the save point.

Savepoint interface gives you the additional transactional control. Most modern DBMS, support save points within their environments such as Oracle's PL/SQL.

Setting a save point

You can set a save point in a database using the setSavepoint(String savepointName) method of the Connection interface, this method accepts a string value representing the name of the save point and returns a Savepoint object.

Releasing a save point

You can release a save point using the releaseSavepoint(Savepoint savepointName) method of the Connection interface, this method accepts the name of the savepoint and releases/deletes the specified save point.

Example

Assume we have a table named customers in the database with 5 records as shown below:

+----+-----------+------+---------+----------------+ | ID | NAME      | AGE  | SALARY | ADDRESS | +----+-----------+------+---------+----------------+ | 1  | Amit      | 25   | 3000.00 | Hyderabad | | 2  | Kalyan    | 27   | 4000.00 | Vishakhapatnam | | 3  | Renuka    | 30   | 5000.00 | Delhi | | 4  | Archana   | 24   | 1500.00 | Mumbai | | 5  | Koushik   | 30 | 9000.00 | Kota | +----+-----------+------+---------+----------------+

Following JDBC program Inserts 7 more records to it, sets a save point, removes some records and roll backs to the save point.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Savepoint; public class SavepointExample {    public static void main(String args[]) throws SQLException {       //Registering the Driver       DriverManager.registerDriver(new com.mysql.jdbc.Driver());       //Getting the connection       String mysqlUrl = "jdbc:mysql://localhost/mydatabase";       Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");       System.out.println("Connection established......");       //Setting auto-commit false       con.setAutoCommit(false);       System.out.println(" ");       //Creating the Statement       PreparedStatement pstmt = con.prepareStatement("INSERT into customers VALUES (?, ?, ?, ?, ?) ");       pstmt.setInt(1, 6);       pstmt.setString(2, "Hardik");       pstmt.setInt(3, 45);       pstmt.setInt(4, 6400);       pstmt.setString(5, "Bhopal");       pstmt.executeUpdate();       pstmt.setInt(1, 7);       pstmt.setString(2, "Trupti");       pstmt.setInt(3, 33);       pstmt.setInt(4, 4360);       pstmt.setString(5, "Ahmedabad");       pstmt.executeUpdate();       pstmt.setInt(1, 8);       pstmt.setString(2, "Mithili");       pstmt.setInt(3, 26);       pstmt.setInt(4, 4100);       pstmt.setString(5, "Vijayawada");       pstmt.executeUpdate();       pstmt.setInt(1, 9);       pstmt.setString(2, "Maneesh");       pstmt.setInt(3, 39);       pstmt.setInt(4, 4000);       pstmt.setString(5, "Hyderabad");       pstmt.executeUpdate();       pstmt.setInt(1, 10);       pstmt.setString(2, "Rajaneesh");       pstmt.setInt(3, 30);       pstmt.setInt(4, 6400);       pstmt.setString(5, "Delhi");       pstmt.executeUpdate();       pstmt.setInt(1, 11);       pstmt.setString(2, "Komal");       pstmt.setInt(3, 29);       pstmt.setInt(4, 8000);       pstmt.setString(5, "Ahmedabad");       pstmt.executeUpdate();       pstmt.setInt(1, 12);       pstmt.setString(2, "Manyata");       pstmt.setInt(3, 25);       pstmt.setInt(4, 5000);       pstmt.setString(5, "Vijayawada");       pstmt.executeUpdate();       //Setting save point       Savepoint savePoint = con.setSavepoint("mysavepoint");       System.out.println(" ");       System.out.println("Contents of the customers table after inserting the records: ");       Statement stmt = con.createStatement();       //Retrieving data       ResultSet rs = stmt.executeQuery("Select * from customers");       while(rs.next()) {          System.out.print("ID: "+rs.getInt("ID")+", ");          System.out.print("Name: "+rs.getString("Name")+", ");          System.out.print("Age: "+rs.getInt("Age")+", ");          System.out.print("Salary: "+rs.getInt("Salary")+", ");          System.out.print("Address: "+rs.getString("Address"));          System.out.println();       }       //Deleting the records       stmt.execute("Delete from customers where id > 5");       System.out.println(" ");       System.out.println("Contents of the customers table after deleting the records: ");       //Retrieving data       rs = stmt.executeQuery("Select * from customers");       while(rs.next()) {          System.out.print("ID: "+rs.getInt("ID")+", ");          System.out.print("Name: "+rs.getString("Name")+", ");          System.out.print("Age: "+rs.getInt("Age")+", ");          System.out.print("Salary: "+rs.getInt("Salary")+", ");          System.out.print("Address: "+rs.getString("Address"));          System.out.println();       }       //Rolling back to the save point       con.rollback(savePoint);       System.out.println(" ");       System.out.println("Contents of the table at the save point: ");       //Retrieving data       rs = stmt.executeQuery("Select * from customers");       while(rs.next()) {          System.out.print("ID: "+rs.getInt("ID")+", ");          System.out.print("Name: "+rs.getString("Name")+", ");          System.out.print("Age: "+rs.getInt("Age")+", ");          System.out.print("Salary: "+rs.getInt("Salary")+", ");          System.out.print("Address: "+rs.getString("Address"));          System.out.println();       }    } }

Output

Connection established...... Contents of the customers table after inserting the records: ID: 1, Name: Amit, Age: 25, Salary: 3000, Address: Hyderabad ID: 2, Name: Kalyan, Age: 27, Salary: 4000, Address: Vishakhapatnam ID: 3, Name: Renuka, Age: 30, Salary: 5000, Address: Delhi ID: 4, Name: Archana, Age: 24, Salary: 1500, Address: Mumbai ID: 5, Name: Koushik, Age: 30, Salary: 9000, Address: Kota ID: 6, Name: Hardik, Age: 45, Salary: 6400, Address: Bhopal ID: 7, Name: Trupti, Age: 33, Salary: 4360, Address: Ahmedabad ID: 8, Name: Mithili, Age: 26, Salary: 4100, Address: Vijayawada ID: 9, Name: Maneesh, Age: 39, Salary: 4000, Address: Hyderabad ID: 10, Name: Rajaneesh, Age: 30, Salary: 6400, Address: Delhi ID: 11, Name: Komal, Age: 29, Salary: 8000, Address: Ahmedabad ID: 12, Name: Manyata, Age: 25, Salary: 5000, Address: Vijayawada Contents of the customers table after deleting the records: ID: 1, Name: Amit, Age: 25, Salary: 3000, Address: Hyderabad ID: 2, Name: Kalyan, Age: 27, Salary: 4000, Address: Vishakhapatnam ID: 3, Name: Renuka, Age: 30, Salary: 5000, Address: Delhi ID: 4, Name: Archana, Age: 24, Salary: 1500, Address: Mumbai ID: 5, Name: Koushik, Age: 30, Salary: 9000, Address: Kota Contents of the table at the save point: ID: 1, Name: Amit, Age: 25, Salary: 3000, Address: Hyderabad ID: 2, Name: Kalyan, Age: 27, Salary: 4000, Address: Vishakhapatnam ID: 3, Name: Renuka, Age: 30, Salary: 5000, Address: Delhi ID: 4, Name: Archana, Age: 24, Salary: 1500, Address: Mumbai ID: 5, Name: Koushik, Age: 30, Salary: 9000, Address: Kota ID: 6, Name: Hardik, Age: 45, Salary: 6400, Address: Bhopal ID: 7, Name: Trupti, Age: 33, Salary: 4360, Address: Ahmedabad ID: 8, Name: Mithili, Age: 26, Salary: 4100, Address: Vijayawada ID: 9, Name: Maneesh, Age: 39, Salary: 4000, Address: Hyderabad ID: 10, Name: Rajaneesh, Age: 30, Salary: 6400, Address: Delhi ID: 11, Name: Komal, Age: 29, Salary: 8000, Address: Ahmedabad ID: 12, Name: Manyata, Age: 25, Salary: 5000, Address: Vijayawada