SQLite Java: Transaction

Summary: in this tutorial, you will learn how to use JDBC API to manage transactions in SQLite databases.

Sample database for transaction demo

We’ll create two more new tables in the my.db database:

1. The materials table stores the materials.
2. The inventory table stores the relationship between the warehouses and materials table. In addition, the inventory table has the qty column that stores the quantity

The following statements create the materials and inventory tables.

CREATE TABLE materials (
    id INTEGER PRIMARY KEY, 
    description TEXT NOT NULL
);

CREATE TABLE inventory (
    warehouse_id INTEGER,
    material_id INTEGER,
    qty REAL,
    PRIMARY KEY (warehouse_id, material_id),
    FOREIGN KEY (warehouse_id) REFERENCES warehouses (id),
    FOREIGN KEY (material_id) REFERENCES materials (id)
  );Code language: SQL (Structured Query Language) (sql)

The following diagram illustrates the tables and their relationship.

Introduction to database transactions

A transaction represents a set of operations performed as a single unit of work. In other words, if any operation in the set fails, SQLite aborts other operations and rolls back the data to its initial state.

A transaction has four attributes, which are known as ACID:

• The letter A stands for atomicity. It means that each transaction must be all or nothing. If any operation in the transaction fails, the database state remains unchanged.
• The letter C stands for consistency. It makes sure that any transaction will bring the data in the database from one valid state to another.
• The letter I stands for isolation. This is for concurrency control. It ensures that all the concurrent execution of transactions produce the same result as if they were executed sequentially.
• The letter D stands for durability. It means when the transaction is committed, it will remain intact regardless of any errors such as power loss.

Java SQLite transaction example

When you connect to an SQLite database using JDBC, the auto-commit mode is enabled. It means that every query is automatically committed.

To disable the auto-commit mode, you use the setAutoCommit() method of the Connection object as follows:

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

To commit the transaction, you use the commit() method of the Connection object.

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

If an error occurs, you can use the rollback() method of the Connection object to roll the transaction back:

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

The following program adds new material to the materials table and posts the inventory within the same transaction:

import java.sql.*;

public class Main {
    public void addInventory(String material, int warehouseId, double qty) {
        String url = "jdbc:sqlite:my.db";

        // store the inserted material id
        int materialId = 0;
        // Connect to the SQLite database
        try (var conn = DriverManager.getConnection(url)) {
            // Disable auto-commit mode
            conn.setAutoCommit(false);

            // Insert a new material
            try (var pstmt1 = conn.prepareStatement("INSERT INTO materials(description) VALUES(?)")) {
                pstmt1.setString(1, material);
                pstmt1.executeUpdate();

            } catch (SQLException e) {
                conn.rollback();
            }

            // Get the inserted material id
            try (var stmt = conn.createStatement()) {
                var generatedKeys = stmt.executeQuery("SELECT last_insert_rowid()");

                if (generatedKeys.next()) {
                    materialId = generatedKeys.getInt(1);
                }
            } catch (SQLException e) {
                conn.rollback();
            }


            // Insert the inventory
            try (var pstmt2 = conn.prepareStatement("INSERT INTO inventory(warehouse_id,material_id,qty) VALUES(?,?,?)")) {
                pstmt2.setInt(1, warehouseId);
                pstmt2.setInt(2, materialId);
                pstmt2.setDouble(3, qty);
                pstmt2.executeUpdate();
            } catch (SQLException e) {
                conn.rollback();
            }

            // commit work
            conn.commit();
        } catch (SQLException e){
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        var app = new Main();
        app.addInventory("HP Laptop", 3, 100);
    }

}Code language: Java (java)

To check the result, you can query data from the materials and inventory table using the inner join clause as follows:

SELECT
  name,
  description,
  qty
FROM
  materials
  INNER JOIN inventory ON inventory.material_id = materials.id
  INNER JOIN warehouses ON warehouses.id = inventory.warehouse_id;Code language: SQL (Structured Query Language) (sql)

Output:

name           description  qty
-------------  -----------  -----
Raw Materials  HP Laptop    100.0Code language: CSS (css)

In this tutorial, you have learned how to manage the transaction in SQLite using Java JDBC.