JDBC Transaction Management Example

In this tutorial, we will explore how to manage transactions using JDBC in Java. Transaction management is a crucial aspect of database operations, ensuring that a series of operations either complete successfully or fail as a unit, maintaining data integrity.

Table of Contents

1. Introduction to JDBC Transaction Management
2. Setting Up the MySQL Database
3. JDBC Driver and Dependencies
4. Transaction Management with JDBC
5. Example Code
6. Exception Handling in Transactions
7. Conclusion

1. Introduction to JDBC Transaction Management

A transaction is a sequence of operations performed as a single logical unit of work. A transaction must be atomic, consistent, isolated, and durable (ACID properties). Using transactions in JDBC, you can ensure that either all operations within the transaction succeed or none of them are applied to the database.

Key Concepts

• Commit: Makes all changes made in the transaction permanent.
• Rollback: Undoes all changes made in the transaction.
• Auto-commit: Each SQL statement is treated as a transaction and is automatically committed.

2. Setting Up the MySQL Database

First, ensure that MySQL is installed and running. Create a new database and table for this example.

CREATE DATABASE test_db;

USE test_db;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL,
    department VARCHAR(50) NOT NULL
);

3. JDBC Driver and Dependencies

To connect to MySQL from Java, you need the MySQL JDBC driver. Download it from the MySQL website.

Add the downloaded JAR file to your project's build path. 

If you are using Maven, add the following dependency to your pom.xml:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.26</version>
</dependency>

4. Transaction Management with JDBC

In JDBC, transactions are managed using the Connection interface. By default, JDBC connections are in auto-commit mode, which means each SQL statement is treated as a transaction and is automatically committed. To manage transactions manually, you need to disable auto-commit mode.

Steps for Transaction Management

1. Disable auto-commit mode.
2. Execute multiple SQL statements.
3. Commit the transaction if all statements succeed.
4. Rollback the transaction if any statement fails.

5. Example Code

Transaction Management Example

package com.example.jdbc;

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

public class JDBCTransactionExample {

    private static final String URL = "jdbc:mysql://localhost:3306/test_db?useSSL=false";
    private static final String USER = "root";
    private static final String PASSWORD = "root";

    private static final String INSERT_EMPLOYEE_SQL = "INSERT INTO employees (name, email, department) VALUES (?, ?, ?);";
    private static final String UPDATE_EMPLOYEE_SQL = "UPDATE employees SET email = ? WHERE name = ?;";

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {
            // Disable auto-commit mode
            connection.setAutoCommit(false);

            try (PreparedStatement insertStatement = connection.prepareStatement(INSERT_EMPLOYEE_SQL);
                 PreparedStatement updateStatement = connection.prepareStatement(UPDATE_EMPLOYEE_SQL)) {

                // Insert a new employee
                insertStatement.setString(1, "Ravi Kumar");
                insertStatement.setString(2, "ravi.kumar@example.com");
                insertStatement.setString(3, "IT");
                insertStatement.executeUpdate();

                // Update an existing employee's email
                updateStatement.setString(1, "ravi.singh@example.com");
                updateStatement.setString(2, "Ravi Kumar");
                updateStatement.executeUpdate();

                // Commit the transaction
                connection.commit();
                System.out.println("Transaction committed successfully!");

            } catch (SQLException e) {
                // Rollback the transaction if any statement fails
                connection.rollback();
                System.out.println("Transaction rolled back.");
                e.printStackTrace();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Explanation

1. Disable Auto-commit: connection.setAutoCommit(false); disables the auto-commit mode.
2. Execute SQL Statements: Insert and update operations are performed within the transaction.
3. Commit the Transaction: connection.commit(); commits the transaction, making all changes permanent.
4. Rollback the Transaction: connection.rollback(); rolls back the transaction if any statement fails.

6. Exception Handling in Transactions

Exception handling is crucial in transaction management to ensure that the transaction is rolled back if any error occurs during the execution of SQL statements. This prevents partial updates and maintains data integrity.

Example Code for Exception Handling

The above example already includes exception handling. If any SQL statement fails, the transaction is rolled back, and an appropriate message is printed.

catch (SQLException e) {
    connection.rollback();
    System.out.println("Transaction rolled back.");
    e.printStackTrace();
}

7. Conclusion

In this tutorial, we covered the basics of JDBC transaction management and demonstrated how to perform transactions using JDBC with a MySQL database. By following these steps, you can ensure that your database operations are atomic and consistent, maintaining data integrity. Transaction management is a powerful feature that allows you to handle complex database operations reliably and efficiently.