JDBC Transactions with Example

A transaction is a set of SQL operations that need to be either executed all successfully or not at all. Failure to perform even one operation leads to an inconsistent and erroneous database.

A database must satisfy the ACID properties (Atomicity, Consistency, Isolation, and Durability) to guarantee the success of a database transaction.

1. Atomicity: Each transaction should be carried out in its entirety; if one part of the transaction fails, then the whole transaction fails.

2. Consistency: The database should be in a valid state before and after the performed transaction.

3. Isolation: Each transaction should execute in complete isolation without knowing the existence of other transactions.

4. Durability: Once the transaction is complete, the changes made by the transaction are permanent (even in the occurrence of unusual events such as power loss).

Disabling Auto-Commit Mode

When a connection is created, it is in auto-commit mode. This means that each individual SQL statement is treated as a transaction and is automatically committed right after it is executed.
The way to allow two or more statements to be grouped into a transaction is to disable the auto-commit mode. This is demonstrated in the following code, where the connection is an active connection:
connection.setAutoCommit(false);

Committing Transactions

After the auto-commit mode is disabled, no SQL statements are committed until you call the method to commit explicitly. All statements executed after the previous call to the method commit are included in the current transaction and committed together as a unit.
In JDBC API, the Connection interface provides the setAutoCommit() , commit() and rollback() methods to perform transaction management.
The below diagram shows all the JDBC Connection interface transaction management methods:
In this method, the auto-commit mode is disabled for the connection con, which means that the two prepared statements insertStmt and updateStmt are committed together when the method commit is called.

JDBC - Transaction Management Example

A transaction is a set of one or more statements that are executed as a unit. Either all statements will be executed successfully or none of them.
In JDBC API, the Connection interface provides the setAutoCommit() , commit() and rollback() methods to perform transaction management.

Technologies used

  • JDK - 1.8 or later
  • MySQL - 5.7.12
  • IDE - Eclipse Neon
  • JDBC API - 4.2
The following steps are required for transaction management in JDBC API.
  1. Disable auto-commit mode by passing the false value to the setAutoCommit() method.
  2. Call the commit() method to commit the transaction if all statements are executed successfully.
  3. Call the rollback() method to cancel the transaction if any one of statements fails. Here is an example to demonstrate the above steps.
package com.javaguides.jdbc.batch;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * JDBC Transaction Management Example
 * @author Ramesh Fadatare
 *
 */
public class JDBCTransactionExample {
    private static final String INSERT_USERS_SQL = "INSERT INTO users " +
        "  (id, name, email, country, password) VALUES " + " (?, ?, ?, ?, ?);";
    private static final String UPDATE_USERS_SQL = "update users set name = ? where id = ?;";

    private static final String jdbcUrl = "jdbc:mysql://localhost:3306/mysql_database?useSSL=false";
    private static final String username = "root";
    private static final String password = "root";

    public static void main(String[] args) {

        try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);) {

            // STEP 1 - Disable auto commit mode
            conn.setAutoCommit(false);

            try (PreparedStatement insertStmt = conn.prepareStatement(INSERT_USERS_SQL); PreparedStatement updateStmt = conn.prepareStatement(UPDATE_USERS_SQL);) {

                // Create insert statement
                insertStmt.setInt(1, 200);
                insertStmt.setString(2, "Tony");
                insertStmt.setString(3, "[email protected]");
                insertStmt.setString(4, "US");
                insertStmt.setString(5, "secret");
                insertStmt.executeUpdate();

                // Create update statement
                updateStmt.setString(1, "Ram");
                updateStmt.setInt(2, 200);
                updateStmt.executeUpdate();

                // STEP 2 - Commit insert and update statement
                conn.commit();
                System.out.println("Transaction is commited successfully.");
            } catch (SQLException e) {
                printSQLException(e);
                if (conn != null) {
                    try {
                        // STEP 3 - Roll back transaction
                        System.out.println("Transaction is being rolled back.");
                        conn.rollback();
                    } catch (Exception ex) {
                        ex.printStackTrace();
                    }
                }
            }
        } catch (SQLException e) {
            printSQLException(e);
        }
    }

    public static void printSQLException(SQLException ex) {
        for (Throwable e: ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}
Output:
Transaction is commited successfully.

JDBC - Transactions Key Points

  1. A transaction is a set of SQL operations that need to be either executed all successfully or not at all.
  2. Transaction-related methods are supported in the Connection interface.
  3. By default auto-commit mode is set to true, so all changes you make through the connection are committed automatically to the database.
  4. You can use setAutoCommit(false); to enable manual commits. With auto-commit not enabled, you need to explicitly commit or rollback transactions.
  5. If the commit() method does not execute in manual commit mode, there will be no change in the database.
  6. You can divide a big transaction into multiple milestones. These milestones are referred to as savepoints. This way you may save the changes to a database up to a milestone once the milestone is achieved.

Comments