Java - Database Connectivity - Transaction Management

Overview

Transaction management is a critical aspect of database connectivity that ensures data integrity and consistency. In Java, transaction management can be achieved using Java Database Connectivity (JDBC). This tutorial explores the basics of transaction management, including how to start, commit, and roll back transactions.

Starting a Transaction

By default, JDBC connections operate in auto-commit mode, which means each SQL statement is committed to the database as soon as it is executed. To start a transaction, you need to disable auto-commit mode:

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

public class TransactionExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String username = "root";
        String password = "password";

        try {
            Connection connection = DriverManager.getConnection(url, username, password);
            connection.setAutoCommit(false);
            System.out.println("Transaction started.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
            

Committing a Transaction

To commit a transaction, you use the commit method of the Connection object. Committing a transaction makes all changes made during the transaction permanent:

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

public class CommitTransactionExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String username = "root";
        String password = "password";

        Connection connection = null;

        try {
            connection = DriverManager.getConnection(url, username, password);
            connection.setAutoCommit(false);

            String sql = "INSERT INTO accounts (name, balance) VALUES (?, ?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "John Doe");
            preparedStatement.setDouble(2, 1000);
            preparedStatement.executeUpdate();

            connection.commit();
            System.out.println("Transaction committed successfully!");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.setAutoCommit(true);
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
            

Rolling Back a Transaction

If an error occurs during a transaction, you can roll back the transaction to undo all changes made during the transaction. Use the rollback method of the Connection object to roll back a transaction:

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

public class RollbackTransactionExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String username = "root";
        String password = "password";

        Connection connection = null;

        try {
            connection = DriverManager.getConnection(url, username, password);
            connection.setAutoCommit(false);

            String sql = "INSERT INTO accounts (name, balance) VALUES (?, ?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "John Doe");
            preparedStatement.setDouble(2, 1000);
            preparedStatement.executeUpdate();

            // Simulate an error
            if (true) {
                throw new SQLException("Simulated error");
            }

            connection.commit();
            System.out.println("Transaction committed successfully!");
        } catch (SQLException e) {
            if (connection != null) {
                try {
                    connection.rollback();
                    System.out.println("Transaction rolled back.");
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.setAutoCommit(true);
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
            

Transaction Management Example

Here is a complete example demonstrating how to start, commit, and roll back a transaction using JDBC:

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

public class TransactionManagementExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String username = "root";
        String password = "password";

        Connection connection = null;

        try {
            connection = DriverManager.getConnection(url, username, password);
            connection.setAutoCommit(false);

            String sql1 = "INSERT INTO accounts (name, balance) VALUES (?, ?)";
            PreparedStatement pstmt1 = connection.prepareStatement(sql1);
            pstmt1.setString(1, "Alice");
            pstmt1.setDouble(2, 1500);
            pstmt1.executeUpdate();

            String sql2 = "UPDATE accounts SET balance = balance - ? WHERE name = ?";
            PreparedStatement pstmt2 = connection.prepareStatement(sql2);
            pstmt2.setDouble(1, 500);
            pstmt2.setString(2, "Alice");
            pstmt2.executeUpdate();

            connection.commit();
            System.out.println("Transaction committed successfully!");
        } catch (SQLException e) {
            if (connection != null) {
                try {
                    connection.rollback();
                    System.out.println("Transaction rolled back.");
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.setAutoCommit(true);
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
            

Summary

In this tutorial, you learned about transaction management in Java Database Connectivity (JDBC). You explored how to start, commit, and roll back transactions to ensure data integrity and consistency. Proper transaction management is crucial for handling errors and maintaining the reliability of your database operations.