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
materialstable stores the materials master. - The
inventorytable stores the relationship between thewarehousesandmaterialstable. In addition, theinventorytable has theqtycolumn that stores the stock data.
The following statements create the materials and inventory tables.
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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) ); |
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:
1 | conn.setAutoCommit(false); |
To commit work, you use the commit method of the Connection object.
1 | conn.commit(); |
In case a failure occurs in the middle of the transaction, you can always use the rollback() method to rollback the transaction.
1 | conn.rollback(); |
The following program adds a new material to the materials table and also posts the inventory. We put both operations in the same transaction.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 | 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); } } |
To check the result, you can query data from the materials and inventory table using the inner join clause as follows:
1 2 3 4 5 6 7 8 | SELECT name, description, qty FROM materials INNER JOIN inventory ON inventory.material_id = materials.id INNER JOIN warehouses ON warehouses.id = inventory.warehouse_id; |
In this tutorial, you have learned how to manage transaction in SQLite using Java JDBC.