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.
- The
materials
table stores the materials master. - The
inventory
table stores the relationship between thewarehouses
andmaterials
table. In addition, theinventory
table has theqty
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.