Batch Operations

This chapter describes how to execute batch SQL statements.

Execute one SQL statement with many parameter sets

Use SqlExecutor.batchExecute when the same SQL statement should run for multiple rows. It creates a KronosAtomicBatchTask and calls the active KronosDataSourceWrapper.batchUpdate(...).

import com.kotlinorm.database.SqlExecutor

val affectedRows: IntArray = with(SqlExecutor) {
    wrapper.batchExecute(
        "UPDATE user SET name = :name WHERE id = :id",
        arrayOf(
            mapOf("name" to "Alice", "id" to 1),
            mapOf("name" to "Bob", "id" to 2)
        )
    )
}

UPDATE user SET name = :name WHERE id = :id

affectedRows == intArrayOf(1, 1)

Each map in the array is bound to the named parameters in the SQL string. The returned IntArray is the per-row result returned by the JDBC driver.

Build a batch task directly

Use KronosAtomicBatchTask when you need to pass operation metadata or when your code already works with wrapper tasks.

import com.kotlinorm.beans.task.KronosAtomicBatchTask
import com.kotlinorm.enums.KOperationType

val task = KronosAtomicBatchTask(
    sql = "INSERT INTO user (name, age) VALUES (:name, :age)",
    paramMapArr = arrayOf(
        mapOf("name" to "Alice", "age" to 18),
        mapOf("name" to "Bob", "age" to 20)
    ),
    operationType = KOperationType.INSERT
)

val affectedRows = wrapper.batchUpdate(task)

INSERT INTO user (name, age) VALUES (:name, :age)

affectedRows == intArrayOf(1, 1)

KronosAtomicBatchTask.parsedArr() converts the named-parameter SQL into one JDBC SQL string and one JDBC parameter list per row.

val (jdbcSql, jdbcParams) = task.parsedArr()

jdbcSql == "INSERT INTO user (name, age) VALUES (?, ?)"
jdbcParams.map { it.toList() } == listOf(
    listOf("Alice", 18),
    listOf("Bob", 20)
)

Batch insert shape

Batch insert uses the same batch task shape. Keep the column list fixed and pass one parameter map per row.

with(SqlExecutor) {
    wrapper.batchExecute(
        "INSERT INTO user (name, age) VALUES (:name, :age)",
        arrayOf(
            mapOf("name" to "Alice", "age" to 18),
            mapOf("name" to "Bob", "age" to 20)
        )
    )
}

For single-row KPojo mutation DSL, use 📚Insert, 📚Update, 📚Delete, and 📚Upsert.