JDBC Transaction Management and Savepoint Example

In this post we’ll see how to manage a transaction using JDBC in a Java application.

A transaction represents a single unit of work where a set of one or more statements are executed as a unit. In a transaction either all of the statements are executed successfully or none of them.

Table of contents

1. Transaction in JDBC
2. Method for setting transaction isolation level
3. JDBC transaction management example
4. Savepoint in JDBC transaction
5. Setting savepoint in JDBC transaction example

Transaction in JDBC

In JDBC API, the Connection interface provides the following methods for transaction management-

• setAutoCommit()- In JDBC by default a connection is in auto-commit mode which means all its SQL statements will be executed and committed as individual transactions. So, first thing is to set auto-commit mode to false so that SQL statements are grouped into transactions and executed as a single unit of work.
• commit()- This method makes all changes made with in the transaction permanent. Also releases any database locks currently held by this Connection object.
• rollback()- This method is used to undo all changes made in the current transaction if any of the statement fails. Also releases any database locks currently held by this Connection object.

Using these methods the transaction steps in JDBC can be summarized as-

1. To start a transaction set auto-commit mode to false by calling setAutoCommit(false) method.
2. If all the statements with in the transaction execute with any error make the changes permanent by calling commit() method.
3. If any statement doesn’t execute properly abort the transaction by rolling back all the changes done as part of transaction.
4. You can also set save point in transaction and roll back to particular save point that gives you an option to salvage some of the work being done in a transaction rather than losing it all.

Method for setting transaction isolation level

You can also set the transaction isolation level using the Connection object. Connection interface defines following constants for different transaction isolation levels.

• TRANSACTION_NONE- Indicates that transactions are not supported.
• TRANSACTION_READ_UNCOMMITTED- Indicates that dirty reads, non-repeatable reads and phantom reads can occur.
• TRANSACTION_READ_COMMITTED- Indicates that dirty reads are prevented; non-repeatable reads and phantom reads can occur.
• TRANSACTION_REPEATABLE_READ- Indicates that dirty reads and non-repeatable reads are prevented; phantom reads can occur.
• TRANSACTION_SERIALIZABLE- Indicates that dirty reads, non-repeatable reads and phantom reads are prevented.

You can pass any of these constants in the setTransactionIsolation(int level) method to set the required isolation level.

For example you want to set the transaction isolation level to read uncommitted.

connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);

JDBC transaction management example

Let’s take an example of transferring amount from one account to another that involves both of the following steps to be executed or none of them.

1. Withdraw amount from the sender’s account.
2. Deposit amount into the beneficiary’s account.

For the example Account table is used with the columns as acct_num, name, balance.

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

public class JDBCTransactionDemo {
  public static void main(String[] args) {
    Connection connection = null;
    try {
      // Connection info
      Class.forName("com.mysql.cj.jdbc.Driver");
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/knpcode", "root", "admin");
      // Auto commit disabled
      connection.setAutoCommit(false);
      
      int fromAccount = 2;
      int toAccount = 7;
      int amount = 200;
      withdrawAmount(connection, fromAccount, amount);
      depositAmount(connection, toAccount, amount);
      // Commit transaction
      connection.commit();
    } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }catch(SQLException e) {
      e.printStackTrace();
      if(connection != null){
        try {
          // Rolling back transaction
          connection.rollback();
        } catch (SQLException e1) {
          // TODO Auto-generated catch block
          e1.printStackTrace();
        }
      }
    }finally{
      if(connection != null){         
        try {
          connection.close();
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      } 
    }	
  }
  
  private static void withdrawAmount(Connection connection, int accountNum, int amount) throws SQLException{
    String sql = "UPDATE ACCOUNT SET balance = balance - ? WHERE acct_num = ?";
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(sql);
      stmt.setInt(1, amount);
      stmt.setInt(2, accountNum);
      int count = stmt.executeUpdate();
      if(count == 0){
        throw new SQLException("Account number not found " + accountNum);
      }
    }finally{
      if(stmt != null){
        stmt.close();
      }
    }
  }	 
  
  private static void depositAmount(Connection connection, int accountNum, int amount) throws SQLException{
    String sql = "UPDATE ACCOUNT SET balance = balance + ? WHERE acct_num = ?";
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(sql);
      stmt.setInt(1, amount);
      stmt.setInt(2, accountNum);
      int count = stmt.executeUpdate();
      if(count == 0){
        throw new SQLException("Account number not found " + accountNum);
      }
    }finally{
      if(stmt != null){
        stmt.close();
      }
    }    
  }
}

Savepoint in JDBC transaction

Using a Savepoint object you can mark a point in current transaction. When a transaction is rolled back to a savepoint all changes made after that savepoint are undone where as the changes made till the savepoint can be committed. If you have lots of statements in your transaction and you don’t want to loose all of the work if something fails you can set savepoints at intervals to get a chance to commit your work at least till that savepoint.

Connection interface provides two overloaded methods for setting savepoint-

• setSavepoint()- Creates an unnamed savepoint in the current transaction and returns the created Savepoint object.
• setSavepoint(String name)- Creates a savepoint with the given name in the current transaction and returns the created Savepoint object.

There is also a method for releasing a savepoint.

• releaseSavepoint?(Savepoint savepoint)- Removes the specified Savepoint from the current transaction.

Setting savepoint in JDBC transaction example

In the example few records are inserted in a transaction and a savepoint is created after few insertions so that we have a chance to insert records till there in case of rollback.

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

public class SavePointDemo {
  public static void main(String[] args) {
    Connection connection = null;
    Savepoint sp = null;
    try {
      // Load driver
      Class.forName("com.mysql.cj.jdbc.Driver");
      // connection object
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/knpcode", "root", "admin");
      SavePointDemo sd = new SavePointDemo();
      
      // Auto commit disabled
      connection.setAutoCommit(false);

      sd.insertEmployee(connection, "Don", "Keaton", "HR");
      sd.insertEmployee(connection, "Virag", "Sharma", "IT");
      // Setting named savepoint
      sp = connection.setSavepoint("MySavePoint");
      sd.insertEmployee(connection, "Kelly", "Dorji", "IT");
      // Commit transaction
      connection.commit();
    } catch (ClassNotFoundException e) {
    	// TODO Auto-generated catch block
        e.printStackTrace();
    }catch(SQLException e) {
      e.printStackTrace();
      if(connection != null){
        try {
          // savepoint is not reached, rollback the whole transaction
          if(sp == null){
            System.out.println("Rollingback the transaction");
            connection.rollback();
          }else{
            System.out.println("Rollingback to savepoint");
            // rollback to created savepoint
            connection.rollback(sp);
            // Commit till the savepoint
            connection.commit();
          }
        } catch (SQLException e1) {
          // TODO Auto-generated catch block
          e1.printStackTrace();
        }
      }
    }finally{
      if(connection != null){         
        try {
          connection.close();
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      } 
    }	      	     
  }

  private void insertEmployee(Connection connection, String fName, String lName, String dept) throws SQLException{
    String insertSQL = "INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME, DEPARTMENT) values (?, ?, ?)";
    PreparedStatement prepStmt = null;
    try {
      prepStmt = connection.prepareStatement(insertSQL);
      prepStmt.setString(1, fName);
      prepStmt.setString(2, lName);
      prepStmt.setString(3, dept);
      int count = prepStmt.executeUpdate();
      System.out.println("Number of records inserted- " + count);
    }finally{
      if(prepStmt != null){
        prepStmt.close();
      }
    }
  }
}

That's all for the topic JDBC Transaction Management and Savepoint Example. If something is missing or you have something to share about the topic please write a comment.

---

You may also like

• JDBC Batch Insert Update Example
• JDBC ResultSet Interface
• Apache DBCP Connection Pooling Java Example
• Search String in Another String in Java - indexOf, lastIndexOf, contains methods
• How to Sort Java ArrayList
• Tree Sort Java Program
• Frequently Used HDFS Commands With Examples
• Pass Data From Child to Parent Component in React