How to write in bulk with a transaction and bulkInsert

Purpose. Insert/update many rows with high performance. Without a transaction, each INSERT becomes a commit to disk (slow). There are three levels: (1) wrap everything in transaction(...), (2) reuse a SqliteStmt with reset(), (3) use bulkInsert, which does the bind/step/reset loop in native code (fastest).

APIs used

• SqliteDb.transaction(Runnable body) — runs body inside BEGIN/COMMIT; performs an automatic ROLLBACK if body throws a RuntimeException (and rethrows). The recommended form for atomicity.
• SqliteDb.begin() / commit() / rollback() — manual transaction control (equivalent to exec("BEGIN"/"COMMIT"/"ROLLBACK")). Prefer transaction(...) so you don’t forget the rollback.
• SqliteDb.prepare(String sql) — prepares a reusable SqliteStmt (compiles once). You must close it (try-with-resources).
• SqliteStmt.bindInt/bindLong/bindDouble/bindString/bindBlob/bindNull(idx, v) and bindAll(Object...) — 1-based bind; return this (fluent). step() executes; reset() prepares for the next execution (keeps the binds; use clearBindings() if you want to reset them).
• SqliteDb.bulkInsert(String sql, List<Object[]> rows) — prepares sql once and executes it for each Object[] within a single transaction, in a native loop. Returns the number of rows processed. Each Object[] has one element per ? parameter; accepted types: Long/Integer, Double/Float, String, byte[], null. The column types are inferred from the FIRST row — keep the rows homogeneous. Aliases: bulkUpdate, bulkDelete.

Why reuse the statement? prepare compiles the SQL; repeating prepare for each row recompiles it. Reusing it with reset() compiles once and only re-binds — much faster in large loops.

Full example (copy, paste and run)

package com.exemplo;

import java.util.*;                          // List, ArrayList

import dev.crossfly.ui.*;                     // Window
import dev.crossfly.ui.layout.*;             // VerticalLayout
import dev.crossfly.sqlite.*;                // SqliteDb, SqliteStmt

/** Runnable main screen. Point the launcher (classname) and run: gradlew :lwjgl3:run */
public class MainWindow extends Window
{
   @Override
   public void onShow() throws Exception
   {
      super.onShow();
      setLayout(new VerticalLayout());

      try (SqliteDb db = SqliteDb.open("app.db"))
      {
         db.exec("CREATE TABLE IF NOT EXISTS t(a INTEGER, b TEXT)");

         // Option A — transaction + reused statement (reset between executions)
         db.transaction(() ->
         {
            try (SqliteStmt st = db.prepare("INSERT INTO t(a, b) VALUES(?, ?)"))
            {
               for (int i = 0; i < 10_000; i++)
               {
                  st.bindInt(1, i)            // idx 1-based
                    .bindString(2, "row" + i)
                    .step();
                  st.reset();                 // ready for the next row
               }
            }
         }); // automatic commit; automatic rollback if it throws

         // Option B — native bulkInsert (fastest for large volumes)
         List<Object[]> rows = new ArrayList<>();
         for (int i = 0; i < 10_000; i++)
            rows.add(new Object[]{ i, "row" + i });   // types inferred from the 1st row
         int n = db.bulkInsert("INSERT INTO t(a, b) VALUES(?, ?)", rows);
         System.out.println(n + " linhas inseridas");
      }
   }
}

The snippet below shows only the core. To run it, place it inside the MainWindow skeleton above.

Minimal snippet

// verified at source: transaction, prepare + bind (1-based) + step + reset, bulkInsert
import dev.crossfly.sqlite.*;
import java.util.*;

try (SqliteDb db = SqliteDb.open("app.db"))
{
   db.exec("CREATE TABLE IF NOT EXISTS t(a INTEGER, b TEXT)");

   // Option A — transaction + reused statement (reset between executions)
   db.transaction(() ->
   {
      try (SqliteStmt st = db.prepare("INSERT INTO t(a, b) VALUES(?, ?)"))
      {
         for (int i = 0; i < 10_000; i++)
         {
            st.bindInt(1, i)            // idx 1-based
              .bindString(2, "row" + i)
              .step();
            st.reset();                 // ready for the next row
         }
      }
   }); // automatic commit; automatic rollback if it throws

   // Option B — native bulkInsert (fastest for large volumes)
   List<Object[]> rows = new ArrayList<>();
   for (int i = 0; i < 10_000; i++)
      rows.add(new Object[]{ i, "row" + i });   // types inferred from the 1st row
   int n = db.bulkInsert("INSERT INTO t(a, b) VALUES(?, ?)", rows);
   System.out.println(n + " linhas inseridas");
}

Dig deeper

• Background work · Performance and large lists
• Sibling recipes: Parameterized CRUD · Use outside the paint thread