JDBC Transactions

Imagine you're at a carnival trying to win a prize at one of those games where you have to toss rings onto bottles. Now, let's say you're having a great round, and you've already got a few rings on the bottles. Suddenly, your friend taps you on the shoulder, distracting you just as you're about to throw the winning ring. Disaster! You miss your shot and lose your progress.

Well, in the world of databases, something similar can happen without the right precautions in place. That's where JDBC transactions come into play, acting like your trusty carnival buddy, making sure your progress is safe even if something unexpected comes up.

In simpler terms, a JDBC transaction is like a safety net for your database actions. It helps ensure that if something goes wrong while you're making changes to your database, like adding new data or updating existing records, you can roll back those changes and keep your data in a consistent state. It's like having a backup plan in case things don't go as smoothly as planned.

So, whether you're managing a database for a small business or working on a big project for a multinational corporation, understanding JDBC transactions is like having a reliable friend by your side, ready to help you keep your data safe and sound, no matter what surprises come your way.

Commit

Absolutely! Let's dive deeper into Java SQL commits with a practical example. Imagine you're building an online bookstore application, and you need to update the inventory when a customer purchases a book. You want to make sure that the inventory update and the customer's order are both saved in the database reliably. That's where JDBC transactions and commits come into play.

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

public class Bookstore {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/bookstore";
    private static final String DB_USER = "username";
    private static final String DB_PASSWORD = "password";

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            // Start a transaction
            connection.setAutoCommit(false);

            // Simulate a customer purchasing a book
            int customerId = 101;
            int bookId = 201;

            // Update the inventory
            updateInventory(connection, bookId);

            // Save the customer's order
            saveOrder(connection, customerId, bookId);

            // Commit the transaction
            connection.commit();
            System.out.println("Transaction committed successfully!");
        } catch (SQLException e) {
            System.err.println("Transaction failed. Rolling back changes...");
            e.printStackTrace();
        }
    }

    private static void updateInventory(Connection connection, int bookId) throws SQLException {
        String updateQuery = "UPDATE books SET quantity = quantity - 1 WHERE id = ?";
        try (PreparedStatement statement = connection.prepareStatement(updateQuery)) {
            statement.setInt(1, bookId);
            statement.executeUpdate();
            System.out.println("Inventory updated successfully!");
        }
    }

    private static void saveOrder(Connection connection, int customerId, int bookId) throws SQLException {
        String insertQuery = "INSERT INTO orders (customer_id, book_id) VALUES (?, ?)";
        try (PreparedStatement statement = connection.prepareStatement(insertQuery)) {
            statement.setInt(1, customerId);
            statement.setInt(2, bookId);
            statement.executeUpdate();
            System.out.println("Order saved successfully!");
        }
    }
}

In this example, we establish a connection to our bookstore database and start a transaction by setting autoCommit to false. Then, we simulate a customer purchasing a book by updating the inventory and saving the order details in the database. After both actions are completed successfully, we commit the transaction, ensuring that either both the inventory update and order insertion succeed, or neither of them does. If an exception occurs during any step, the transaction is rolled back, reverting any changes made so far.

Rollback

Let's extend the example to include rollback functionality. Imagine that after a successful order is placed, we encounter an error while trying to update the inventory. In such a scenario, we need to rollback the transaction to ensure that the database remains consistent.

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

public class Bookstore {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/bookstore";
    private static final String DB_USER = "username";
    private static final String DB_PASSWORD = "password";

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            // Start a transaction
            connection.setAutoCommit(false);

            // Simulate a customer purchasing a book
            int customerId = 101;
            int bookId = 201;

            // Update the inventory
            updateInventory(connection, bookId);

            // Save the customer's order
            saveOrder(connection, customerId, bookId);

            // Commit the transaction
            connection.commit();
            System.out.println("Transaction committed successfully!");
        } catch (SQLException e) {
            System.err.println("Transaction failed. Rolling back changes...");
            e.printStackTrace();
        }
    }

    private static void updateInventory(Connection connection, int bookId) throws SQLException {
        String updateQuery = "UPDATE books SET quantity = quantity - 1 WHERE id = ?";
        try (PreparedStatement statement = connection.prepareStatement(updateQuery)) {
            statement.setInt(1, bookId);
            statement.executeUpdate();
            System.out.println("Inventory updated successfully!");
        }
    }

    private static void saveOrder(Connection connection, int customerId, int bookId) throws SQLException {
        String insertQuery = "INSERT INTO orders (customer_id, book_id) VALUES (?, ?)";
        try (PreparedStatement statement = connection.prepareStatement(insertQuery)) {
            statement.setInt(1, customerId);
            statement.setInt(2, bookId);
            statement.executeUpdate();
            System.out.println("Order saved successfully!");
        }
    }
}

In this extended example, let's introduce a hypothetical error scenario where the inventory update fails due to some unforeseen issue:

// Simulate a customer purchasing a book
int customerId = 101;
int bookId = 201;

try {
    // Start a transaction
    connection.setAutoCommit(false);

    // Update the inventory
    updateInventory(connection, bookId);

    // Save the customer's order
    saveOrder(connection, customerId, bookId);

    // Introduce a hypothetical error - e.g., division by zero
    int error = 1 / 0; // This line will throw an ArithmeticException

    // Commit the transaction
    connection.commit();
    System.out.println("Transaction committed successfully!");
} catch (SQLException e) {
    System.err.println("Transaction failed. Rolling back changes...");
    e.printStackTrace();
    connection.rollback(); // Rollback the transaction if an SQL exception occurs
} catch (Exception e) {
    System.err.println("An unexpected error occurred. Rolling back changes...");
    e.printStackTrace();
    connection.rollback(); // Rollback the transaction for any other unexpected exception
}

Now, if an error occurs during the execution of the transaction, whether due to an SQL exception or any other unexpected exception, the catch block will be triggered, and the rollback() method will be called to revert any changes made so far. This ensures that the database remains consistent even in the event of errors, providing a safety net for our data operations.

Using Savepoint

Savepoints allow us to create checkpoints within a transaction, giving us the flexibility to roll back to a specific point if needed without rolling back the entire transaction. Let's extend our bookstore example to include savepoints:

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

public class Bookstore {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/bookstore";
    private static final String DB_USER = "username";
    private static final String DB_PASSWORD = "password";

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            // Start a transaction
            connection.setAutoCommit(false);

            // Simulate a customer purchasing books
            int customerId = 101;
            int[] bookIds = {201, 202};

            // Create a savepoint
            Savepoint savepoint = connection.setSavepoint("startTransaction");

            try {
                // Process each book purchase
                for (int bookId : bookIds) {
                    // Update the inventory
                    updateInventory(connection, bookId);

                    // Save the customer's order
                    saveOrder(connection, customerId, bookId);
                }

                // Commit the transaction
                connection.commit();
                System.out.println("Transaction committed successfully!");
            } catch (SQLException e) {
                System.err.println("Transaction failed. Rolling back to savepoint...");
                e.printStackTrace();
                connection.rollback(savepoint); // Rollback to the savepoint
            }
        } catch (SQLException e) {
            System.err.println("Database connection error: " + e.getMessage());
            e.printStackTrace();
        }
    }

    private static void updateInventory(Connection connection, int bookId) throws SQLException {
        String updateQuery = "UPDATE books SET quantity = quantity - 1 WHERE id = ?";
        try (PreparedStatement statement = connection.prepareStatement(updateQuery)) {
            statement.setInt(1, bookId);
            statement.executeUpdate();
            System.out.println("Inventory updated successfully for book ID: " + bookId);
        }
    }

    private static void saveOrder(Connection connection, int customerId, int bookId) throws SQLException {
        String insertQuery = "INSERT INTO orders (customer_id, book_id) VALUES (?, ?)";
        try (PreparedStatement statement = connection.prepareStatement(insertQuery)) {
            statement.setInt(1, customerId);
            statement.setInt(2, bookId);
            statement.executeUpdate();
            System.out.println("Order saved successfully for book ID: " + bookId);
        }
    }
}

In this example, we introduce an array of book IDs representing multiple books purchased by a customer. Before processing each book purchase, we create a savepoint named "startTransaction" using connection.setSavepoint("startTransaction"). This savepoint marks the beginning of our transaction.

If an error occurs during the processing of any book purchase, we catch the SQLException, rollback to the savepoint using connection.rollback(savepoint), and thereby revert the changes made for that specific book purchase while keeping the changes for previous purchases intact.