Chapter 15 · Embedding SQLite in Applications
.NET and Java: ADO.NET/Provider and JDBC Patterns
Compare Microsoft.Data.Sqlite and the maintained Xerial SQLite JDBC driver using the same FieldNotes schema and query, highlighting common connection, parameter, transaction, result-reading, packaging, and runtime-version concepts beneath different language APIs.
Learning outcomes
.NET and Java expose different ecosystem conventions—ADO.NET versus JDBC—but both eventually drive an SQLite library. We use the same FieldNotes query in both languages so API vocabulary does not hide the common embedded-database model.
Use Microsoft.Data.Sqlite as a lightweight ADO.NET provider and Xerial sqlite-jdbc as a maintained JDBC driver.
Compare connection string/URL, command/prepared statement, parameter binding, transaction, result reader, and disposal patterns.
Initialize foreign-key and busy policy per connection instead of assuming driver defaults match.
Inspect the SQLite engine version from inside each application.
Explain how native SQLite binaries are supplied by each provider/driver and why deployment architecture matters.
Recognize the same transaction and binding invariants beneath different API names.
Provider choice is part of your application dependency graph
Microsoft.Data.Sqlite is Microsoft's lightweight ADO.NET provider used underneath the EF Core SQLite provider and can also be used directly. For Java, this lesson uses Xerial sqlite-jdbc, whose project currently describes itself as maintained, tracks SQLite releases, and packages native libraries for major platforms in its normal JAR distribution.
At generation time SQLite upstream is 3.53.4, while the latest Xerial sqlite-jdbc release surfaced by the project is 3.53.1.0. That is not a contradiction: driver release cadence can trail SQLite patches. Always query sqlite_version() in the running process.
Same concepts, different API names
| Concept | Microsoft.Data.Sqlite / ADO.NET | Java / JDBC |
|---|---|---|
| Open connection | SqliteConnection.Open() | DriverManager.getConnection(...) |
| Prepared/bound SQL | SqliteCommand + Parameters | PreparedStatement + setXxx() |
| Read rows | SqliteDataReader | ResultSet |
| Write count | ExecuteNonQuery() | executeUpdate() |
| Transaction | BeginTransaction() / commit / rollback | setAutoCommit(false) / commit / rollback |
| Cleanup | using/await using / Dispose | try-with-resources / AutoCloseable |
| Database path | ADO.NET connection string Data Source=... | JDBC URL jdbc:sqlite:... |
Shared schema for both examples
CREATE TABLE IF NOT EXISTS device( device_id INTEGER PRIMARY KEY, device_code TEXT NOT NULL UNIQUE, status TEXT NOT NULL CHECK(status IN ('active','inspection_due','retired')), last_service_at TEXT);CREATE TABLE IF NOT EXISTS maintenance_note( note_id INTEGER PRIMARY KEY, device_id INTEGER NOT NULL REFERENCES device(device_id), noted_at TEXT NOT NULL, note_text TEXT NOT NULL);.NET: open, initialize, bind, read, dispose
using Microsoft.Data.Sqlite;var cs = new SqliteConnectionStringBuilder{ DataSource = "fieldnotes.db", Mode = SqliteOpenMode.ReadWriteCreate, ForeignKeys = true, DefaultTimeout = 5,}.ToString();using var connection = new SqliteConnection(cs);connection.Open();using (var version = connection.CreateCommand()){ version.CommandText = "SELECT sqlite_version()"; Console.WriteLine($"SQLite: {version.ExecuteScalar()}");}using var command = connection.CreateCommand();command.CommandText = """ SELECT device_id, device_code, status FROM device WHERE status = $status ORDER BY device_code """;command.Parameters.AddWithValue("$status", "active");using var reader = command.ExecuteReader();while (reader.Read()){ Console.WriteLine($"{reader.GetInt64(0)} {reader.GetString(1)} {reader.GetString(2)}");}Microsoft's provider documents parameters specifically as the SQL-injection-safe way to supply literal values. Connection-string options include read-only/read-write modes, foreign-key initialization, default timeout, and pooling. Treat those as provider behavior layered on top of SQLite rather than generic SQL syntax.
.NET transaction: command ownership must be explicit
using var tx = connection.BeginTransaction();try{ using var insert = connection.CreateCommand(); insert.Transaction = tx; insert.CommandText = """ INSERT INTO maintenance_note(device_id,noted_at,note_text) VALUES ($device,$when,$text) RETURNING note_id """; insert.Parameters.AddWithValue("$device", deviceId); insert.Parameters.AddWithValue("$when", when); insert.Parameters.AddWithValue("$text", text); var noteId = (long)insert.ExecuteScalar()!; using var update = connection.CreateCommand(); update.Transaction = tx; update.CommandText = "UPDATE device SET last_service_at=$when WHERE device_id=$device"; update.Parameters.AddWithValue("$when", when); update.Parameters.AddWithValue("$device", deviceId); if (update.ExecuteNonQuery() != 1) throw new InvalidOperationException("device not found"); tx.Commit(); Console.WriteLine(noteId);}catch{ tx.Rollback(); throw;}ADO.NET commands have a transaction property. Keep command/transaction association clear, especially if a connection is reused. Microsoft.Data.Sqlite also supports deferred transactions and savepoints in current provider generations; choose those features only when your workload needs them.
Java: JDBC URL, PreparedStatement, ResultSet
import java.sql.*;String url = "jdbc:sqlite:fieldnotes.db";try (Connection con = DriverManager.getConnection(url)) { try (Statement init = con.createStatement()) { init.execute("PRAGMA foreign_keys = ON"); init.execute("PRAGMA busy_timeout = 5000"); try (ResultSet rs = init.executeQuery("SELECT sqlite_version()")) { if (rs.next()) System.out.println("SQLite: " + rs.getString(1)); } } String sql = """ SELECT device_id, device_code, status FROM device WHERE status = ? ORDER BY device_code """; try (PreparedStatement ps = con.prepareStatement(sql)) { ps.setString(1, "active"); try (ResultSet rs = ps.executeQuery()) { while (rs.next()) { System.out.printf("%d %s %s%n", rs.getLong("device_id"), rs.getString("device_code"), rs.getString("status")); } } }}JDBC parameter indexes start at 1, mirroring the SQLite C binding convention. Use type-appropriate setters and retrieve values deliberately; SQLite's dynamic types still exist beneath Java's stronger host type system.
Java transaction: turn off JDBC auto-commit for the unit of work
con.setAutoCommit(false);try { long noteId; try (PreparedStatement insert = con.prepareStatement(""" INSERT INTO maintenance_note(device_id,noted_at,note_text) VALUES (?,?,?) RETURNING note_id """)) { insert.setLong(1, deviceId); insert.setString(2, when); insert.setString(3, text); try (ResultSet rs = insert.executeQuery()) { if (!rs.next()) throw new SQLException("no RETURNING row"); noteId = rs.getLong(1); } } try (PreparedStatement update = con.prepareStatement( "UPDATE device SET last_service_at=? WHERE device_id=?")) { update.setString(1, when); update.setLong(2, deviceId); if (update.executeUpdate() != 1) throw new SQLException("device not found"); } con.commit(); System.out.println(noteId);} catch (SQLException ex) { con.rollback(); throw ex;} finally { con.setAutoCommit(true);}PRAGMA initialization is driver-specific glue around SQLite state
Microsoft.Data.Sqlite can enable foreign keys through its connection string, which sends the appropriate PRAGMA after open. JDBC configurations vary, and a portable application can execute/verify PRAGMA foreign_keys=ON as part of its connection factory. If you use a pool, run required initialization for every physical connection and understand what reset behavior the pool provides.
SELECT sqlite_version() AS sqlite_version;PRAGMA foreign_keys;PRAGMA journal_mode;PRAGMA compile_options;Native library packaging is part of deployment
| Runtime | Typical SQLite delivery | Deployment implication |
|---|---|---|
| Microsoft.Data.Sqlite | Provider packages work with SQLite native bundles supplied by the selected package/configuration | Architecture/runtime RID/native package choice can change capabilities and SQLite version. |
| Xerial sqlite-jdbc | Default JAR includes native libraries for major OS/CPU targets; newer releases publish classifier variants too | Container/CPU architecture and extraction/temp-folder policy matter; query runtime version. |
| System-linked binding | Uses OS SQLite library | Security updates may come from OS packages, but feature/version consistency varies by host. |
| Statically bundled app | SQLite compiled into application/provider | Predictable release artifact, but you own upgrade cadence. |
File paths are application configuration, not an afterthought
Relative database paths are resolved from the process working directory and can create the classic “wrong empty database” bug. Resolve an application data directory intentionally, log the canonical path at startup where appropriate, and use read-only modes for packaged reference databases. Ensure the process identity has the required directory permissions—not just file permissions—because SQLite may create journal/WAL/SHM files beside the database.
Checkpoint
Cross-language integration check
Translate concepts rather than memorizing method names.
- What is the ADO.NET equivalent role of a JDBC PreparedStatement?
- Why must a pooled connection still run/verify initialization policy?
- What query proves which SQLite engine version is actually running?
- Why can a relative database path create a dangerous “works but empty” failure?
- Does Xerial’s bundled native library mean the Java application uses the OS sqlite3 CLI library?
- Who owns commit/rollback in both transaction examples?
Review the answers
SqliteCommand plus parameters plays the prepared/bound role. Every physical connection has its own SQLite connection state, so initialization must be guaranteed. SELECT sqlite_version() reports the engine in that process. Relative paths can resolve somewhere unexpected and create/open the wrong file. Xerial normally bundles native SQLite libraries, independent of the CLI. Application code that defines the business unit owns commit or rollback.
Bridge to the C API
Names differ across ADO.NET and JDBC, but both are wrappers around a smaller SQLite lifecycle. Lesson 5 exposes that lifecycle so errors such as “statement already finalized,” binding mismatch, SQLITE_BUSY, and constraint codes become easier to understand in any driver.