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.

MethodDescription
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

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.

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.

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

Table 1
Table 1
Table 2
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.

Output

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

Table 1
Table 1
Table 2
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.


Leave a Reply

Your email address will not be published. Required fields are marked *