Insert

This chapter describes how to insert records into the database.

Insert a single record

In Kronos, we can use the KPojo.insert().execute() method to insert a record into the database.

val user: User = User(
        id = 1,
        name = "Kronos",
        age = 18
    )

user.insert().execute()

Last insert ID and affected rows

execute() returns a KronosOperationResult.

val user: User = User(
    name = "Kronos",
    age = 18
)

val result = user.insert().withId().execute()
val affectedRows = result.affectedRows
val lastInsertId = result.lastInsertId

.withId() declares that this insert should read the generated ID. The target KPojo needs an identity primary key.

Read more about 📚generated identity IDs.

Batch insert records

Use 📚Batch Operations when the same SQL insert should run with many parameter sets.

INSERT SELECT

Use KSelectable<Selected>.insert<Target>() to insert rows from a query. Without explicit values, Kronos inserts the source select list into the target insertable fields.

Order()
    .select { [it.id, it.userId, it.status] }
    .where { it.status == 1 }
    .insert<OrderArchive>()
    .execute()

INSERT INTO `order_archive` (`id`, `user_id`, `status`)
SELECT `id`, `user_id` AS `userId`, `status`
FROM `order`
WHERE `order`.`status` = :status

execute() returns KronosOperationResult. For INSERT SELECT, affectedRows is the number of rows inserted by the generated statement.

val result = Order()
    .select { [it.id, it.userId, it.status] }
    .where { it.status == 1 }
    .insert<OrderArchive>()
    .execute()

val insertedRows = result.affectedRows

The source select list must match the target insertable field count when no explicit values are provided.

Order()
    .select { it.id }
    .insert<OrderArchive>()
    .build()

Insert-select source column count (1) must match target insertable field count (3).

Use explicit values when inserted values come from source expressions or scalar subqueries.

Order()
    .select { [it.id, it.userId, it.status] }
    .where { it.status == 1 }
    .insert<OrderArchive> {
        [
            it.id,
            it.userId + 1,
            Order()
                .select { order -> order.status }
                .where { order -> order.userId == 38 }
                .limit(1)
        ]
    }
    .execute()

INSERT INTO `order_archive` (`id`, `user_id`, `status`)
SELECT `id`,
       (`user_id` + 1),
       (
           SELECT `status`
           FROM `order`
           WHERE `order`.`user_id` = :userId
           LIMIT 1
       )
FROM `order`
WHERE `order`.`status` = :status

The source can also be a derived selectable. The target field order still comes from the target KPojo insertable fields.

val paidOrders = Order()
    .where { it.status == 1 }
    .select { [it.id, it.userId, it.status] }
    .where { it.userId == 42 }

paidOrders
    .insert<OrderArchive>()
    .execute()

INSERT INTO `order_archive` (`id`, `user_id`, `status`)
SELECT `q`.`id`, `q`.`user_id` AS `userId`, `q`.`status`
FROM (
    SELECT `id`, `user_id` AS `userId`, `status`
    FROM `order`
    WHERE `order`.`status` = :status
) AS `q`
WHERE `q`.`user_id` = :userId

When the target KPojo uses an identity primary key, Kronos excludes that field from the target field list.

Order()
    .select { [it.userId, it.status] }
    .where { it.status == 1 }
    .insert<OrderIdentityArchive>()
    .execute()

INSERT INTO `order_identity_archive` (`user_id`, `status`)
SELECT `user_id` AS `userId`, `status`
FROM `order`
WHERE `order`.`status` = :status

For join sources, union sources, scalar subquery rules, and projection details, see 📚Subqueries.

Specify the data source to use

In Kronos, we can pass KronosDataSourceWrapper into the execute method to achieve a custom database connection.

val customWrapper = CustomWrapper()

val user: User = User(
        id = 1,
        name = "Kronos",
        age = 18
    )

user.insert().execute(customWrapper)