Transaction Management in JDBC Example

 Must Know :

By default connection is auto-commit, so all delete queries will be committed automatically.

(Also must know : delete is a DML(Data Manipulation Language) command, queries are not committed automatically in database)

We may stop connection from doing auto-commits by setting auto-commit to false using > con.setAutoCommit(false);

Well in that we may commit transactions any time using con.commit();

Also, We may set connection back to default behavior of doing auto-commit by using > con.setAutoCommit(true) or simply using con.setAutoCommit();

But what will be benefit of using con.setAutoCommit(false)?

If any transaction goes wrong than we may halt execution further related transactions before calling con.commit() and call con.rollback() in catch block.

Transaction Management in JDBC :

Here are the most important points about Transaction Management in JDBC.

• A transaction means, it is a group of operations used to perform a task.
• A transaction can reach either success state or failure state.
• If all operations are completed successfully then the transaction becomes success.
• If any one of the operation fail then all remaining operations will be cancelled and finally transaction will reach to fail state.

Types of Transactions :

The basic transactions are of two types.

• Local Transactions
• Global / Distributed Transactions

Local Transactions :

If all the operations are executed on one/same database, then it is called as local transaction.

Global / Distributed Transaction :

If the operations are executed on more than one database then it is called as global transactions.

Example : If we transfer the money from account1 to account2 of same bank, then it is called as local transaction. If we transfer the money from account1 to account2 of different banks, then it is called as global or distributed transaction.

JDBC can supports only local transactions. For distributed transactions, we must use either EJB technology or Spring Framework.

Transaction Management in JDBC Example :

We can get the Transaction support in JDBC from Connection interface. The Connection interface given 3 methods to perform Transaction Management in JDBC.

• setAutoCommit()
• commit()
• rollback()

Transaction setAutoCommit() :

Before going to begin the operations, first we need to disable the auto commit mode. This can be done by calling setAutoCommit(false).

By default, all operations done from the java program are going to execute permanently in database. Once the permanent execution happened in database, we can’t revert back them (Transaction Management is not possible).

Transaction commit() :

If all operations are executed successfully, then we commit a transaction manually by calling the commit() method.

Transaction rollback() :

If any one of the operation failed, then we cancel the transaction by calling rollback() method.

connection.setAutoCommit(false);

try{

----------
----------

connection.commit();

}catch(Exception e){

connection.rollback();

}

Complete Example :

Jdbc_TransactionManagement_Example .java

package com.onlinetutorialspoint.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Jdbc_TransactionManagement_Example {

    public static void main(String[] args) throws Exception {
        Connection connection = null;
        Statement statement = null;

        try {

            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/onlinetutorialspoint", "root",
                "123456");
            connection.setAutoCommit(false);
            statement = connection.createStatement();
            statement
                .executeUpdate("insert person values ('5001','Hyderabad','Chandra Shekhar')");
            statement
                .executeUpdate("insert person values ('5002','Banglore','Ram')");
            connection.commit();
            System.out.println("Transaction is commited.");
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
            connection.rollback();
        }
    }
}

Output:

Transaction is committed.

If we update with the statements,

statement.executeUpdate("insert person values ('5003','Hyderabad','Chandra Shekhar')");
statement.executeUpdate("insert person values ('5001','Banglore','Ram')");

We can get the Exception like below and the transaction will be roll backed.

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '5001' for key 'PRIMARY'
Transaction is rollbacked !

Reference:

https://www.onlinetutorialspoint.com/jdbc/transaction-management-jdbc-example.html

https://www.javamadesoeasy.com/2015/12/jdbc-transactions-create-savepoint.html

https://www.mainjava.com/jdbc/jdbc-transaction-tutorial-commit-and-rollback-example/