Transaction Management In JDBC
Posted By : Kiran Sharma | 30-Apr-2018
Transaction – A small unit of a program is called transaction. Here we are talking about database transaction.
A database transaction must maintain ACID properties.
Atomicity: As per this property either all transactions should be successfully done or not all.
Consistency: This property states that database should be in consistent state after transaction execution.
Isolation: One transaction should be isolated from another.
Durability: It means once a transaction is committed , it is durable even in the case of error or power failure.
Transaction makes the performance fast because database is hit only at the time of transaction commit.
In JDBC ,connection interface provides following methods:
void setAutoCommit(boolean status) : this is true by default, which means transaction will be auto commited.
void commit() : commits the transaction. Once you are finished with changes and you want to save the changes then call
commit() method on connection object.
void rollback() : cancels the transaction.
- If your Connection is in auto-commit mode then every SQL statement will be committed to the database on its completion. For simple applications its fine to set auto comit mode on but there are reasons you may want to set it off and manage your own transactions. And the reasons are -to increase performance , to maintain integrity.
?
Demo
import java.sql.*;
import java.io.*;
class TM{
public static void main(String args[]){
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection connection=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
connection.setAutoCommit(false);
PreparedStatement ps=connection.prepareStatement("insert into datatab 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();
String answer=br.readLine();
if(answer.equals("commit")){
connection.commit();
}
if(answer.equals("rollback")){
connection.rollback();
}
System.out.println("add more records y/n");
String ans=br.readLine();
if(ans.equals("n")){
break;
}
}
connection.commit();
System.out.println("record added successfully");
connection.close();
}catch(Exception e){System.out.println(e);}
}}
SAVE POINTS
When you are setting a savepoint that means you are defining a logical rollback point within a transaction
Dem
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
//set a Savepoint
Savepoint savepoint1 = conn.setSavepoint("Savepoint1");
String SQL = "INSERT INTO Employees " +
"VALUES (106, 20, 'Rita', 'Tez')";
stmt.executeUpdate(SQL);
//Submit a malformed SQL statement that breaks
String SQL = "INSERTED IN Employees " +
"VALUES (107, 22, 'Sita', 'Tez')";
stmt.executeUpdate(SQL);
// If there is no error, commit the changes.
conn.commit();
}catch(SQLException se){
// If there is any error.
conn.rollback(savepoint1);
}
Cookies are important to the proper functioning of a site. To improve your experience, we use cookies to remember log-in details and provide secure log-in, collect statistics to optimize site functionality, and deliver content tailored to your interests. Click Agree and Proceed to accept cookies and go directly to the site or click on View Cookie Settings to see detailed descriptions of the types of cookies and choose whether to accept certain cookies while on the site.
About Author
Kiran Sharma
Kiran has good knowledge of java with Servlets, JSPs, Spring, and hibernate frameworks. She is very honest towards her work. Her hobby is listening to music.