A savepoint is a point within a database transaction to which the transaction can be partially rolled back. JDBC provides the Savepoint interface and the Connection methods setSavepoint() and rollback(Savepoint) for working with savepoints. Unlike rollback(), which normally cancels the entire transaction, rolling back to a savepoint cancels only the operations performed after that savepoint. Savepoints are useful when a transaction contains multiple operations and you want to undo only a specific part while keeping earlier successful operations.
Program: Using Savepoints in a Transaction
This program performs multiple employee salary updates in a single transaction. A savepoint is created after the first update. The second update is then rolled back to the savepoint, while the first update remains part of the transaction and is finally committed.
import java.sql.*;
public class SavepointExample {
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(
"First salary update completed.");
// 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(
"Second salary update completed.");
// Roll back only the second update
con.rollback(savepoint);
System.out.println(
"Rolled back to savepoint.");
// Commit the remaining transaction
con.commit();
System.out.println(
"Transaction committed.");
// Close resources
ps1.close();
ps2.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" SavepointExample.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" SavepointExample
Transaction started.
First salary update completed.
Savepoint created.
Second salary update completed.
Rolled back to savepoint.
Transaction committed.Code language: CSS (css)