Transaction Management in java

Definition: A database application is an application that is running against a relational database and executes one or more transactions.

A transaction is an executing program that contains some database operations, like reading or applying insertions, deletions, or updates to the database.

Transaction example:

package com.STH.JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
public class Transaction_Management {
public static void main(String[] args) throws ClassNotFoundException {
//Select the Query to get the Data from employee table
String QUERY = "select * from employee where empNum = 1";
String QUERY1 = "select * from employee where empNum = 2";
Boolean autoCommit;
String update_query = "update employee set salary = 40000 where empNum = 1"; 
String update_query1 = "update employee set salary = 50000 where empNum = 2"; 
//Update the query to set the dept id for the employee whose empNUM is 3
Class.forName("oracle.jdbc.driver.OracleDriver"); 
try(Connection conn = DriverManager.getConnection("jdbc:oracle:thin:system/pass123@localhost:1521:XE"))
{
Statement statemnt1 = conn.createStatement();
ResultSet rs1 =null;
rs1 = statemnt1.executeQuery(QUERY); 
//Executed the SELECT Query
System.out.println("Getting the data from employee_details table");
displayData(rs1);
System.out.println("Setting the AutoCommit value as FALSE");
conn.setAutoCommit(false);
autoCommit = conn.getAutoCommit();
System.out.println("AutoCommit value of the Connection = "+ autoCommit);
statemnt1 = conn.createStatement();
System.out.println("Executing Update query to update salary of EMPNUM = 1");
System.out.println("Update Query is " + update_query);
int return_rows = statemnt1.executeUpdate(update_query);
System.out.println("Update the data but didn't commit");
Connection conn1 = DriverManager.getConnection("jdbc:oracle:thin:system/pass123@localhost:1521:XE");
System.out.println("Opening new connection");
System.out.println("EMPNUM = 1 data");
Statement statement2 = conn1.createStatement();
ResultSet rs;
rs = statement2.executeQuery(QUERY); 
displayData(rs);
System.out.println("Commit has been done");
conn.commit();
Savepoint s1 = conn.setSavepoint();
System.out.println("SavePoint has been created");
System.out.println("Displaying data of EMPNUM = 1");
System.out.println("Using the Second Connection");
rs = statement2.executeQuery(QUERY); 
displayData(rs);
rs = statemnt1.executeQuery(QUERY); 
//Rollback the transaction
System.out.println("Data of EMPNUM = 2");
rs1 = statemnt1.executeQuery(QUERY1); 
displayData(rs1); 
System.out.println("Updating the salary of EMPNUM = 2");
System.out.println("Update Query is " + update_query1);
statemnt1.executeUpdate(update_query1);
System.out.println("Data of EMPNUM = 2 but didn't commit");
rs1 = statemnt1.executeQuery(QUERY1); 
displayData(rs1);
System.out.println("Rollback is done... so updated data won't be reflected");
conn.rollback(s1);
System.out.println("Data of EMPNUM = 2 after Rollback till the last savepoint");
rs1 = statemnt1.executeQuery(QUERY1); 
displayData(rs1);
}
catch (SQLException e) {
e.printStackTrace();
}
}
public static void displayData(ResultSet rs1) throws SQLException
{
while(rs1.next())
{
int empNum = rs1.getInt("empNum");
String lastName = rs1.getString("lastName");
String firstName = rs1.getString("firstName");
String email = rs1.getString("dept");
String deptNum = rs1.getString("deptNum");
String salary = rs1.getString("salary");
System.out.println(empNum + "," +lastName+ "," +firstName+ "," +dept +","+deptNum +"," +salary);
}
}
}

Acid Properties of Transactions:

1. Atomicity: If every program works correctly without any errors, then everything gets committed to the database. If any of the parts of the execution of the transaction gets rolled back, then it will not commit.
2. Consistency: The consistency property is a property that if any database was in a consistent state before and after the execution of a transaction, the database will also be in a consistent state.
3. Isolation: In this property, multiple transactions will execute concurrently, the system says that for every pair of transactions. It will execute one after the other pair.
4. Durability: Durability ensures that, once a transaction has been committed even if there is a system failure or crash, the transaction’s update does not lose.

Transaction methods:

Method	Description
void setAutoCommit(boolean status)	In this method, It is true by default means each transaction is committed by default.
void commit()	It is used to commit the transaction.
void rollback()	It is used to cancel the transaction.

Transaction management in JDBC using statement:

import java.sql.*; 
class Records{ 
public static void main(String args[])throws Exception{ 
Class.forName("oracle.jdbc.driver.OracleDriver"); 
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle"); 
con.setAutoCommit(false); 
Statement stmt=con.createStatement(); 
stmt.executeUpdate("insert into user420 values(1,'hari',40000)"); 
stmt.executeUpdate("insert into user420 values(2,'Raju',50000)"); 
con.commit(); 
con.close(); 
}} 

Transaction management in JDBC using preparedStatement:

import java.sql.*; 
import java.io.*; 
class TM{ 
public static void main(String args[]){ 
try{ 
Class.forName("oracle.jdbc.driver.OracleDriver"); 
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle"); 
con.setAutoCommit(false); 
PreparedStatement ps=con.prepareStatement("insert into user 420 values(?,?,?)"); 
BufferedReader br=new BufferedReader(new InputStreamReader(System.in)); 
while(true){ 
System.out.println("enter id"); 
String s1=br.readLine(); 
int id=Integer.parseInt(s1); 
System.out.println("enter name"); 
String name=br.readLine(); 
System.out.println("enter salary"); 
String s3=br.readLine(); 
int salary=Integer.parseInt(s3); 
ps.setInt(1,id); 
ps.setString(2,name); 
ps.setInt(3,salary); 
ps.executeUpdate(); 
System.out.println("commit/rollback"); 
String answer=br.readLine(); 
if(answer.equals("commit")){ 
con.commit(); 
} 
if(answer.equals("rollback")){ 
con.rollback(); 
} 
System.out.println("Want to add more records y/n"); 
String ans=br.readLine(); 
if(ans.equals("n")){ 
break; 
} 
} 
con.commit(); 
System.out.println("record successfully saved"); 
con.close();//before closing connection commit() is called 
}catch(Exception e){System.out.println(e);} 
}
}