org.xerial.sqlitejdbc

What is it

org.xerial.sqlitejdbc lets you create and interact with a SQLite database from Java.

Why use it

To quote the blurb on the SQLite website

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is the most used database engine in the world. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day.

The SQLite file format is stable, cross-platform, and backwards compatible and the developers pledge to keep it that way through the year 2050. SQLite database files are commonly used as containers to transfer rich content between systems and as a long-term archival format for data. There are over 1 trillion (1e12) SQLite databases in active use.

SQLite source code is in the public-domain and is free to everyone to use for any purpose.

So if you want a data store, and it's okay if that data is in a file on the filesystem, SQLite is a very good choice.

Getting Started

import org.sqlite.SQLiteDataSource;

import java.util.List;

void main() throws Exception {
    var db = new SQLiteDataSource();
    db.setUrl("jdbc:sqlite:database.db");

    try (var conn = db.getConnection();
         var stmt = conn.prepareStatement("""
                 CREATE TABLE IF NOT EXISTS widget(
                    id integer not null primary key,
                    name text not null
                 )
                 """)) {
        stmt.execute();
    }

    try (var conn = db.getConnection()) {
        for (var name : List.of("Bob", "Susan", "Sob", "Busan")) {
            try (var stmt = conn.prepareStatement("""
                 INSERT INTO widget(name) VALUES (?)
                 """)) {
                stmt.setString(1, name);
                stmt.execute();
            }
        }
    }

    // id=1, name=Bob
    // id=2, name=Susan
    // id=3, name=Sob
    // id=4, name=Busan
    try (var conn = db.getConnection();
         var stmt = conn.prepareStatement("""
                 SELECT id, name
                 FROM widget
                 """)) {
        var rs = stmt.executeQuery();
        while (rs.next()) {
            System.out.println(
                    STR."id=\{rs.getInt("id")}, name=\{rs.getString("name")}"
            );
        }
    }
}

<- Index