# How to write in bulk with a transaction and bulkInsert

> Insert thousands of rows fast — transaction, a reused statement with reset(), or native bulkInsert.

Source: https://crossfly.dev/docs/cookbook/data/transacao-e-bulk — CrossFly documentation. Full docs as one file: https://crossfly.dev/llms-full.txt

# 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)

```java
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
```java
// 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](/docs/data-and-services/background-work) · [Performance and large lists](/docs/best-practices/performance-e-listas-grandes)
- Sibling recipes: [Parameterized CRUD](/docs/cookbook/data/crud) · [Use outside the paint thread](/docs/cookbook/data/banco-fora-da-paint-thread)