The JDBC Savepoint Interface offers the mechanism for establishing the points within the transactions and allowing for partial rollback if needed to the user. Transactions in JDBC are particularly crucial for maintaining data integrity by ensuring that the set of operations either entirely succeeds or entirely fails called ACID properties. ACID properties i.e. Atomicity, Consistency, Isolation, and Durability.

Prerequisites:

The following are the prerequisites to use the JDBC Savepoint interface for nested transactions.

• Java Development Kit (JDK)
• Database Server
• JDBC Driver
• Development Environment ( Example: Eclipse)
• Database Setup

Note: Here, we will use Eclipse IDE for project Creation.

Step-by-Step Implementation to use the JDBC Savepoint Interface for Nested Transactions

Below are the implementation steps to use the JDBC Savepoint Interface for Nested Transactions.

Step 1: Create table in Database

We need to create a table in the database and name it as savepoint. This table is used for the testing. Here is the sample table creation in the database.

 CREATE TABLE savepoint (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

Step 2: Set up the project in Eclipse IDE

• Open Eclipse IDE.
• Create a new Java project.
• After that, create java project, create a class file in it, and name it as “Example”.
• After that, we need to add the JDBC driver library to the project’s build path. For MySQL database, we need to download the MySQL Connector/J driver and add it into the project’s build path.

Here is the path for class file and the JDBC driver library:

Step 3: Implement the code

Open Example.java class file, write the below code to use the JDBC Savepoint interface for nested transactions.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Savepoint;

public class Example {

    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement stmt = null;
        Savepoint savepoint1 = null;
        Savepoint savepoint2 = null;

        try {
            // Establish connection
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3307/work", "root", "tiger");
            conn.setAutoCommit(false); // Start transaction

            // Create a savepoint
            savepoint1 = conn.setSavepoint("Savepoint1");

            // Perform some operations
            stmt = conn.prepareStatement("INSERT INTO savepoint (name) VALUES (?)");
            stmt.setString(1, "John");
            stmt.executeUpdate();

            // Nested transaction
            savepoint2 = conn.setSavepoint("Savepoint2");
            stmt.setString(1, "Alice");
            stmt.executeUpdate();

            // Commit transaction
            conn.commit();
            System.out.println("Transaction committed successfully!");
        } catch (SQLException e) {
            try {
                if (conn != null) {
                    if (savepoint2 != null) {
                        conn.rollback(savepoint2); // Rollback to savepoint2
                    } else if (savepoint1 != null) {
                        conn.rollback(savepoint1); // Rollback to savepoint1
                    }
                    conn.commit(); // Commit outer transaction rollback
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            // Close resources
            try {
                if (stmt != null)
                    stmt.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Explanation of the above Code:

• In the above example, we established the connection to the MySQL database and set the auto-commit to the false to start the transaction.
• We have created the savepoint (Example: savepoint1) before we inserting the initial record in to the database.
• After that, we insert the first record into the “savepoint” table.
• Then we set another savepoint (Example: savepoint2) before we inserting the another record in to the database.
• If any exception will be occurred while the nested transaction, we can rollback to the savepoint2.
• If any exception will be occurred within the outer transaction, we can rollback to the savepoint1.
• Finally, we can commit the transaction in the database, if everything was successful.

Step 4: Run the Code

Save the code in Eclipse IDE. After that run the code on Java Application.

Output:

The output will be shown in the console window in Eclipse IDE as shown below.

Step 5: Verification

If we want to verify the table, the transaction is successfully committed or not, we can open our database and click the following code in the database.

SELECT * FROM `savepoint`;

It will show the below table:

If the table contains the data, our transaction is successfully committed. Otherwise, the transaction is not committed.