Font Awesome Icons

Tutorial

Transaction Management in JDBC

Transaction represents a single unit of work.

The ACID properties describes the transaction management well. ACID stands for Atomicity, Consistency, isolation and durability.

Atomicity means either all successful or none.

Consistency ensures bringing the database from one consistent state to another consistent state.

Isolation ensures that transaction is isolated from other transaction.

Durability means once a transaction has been committed, it will remain so, even in the event of errors, power loss etc

In JDBC, Connection interface provides methods to manage transaction.

Example of transaction management in jdbc using Statement

import java.sql.*;

class FetchRecords

{

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(190,’alok’,40000)”);

stmt.executeUpdate(“insert into user420 values(191,‘shashikant’,50000)”);

con.commit();

con.close();

}}

Example of 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 user420 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);}

}}