Java SQL Server: Transaction

Summary: in this tutorial, you will learn how to perform an SQL Server transaction in Java using JDBC API.

This tutorial begins where the Deleting From a Table in Java tutorial left off.

Managing transactions in JDBC

In JDBC, a transaction is a set of database operations executed as a single unit of work. Transactions allow you to maintain data integrity and consistency, and ensure that all the database operations either complete successfully or fail.

To manage transactions in JDBC, you use the methods of the java.sql.Connection interface:

• setAutoCommit() – This method controls the auto-commit mode of the connection. The auto-commit mode is either true or false. By default, the auto-commit is true. If the auto-commit is true, JDBC treats each SQL statement as a transaction and automatically commits to the database. If the auto-commit is false, you can manage the transaction manually. In this mode, you need to call the commit() method explicitly to save your changes to the database or call rollback() method to undo them.
• commit() – This method makes all changes permanent in the database. Typically, you call the commit() method after a series of successful operations to save the changes together.
• rollback() – This method undoes all changes made in the current transaction by reverting the database to its previous state. In practice, you call the rollback() method if any operation fails or when you want to cancel the transaction.

We’ll show you how to create a new order in the BookStore database that does the following within a transaction.

• First, check the inventory quantity with the order quantity.
• Next, insert a new row into the Orders table.
• Then, insert a new row into the OrderDetails table.
• After that, reduce the book inventory by the order quantity.
• Finally, commit the transaction or roll it back if any of the above operations fail.

Setting up sample data

Step 1. Launch the SQL Server Management Studio (SSMS) and connect to the SQL Server.

Step 2. Create a query and execute the following statements to insert data into the Books, BookAuthors, and Inventories tables:

-- 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');Code language: SQL (Structured Query Language) (sql)

Performing a transaction

Step 1. Create a new file OrderDB that stores the OrderDB class.

Step 2. Define the OrderDB class that handles order tasks:

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());
        }
    }
}Code language: Java (java)

How it works.

First, define a private property connection:

private final Connection connection;Code language: Java (java)

Second, initialize the connection property with the argument in the constructor of the OrderDB class:

public OrderDB(Connection connection) {
   this.connection = connection;
}Code language: Java (java)

Third, define a public method createOrder that creates a new order:

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();
  }
}Code language: Java (java)

How it works.

First, define a createOrder method that creates an order for a customerId, bookId, quantity, price, and order date:

public void create(int customerId, int bookId, int orderQty, double bookPrice, LocalDate orderDate) throws DBException {Code language: Java (java)

The method throws a DBException if any error occurs during creating the order.

Second, set the auto-commit to false to start the transaction:

connection.setAutoCommit(false);Code language: Java (java)

When you set the auto-commit mode to false, changes you made to the database are not permanent until you commit the transaction. By default, the auto-commit is set to true.

Third, check whether the inventory is higher than the order quantity by calling the getInventoryQuantity( ) and compare the inventory quantity with the order quantity. If the inventory is less than the order quantity, throw a DBException:

int inventoryQty = getInventoryQuantity(bookId);
if (inventoryQty < orderQty) {
  throw new DBException("Insufficient inventory");
}Code language: Java (java)

Fourth, insert a new order and get the inserted order ID by calling the insertOrder method.

int orderId = insertOrder(customerId, orderQty, bookPrice, orderDate);Code language: Java (java)

Fifth, use the orderId to insert a new row into the OrderDetails table by calling the insertOrderDetails() method:

insertOrderDetails(orderId, bookId, orderQty, bookPrice);Code language: Java (java)

Sixth, update the inventory by calling the updateInventory() method:

updateInventory(orderQty, bookId);Code language: Java (java)

Seventh, apply the changes to the database by calling the commit() method of the Connection object:

connection.commit();Code language: Java (java)

Eighth, roll back the transaction if any SQLException occurs by calling the rollbackTransaction() method:

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

Finally, set the auto-commit back to true in the finally block:

finally {
  setAutoCommitTrue();
}Code language: Java (java)

Step 3. Define the private method getInventoryQuantity() that gets the inventory quantity of a book specified by book ID:

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");
      }
    }
  }
}Code language: Java (java)

Step 4. Define the private method insertOrder() that inserts a new row into the Orders table:

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.");
      }
    }
  }
}Code language: Java (java)

The insertOrder() method returns the generated ID of the inserted row.

Step 5. Define the private method insertOrderDetails that inserts a new row into the OrderDetails table:

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();
  }
}Code language: Java (java)

Step 6. Define the updateInventory() method that updates the inventory quantity by reducing it by the order quantity:

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();
  }
}Code language: Java (java)

Step 7. Define the rollbackTransaction() method that rolls back a transaction by calling rollback() method of the Connection object:

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

Step 8. Define the private method setAutoCommitTrue to set the auto-commit mode back to true by calling the setAutoCommit method of the Connection object:

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

Step 9. Modify the main() method of the Main class to use the create() method of the OrderDB class:

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());
        }
    }
}Code language: Java (java)

How it works.

First, connect to the SQL Server:

try (var connection = SQLServerConnection.connect()) {Code language: Java (java)

Next, create a new OrderDB object:

var orderDB = new OrderDB(connection);Code language: Java (java)

Then, initialize variables that store the order data:

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

After that, call the create() method of the OrderDB object to create a new order:

orderDB.create(customerId, bookId,orderQty, bookPrice,orderDate );Code language: Java (java)

Finally, display the error message if any exception occurs:

} catch(SQLException | DBException e) {
  System.err.println(e.getMessage());
}Code language: Java (java)

Download the project source code

Download the project source code

Summary

• Use setAutoCommit, commit, and rollback methods of the Connection object to manage transactions in JDBC.
• Call the setAutoCommit(true) method to start a transaction.
• Call the commit() method to make permanent changes to the database.
• Call the rollback() metthod to undo the changes within a transaction.