Java SQL Server: транзакция

В этом руководстве вы узнаете, как выполнить транзакцию SQL Server на Java с использованием JDBC API.

Это руководство начинается с того места, где закончилось руководство по удалению из таблицы в Java.

Управление транзакциями в JDBC

В JDBC транзакция — это набор операций базы данных, выполняемых как единая единица работы. Транзакции позволяют поддерживать целостность и согласованность данных и гарантировать, что все операции базы данных либо завершатся успешно, либо завершатся неудачей.

Для управления транзакциями в JDBC используются методы интерфейса java.sql.Connection:

• setAutoCommit() – этот метод управляет режимом автоматической фиксации соединения. Режим автоматической фиксации может быть true или false. По умолчанию автоматическая фиксация имеет значение true. Если автоматическая фиксация имеет значение true, JDBC обрабатывает каждый оператор SQL как транзакцию и автоматически фиксирует его в базе данных. Если автоматическая фиксация имеет значение false, вы можете управлять транзакцией вручную. В этом режиме вам необходимо явно вызвать метод commit(), чтобы сохранить изменения в базе данных, или вызвать метод rollback(), чтобы отменить их.
• commit() – Этот метод делает все изменения постоянными в базе данных. Обычно метод commit() вызывается после серии успешных операций, чтобы сохранить изменения вместе.
• rollback() – Этот метод отменяет все изменения, внесенные в текущей транзакции, возвращая базу данных в предыдущее состояние. На практике метод rollback() вызывается, если какая-либо операция завершается неудачей или когда вы хотите отменить транзакцию.

Мы покажем вам, как создать новый заказ в базе данных BookStore, который выполняет следующие действия в рамках транзакции.

• Сначала сравните количество товара на складе с количеством заказа.
• Далее вставьте новую строку в таблицу «Заказы».
• Затем вставьте новую строку в таблицу OrderDetails.
• После этого уменьшите количество книг на сумму заказа.
• Наконец, зафиксируйте транзакцию или откатите ее, если какая-либо из вышеперечисленных операций завершится неудачей.

Настройка выборочных данных

Шаг 1. Запустите SQL Server Management Studio(SSMS) и подключитесь к SQL Server.

Шаг 2. Создайте запрос и выполните следующие операторы для вставки данных в таблицы Books, BookAuthors и Inventories:

-- Insert books into the Books table
INSERT INTO Books(Title, Publisher, ISBN, PublishedDate) VALUES
('SQL Server Performance Tuning', 'Tech Books Publishing', '978-5432167890', '2022-04-15'),
('Inside SQL Server', 'Expert Press', '978-1987654321', '2021-07-20'),
('Pro SQL Server Administration', 'Practical SQL Publishing', '978-2122334455', '2023-05-30'),
('SQL Server Advanced Data Management', 'Pro Code Press', '978-6677881234', '2020-10-25'),
('SQL Server Internals: A Deep Dive', 'Academic Press', '978-5566773322', '2022-12-15');
DECLARE @BookID1 INT, @BookID2 INT, @BookID3 INT, @BookID4 INT, @BookID5 INT;
SELECT @BookID1 = BookID FROM Books WHERE ISBN = '978-5432167890';
SELECT @BookID2 = BookID FROM Books WHERE ISBN = '978-1987654321';
SELECT @BookID3 = BookID FROM Books WHERE ISBN = '978-2122334455';
SELECT @BookID4 = BookID FROM Books WHERE ISBN = '978-6677881234';
SELECT @BookID5 = BookID FROM Books WHERE ISBN = '978-5566773322';
-- Insert records into the BookAuthors table with AuthorID 2
INSERT INTO BookAuthors(BookID, AuthorID)
VALUES(@BookID1, 2),
      (@BookID2, 2),
      (@BookID3, 2),
      (@BookID4, 2),
      (@BookID5, 2);
-- Populate the Inventories table with random quantities
INSERT INTO Inventories(BookID, Qty)
SELECT BookID, ABS(CHECKSUM(NEWID()) % 101) + 100
FROM Books;
-- Insert customers
INSERT INTO [dbo].[Customers](FirstName, LastName, Email, PhoneNumber, Address) VALUES
('John', 'Doe', ' [email protected] ', '123-456-7890', '123 Elm Street, Springfield, IL 62701'),
('Jane', 'Smith', ' [email protected] ', '234-567-8901', '456 Oak Avenue, Metropolis, NY 10001'),
('Michael', 'Johnson', ' [email protected] ', '345-678-9012', '789 Maple Road, Gotham City, NJ 07001'),
('Emily', 'Davis', ' [email protected] ', '456-789-0123', '321 Pine Lane, Smallville, KS 67501'),
('David', 'Williams', ' [email protected] ', '567-890-1234', '654 Cedar Blvd, Star City, CA 90210');

Выполнение транзакции

Шаг 1. Создайте новый файл OrderDB, в котором хранится класс OrderDB.

Шаг 2. Определите класс OrderDB, который обрабатывает задачи заказа:

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.LocalDate;
import java.sql.Date;
import java.sql.Connection;
public class OrderDB {
    private final Connection connection;
    public OrderDB(Connection connection) {
        this.connection = connection;
    }
    public void create(int customerId, int bookId, int quantity, double price, LocalDate orderDate) throws DBException {
        try {
            // Begin transaction
            connection.setAutoCommit(false);
            // Check inventory
            int currentQty = getInventoryQuantity(bookId);
            if(currentQty < quantity) {
                throw new DBException("Insufficient inventory");
            }
            // Insert into Orders and get the generated order ID
            int orderId = insertOrder(customerId, quantity, price, orderDate);
            // Insert into OrderDetails
            insertOrderDetails(orderId, bookId, quantity, price);
            // Update inventory
            updateInventory(quantity, bookId);
            // Commit the transaction
            connection.commit();
        } catch(SQLException e) {
            rollbackTransaction();
            throw new DBException("Database error: " + e.getMessage());
        } finally {
            setAutoCommitTrue();
        }
    }
    private int getInventoryQuantity(int bookId) throws SQLException, DBException {
        var sql = "SELECT Qty FROM Inventories WHERE BookId = ?";
        try(var stmt = connection.prepareStatement(sql)) {
            stmt.setInt(1, bookId);
            try(var rs = stmt.executeQuery()) {
                if(rs.next()) {
                    return rs.getInt("Qty");
                } else {
                    throw new DBException("Book not found in inventory");
                }
            }
        }
    }
    private int insertOrder(int customerId, int quantity, double price, LocalDate orderDate) throws SQLException, DBException {
        var sql = "INSERT INTO Orders(OrderDate, CustomerId, TotalAmount) VALUES(?, ?, ?)";
        try(var stmt = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS)) {
            stmt.setDate(1, Date.valueOf(orderDate));
            stmt.setInt(2, customerId);
            stmt.setDouble(3, price * quantity);
            stmt.executeUpdate();
            try(var rs = stmt.getGeneratedKeys()) {
                if(rs.next()) {
                    return rs.getInt(1);
                } else {
                    throw new DBException("Failed to retrieve order ID.");
                }
            }
        }
    }
    private void insertOrderDetails(int orderId, int bookId, int quantity, double price) throws SQLException {
        var sql = "INSERT INTO OrderDetails(OrderId, BookId, Quantity, Price) VALUES(?, ?, ?, ?)";
        try(var stmt = connection.prepareStatement(sql)) {
            stmt.setInt(1, orderId);
            stmt.setInt(2, bookId);
            stmt.setInt(3, quantity);
            stmt.setDouble(4, price);
            stmt.executeUpdate();
        }
    }
    private void updateInventory(int quantity, int bookId) throws SQLException {
        var sql = "UPDATE Inventories SET Qty = Qty - ? WHERE BookId = ?";
        try(var stmt = connection.prepareStatement(sql)) {
            stmt.setInt(1, quantity);
            stmt.setInt(2, bookId);
            stmt.executeUpdate();
        }
    }
    private void rollbackTransaction() throws DBException {
        try {
            connection.rollback();
        } catch(SQLException e) {
            throw new DBException("Error rolling back transaction: " + e.getMessage());
        }
    }
    private void setAutoCommitTrue() throws DBException {
        try {
            connection.setAutoCommit(true);
        } catch(SQLException e) {
            throw new DBException("Error setting auto-commit: " + e.getMessage());
        }
    }
}

Как это работает.

Сначала определим связь с частной собственностью:

private final Connection connection;

Во-вторых, инициализируйте свойство соединения с аргументом в конструкторе класса OrderDB:

public OrderDB(Connection connection) {
   this.connection = connection;
}

В-третьих, определите публичный метод createOrder, который создает новый заказ:

public void create(int customerId, int bookId, int orderQty, double bookPrice, LocalDate orderDate) throws DBException {
  try {
    // Begin transaction
    connection.setAutoCommit(false);
    // Check inventory
    int inventoryQty = getInventoryQuantity(bookId);
    if(inventoryQty < orderQty) {
      throw new DBException("Insufficient inventory");
    }
    // Insert into Orders and get the generated order ID
    int orderId = insertOrder(customerId, orderQty, bookPrice, orderDate);
    // Insert into OrderDetails
    insertOrderDetails(orderId, bookId, orderQty, bookPrice);
    // Update inventory
    updateInventory(orderQty, bookId);
    // Commit the transaction
    connection.commit();
  } catch(SQLException e) {
    rollbackTransaction();
    throw new DBException("Database error: " + e.getMessage());
  } finally {
    setAutoCommitTrue();
  }
}

Как это работает.

Сначала определите метод createOrder, который создает заказ для customerId, bookId, количества, цены и даты заказа:

public void create(int customerId, int bookId, int orderQty, double bookPrice, LocalDate orderDate) throws DBException {

Метод выдает исключение DBException, если во время создания заказа возникает какая-либо ошибка.

Во-вторых, установите для параметра auto-commit значение false, чтобы начать транзакцию:

connection.setAutoCommit(false);

Если вы устанавливаете режим автокоммита на false, изменения, внесенные вами в базу данных, не будут постоянными, пока вы не зафиксируете транзакцию. По умолчанию автокоммит установлен на true.

В-третьих, проверьте, превышает ли инвентарь количество заказа, вызвав getInventoryQuantity( ) и сравните количество инвентаря с количеством заказа. Если инвентарь меньше количества заказа, выдайте DBException:

int inventoryQty = getInventoryQuantity(bookId);
if(inventoryQty < orderQty) {
  throw new DBException("Insufficient inventory");
}

В-четвертых, вставьте новый заказ и получите идентификатор вставленного заказа, вызвав метод insertOrder.

int orderId = insertOrder(customerId, orderQty, bookPrice, orderDate);

В-пятых, используйте orderId для вставки новой строки в таблицу OrderDetails, вызвав метод insertOrderDetails():

insertOrderDetails(orderId, bookId, orderQty, bookPrice);

В-шестых, обновите инвентарь, вызвав метод updateInventory():

updateInventory(orderQty, bookId);

В-седьмых, примените изменения к базе данных, вызвав метод commit() объекта Connection:

connection.commit();

Восьмое, откатите транзакцию, если возникнет какое-либо исключение SQLException, вызвав метод rollbackTransaction():

} catch(SQLException e) {
  rollbackTransaction();
  throw new DBException("Database error: " + e.getMessage());
}

Наконец, верните автоматическую фиксацию в значение true в блоке finally:

finally {
  setAutoCommitTrue();
}

Шаг 3. Определите закрытый метод getInventoryQuantity(), который получает количество книг в инвентаре, указанное по идентификатору книги:

private int getInventoryQuantity(int bookId) throws SQLException, DBException {
  var sql = "SELECT Qty FROM Inventories WHERE BookId = ?";
  try(var stmt = connection.prepareStatement(sql)) {
    stmt.setInt(1, bookId);
    try(var rs = stmt.executeQuery()) {
      if(rs.next()) {
        return rs.getInt("Qty");
      } else {
        throw new DBException("Book not found in inventory");
      }
    }
  }
}

Шаг 4. Определите закрытый метод insertOrder(), который вставляет новую строку в таблицу Orders:

private int insertOrder(int customerId, int quantity, double price, LocalDate orderDate) throws SQLException, DBException {
  var sql = "INSERT INTO Orders(OrderDate, CustomerId, TotalAmount) VALUES(?, ?, ?)";
  try(var stmt = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS)) {
    stmt.setDate(1, Date.valueOf(orderDate));
    stmt.setInt(2, customerId);
    stmt.setDouble(3, price * quantity);
    stmt.executeUpdate();
    try(var rs = stmt.getGeneratedKeys()) {
      if(rs.next()) {
        return rs.getInt(1);
      } else {
        throw new DBException("Failed to retrieve order ID.");
      }
    }
  }
}

Метод insertOrder() возвращает сгенерированный идентификатор вставленной строки.

Шаг 5. Определите закрытый метод insertOrderDetails, который вставляет новую строку в таблицу OrderDetails:

private void insertOrderDetails(int orderId, int bookId, int quantity, double price) throws SQLException {
  var sql = "INSERT INTO OrderDetails(OrderId, BookId, Quantity, Price) VALUES(?, ?, ?, ?)";
  try(var stmt = connection.prepareStatement(sql)) {
    stmt.setInt(1, orderId);
    stmt.setInt(2, bookId);
    stmt.setInt(3, quantity);
    stmt.setDouble(4, price);
    stmt.executeUpdate();
  }
}

Шаг 6. Определите метод updateInventory(), который обновляет количество запасов, уменьшая его на количество заказа:

private void updateInventory(int quantity, int bookId) throws SQLException {
  var sql = "UPDATE Inventories SET Qty = Qty - ? WHERE BookId = ?";
  try(var stmt = connection.prepareStatement(sql)) {
    stmt.setInt(1, quantity);
    stmt.setInt(2, bookId);
    stmt.executeUpdate();
  }
}

Шаг 7. Определите метод rollbackTransaction(), который откатывает транзакцию, вызывая метод rollback() объекта Connection:

private void rollbackTransaction() throws DBException {
  try {
    connection.rollback();
  } catch(SQLException e) {
    throw new DBException("Error rolling back transaction: " + e.getMessage());
  }
}

Шаг 8. Определите закрытый метод setAutoCommitTrue, чтобы вернуть режим автоматической фиксации в значение true, вызвав метод setAutoCommit объекта Connection:

private void setAutoCommitTrue() throws DBException {
  try {
    connection.setAutoCommit(true);
  } catch(SQLException e) {
    throw new DBException("Error setting auto-commit: " + e.getMessage());
  }
}

Шаг 9. Измените метод main() класса Main, чтобы использовать метод create() класса OrderDB:

import java.sql.SQLException;
import java.time.LocalDate;
public class Main {
    public static void main(String[] args) {
        try(var connection = SQLServerConnection.connect()) {
            //
            var orderDB = new OrderDB(connection);
            var customerId = 1;
            var bookId = 1;
            var orderQty = 5;
            var bookPrice = 29.99;
            var orderDate = LocalDate.of(2024,7,27);
            orderDB.create(customerId, bookId,orderQty, bookPrice,orderDate );
        } catch(SQLException | DBException e) {
            System.err.println(e.getMessage());
        }
    }
}

Как это работает.

Сначала подключитесь к SQL Server:

try(var connection = SQLServerConnection.connect()) {

Далее создайте новый объект OrderDB:

var orderDB = new OrderDB(connection);

Затем инициализируйте переменные, в которых хранятся данные заказа:

var customerId = 1;
var bookId = 1;
var orderQty = 5;
var bookPrice = 29.99;
var orderDate = LocalDate.of(2024, 7, 27);

После этого вызовите метод create() объекта OrderDB для создания нового заказа:

orderDB.create(customerId, bookId,orderQty, bookPrice,orderDate );

Наконец, отобразите сообщение об ошибке, если возникнет какое-либо исключение:

} catch(SQLException | DBException e) {
  System.err.println(e.getMessage());
}

Загрузите исходный код проекта

Загрузите исходный код проекта

Краткое содержание

• Используйте методы setAutoCommit, commit и rollback объекта Connection для управления транзакциями в JDBC.
• Вызовите метод setAutoCommit(true), чтобы начать транзакцию.
• Вызовите метод commit(), чтобы внести постоянные изменения в базу данных.
• Вызовите метод rollback(), чтобы отменить изменения внутри транзакции.