17.1 Overview of JDBC Transaction Management

A transaction is a unit of work in which either all operations must execute or none of them. To understand the importance of transaction, think of a example which applies on all of us. “Transferring Amount from one account to another “ – this operations includes below at least below two steps

1. Deduct the balance from sender’s account
2. Add the amount to the receiver’s account.

Now think of the situation where amount is deducted from sender’s account but not gets delivered to receiver account due to some errors. Such issues are managed by transaction management in which both the steps are performed in a single unit of work where either both steps are performed successfully or in case anyone gets failed, it should be roll backed.

In this chapter we will discuss the transaction management support of JDBC.

17.2 Transaction Management API

By default the auto-commit mode of JDBC connection is set as true which means after every Data manipulation statement (DML) like insert, delete, update , data is committed.

This feature is fine for simple application but think if we are developing application where we need to make sure that a bunch or group of statements should execute all or none to make  the data consistent (like the balance scenario we highlighted in overview section).

To support the transaction, JDBC connection object provides methods.

17.3 Examples of Trnsaction Management

We will use the MySQL database .

    Create schema JDBCTutorial;

     CREATE  TABLE `Account` (

     'accountNo'  INT NOT NULL ,

    'balance' INT NULL ,

    PRIMARY KEY ('accountNo' );

 

17.3.1 – Write an Example of amount transfer from one account to another without transaction management.

Solution –

Below program will create two account and then will ask sender and receiver account number and amount to transfer. In below example we are not using any transaction management.

1. setAutoCommit(boolean) – Default value of auto-commit is true and we need to set it to false so that we can commit or rollback the data based on conditions.
2. commit() – After successful execution of all related SQL statements in group, we can call a commit() method on a connection object to commit the data in the database.
3. rollback()- this method is used to rollback the changes so in case any error occurred in any of the logically grouped statement  then complete group of statements will be roll backed. Usually we should add this statement in catch block.
4. Savepoint setSavepoint(String name) – We can set a savepoint  using setSavePoint() method .This method allows us to assign or create a logical group and using rollback(Sting) method we can rollback all the statements after the given savepoint has been set.
5. rollback(String name) – We can rollback all the statements that are executed after savepoint has been set.
   1.  Use the below sql statement to create a schema
   2. Create table Account with 2 columns  (account number and balance ) using below SQL.