JDBC - Transaction

A transfer is the best example of a transaction. If you transfer $ 1500 from account A to account B, you have the following process: 

Process One: Withdraw $ 1,500 from the A account. 
Process Two: Deposit $ 1,500 into the B account.

If Process One succeeds and Process Two fails, the purpose of transaction management is to cancel Process One and restore the state before Process One executes. You need to consider Process One and Process Two as one unit to manage the transaction. In this case, the bundle of Process One and Process Two is called a transaction unit.

The Auto-commit mode of the Connection instance is the default. Auto-commit mode regards each SQL statement as a transaction unit.

Change the auto-commit mode to false, and defining the commit time in the program is the coding that manages transactions. 

con.setAutoCommit(false);
Withdraw $ 1500 from Account A.
Deposit $ 1500 into Account B.
con.commit();

For the transaction example, create the ACCOUNT table in the Scott account and insert the test data as below. 

create table account (
 accountno varchar2(3) primary key,
 balance number,
 constraint account_balance_ck check(balance between 0 and 3000)
)
/
insert into account values ('111', 3000)
/
insert into account values ('222', 2000)
/

Refer to the JDBC program procedure below and complete the main() of TransactionPairs.java.

  1. Loading a JDBC Driver
  2. Getting a Connection
  3. Execute SQL
  4. [If the SQL statement is a select statement, use the returned ResultSet to process the data.]
  5. Returning Resources

Our goal is to execute the following SQL: 

UPDATE account SET balance = balance - 1500 WHERE accountno = '111'
UPDATE account SET balance = balance + 1500 WHERE accountno = '222'
TransactionPairs.java
package net.java_school.jdbc.test;

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

public class TransactionPairs {
  static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
  static final String USER = "scott";
  static final String PASS = "tiger";
	
  public static void main(String[] args) {
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    }
		
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "UPDATE account SET balance = balance + ? WHERE accountno = ?";
		
    try {
      con = DriverManager.getConnection(URL, USER, PASS);
      
      con.setAutoCommit(false);
      
      pstmt = con.prepareStatement(sql);
			
      pstmt.setInt(1, -1500);
      pstmt.setString(2, "111");
      pstmt.executeUpdate();
			
      pstmt.setInt(1, 1500);
      pstmt.setString(2, "222");
      pstmt.executeUpdate();
      
      con.commit();
      
    } catch (SQLException e) {
      e.printStackTrace();
      try {
        con.rollback();
      } catch (SQLException e1) {
        e1.printStackTrace();
      }
    } finally {
      try {
        pstmt.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
      try {
        con.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }

}

Run it and check the results with SQL * PLUS. You will see an the following exception message.

java.sql.SQLException: ORA-02290: check constraint (SCOTT.ACCOUNT_BALANCE_CK) is violated.

The cause of the error is that the BALANCE column of the ACCOUNT table can only store a value between 0 and 3000. The example rollbacks the bank transfer in the catch block when an exception occurs. Check the rollback via SQL*PLUS.