SQLite
suggest changeSQLite 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:
- Money is stored as an integer number of cents, and
CHECKconstraints validate values. That matters because SQLite rejects very little on its own. CURRENT_TIMESTAMPproduces UTC text in the formYYYY-MM-DD HH:MM:SS(with a space, not aT), hence thereplacebeforeLocalDateTime.parse.RETURNINGis read withexecuteQuery(), because the statement returns rows.
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:
- ISO-8601
TEXT(2026-10-17T14:30:05, ideally in UTC): readable, sorts correctly, and works with SQLite'sdate(),time(),datetime()andstrftime()functions. INTEGERUnix seconds (or millis) fromInstant: compact, and unambiguous about time zones. Usedatetime(col, 'unixepoch')to make it readable in SQL.
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:
- Give each thread its own
Connection(or use a pool). Don't share one connection between threads. - Keep write transactions short. Never hold one open while waiting for user input or a network call.
- Set
busy_timeoutto a few seconds. - Use
SQLiteConfig.TransactionMode.IMMEDIATE, which makessetAutoCommit(false)start transactions withBEGIN IMMEDIATE. With the defaultDEFERREDmode a transaction that reads first and then writes takes the write lock late. If another connection wrote in the meantime, it fails withSQLITE_BUSYimmediately, andbusy_timeoutcan't help. - If you have many concurrent writers, consider funneling writes through a single thread, or use a client-server database such as PostgreSQL.
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
- Turn on
foreign_keysfor every connection, and setjournal_mode=wal,synchronous=normalandbusy_timeout, either in the URL or withSQLiteConfig. - Use
STRICTtables, or at leastCHECKconstraints, so wrong types are rejected instead of stored silently. - Use
INTEGER PRIMARY KEYfor ids andINSERT ... RETURNINGto get them. - Do bulk inserts and updates inside a single transaction.
- Store money as integer cents, and dates as ISO-8601 UTC text or epoch integers. Pick one format for the whole application.
- Keep write transactions short, use one connection per thread, and use
TransactionMode.IMMEDIATEfor transactions that write. - Back up with
backup toorVACUUM INTO, not by copying a file that's in use. - Run
PRAGMA optimizebefore closing long-lived connections (or periodically) to keep the query planner's statistics current. - Use
PreparedStatementfor every query that takes values. SQL injection is as much of a problem in SQLite as anywhere else.