JDBC - Savepoint example

Posted on February 20, 2017


Technologies used:   JDK 1.8.0_121 | MySQL 5.7.12 | Eclipse Mars.2 (4.5.2)

A Savepoint object is used to mark intermediate point within the current transaction. After setting a savepoint, the transaction can be rolled back to that savepoint without affecting preceding work.

The Connection.setSavepoint() method is used to set a savepoint object within the current transaction.

The following example demonstrates how the Savepoint object is used in JDBC application.

JDBCSavepointExample.java

package com.boraji.tutorial.jdbc;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;

/**
 * @author imssbora
 */
public class JDBCSavepointExample {
   private static final String INSERT_SQL = "INSERT INTO employee "
         + "(EMP_ID, NAME, DOB) VALUES (?,?,?)";

   public static void main(String[] args) {
      String jdbcUrl = "jdbc:mysql://localhost:3306/BORAJI";
      String username = "root";
      String password = "admin";
      DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
      try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);) {

         // Disable auto commit mode
         conn.setAutoCommit(false);

         try (PreparedStatement insertStmt = conn.prepareStatement(INSERT_SQL);) {

            // Insert 1st record
            insertStmt.setInt(1, 1);
            insertStmt.setString(2, "Michael");
            insertStmt.setDate(3, new Date(dateFormat.parse("1995-07-01").getTime()));
            insertStmt.executeUpdate();

            // Insert 2st record
            insertStmt.setInt(1, 2);
            insertStmt.setString(2, "Sunil");
            insertStmt.setDate(3, new Date(dateFormat.parse("1988-03-22").getTime()));
            insertStmt.executeUpdate();

            // Insert 3st record
            insertStmt.setInt(1, 3);
            insertStmt.setString(2, "Mike");
            insertStmt.setDate(3, new Date(dateFormat.parse("1980-05-12").getTime()));
            insertStmt.executeUpdate();

            // Create Savepoint
            Savepoint savepoint = conn.setSavepoint();

            // Insert 4st record
            insertStmt.setInt(1, 4);
            insertStmt.setString(2, "Manish");
            insertStmt.setDate(3, new Date(dateFormat.parse("1992-01-21").getTime()));
            insertStmt.executeUpdate();

            // Insert 5st record
            insertStmt.setInt(1, 5);
            insertStmt.setString(2, "Albert");
            insertStmt.setDate(3, new Date(dateFormat.parse("1972-07-05").getTime()));
            insertStmt.executeUpdate();

            // Rollback to savepoint
            conn.rollback(savepoint);

            // Commit statement
            conn.commit();

            System.out.println("Transaction is commited successfully.");
         } catch (SQLException | ParseException e) {
            e.printStackTrace();
            if (conn != null) {
               try {
                  // Roll back transaction
                  System.out.println("Transaction is being rolled back.");
                  conn.rollback();
               } catch (Exception ex) {
                  ex.printStackTrace();
               }
            }
         }
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

Output

Transaction is commited successfully.

Data in the EMPLOYEE table after running the above program. 

mysql> select EMP_ID,NAME,DOB from employee;
+--------+---------+------------+
| EMP_ID | NAME    | DOB        |
+--------+---------+------------+
|      1 | Michael | 1995-07-01 |
|      2 | Sunil   | 1988-03-22 |
|      3 | Mike    | 1980-05-12 |
+--------+---------+------------+