ktorm-support-sqlite/org.ktorm.support.sqlite/bulkInsertOrUpdateReturning

bulkInsertOrUpdateReturning

fun <T : BaseTable<*>, C : Any> Database.bulkInsertOrUpdateReturning(table: T, returning: Column<C>, block: BulkInsertOrUpdateStatementBuilder<T>.(T) -> Unit): List<C?>(source)

Bulk insert records to the table, determining if there is a key conflict while inserting each of them, automatically performs updates if any conflict exists, and finally returns the specific column.

Usage:

database.bulkInsertOrUpdateReturning(Employees, Employees.id) {
    item {
        set(it.id, 1)
        set(it.name, "vince")
        set(it.job, "engineer")
        set(it.salary, 1000)
        set(it.hireDate, LocalDate.now())
        set(it.departmentId, 1)
    }
    item {
        set(it.id, 5)
        set(it.name, "vince")
        set(it.job, "engineer")
        set(it.salary, 1000)
        set(it.hireDate, LocalDate.now())
        set(it.departmentId, 1)
    }
    onConflict {
        set(it.salary, it.salary + 900)
    }
}
Content copied to clipboard

Generated SQL:

insert into t_employee (id, name, job, salary, hire_date, department_id)
values (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?)
on conflict (id) do update set salary = t_employee.salary + ?
returning id
Content copied to clipboard

By default, the column used in the on conflict statement is the primary key you already defined in the schema definition. If you want, you can specify one or more columns for the on conflict statement as belows:

database.bulkInsertOrUpdateReturning(Employees, Employees.id) {
    item {
        set(it.id, 1)
        set(it.name, "vince")
        set(it.job, "engineer")
        set(it.salary, 1000)
        set(it.hireDate, LocalDate.now())
        set(it.departmentId, 1)
    }
    item {
        set(it.id, 5)
        set(it.name, "vince")
        set(it.job, "engineer")
        set(it.salary, 1000)
        set(it.hireDate, LocalDate.now())
        set(it.departmentId, 1)
    }
    onConflict(it.name, it.job) {
        set(it.salary, it.salary + 900)
    }
}
Content copied to clipboard

Generated SQL:

insert into t_employee (id, name, job, salary, hire_date, department_id)
values (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?)
on conflict (name, job) do update set salary = t_employee.salary + ?
returning id
Content copied to clipboard

Since

3.6.0

Return

the returning column's values.

Parameters

table

the table to be inserted.

returning

the column to return

block

the DSL block used to construct the expression.

fun <T : BaseTable<*>, C1 : Any, C2 : Any> Database.bulkInsertOrUpdateReturning(table: T, returning: Pair<Column<C1>, Column<C2>>, block: BulkInsertOrUpdateStatementBuilder<T>.(T) -> Unit): List<Pair<C1?, C2?>>(source)

Bulk insert records to the table, determining if there is a key conflict while inserting each of them, automatically performs updates if any conflict exists, and finally returns the specific columns.

Usage:

database.bulkInsertOrUpdateReturning(Employees, Pair(Employees.id, Employees.job)) {
    item {
        set(it.id, 1)
        set(it.name, "vince")
        set(it.job, "engineer")
        set(it.salary, 1000)
        set(it.hireDate, LocalDate.now())
        set(it.departmentId, 1)
    }
    item {
        set(it.id, 5)
        set(it.name, "vince")
        set(it.job, "engineer")
        set(it.salary, 1000)
        set(it.hireDate, LocalDate.now())
        set(it.departmentId, 1)
    }
    onConflict {
        set(it.salary, it.salary + 900)
    }
}
Content copied to clipboard

Generated SQL:

insert into t_employee (id, name, job, salary, hire_date, department_id)
values (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?)
on conflict (id) do update set salary = t_employee.salary + ?
returning id, job
Content copied to clipboard

By default, the column used in the on conflict statement is the primary key you already defined in the schema definition. If you want, you can specify one or more columns for the on conflict statement as belows:

database.bulkInsertOrUpdateReturning(Employees, Pair(Employees.id, Employees.job)) {
    item {
        set(it.id, 1)
        set(it.name, "vince")
        set(it.job, "engineer")
        set(it.salary, 1000)
        set(it.hireDate, LocalDate.now())
        set(it.departmentId, 1)
    }
    item {
        set(it.id, 5)
        set(it.name, "vince")
        set(it.job, "engineer")
        set(it.salary, 1000)
        set(it.hireDate, LocalDate.now())
        set(it.departmentId, 1)
    }
    onConflict(it.name, it.job) {
        set(it.salary, it.salary + 900)
    }
}
Content copied to clipboard

Generated SQL:

insert into t_employee (id, name, job, salary, hire_date, department_id)
values (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?)
on conflict (name, job) do update set salary = t_employee.salary + ?
returning id, job
Content copied to clipboard

Since

3.6.0

Return

the returning columns' values.

Parameters

table

the table to be inserted.

returning

the columns to return

block

the DSL block used to construct the expression.

fun <T : BaseTable<*>, C1 : Any, C2 : Any, C3 : Any> Database.bulkInsertOrUpdateReturning(table: T, returning: Triple<Column<C1>, Column<C2>, Column<C3>>, block: BulkInsertOrUpdateStatementBuilder<T>.(T) -> Unit): List<Triple<C1?, C2?, C3?>>(source)

Bulk insert records to the table, determining if there is a key conflict while inserting each of them, automatically performs updates if any conflict exists, and finally returns the specific columns.

Usage:

database.bulkInsertOrUpdateReturning(Employees, Triple(Employees.id, Employees.job, Employees.salary)) {
    item {
        set(it.id, 1)
        set(it.name, "vince")
        set(it.job, "engineer")
        set(it.salary, 1000)
        set(it.hireDate, LocalDate.now())
        set(it.departmentId, 1)
    }
    item {
        set(it.id, 5)
        set(it.name, "vince")
        set(it.job, "engineer")
        set(it.salary, 1000)
        set(it.hireDate, LocalDate.now())
        set(it.departmentId, 1)
    }
    onConflict {
        set(it.salary, it.salary + 900)
    }
}
Content copied to clipboard

Generated SQL:

insert into t_employee (id, name, job, salary, hire_date, department_id)
values (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?)
on conflict (id) do update set salary = t_employee.salary + ?
returning id, job, salary
Content copied to clipboard

By default, the column used in the on conflict statement is the primary key you already defined in the schema definition. If you want, you can specify one or more columns for the on conflict statement as belows:

database.bulkInsertOrUpdateReturning(Employees, Triple(Employees.id, Employees.job, Employees.salary)) {
    item {
        set(it.id, 1)
        set(it.name, "vince")
        set(it.job, "engineer")
        set(it.salary, 1000)
        set(it.hireDate, LocalDate.now())
        set(it.departmentId, 1)
    }
    item {
        set(it.id, 5)
        set(it.name, "vince")
        set(it.job, "engineer")
        set(it.salary, 1000)
        set(it.hireDate, LocalDate.now())
        set(it.departmentId, 1)
    }
    onConflict(it.name, it.job) {
        set(it.salary, it.salary + 900)
    }
}
Content copied to clipboard

Generated SQL:

insert into t_employee (id, name, job, salary, hire_date, department_id)
values (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?)
on conflict (name, job) do update set salary = t_employee.salary + ?
returning id, job, salary
Content copied to clipboard

Since

3.6.0

Return

the returning columns' values.

Parameters

table

the table to be inserted.

returning

the columns to return

block

the DSL block used to construct the expression.