Rolling back to a savepoint allows a JDBC transaction to undo only the changes made after a particular savepoint. The Savepoint interface is used to mark a specific point within an active transaction. The Connection.rollback(Savepoint) method rolls the transaction back to that point without canceling the operations performed before the savepoint. This is useful when a transaction contains multiple operations and only one part of the transaction needs to be undone.
Program: Rolling Back to a Savepoint
This program performs three salary updates within a single transaction. A savepoint is created after the first update, and after the second update, the transaction is rolled back to that savepoint. Therefore, the first update is retained, while the second update is undone. The third update is then performed and the complete transaction is committed.
import java.sql.*;
public class RollbackToSavepointExample {
public static void main(String[] args) {
String url =
"jdbc:mysql://localhost:3306/jdbc_demo";
String username = "root";
String password = "password";
try {
Connection con =
DriverManager.getConnection(
url, username, password);
// Disable auto-commit
con.setAutoCommit(false);
System.out.println(
"Transaction started.");
// First update
PreparedStatement ps1 =
con.prepareStatement(
"UPDATE employee " +
"SET salary = salary + 5000 " +
"WHERE employee_id = 101");
ps1.executeUpdate();
System.out.println(
"Employee 101 salary updated.");
// Create savepoint
Savepoint savepoint =
con.setSavepoint("AfterFirstUpdate");
System.out.println(
"Savepoint created.");
// Second update
PreparedStatement ps2 =
con.prepareStatement(
"UPDATE employee " +
"SET salary = salary + 10000 " +
"WHERE employee_id = 102");
ps2.executeUpdate();
System.out.println(
"Employee 102 salary updated.");
// Roll back to savepoint
con.rollback(savepoint);
System.out.println(
"Rolled back to savepoint.");
// Third update
PreparedStatement ps3 =
con.prepareStatement(
"UPDATE employee " +
"SET salary = salary + 3000 " +
"WHERE employee_id = 103");
ps3.executeUpdate();
System.out.println(
"Employee 103 salary updated.");
// Commit remaining changes
con.commit();
System.out.println(
"Transaction committed.");
// Close resources
ps1.close();
ps2.close();
ps3.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" RollbackToSavepointExample.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" RollbackToSavepointExample
Transaction started.
Employee 101 salary updated.
Savepoint created.
Employee 102 salary updated.
Rolled back to savepoint.
Employee 103 salary updated.
Transaction committed.Code language: CSS (css)