Home » JDBC Tutorial » JDBC Transaction

JDBC Transaction

Summary: in this tutorial, you will learn how to perform database transactions in a Java program using JDBC API.

Introduction to JDBC transactions

In JDBC, a transaction is a unit of work performed on a database that should be treated as a single operation. A transaction ensures data integrity and consistency by allowing multiple database operations to either succeed or fail as a whole.

JDBC allows you to manage transactions using the methods of the java.sql.Connection interface:

  • setAutoCommit(boolean autoCommit) sets whether auto-commit mode is enabled. When you set the auto-commit mode to false, you need to explicitly commit or roll back transactions. By default, the autocommit is true.
  • commit() commits the current transaction, saving all changes permanently to the database.
  • rollback() rolls back the current transaction, discarding all changes made since the start of the transaction.

Here’s the basic example of using JDBC transactions:

try {
    // disable auto-commit mode
    conn.setAutoCommit(false); 

    // perform multiple database operations within the transaction
    // ...


    // commit the transaction if all operations succeed
    conn.commit(); 
} catch (SQLException e) {
    // Roll back the transaction if an error occurs
    conn.rollback(); 
} finally {
   // enable auto-commit mode
    conn.setAutoCommit(true); 
}Code language: Java (java)

JDBC transaction examples

Let’s take an example of using a JDBC transaction.

1) Creating new tables

We’ll create some new tables to illustrate the transaction in JDBC.

First, connect to the MariaDB server using the bob account:

mysql -u bob -pCode language: plaintext (plaintext)

It’ll prompt you for a password for the bob user account. Please enter a valid password and press the Enter key to connect.

Second, change the current database to sales:

use sales;Code language: SQL (Structured Query Language) (sql)

Third, create a new table called customers to store customer data:

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE
);Code language: SQL (Structured Query Language) (sql)

Fourth, insert some rows into the customers table:

INSERT INTO
  customers (name, address, email)
VALUES
  (
    'John Doe',
    '123 Main St, Anytown, USA',
    '[email protected]'
  ),
  (
    'Jane Smith',
    '456 Elm St, Springfield, USA',
    '[email protected]'
  ),
  (
    'David Johnson',
    '789 Oak St, Metro City, USA',
    '[email protected]'
  );Code language: SQL (Structured Query Language) (sql)

Fifth, create the orders table to store order data:

CREATE TABLE orders(
   id INT AUTO_INCREMENT PRIMARY KEY,
   order_date DATE NOT NULL,
   customer_id INT NOT NULL,
   FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE CASCADE
);Code language: SQL (Structured Query Language) (sql)

Sixth, create the order_details table to store order line items:

CREATE TABLE order_details(
   id INT AUTO_INCREMENT,
   order_id INT NOT NULL,
   product_id INT NOT NULL,
   price DECIMAL(19,2) NOT NULL,
   tax DECIMAL(19,2) NOT NULL,
   FOREIGN KEY(order_id)  REFERENCES orders(id) ON DELETE CASCADE,
   FOREIGN KEY(product_id)  REFERENCES products(id) ON DELETE CASCADE,
   PRIMARY KEY(id, order_id)
);Code language: SQL (Structured Query Language) (sql)

2) Create an order transaction

The following program creates an order with order details within a transaction:

import java.math.BigDecimal;
import java.sql.Date;
import java.sql.SQLException;
import java.sql.Statement;


public class Main {
    public static void main(String[] args) {

        try (var conn = DBConnection.connect()) {

            if(conn == null) throw new SQLException("Failed to connect to the database.");

            conn.setAutoCommit(false);

            // Insert a row into the orders table
            var insertOrderSQL = "INSERT INTO orders (order_date, customer_id) VALUES (?, ?)";
            try (var pstmt = conn.prepareStatement(insertOrderSQL, Statement.RETURN_GENERATED_KEYS)) {
                pstmt.setDate(1, Date.valueOf("2024-05-10"));
                pstmt.setInt(2, 1);
                pstmt.executeUpdate();

                // Get the order id
                var generatedKeys = pstmt.getGeneratedKeys();
                int orderId = -1;
                if (generatedKeys.next()) {
                    orderId = generatedKeys.getInt(1);
                } else {
                    throw new SQLException("Failed to get the generated order ID.");
                }

                // Insert rows into the order_details table
                var insertOrderDetailsSQL = "INSERT INTO order_details (order_id, product_id, price, tax) " +
                        "VALUES (?, ?, ?, ?)";
                try (var pstmt2 = conn.prepareStatement(insertOrderDetailsSQL)) {
                    // First row
                    pstmt2.setInt(1, orderId);
                    pstmt2.setInt(2, 1);
                    pstmt2.setBigDecimal(3, new BigDecimal("10.00"));
                    pstmt2.setBigDecimal(4, new BigDecimal("1.50"));
                    pstmt2.executeUpdate();

                    // Second row
                    pstmt2.setInt(1, orderId);
                    pstmt2.setInt(2, 2);
                    pstmt2.setBigDecimal(3, new BigDecimal("15.00"));
                    pstmt2.setBigDecimal(4, new BigDecimal("2.25"));
                    pstmt2.executeUpdate();

                    conn.commit(); // Commit the transaction if all operations succeed
                    System.out.println("Transaction committed successfully.");

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

How it works.

First, open a database connection:

var conn = DBConnection.connect()Code language: Java (java)

Second, set the auto-commit mode to false:

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

Third, insert a row into the orders table:

var insertOrderSQL = "INSERT INTO orders (order_date, customer_id) VALUES (?, ?)";
try (var pstmt = conn.prepareStatement(insertOrderSQL, Statement.RETURN_GENERATED_KEYS)) {
    pstmt.setDate(1, Date.valueOf("2024-05-10"));
    pstmt.setInt(2, 1);
    pstmt.executeUpdate();Code language: Java (java)

Fourth, get the inserted order id and use it for inserting rows into the order_details table in the next step:

var generatedKeys = pstmt.getGeneratedKeys();
int orderId = -1;
if (generatedKeys.next()) {
    orderId = generatedKeys.getInt(1);
} else {
    throw new SQLException("Failed to get the generated order ID.");
}Code language: Java (java)

Fifth, insert two rows into the order_details table:

 var insertOrderDetailsSQL = "INSERT INTO order_details (order_id, product_id, price, tax) " +
     "VALUES (?, ?, ?, ?)";
 try (var pstmt2 = conn.prepareStatement(insertOrderDetailsSQL)) {
     // First row
     pstmt2.setInt(1, orderId);
     pstmt2.setInt(2, 2);
     pstmt2.setBigDecimal(3, new BigDecimal("19.99"));
     pstmt2.setBigDecimal(4, new BigDecimal("0.08"));
     pstmt2.executeUpdate();

     // Second row
     pstmt2.setInt(1, orderId);
     pstmt2.setInt(2, 3);
     pstmt2.setBigDecimal(3, new BigDecimal("24.99"));
     pstmt2.setBigDecimal(4, new BigDecimal("0.08"));
     pstmt2.executeUpdate();Code language: Java (java)

Sixth, commit the transaction by calling the commit() method:

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

Finally, roll back the transaction if an error occurs in the catch blocks:

conn.rollback();Code language: Java (java)

Verify the transaction

First, connect to the MariaDB server using the bob account:

mysql -u bob -pCode language: plaintext (plaintext)

It’ll prompt you for a password for the bob user account. Please enter a valid password and press the Enter key to connect.

Second, change the current database to sales:

use sales;Code language: SQL (Structured Query Language) (sql)

Third, retrieve data from the orders table:

SELECT * FROM orders;Code language: SQL (Structured Query Language) (sql)

Output:

+----+------------+-------------+
| id | order_date | customer_id |
+----+------------+-------------+
|  1 | 2024-05-10 |           1 |
+----+------------+-------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

Fourth, retrieve data from the order_details table:

SELECT * FROM order_details;Code language: SQL (Structured Query Language) (sql)

Output:

+----+----------+------------+-------+------+
| id | order_id | product_id | price | tax  |
+----+----------+------------+-------+------+
|  2 |        1 |          2 | 19.99 | 0.08 |
|  3 |        1 |          3 | 24.99 | 0.08 |
+----+----------+------------+-------+------+
2 rows in set (0.00 sec)Code language: JavaScript (javascript)

The output shows that the program has successfully performed a transaction that inserts the rows into the orders and order_details tables.

Summary

  • Use the setAutoCommit() method to set the auto-commit mode to false to start a transaction.
  • Call the commit() method to apply the changes permanently to the database.
  • Call the rollback() method to discard all the changes made since the start of the transaction.
JDBC Delete
Previously
JDBC Delete
Up Next
Java Swing
Java Swing