SQLite

suggest change

SQLite is an embedded SQL database: the whole database is a single file, and the engine runs inside your process. There's no server to install, no port and no user accounts. That makes it a good fit for desktop and command-line tools, local caches, test fixtures, small web applications and any time you'd otherwise invent your own file format.

From Java you use SQLite through the standard JDBC API. Everything from that article (PreparedStatement, mapping rows to records, transactions, batches) works the same way. This article covers what's different about SQLite: typing, pragmas, dates, concurrency, and features you only get from an embedded database.

The usual driver is xerial sqlite-jdbc (org.xerial:sqlite-jdbc). It bundles the SQLite C library compiled for Windows, macOS and Linux (x86-64 and ARM), and loads the right one at runtime, so you only need the jar.

<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.53.4.0</version>
</dependency>

With Gradle: implementation("org.xerial:sqlite-jdbc:3.53.4.0"). The first three numbers of the driver version are the version of SQLite it bundles.

The examples were run with JDK 25. Each one is a standalone program:

$ java --enable-native-access=ALL-UNNAMED -cp sqlite-jdbc-3.53.4.0.jar Example.java

The driver loads a native library with System.load(). Since JDK 24 that prints a "restricted method" warning unless you pass --enable-native-access=ALL-UNNAMED (or add Enable-Native-Access: ALL-UNNAMED to the manifest of an executable jar).

Connecting

The URL is jdbc:sqlite: followed by a file path. The file is created if it doesn't exist. jdbc:sqlite::memory: opens a private in-memory database that disappears when the connection is closed:

import java.nio.file.Files;
import java.nio.file.Path;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Connect {
    public static void main(String[] args) throws Exception {
        Files.deleteIfExists(Path.of("shop.db"));
        try (Connection conn = DriverManager.getConnection("jdbc:sqlite:shop.db")) {
            DatabaseMetaData meta = conn.getMetaData();
            System.out.println("Driver:  " + meta.getDriverName() + " " + meta.getDriverVersion());
            System.out.println("SQLite:  " + meta.getDatabaseProductVersion());
            try (Statement st = conn.createStatement();
                 ResultSet rs = st.executeQuery("SELECT sqlite_version()")) {
                rs.next();
                System.out.println("Version: " + rs.getString(1));
            }
        }
        System.out.println("File exists: " + Files.exists(Path.of("shop.db"))
                + ", size " + Files.size(Path.of("shop.db")));

        // in-memory database: gone when the connection closes
        try (Connection mem = DriverManager.getConnection("jdbc:sqlite::memory:");
             Statement st = mem.createStatement()) {
            st.execute("CREATE TABLE t (x)");
            st.execute("INSERT INTO t VALUES (1), (2), (3)");
            try (ResultSet rs = st.executeQuery("SELECT sum(x) FROM t")) {
                rs.next();
                System.out.println("In-memory sum: " + rs.getInt(1));
            }
        }
    }
}

Output:

Driver:  SQLite JDBC 3.53.4.0
SQLite:  3.53.4
Version: 3.53.4
File exists: true, size 0
In-memory sum: 6

The new file is empty (0 bytes) until you create the first table. Paths are relative to the working directory of the JVM, so in real applications build an absolute path, for example "jdbc:sqlite:" + dataDir.resolve("app.db"). Open connections with try-with-resources as usual: an unclosed connection keeps the file locked.

Types: dynamic typing and STRICT tables

SQLite's biggest surprise for people coming from other databases is that column types are only a suggestion. A column's declared type gives it an affinity: SQLite tries to convert values to that type, but if the conversion would lose information it stores the value as it is. typeof() shows what was actually stored:

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

public class Types {
    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
             Statement st = conn.createStatement()) {
            st.execute("CREATE TABLE loose (n INTEGER, s TEXT)");
            st.execute("INSERT INTO loose VALUES (42, 'a'), ('17', 99), ('hello', 3.5), (3.0, NULL)");
            try (ResultSet rs = st.executeQuery("SELECT n, typeof(n), s, typeof(s) FROM loose")) {
                while (rs.next()) {
                    System.out.printf("%-6s %-8s %-6s %s%n",
                            rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4));
                }
            }

            st.execute("CREATE TABLE strict_t (n INTEGER, s TEXT) STRICT");
            st.execute("INSERT INTO strict_t VALUES ('17', 'ok')"); // '17' converts losslessly
            try {
                st.execute("INSERT INTO strict_t VALUES ('hello', 'x')");
            } catch (SQLException e) {
                System.out.println("STRICT: " + e.getMessage());
            }
        }
    }
}

Output:

42     integer  a      text
17     integer  99     text
hello  text     3.5    text
3      integer  null   null
STRICT: [SQLITE_CONSTRAINT_DATATYPE] An insert or update attempted to store a value inconsistent with the column's declared type in a table defined as STRICT (cannot store TEXT value in INTEGER column strict_t.n)

'17' was converted to an integer and 3.0 to 3, but 'hello' went into the INTEGER column as text, and numbers went into the TEXT column as text. Add STRICT after the closing parenthesis of CREATE TABLE (SQLite 3.37+) to get the behavior you'd expect from other databases. STRICT tables only accept the types INTEGER, REAL, TEXT, BLOB and ANY.

SQLite has only five storage classes: NULL, INTEGER (64-bit), REAL (double), TEXT and BLOB. There's no separate boolean, decimal or date type:

Java type Store as Notes
boolean INTEGER 0/1 setBoolean/getBoolean handle the conversion
long, int INTEGER
double REAL
BigDecimal, money INTEGER cents, or TEXT there's no exact decimal type; REAL loses precision
String TEXT always UTF-8
byte[] BLOB setBytes/getBytes
LocalDateTime, Instant TEXT ISO-8601 or INTEGER epoch see "Dates and times" below

Creating tables, inserting and querying

A typical table uses INTEGER PRIMARY KEY for the id. That column becomes an alias for SQLite's internal rowid, so ids are assigned automatically and lookups by id are as fast as they can be. You don't need AUTOINCREMENT: it only prevents reuse of ids of deleted rows, and makes inserts slower.

To get the id of a new row, use INSERT ... RETURNING (SQLite 3.35+) or Statement.RETURN_GENERATED_KEYS:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

public class Crud {
    record Product(long id, String sku, String name, long priceCents, boolean inStock,
                   LocalDateTime createdAt) {}

    static long insert(Connection conn, String sku, String name, long priceCents) throws SQLException {
        String sql = "INSERT INTO products (sku, name, price_cents) VALUES (?, ?, ?) RETURNING id";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, sku);
            ps.setString(2, name);
            ps.setLong(3, priceCents);
            try (ResultSet rs = ps.executeQuery()) {
                rs.next();
                return rs.getLong(1);
            }
        }
    }

    static List<Product> all(Connection conn) throws SQLException {
        String sql = "SELECT id, sku, name, price_cents, in_stock, created_at FROM products ORDER BY id";
        try (PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            List<Product> list = new ArrayList<>();
            while (rs.next()) {
                list.add(new Product(
                        rs.getLong("id"),
                        rs.getString("sku"),
                        rs.getString("name"),
                        rs.getLong("price_cents"),
                        rs.getBoolean("in_stock"),
                        LocalDateTime.parse(rs.getString("created_at").replace(' ', 'T'))));
            }
            return list;
        }
    }

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:sqlite:shop.db")) {
            try (Statement st = conn.createStatement()) {
                st.execute("DROP TABLE IF EXISTS products");
                st.execute("""
                    CREATE TABLE products (
                        id          INTEGER PRIMARY KEY,
                        sku         TEXT    NOT NULL UNIQUE,
                        name        TEXT    NOT NULL,
                        price_cents INTEGER NOT NULL CHECK (price_cents >= 0),
                        in_stock    INTEGER NOT NULL DEFAULT 1 CHECK (in_stock IN (0, 1)),
                        created_at  TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
                    ) STRICT
                    """);
            }

            System.out.println("id " + insert(conn, "KB-01", "Mechanical keyboard", 8999));
            System.out.println("id " + insert(conn, "MS-02", "Wireless mouse", 2450));

            try (PreparedStatement ps = conn.prepareStatement(
                    "INSERT INTO products (sku, name, price_cents) VALUES (?, ?, ?)",
                    Statement.RETURN_GENERATED_KEYS)) {
                ps.setString(1, "MN-03");
                ps.setString(2, "27\" monitor");
                ps.setLong(3, 27900);
                ps.executeUpdate();
                try (ResultSet keys = ps.getGeneratedKeys()) {
                    keys.next();
                    System.out.println("id " + keys.getLong(1) + " (getGeneratedKeys)");
                }
            }

            try (PreparedStatement ps = conn.prepareStatement(
                    "UPDATE products SET in_stock = ? WHERE sku = ?")) {
                ps.setBoolean(1, false);
                ps.setString(2, "MS-02");
                System.out.println("updated " + ps.executeUpdate() + " row(s)");
            }

            for (Product p : all(conn)) {
                System.out.println(p);
            }
        }
    }
}

Output:

id 1
id 2
id 3 (getGeneratedKeys)
updated 1 row(s)
Product[id=1, sku=KB-01, name=Mechanical keyboard, priceCents=8999, inStock=true, createdAt=2026-10-17T19:36:59]
Product[id=2, sku=MS-02, name=Wireless mouse, priceCents=2450, inStock=false, createdAt=2026-10-17T19:36:59]
Product[id=3, sku=MN-03, name=27" monitor, priceCents=27900, inStock=true, createdAt=2026-10-17T19:36:59]

Notes:

Dates and times

Since there's no date type, you choose a representation. The driver's default for setTimestamp/setDate is milliseconds since the epoch, stored as INTEGER. java.time values passed to setObject are stored as ISO-8601 text:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.time.Instant;
import java.time.LocalDateTime;

public class Dates {
    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
             Statement st = conn.createStatement()) {
            st.execute("CREATE TABLE ev (ts_legacy, ts_iso, ts_epoch)");
            LocalDateTime ldt = LocalDateTime.of(2026, 10, 17, 14, 30, 5);
            Instant instant = Instant.parse("2026-10-17T12:30:05Z");
            try (PreparedStatement ps = conn.prepareStatement("INSERT INTO ev VALUES (?, ?, ?)")) {
                ps.setTimestamp(1, Timestamp.valueOf(ldt));   // stored as epoch millis
                ps.setObject(2, ldt);                         // stored as ISO-8601 text
                ps.setLong(3, instant.getEpochSecond());      // explicit unix seconds
                ps.executeUpdate();
            }
            try (ResultSet rs = st.executeQuery("""
                    SELECT ts_legacy, typeof(ts_legacy), ts_iso, typeof(ts_iso),
                           ts_epoch, datetime(ts_epoch, 'unixepoch')
                    FROM ev""")) {
                rs.next();
                System.out.println("ts_legacy: " + rs.getString(1) + " (" + rs.getString(2) + ")");
                System.out.println("ts_iso:    " + rs.getString(3) + " (" + rs.getString(4) + ")");
                System.out.println("ts_epoch:  " + rs.getLong(5) + " = " + rs.getString(6) + " UTC");
                LocalDateTime back = rs.getObject(3, LocalDateTime.class);
                Instant backInstant = Instant.ofEpochSecond(rs.getLong(5));
                System.out.println("read back: " + back + " / " + backInstant);
            }
            try (ResultSet rs = st.executeQuery(
                    "SELECT date(ts_iso, '+1 month', 'start of month'), strftime('%H:%M', ts_iso) FROM ev")) {
                rs.next();
                System.out.println("SQL date math: " + rs.getString(1) + " " + rs.getString(2));
            }
        }
    }
}

Output:

ts_legacy: 1792240205000 (integer)
ts_iso:    2026-10-17T14:30:05 (text)
ts_epoch:  1792240205 = 2026-10-17 12:30:05 UTC
read back: 2026-10-17T14:30:05 / 2026-10-17T12:30:05Z
SQL date math: 2026-11-01 14:30

Timestamp.valueOf() interprets the LocalDateTime in the JVM's default time zone (UTC+2 on the machine that produced this output), so the stored number changes if the zone does. Pick one of two conventions and use it everywhere:

The driver URL parameters date_class, date_precision and date_string_format change how setTimestamp/getTimestamp work, but it's simpler to avoid the legacy java.sql date types and convert explicitly.

Pragmas: foreign keys, WAL and busy timeout

SQLite is configured per connection with PRAGMA statements. A few defaults exist only for backwards compatibility and should almost always be changed. Most importantly, foreign key constraints are not enforced unless you turn them on for every connection:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.sqlite.SQLiteConfig;

public class Pragmas {
    static String pragma(Connection conn, String name) throws SQLException {
        try (Statement st = conn.createStatement();
             ResultSet rs = st.executeQuery("PRAGMA " + name)) {
            return rs.next() ? rs.getString(1) : null;
        }
    }

    static void tryOrphan(Connection conn) throws SQLException {
        try (Statement st = conn.createStatement()) {
            st.execute("CREATE TABLE IF NOT EXISTS customers (id INTEGER PRIMARY KEY, name TEXT)");
            st.execute("""
                CREATE TABLE IF NOT EXISTS orders (
                    id INTEGER PRIMARY KEY,
                    customer_id INTEGER NOT NULL REFERENCES customers(id))""");
            st.execute("INSERT INTO orders (customer_id) VALUES (999)");
            System.out.println("  orphan row inserted");
        } catch (SQLException e) {
            System.out.println("  " + e.getMessage());
        }
    }

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
            System.out.println("defaults: foreign_keys=" + pragma(conn, "foreign_keys")
                    + " busy_timeout=" + pragma(conn, "busy_timeout"));
            tryOrphan(conn);
        }

        // 1) pragmas as URL parameters
        String url = "jdbc:sqlite:app.db?foreign_keys=on&journal_mode=wal&busy_timeout=5000";
        try (Connection conn = DriverManager.getConnection(url)) {
            System.out.println("URL:      foreign_keys=" + pragma(conn, "foreign_keys")
                    + " journal_mode=" + pragma(conn, "journal_mode")
                    + " busy_timeout=" + pragma(conn, "busy_timeout"));
        }

        // 2) the same with SQLiteConfig
        SQLiteConfig config = new SQLiteConfig();
        config.enforceForeignKeys(true);
        config.setJournalMode(SQLiteConfig.JournalMode.WAL);
        config.setSynchronous(SQLiteConfig.SynchronousMode.NORMAL);
        config.setBusyTimeout(5000);
        try (Connection conn = config.createConnection("jdbc:sqlite::memory:")) {
            System.out.println("config:   foreign_keys=" + pragma(conn, "foreign_keys")
                    + " synchronous=" + pragma(conn, "synchronous"));
            tryOrphan(conn);
        }
    }
}

Output:

defaults: foreign_keys=0 busy_timeout=3000
  orphan row inserted
URL:      foreign_keys=1 journal_mode=wal busy_timeout=5000
config:   foreign_keys=1 synchronous=1
  [SQLITE_CONSTRAINT_FOREIGNKEY] A foreign key constraint failed (FOREIGN KEY constraint failed)

You can set pragmas as URL parameters or with org.sqlite.SQLiteConfig, whose createConnection(url) opens a connection with those settings. config.toProperties() gives you the same settings as Properties for DriverManager.getConnection(url, props). Running PRAGMA ... yourself after connecting also works, but it's easy to forget on one code path.

A good starting configuration for an application database:

Pragma Value Why
foreign_keys on Enforce REFERENCES constraints (off by default)
journal_mode wal Readers don't block the writer and the writer doesn't block readers; faster writes. Stored in the file, so it persists
synchronous normal Safe with WAL (a power loss can lose the last transactions but won't corrupt the file), much faster than the default full
busy_timeout 5000 Milliseconds to wait for a lock before failing with SQLITE_BUSY (the driver's default is 3000)

In WAL mode SQLite creates two extra files next to the database, app.db-wal and app.db-shm. They're part of the database: copy all three, or better, use the backup methods shown below. WAL doesn't work on network file systems, and SQLite in general shouldn't be used over NFS or SMB shares.

Transactions and bulk inserts

In auto-commit mode every statement is its own transaction, and each commit waits for the data to reach the disk. With SQLite that's the main cause of slow inserts. Wrap bulk work in one transaction:

import java.nio.file.Files;
import java.nio.file.Path;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Bulk {
    static final int N = 2_000;

    static void reset(Connection conn) throws SQLException {
        try (Statement st = conn.createStatement()) {
            st.execute("DROP TABLE IF EXISTS readings");
            st.execute("CREATE TABLE readings (id INTEGER PRIMARY KEY, sensor TEXT, value REAL)");
        }
    }

    static void insertAutoCommit(Connection conn) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(
                "INSERT INTO readings (sensor, value) VALUES (?, ?)")) {
            for (int i = 0; i < N; i++) {
                ps.setString(1, "s" + (i % 10));
                ps.setDouble(2, i * 0.5);
                ps.executeUpdate();            // each insert is its own transaction
            }
        }
    }

    static void insertInTransaction(Connection conn) throws SQLException {
        conn.setAutoCommit(false);
        try (PreparedStatement ps = conn.prepareStatement(
                "INSERT INTO readings (sensor, value) VALUES (?, ?)")) {
            for (int i = 0; i < N; i++) {
                ps.setString(1, "s" + (i % 10));
                ps.setDouble(2, i * 0.5);
                ps.addBatch();
            }
            ps.executeBatch();
            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
    }

    static long count(Connection conn) throws SQLException {
        try (Statement st = conn.createStatement();
             ResultSet rs = st.executeQuery("SELECT count(*) FROM readings")) {
            rs.next();
            return rs.getLong(1);
        }
    }

    public static void main(String[] args) throws Exception {
        Files.deleteIfExists(Path.of("bulk.db"));
        try (Connection conn = DriverManager.getConnection("jdbc:sqlite:bulk.db")) {
            reset(conn);
            long t0 = System.nanoTime();
            insertAutoCommit(conn);
            System.out.printf("auto-commit:     %d rows in %5d ms%n", count(conn), (System.nanoTime() - t0) / 1_000_000);

            reset(conn);
            t0 = System.nanoTime();
            insertInTransaction(conn);
            System.out.printf("one transaction: %d rows in %5d ms%n", count(conn), (System.nanoTime() - t0) / 1_000_000);
        }
    }
}

Output:

auto-commit:     2000 rows in 11704 ms
one transaction: 2000 rows in    17 ms

The same 2,000 rows went from about 12 seconds to 17 ms, several hundred times faster. (Exact numbers depend on the disk; WAL with synchronous=normal narrows the gap, but one transaction always wins.) The transaction is what matters. addBatch() saves a bit of JNI overhead, but unlike with a client-server database there are no network round trips to save, so a loop of executeUpdate() calls inside the transaction is almost as fast. For big imports, commit every 10,000 to 100,000 rows to keep the WAL file from growing too large.

The transaction pattern itself (setAutoCommit(false), commit(), rollback() in a catch) is covered in JDBC.

Upserts and error codes

SQLite supports PostgreSQL-style ON CONFLICT ... DO UPDATE (where excluded refers to the row you tried to insert), plus the shorter INSERT OR IGNORE and INSERT OR REPLACE. When a statement fails, the driver throws org.sqlite.SQLiteException, a subclass of SQLException. getResultCode() returns the extended result code, which tells you exactly which constraint failed:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.sqlite.SQLiteErrorCode;
import org.sqlite.SQLiteException;

public class Upsert {
    static void addStock(Connection conn, String sku, int qty) throws SQLException {
        String sql = """
            INSERT INTO stock (sku, qty) VALUES (?, ?)
            ON CONFLICT (sku) DO UPDATE SET qty = qty + excluded.qty
            RETURNING qty""";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, sku);
            ps.setInt(2, qty);
            try (ResultSet rs = ps.executeQuery()) {
                rs.next();
                System.out.println(sku + " -> " + rs.getInt(1));
            }
        }
    }

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
             Statement st = conn.createStatement()) {
            st.execute("CREATE TABLE stock (sku TEXT PRIMARY KEY, qty INTEGER NOT NULL CHECK (qty >= 0))");
            addStock(conn, "KB-01", 10);
            addStock(conn, "KB-01", 5);
            addStock(conn, "MS-02", 3);

            st.execute("INSERT OR IGNORE INTO stock VALUES ('MS-02', 100)");  // silently skipped
            st.execute("INSERT OR REPLACE INTO stock VALUES ('HS-04', 7)");

            String[] bad = {
                "INSERT INTO stock VALUES ('KB-01', 1)",
                "INSERT INTO stock VALUES ('XX-99', -1)",
                "INSERT INTO stok VALUES ('XX-99', 1)",
            };
            for (String sql : bad) {
                try {
                    st.execute(sql);
                } catch (SQLiteException e) {
                    SQLiteErrorCode code = e.getResultCode();
                    System.out.println(code.name() + " (" + code.code + "), getErrorCode()=" + e.getErrorCode());
                    if (code == SQLiteErrorCode.SQLITE_CONSTRAINT_PRIMARYKEY) {
                        System.out.println("  -> duplicate SKU");
                    }
                }
            }

            try (ResultSet rs = st.executeQuery("SELECT sku, qty FROM stock ORDER BY sku")) {
                while (rs.next()) {
                    System.out.println(rs.getString(1) + " = " + rs.getInt(2));
                }
            }
        }
    }
}

Output:

KB-01 -> 10
KB-01 -> 15
MS-02 -> 3
SQLITE_CONSTRAINT_PRIMARYKEY (1555), getErrorCode()=19
  -> duplicate SKU
SQLITE_CONSTRAINT_CHECK (275), getErrorCode()=19
SQLITE_ERROR (1), getErrorCode()=1
HS-04 = 7
KB-01 = 15
MS-02 = 3

getErrorCode() (the standard JDBC method) only returns the primary code, 19 (SQLITE_CONSTRAINT) for every constraint violation, so compare getResultCode() with the SQLiteErrorCode enum when you need to tell a duplicate key from a failed check. Note that INSERT OR REPLACE deletes the old row and inserts a new one. That fires delete triggers and cascades, and resets columns you didn't specify to their defaults. Prefer ON CONFLICT DO UPDATE when you mean "update if it exists".

JSON

SQLite has built-in JSON functions. JSON is stored as TEXT and you read and write it with setString/getString. The -> operator returns JSON and ->> returns an SQL value. A generated column with an index makes a JSON field fast to search:

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

public class Json {
    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
             Statement st = conn.createStatement()) {
            st.execute("""
                CREATE TABLE events (
                    id      INTEGER PRIMARY KEY,
                    payload TEXT NOT NULL CHECK (json_valid(payload)),
                    kind    TEXT GENERATED ALWAYS AS (payload ->> '$.kind') VIRTUAL
                )""");
            st.execute("CREATE INDEX events_kind ON events (kind)");

            try (PreparedStatement ps = conn.prepareStatement("INSERT INTO events (payload) VALUES (?)")) {
                for (String json : new String[] {
                        """
                        {"kind":"login","user":"ana","tags":["web","mfa"]}""",
                        """
                        {"kind":"purchase","user":"bob","amount":42.5}""",
                        """
                        {"kind":"login","user":"bob","tags":["mobile"]}"""}) {
                    ps.setString(1, json);
                    ps.addBatch();
                }
                ps.executeBatch();
            }

            System.out.println("-- logins (uses the index on the generated column)");
            try (PreparedStatement ps = conn.prepareStatement(
                    "SELECT id, payload ->> '$.user', payload -> '$.tags' FROM events WHERE kind = ?")) {
                ps.setString(1, "login");
                try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        System.out.println(rs.getInt(1) + " " + rs.getString(2) + " " + rs.getString(3));
                    }
                }
            }

            System.out.println("-- one row per tag with json_each");
            try (ResultSet rs = st.executeQuery("""
                    SELECT e.payload ->> '$.user', t.value
                    FROM events e, json_each(e.payload, '$.tags') t
                    ORDER BY e.id""")) {
                while (rs.next()) {
                    System.out.println(rs.getString(1) + " " + rs.getString(2));
                }
            }

            System.out.println("-- build JSON in SQL");
            try (ResultSet rs = st.executeQuery("""
                    SELECT json_group_array(json_object('id', id, 'kind', kind))
                    FROM events""")) {
                rs.next();
                System.out.println(rs.getString(1));
            }

            try {
                st.execute("INSERT INTO events (payload) VALUES ('not json')");
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
    }
}

Output:

-- logins (uses the index on the generated column)
1 ana ["web","mfa"]
3 bob ["mobile"]
-- one row per tag with json_each
ana web
ana mfa
bob mobile
-- build JSON in SQL
[{"id":1,"kind":"login"},{"id":2,"kind":"purchase"},{"id":3,"kind":"login"}]
[SQLITE_ERROR] SQL error or missing database (malformed JSON)

The invalid document was rejected while SQLite computed the kind column; without the generated column, the json_valid check would have rejected it. Use a library such as Jackson to turn the JSON strings into Java objects.

Concurrency

SQLite allows many readers but only one writer at a time for the whole database. In WAL mode readers keep seeing the last committed data while a write transaction is open. A second writer waits up to busy_timeout milliseconds and then fails with SQLITE_BUSY:

import java.nio.file.Files;
import java.nio.file.Path;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.sqlite.SQLiteConfig;

public class Concurrency {
    static Connection open(int busyTimeoutMs) throws SQLException {
        SQLiteConfig config = new SQLiteConfig();
        config.setJournalMode(SQLiteConfig.JournalMode.WAL);
        config.setBusyTimeout(busyTimeoutMs);
        config.setTransactionMode(SQLiteConfig.TransactionMode.IMMEDIATE);
        return config.createConnection("jdbc:sqlite:counter.db");
    }

    static long read(Connection conn) throws SQLException {
        try (Statement st = conn.createStatement();
             ResultSet rs = st.executeQuery("SELECT n FROM counter")) {
            rs.next();
            return rs.getLong(1);
        }
    }

    public static void main(String[] args) throws Exception {
        for (String f : new String[] {"counter.db", "counter.db-wal", "counter.db-shm"}) {
            Files.deleteIfExists(Path.of(f));
        }
        try (Connection setup = open(5000); Statement st = setup.createStatement()) {
            st.execute("CREATE TABLE counter (n INTEGER NOT NULL)");
            st.execute("INSERT INTO counter VALUES (0)");
        }

        try (Connection writer = open(5000); Connection other = open(200)) {
            writer.setAutoCommit(false);  // BEGIN IMMEDIATE: takes the write lock now
            try (Statement st = writer.createStatement()) {
                st.executeUpdate("UPDATE counter SET n = n + 1");
            }
            System.out.println("reader sees " + read(other) + " while writer is uncommitted");

            long t0 = System.nanoTime();
            try (Statement st = other.createStatement()) {
                st.executeUpdate("UPDATE counter SET n = n + 100");
            } catch (SQLException e) {
                System.out.printf("second writer after %d ms: %s%n",
                        (System.nanoTime() - t0) / 1_000_000, e.getMessage());
            }
            writer.commit();
            System.out.println("after commit reader sees " + read(other));
        }

        // Threads sharing one file: each thread gets its own connection,
        // busy_timeout makes writers wait for each other instead of failing.
        Thread[] threads = new Thread[4];
        for (int t = 0; t < threads.length; t++) {
            threads[t] = Thread.ofVirtual().start(() -> {
                try (Connection conn = open(10_000); Statement st = conn.createStatement()) {
                    for (int i = 0; i < 250; i++) {
                        st.executeUpdate("UPDATE counter SET n = n + 1");
                    }
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            });
        }
        for (Thread t : threads) t.join();
        try (Connection conn = open(5000)) {
            System.out.println("after 4 x 250 concurrent increments: " + read(conn));
        }
    }
}

Output:

reader sees 0 while writer is uncommitted
second writer after 207 ms: [SQLITE_BUSY] The database file is locked (database is locked)
after commit reader sees 1
after 4 x 250 concurrent increments: 1001

The final value is 1001: the 1 from the first part plus 1,000 increments from four threads, none lost and none failed, because each thread waited for the lock. Rules that keep you out of trouble:

A connection pool such as HikariCP works with SQLite and saves the cost of opening connections and re-applying pragmas. Some applications use a pool for reads and a separate single connection for writes.

In-memory databases for tests

jdbc:sqlite::memory: is private to one connection. To share an in-memory database between several connections, for example in a test that uses a DataSource, give it a name with a file: URI and cache=shared. The driver's SQLiteDataSource takes the same settings as SQLiteConfig:

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.sqlite.SQLiteDataSource;

public class SharedMemory {
    public static void main(String[] args) throws SQLException {
        SQLiteDataSource ds = new SQLiteDataSource();
        ds.setUrl("jdbc:sqlite:file:testdb?mode=memory&cache=shared");
        ds.setEnforceForeignKeys(true);

        // keep one connection open: the database lives as long as any connection to it
        try (Connection keepAlive = ds.getConnection()) {
            try (Connection c1 = ds.getConnection(); Statement st = c1.createStatement()) {
                st.execute("CREATE TABLE users (name TEXT)");
                st.execute("INSERT INTO users VALUES ('ana'), ('bob')");
            }
            try (Connection c2 = ds.getConnection(); Statement st = c2.createStatement();
                 ResultSet rs = st.executeQuery("SELECT count(*) FROM users")) {
                rs.next();
                System.out.println("second connection sees " + rs.getInt(1) + " users");
            }
        }
        try (Connection c3 = ds.getConnection(); Statement st = c3.createStatement()) {
            st.executeQuery("SELECT count(*) FROM users");
        } catch (SQLException e) {
            System.out.println("after last close: " + e.getMessage());
        }
    }
}

Output:

second connection sees 2 users
after last close: [SQLITE_ERROR] SQL error or missing database (no such table: users)

The database is deleted when its last connection closes, so tests usually hold one connection open for their duration. Each test can use a different name to get a fresh database. Tests that run against SQLite can't catch SQL dialect differences when production uses another database, so this is most useful when SQLite is also what you run in production.

Backups and copies

Because a database is just a file, it's tempting to copy it with Files.copy. That isn't safe while it's open: you can copy a half-written transaction, or miss data still in the -wal file. Use SQLite's online backup instead. The driver exposes it as the non-standard statements backup to and restore from, and plain SQL offers VACUUM INTO:

import java.nio.file.Files;
import java.nio.file.Path;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Backup {
    static long count(Connection conn) throws SQLException {
        try (Statement st = conn.createStatement();
             ResultSet rs = st.executeQuery("SELECT count(*) FROM products")) {
            rs.next();
            return rs.getLong(1);
        }
    }

    public static void main(String[] args) throws Exception {
        Files.deleteIfExists(Path.of("backup.db"));
        Files.deleteIfExists(Path.of("snapshot.db"));

        try (Connection conn = DriverManager.getConnection("jdbc:sqlite:shop.db");
             Statement st = conn.createStatement()) {
            // online backup API: safe while other connections use the database
            st.executeUpdate("backup to backup.db");
            // VACUUM INTO writes a compacted copy (plain SQL, SQLite 3.27+)
            st.execute("VACUUM INTO 'snapshot.db'");
        }
        System.out.println("backup.db:   " + Files.size(Path.of("backup.db")) + " bytes");
        System.out.println("snapshot.db: " + Files.size(Path.of("snapshot.db")) + " bytes");

        // load a file into an in-memory database, e.g. for fast read-only lookups or tests
        try (Connection mem = DriverManager.getConnection("jdbc:sqlite::memory:");
             Statement st = mem.createStatement()) {
            st.executeUpdate("restore from backup.db");
            System.out.println("in-memory copy has " + count(mem) + " products");
            st.executeUpdate("DELETE FROM products");
            System.out.println("in-memory copy now has " + count(mem) + " products");
        }
        try (Connection conn = DriverManager.getConnection("jdbc:sqlite:backup.db")) {
            System.out.println("backup.db still has " + count(conn) + " products");
        }
    }
}

Output:

backup.db:   12288 bytes
snapshot.db: 12288 bytes
in-memory copy has 3 products
in-memory copy now has 0 products
backup.db still has 3 products

restore from into an in-memory database is a handy way to load a read-only reference database (or test fixture) into RAM. VACUUM INTO also defragments the copy, which can make it smaller than the original after many deletes.

Custom SQL functions

Because SQLite runs inside the JVM, you can add SQL functions written in Java. Subclass org.sqlite.Function for a scalar function or Function.Aggregate for an aggregate, and register it on a connection:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.Normalizer;
import org.sqlite.Function;

public class Functions {
    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
             Statement st = conn.createStatement()) {

            // scalar function: slugify(text) -> text
            Function.create(conn, "slugify", new Function() {
                @Override
                protected void xFunc() throws SQLException {
                    if (args() != 1) throw new SQLException("slugify(text) takes 1 argument");
                    String s = value_text(0);
                    if (s == null) { result(); return; }   // NULL in, NULL out
                    String ascii = Normalizer.normalize(s, Normalizer.Form.NFD)
                            .replaceAll("\\p{M}", "");
                    result(ascii.toLowerCase().replaceAll("[^a-z0-9]+", "-")
                            .replaceAll("(^-|-$)", ""));
                }
            }, Function.FLAG_DETERMINISTIC);

            // aggregate function: product of values
            Function.create(conn, "product", new Function.Aggregate() {
                private double acc = 1;
                @Override protected void xStep() throws SQLException { acc *= value_double(0); }
                @Override protected void xFinal() throws SQLException { result(acc); }
            });

            st.execute("CREATE TABLE posts (title TEXT)");
            st.execute("INSERT INTO posts VALUES ('Hello, World!'), ('Crème brûlée in 10 minutes'), (NULL)");
            try (ResultSet rs = st.executeQuery("SELECT title, slugify(title) FROM posts")) {
                while (rs.next()) {
                    System.out.println(rs.getString(1) + " -> " + rs.getString(2));
                }
            }
            try (ResultSet rs = st.executeQuery(
                    "SELECT product(x) FROM (SELECT 1.5 AS x UNION ALL SELECT 2 UNION ALL SELECT 4)")) {
                rs.next();
                System.out.println("product = " + rs.getDouble(1));
            }
        }
    }
}

Output:

Hello, World! -> hello-world
Crème brûlée in 10 minutes -> creme-brulee-in-10-minutes
null -> null
product = 12.0

Functions are registered per connection, so register them wherever you open connections. FLAG_DETERMINISTIC tells SQLite that the same input always gives the same output, which lets you use the function in indexes and generated columns. The driver also supports custom collations (Collation.create) and update and commit hooks.

Best practices

Feedback about page:

Feedback:
Optional: your email if you want me to get back to you:



Table Of Contents