更新

本章将介绍如何更新数据库行记录。

set 设置更新的字段和值

在Kronos中，我们可以使用KPojo.update().set方法设置需要更新的字段和值，然后使用execute方法执行更新操作。

使用 where() 或 by { ... } 指定目标行条件。空 where() 会按当前 KPojo 的可查询非空字段生成等值条件。

update().execute() 会更新当前表中所有符合框架策略的行。需要拒绝全表写入时，请启用 DataGuard。

生成 update 中追加的策略字段见 📚逻辑删除 和 📚乐观锁。

kotlin

val user: User = User(
    id = 1
)

user
    .update()
    .set { it.name = "Kronos ORM" }
    .where()
    .execute()

// 根据字符串动态设置赋值列：
//    .set { it["name"] = "Kronos ORM" }

Mysql

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

PostgreSQL

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

SQLite

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

SQLServer

UPDATE [user]
SET [name] = :nameNew
WHERE [id] = :id

Oracle

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

+= -= 复合赋值运算符

Kronos支持语义化的+=和-=加减法赋值运算来更新数据库中的数值列

kotlin

user
    .update()
    .set { it.age += 2 }
    .where()
    .execute()

// 根据字符串动态设置赋值列：
//    .set { it["age"] += 2 }

Mysql

UPDATE `user`
SET `age` = `age` + :age2PlusNew
WHERE `id` = :id

PostgreSQL

UPDATE "user"
SET "age" = "age" + :age2PlusNew
WHERE "id" = :id

SQLite

UPDATE `user`
SET `age` = `age` + :age2PlusNew
WHERE `id` = :id

SQLServer

UPDATE [user]
SET [age] = [age] + :age2PlusNew
WHERE [id] = :id

Oracle

UPDATE "user"
SET "age" = "age" + :age2PlusNew
WHERE "id" = :id

使用 Kotlin 表达式作为赋值

set 赋值右侧可以是普通运行时 Kotlin 表达式。Kronos 会把表达式结果绑定为本次更新值。

kotlin

fun displayName(): String? = null

User(id = 1)
    .update()
    .set { it.name = displayName() ?: "匿名" }
    .where()
    .execute()

Mysql

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

使用标量子查询赋值

当更新值来自另一条查询时，可以在set中使用标量子查询。子查询选择一列，并使用limit(1)限定单值。

kotlin

Order()
    .update()
    .set {
        it.status = (Order()
            .select { order -> order.status }
            .where { order -> order.userId == 44 }
            .limit(1) as Int?)
    }
    .where { it.id == 3 }
    .execute()

Mysql

UPDATE `order`
SET `status` = (
    SELECT `status`
    FROM `order`
    WHERE `order`.`user_id` = :userId
    LIMIT 1
)
WHERE `order`.`id` = :id

by 设置更新条件

在Kronos中，我们可以使用by方法设置更新条件，此时Kronos会根据by方法设置的字段生成更新条件语句。

kotlin

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

user
    .update()
    .set { it.name = "Kronos ORM" }
    .by { it.id }
    .execute()

Mysql

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

PostgreSQL

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

SQLite

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

SQLServer

UPDATE [user]
SET [name] = :nameNew
WHERE [id] = :id

Oracle

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

where 设置更新条件

where() 按当前对象值生成 query-by-example 更新条件。where { ... } 使用 lambda 生成更新📚Criteria条件语句。

kotlin

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

user
    .update()
    .set { it.name = "Kronos ORM" }
    .where { it.id == 1 }
    .execute()

Mysql

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

PostgreSQL

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

SQLite

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

SQLServer

UPDATE [user]
SET [name] = :nameNew
WHERE [id] = :id

Oracle

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

使用 IN 子查询筛选更新行

在where中使用field in query，可以更新另一条查询选中的行。

kotlin

User(name = "active")
    .update { [it.name] }
    .where {
        it.id in Order()
            .select { order -> order.userId }
            .where { order -> order.status == 1 }
    }
    .execute()

Mysql

UPDATE `user`
SET `name` = :nameNew
WHERE `user`.`id` IN (
    SELECT `user_id` AS `userId`
    FROM `order`
    WHERE `order`.`status` = :status
)

更多标量和谓词子查询写法见 📚子查询。

在显式where块中，可以使用.eq将当前 KPojo 对象的字段值展开为等值条件，并继续组合其他条件表达式：

kotlin

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

user.update { it.name }.where { it.eq && it.status > 1 }.execute()

Mysql

UPDATE `user`
SET `name` = :nameNew
WHERE `id` = :id
  and `name` = :name
  and `status` = :status
  and `status` > :statusMin

PostgreSQL

UPDATE "user"
SET "name" = :nameNew
WHERE "id" = :id
  and "name" = :name
  and "status" = :status
  and "status" > :statusMin

SQLite

UPDATE `user`
SET `name` = :nameNew
WHERE "id" = :id
  and "name" = :name
  and "status" = :status
  and "status" > :statusMin

SQLServer

UPDATE [user]
SET [name] = :nameNew
WHERE [id] = :id
  and [name] = :name
  and [status] = :status
  and [status] > :statusMin

Oracle

UPDATE "user"
SET "name" = :nameNew
WHERE "id" = :id
  and "name" = :name
  and "status" = :status
  and "status" > :statusMin

Kronos提供了减号运算符-，用于在显式.eq展开时排除指定字段。

kotlin

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

user.update { it.name }.where { (it - it.name).eq && it.status > 1 }.execute()

Mysql

UPDATE `user`
SET `name` = :nameNew
WHERE `id` = :id
  and `status` > :statusMin

PostgreSQL

UPDATE "user"
SET "name" = :nameNew
WHERE "id" = :id
  and "status" > :statusMin

SQLite

UPDATE "user"
SET "name" = :nameNew
WHERE "id" = :id
  and "status" > :statusMin

SQLServer

UPDATE [user]
SET [name] = :nameNew
WHERE [id] = :id
  and [status] > :statusMin

Oracle

UPDATE "user"
SET "name" = :nameNew
WHERE "id" = :id
  and "status" > :statusMin

patch 为自定义更新条件添加参数

在Kronos中，我们可以使用patch方法为自定义更新条件添加参数。

kotlin

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

user
    .update()
    .set { it.name = "Kronos ORM" }
    .where { "id = :id".asSql() }
    .patch("id" to 1)
    .execute()

Mysql

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

PostgreSQL

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

SQLite

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

SQLServer

UPDATE [user]
SET [name] = :nameNew
WHERE id = :id

Oracle

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

update 设置需要更新的字段

在Kronos中，我们在update方法中设置需要更新的字段，未在update方法中设置的字段将不会被更新。

kotlin

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

user
    .update { [it.name, it.age] }
    .where { it.id == 1 }
    .execute()

Mysql

UPDATE `user`
SET `name` = :nameNew,
    `age`  = :ageNew
WHERE `id` = :id

PostgreSQL

UPDATE "user"
SET "name" = :nameNew,
    "age"  = :ageNew
WHERE "id" = :id

SQLite

UPDATE `user`
SET `name` = :nameNew,
    `age`  = :ageNew
WHERE `id` = :id

SQLServer

UPDATE [user]
SET [name] = :nameNew, [age] = :ageNew
WHERE [id] = :id

Oracle

UPDATE "user"
SET "name" = :nameNew,
    "age"  = :ageNew
WHERE "id" = :id

update - 设置排除的字段

在Kronos中，我们可以使用减号表达式-设置需要排除的字段。

kotlin

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

user
    .update { it - it.id }
    .where { it.id == 1 }
    .execute()

Mysql

UPDATE `user`
SET `name` = :nameNew,
    `age`  = :ageNew
WHERE `id` = :id

PostgreSQL

UPDATE "user"
SET "name" = :nameNew,
    "age"  = :ageNew
WHERE "id" = :id

SQLite

UPDATE `user`
SET `name` = :nameNew,
    `age`  = :ageNew
WHERE `id` = :id

SQLServer

UPDATE [user]
SET [name] = :nameNew, [age] = :ageNew
WHERE [id] = :id

Oracle

UPDATE "user"
SET "name" = :nameNew,
    "age"  = :ageNew
WHERE "id" = :id

影响行数

在Kronos中，我们可以使用execute方法执行更新操作，并获取更新操作的影响行数。

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

val (affectedRows) = user
    .update()
    .set { it.name = "Kronos ORM" }
    .execute()

批量更新记录

同一条 SQL 需要用多组参数执行时，见 📚批量操作。

指定使用的数据源

在Kronos中，我们可以将KronosDataSourceWrapper传入execute方法，以实现自定义的数据库连接。

val customWrapper = CustomWrapper()

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

user
    .update()
    .set { it.name = "Kronos ORM" }
    .execute(customWrapper)