In this tutorial you will learn how to control a JDBC Transaction behavior, and how to use them in your application

How 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

Download this example code

Related Tags for JDBC Controlling Transation Behavior: