Persistence with SQLite

The canonical way to persist structured data in CrossFly is the dev.crossfly.sqlite module — a thin layer over the native cfsqlite lib (SQLite3MultipleCiphers). It replaces the old dev.crossfly.sql (JDBC).

Run the database off the paint thread and return to the UI with Component.onUI(...) (see Background work). SqliteDb, SqliteStmt and SqliteCursor are all AutoCloseable → use try-with-resources.

Open / close

try (SqliteDb db = SqliteDb.open("app.db")) {
   db.exec("CREATE TABLE IF NOT EXISTS cliente(id INTEGER PRIMARY KEY, nome TEXT, idade INTEGER)");
   // … use db …
}   // closes on its own

Factories: SqliteDb.open(String), open(String, int flags), open(String, byte[] key) (encrypted), openMemory().

Parameterized CRUD

// INSERT/UPDATE/DELETE → execute(sql, params...) returns the number of affected rows
db.execute("INSERT INTO cliente(nome, idade) VALUES(?, ?)", "Ana", 30);
db.execute("UPDATE cliente SET idade=? WHERE id=?", 31, 1);

// SELECT → query(sql, params...) returns a cursor
try (SqliteCursor c = db.query("SELECT id, nome, idade FROM cliente WHERE idade > ?", 18)) {
   while (c.next()) {
      long id    = c.getLong(0);      // ⚠️ cursor getters are 0-based
      String nome= c.getString(1);
      int idade  = c.getInt(2);
   }
}

// single value:
long total = db.queryLong("SELECT COUNT(*) FROM cliente", 0);
String nome = db.queryString("SELECT nome FROM cliente WHERE id=?", 1);

⚠️ The index gotcha: bind 1-based, getters 0-based

It is the module’s most common mistake — the ? markers start at 1, the result columns at 0.

Transactions and bulk writes

db.transaction(() -> {                      // automatic begin/commit (rollback on exception)
   db.execute("INSERT INTO cliente(nome) VALUES(?)", "A");
   db.execute("INSERT INTO cliente(nome) VALUES(?)", "B");
});

// manual begin/commit/rollback also exist:
db.begin(); /* … */ db.commit();

// bulk write (statement reused internally):
List<Object[]> linhas = List.of(new Object[]{"A", 1}, new Object[]{"B", 2});
db.bulkInsert("INSERT INTO cliente(nome, idade) VALUES(?, ?)", linhas);

Reusable prepared statement

try (SqliteStmt st = db.prepare("INSERT INTO cliente(nome, idade) VALUES(?, ?)")) {
   for (Cliente c : lista) {
      st.bindAll(c.nome, c.idade);   // or bindString(1, ...).bindInt(2, ...)
      st.step();
      st.reset();                    // reuses the statement
   }
}

Optional encryption

byte[] chave = obterChave();                       // your key (byte[])
try (SqliteDb db = SqliteDb.open("secreto.db", chave)) {   // ChaCha20-Poly1305 cipher by default
   // … encrypted database …
   db.rekey(novaChave);                            // changes the key (or removes it by passing another)
}

No key = plain SQLite, zero overhead. Supported ciphers include CHACHA20 (default), SQLCIPHER, AES256CBC, etc. (Sqlite.Cipher).

Key/value table (replaces the old lightweight persistence)

Lightweight persistence (Settings.appSettings) has been removed. For flags/theme/last login, a key/value table does the job:

db.exec("CREATE TABLE IF NOT EXISTS kv(k TEXT PRIMARY KEY, v TEXT)");
db.execute("INSERT OR REPLACE INTO kv(k, v) VALUES(?, ?)", "tema", "escuro");
String tema = db.queryString("SELECT v FROM kv WHERE k=?", "tema");

See Files and preferences.

Best practices

• Always use try-with-resources (db, stmt, cursor) — avoids leaking native handles.
• Batch writes → transaction(...)/bulkInsert(...) (orders of magnitude faster).
• Database off the paint thread; results back with onUI(...).

Common mistakes

• Swapping the bases: ? is 1-based, cursor column is 0-based.
• Running queries on the paint thread and freezing the UI.
• Forgetting to close cursor/statement (leaks a native resource).

See also

• State management · Background work