SQLite Java: Transaction

Summary: in this tutorial, we will show you how to use JDBC transaction to manage transactions in SQLite database.

Sample database for transaction demo

Before starting with the Java, we will create two new tables in the test.db database.

1. The materials table stores the materials master.
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 stock data.

The following statements create the materials and inventory tables.

CREATE TABLE IF NOT EXISTS materials (
	id integer PRIMARY KEY,
	description text NOT NULL
);

CREATE TABLE IF NOT EXISTS 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.

What is a database transaction

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

A transaction has four main 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 the all 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, the default mode is auto-commit. It means that every query you issue to the SQLite database 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 work, you use the commit method of the Connection object.

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

In case a failure occurs in the middle of the transaction, you can always use the rollback() method to roll back the transaction.

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

The following program adds new material to the materials table and also posts the inventory. We put both operations in the same transaction.

package net.sqlitetutorial;

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

/**
 *
 * @author sqlitetutorial.net
 */
public class TransactionApp {

    /**
     * Connect to the test.db database
     *
     * @return the Connection object
     */
    private Connection connect() {
        // SQLite connection string
        String url = "jdbc:sqlite:C://sqlite/db/test.db";
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return conn;
    }

    /**
     * Create a new material and add initial quantity to the warehouse
     *
     * @param material
     * @param warehouseId
     * @param qty
     */
    public void addInventory(String material, int warehouseId, double qty) {
        // SQL for creating a new material
        String sqlMaterial = "INSERT INTO materials(description) VALUES(?)";
        
        // SQL for posting inventory
        String sqlInventory = "INSERT INTO inventory(warehouse_id,material_id,qty)"
                + "VALUES(?,?,?)";

        ResultSet rs = null;
        Connection conn = null;
        PreparedStatement pstmt1 = null, pstmt2 = null;
        
        try {
            // connect to the database
            conn = this.connect();
            if(conn == null)
                return;
            
            // set auto-commit mode to false
            conn.setAutoCommit(false);
            
            // 1. insert a new material
            pstmt1 = conn.prepareStatement(sqlMaterial,
                    Statement.RETURN_GENERATED_KEYS);

            pstmt1.setString(1, material);
            int rowAffected = pstmt1.executeUpdate();

            // get the material id
            rs = pstmt1.getGeneratedKeys();
            int materialId = 0;
            if (rs.next()) {
                materialId = rs.getInt(1);
            }

            if (rowAffected != 1) {
                conn.rollback();
            }
            // 2. insert the inventory
            pstmt2 = conn.prepareStatement(sqlInventory);
            pstmt2.setInt(1, warehouseId);
            pstmt2.setInt(2, materialId);
            pstmt2.setDouble(3, qty);
            // 
            pstmt2.executeUpdate();
            // commit work
            conn.commit();

        } catch (SQLException e1) {
            try {
                if (conn != null) {
                    conn.rollback();
                }
            } catch (SQLException e2) {
                System.out.println(e2.getMessage());
            }
            System.out.println(e1.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstmt1 != null) {
                    pstmt1.close();
                }
                if (pstmt2 != null) {
                    pstmt2.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e3) {
                System.out.println(e3.getMessage());
            }
        }
    }

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        TransactionApp app = new TransactionApp();
        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)

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