JDBC Controlling Transation Behavior
In this tutorial you will learn how to control a JDBC Transaction behavior, and how to use them in your application
In this tutorial you will learn how to control a JDBC Transaction behavior, and how to use them in your applicationHow To Control Transaction Behavior of JDBC
A transaction is an individual unit of work comprised of several operation. These operations must perform well in order to preserve data integrity.
All transactions shares some properties they are Atomicity, Isolation, Consistency.
Atomicity- This implies individuality, any individual operation is said to be atomic.
Isolation- Isolation is a transaction property in which any transaction could not affect each other. A transaction in progress not rolled back or not committed can be isolated from other transaction.
Durability- Once the transaction is committed successfully, state changed committed by the transaction, must be durable and persistence.
Therefore a transaction ends in two ways 1. Committed successfully or 2. Roll Back.
Transaction Isolation Levels
There are four levels of transaction isolation
1. TRANSACTION_READ_UNCOMMITTED- It allows non-repeatable reads, dirty reads and phantom reads to occur
2. TRANSACTION_READ_COMMITTED- It ensures only those data can be read which is committed.
3. TRANSACTION_REPEATABLE_READ- It is closer to serializable but phantom reads are also possible.
4. TRANSACTION_SERIALIZABLE- In this level of isolation dirty reads, non-repeatable reads, and phantom reads are prevented.
An example given below which controls the transaction using Transaction Isolation level
At first create table named student in MySql database and inset values into it as.
CREATE TABLE student (
RollNo int(9) PRIMARY KEY NOT NULL,
Name tinytext NOT NULL,
Course varchar(25) NOT NULL,
Address text
);
Insert Value Into student table
INSERT INTO student VALUES(1, 'Vinay', 'MCA', 'Delhi') ;
JDBCTransactionExmple.java
package roseindia.net;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCTransactionExmple {
public static void main(String[] args) throws SQLException {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String driverName = "com.mysql.jdbc.Driver";
String connectionUrl = "jdbc:mysql://localhost:3306/";
String databaseName = "student";
String userName = "root";
String password = "root";
try {
// Loading Driver
Class.forName(driverName);
// Creating Connection
conn = DriverManager.getConnection(connectionUrl + databaseName,
userName, password);
// Setting auto commit false
conn.setAutoCommit(false);
System.out.println("Databese Connection Done........");
} catch (ClassNotFoundException e) {
System.out.println(e.toString());
System.out.println("Error In Connection");
System.exit(0);
}
try {
// Creating Statement
stmt = conn.createStatement();
// Creating Query String
String updateQuery1 = "INSERT INTO student VALUES(3,'Vinay','MCA','Motihari')";
String updateQuery2 = "INSERT INTO student VALUES(4,'Ram','BCA','Patna')";
String selectQuery = "SELECT * FROM student";
stmt.executeUpdate(updateQuery1);
stmt.executeUpdate(updateQuery2);
rs = stmt.executeQuery(selectQuery);
// Calling commit() method
conn.commit();
while (rs.next()) {
System.out.println("Roll No.- " + rs.getInt("RollNo")
+ ", Name- " + rs.getString("Name") + ", Course- "
+ rs.getString("Course") + ", Address- "
+ rs.getString("Address"));
}
} catch (Exception e) {
System.out.println(e.toString());
System.exit(0);
} finally {
// Closing Connection
conn.close();
stmt.close();
rs.close();
System.out.println("Connection Closed..........");
}
}
}
When you run this application it will display message as shown below:| Databese Connection Done........ Roll No.- 1, Name- vnay, Course- MCA, Address- Motihari Roll No.- 2, Name- John, Course- BCA, Address- Patna Roll No.- 3, Name- Vinay, Course- MCA, Address- Motihari Roll No.- 4, Name- Ram, Course- BCA, Address- Patna Connection Closed.......... |
TransactionIsolationExample.java
package roseindia.net;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TransactionIsolationExample {
public static void main(String[] args) throws SQLException {
Connection con = null; // connection reference variable for getting
// connection
Statement stmt = null; // Statement reference variable for query
// Execution
ResultSet rs = null; // ResultSet reference variable for saving query
// result
String conUrl = "jdbc:mysql://192.168.10.13:3306/";
String driverName = "com.mysql.jdbc.Driver";
String databaseName = "student";
String usrName = "root";
String usrPass = "root";
try {
// Loading Driver
Class.forName(driverName);
} catch (ClassNotFoundException e) {
System.out.println(e.toString());
}
try {
// Getting Connection
con = DriverManager.getConnection(conUrl + databaseName, usrName,
usrPass);
// Creating Statement for query execution
stmt = con.createStatement();
// creating Query String
String query = "SELECT * FROM student";
// excecuting query
rs = stmt.executeQuery(query);
while (rs.next()) {
// Didplaying data of tables
System.out.println("Roll No " + rs.getInt("RollNo") + ", Name "
+ rs.getString("Name") + ", Course "
+ rs.getString("Course") + ", Address "
+ rs.getString("Address"));
}
DatabaseMetaData dbMetaData = con.getMetaData();
if (dbMetaData
.supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE)) {
System.out.println("Transaction Isolation level= "
+ con.getTransactionIsolation());
// Setting Transaction Isolation Level
// You can set Its String Value or its int value
con.setTransactionIsolation(2);
}
} catch (Exception e) {
System.out.println(e.toString());
} finally {
// Closing connection
con.close();
stmt.close();
rs.close();
}
}
}
When you run this application it will display message as shown below:
|
Roll No 1, Name vnay, Course MCA, Address Motihari Roll No 2, Name John, Course BCA, Address Patna Roll No 3, Name Vinay, Course MCA, Address Motihari Roll No 4, Name Ram, Course BCA, Address Patna Transaction Isolation level= 4 |
