JDBC Transaction Tutorial: Commit() And Rollback() Example

 In this tutorial you will learn

1. What is Transaction Management in JDBC?
2. Advantage of Transaction Management
3. Programming Example

WHAT IS TRANSACTION MANAGEMENT IN JDBC?

Transaction Management works great when you need to execute set of task and each task executes when previous task completes. Transaction Management ensures that all the task executes successfully and if one task fails, the whole task would be rollback to previous state. Simply, either all the statements are executed, or none of the statements is executed.

FACT ABOUT TRANSACTION

1. ACID: It represents ACID Properties.

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.

---

2. Disable Auto Commit Mode: When the connection is created it is auto commit mode. It means all the individual SQL Statements will be treated as Transaction. When using Transaction, Disable Auto Commit Mode and call it explicitly. After disabling commit mode, no statement will be execute until you call Commit() method explicitly. Each statement will be executed after the previous call of Commit method.

con.setAutoCommit(false);

In JDBC, Connection interface provides methods to manage transaction.

METHOD	DESCRIPTION
void setAutoCommit (boolean status)	It is true bydefault means each transaction is committed bydefault.
void commit()	commits the transaction.
void rollback()	cancels the transaction.

ADVANTAGE OF TRANSACTION MANAGEMENT

1. Transaction Management widely used in financial application where you want to ensure that if any problem happens meanwhile the payment process, the whole transaction rollback.
2. Transaction Management is also very beneficial when you need to insert multiple row in multiple table simultaneously. It ensures that all the table gets successful execution of statement and if any table fails to execute statement, all the row roll backed and none of table affected.
3. Transaction Treats all the SQL statements as a single logical unit and if one statement fails the entire transaction fails.
4. Commit and Rollback : Commit() method does the changes in database table and Rollback methods undo all the changes done by current connection con.

PROGRAMMING EXAMPLE

In this programming example, I will insert two row in a two different table using Transaction. This example explains Commit() method. In the next example you will get Rollback.

Step 1. Create Two Tables table1 and table2 with following description

CREATE TABLE Table1 (
  ID int(11) NOT NULL AUTO_INCREMENT,
  PRODUCT varchar(50) NULL,
  PRICE varchar(10) NULL,
PRIMARY KEY(ID)
)

CREATE TABLE Table2(
  ID int(11) NOT NULL AUTO_INCREMENT,
  PRODUCT varchar(50) NULL,
  PRICE varchar(10) NULL,
PRIMARY KEY(ID)
)

You can use your own table to execute query. If you don’t have any, then create table using above query.

Step 2. Programming Example of Commit() Method.

package AdvanceJDBC;

import java.sql.*;

public class Transaction_Example
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	Statement stmt = null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);
	    con.setAutoCommit(false);
	    //Step 2 : Initialize Statement
	    stmt=con.createStatement();
	    //Step 3 : SQL Query
	    String query1="INSERT INTO Table1(PRODUCT,PRICE) VALUES('Laptop','29000')";
	    String query2="INSERT INTO Table2(PRODUCT,PRICE) VALUES('Mouse','305')";
	    
	    stmt.executeUpdate(query1);
	    stmt.executeUpdate(query2);
	    //If you run this program without con.commit you will notice that there is no insert in table1 and table2
	    con.commit();
	    System.out.println("Row Inserted");
	    
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output

Row Inserted
Closing the connection

ROLLBACK EXAMPLE

In this example I will use Rollback method to ensure all the successful insertion in both table. You will see what happen when statement execution fails in table 2.

Programming Example

Example 1: With Commit(false)

In this program I have Passed long value in PRICE column for table2. It will raise exception because of size of PRICE is set to varchar(10). This is for showing you what happened when one query executed but another get failed in transaction. You will notice that there were no changes in table.

package AdvanceJDBC;

import java.sql.*;

public class Rollback_Example
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args) throws SQLException
{
	Connection con = null;
	Statement stmt = null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);
	    con.setAutoCommit(false);
	    //Step 2 : Initialize Statement
	    stmt=con.createStatement();
	    //Step 3 : SQL Query
	    String query1="INSERT INTO Table1(PRODUCT,PRICE) VALUES('Mobile','11500')";
	    stmt.executeUpdate(query1);
	    System.out.println("Table1 Successfull");
	    String query2="INSERT INTO Table2(PRODUCT,PRICE) VALUES('Charger','3055241234534')";
	    stmt.executeUpdate(query2);
	    //If you run this program without con.commit you will notice that there is no insert in table1 and table2
	    con.commit();
	    System.out.println("Row Inserted");
	    
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    con.rollback();
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output
Table1 Successfull
Cannot connect !
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column ‘PRICE’ at row 1

Table 1

Table 2

 

Example 2: Without Commit() method or Commit(true)

See this example. In this example I haven’t set Commit(false) so when the program executes query, first statement successfully inserted but raise exception in second statement. It is because I have passed more than 10 character long in PRICE that is set to varchar(10) only.

package AdvanceJDBC;

import java.sql.*;

public class Rollback_Example
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args) throws SQLException
{
	Connection con = null;
	Statement stmt = null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);
	    con.setAutoCommit(true);
	    //Step 2 : Initialize Statement
	    stmt=con.createStatement();
	    //Step 3 : SQL Query
	    String query1="INSERT INTO Table1(PRODUCT,PRICE) VALUES('Mobile','11500')";
	    stmt.executeUpdate(query1);
	    System.out.println("Table1 Successfull");
	    String query2="INSERT INTO Table2(PRODUCT,PRICE) VALUES('Charger','3055241234534')";
	    stmt.executeUpdate(query2);
	    //If you run this program without con.commit you will notice that there is no insert in table1 and table2
	    con.commit();
	    System.out.println("Row Inserted");
	    
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    //con.rollback();
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output

Table1 Successfull
Cannot connect !
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column ‘PRICE’ at row 1

Table 1

Table 2

SUMMARY

In this tutorial you learned what is Transaction Management in JDBC and how to use it in Programming. The Commit(false) ensures all the successful execution of statement. If any of them not execute, the whole process gets failed. Rollback() method reverse all the changes that is made by current connection.