删除

本章将介绍如何删除数据库行记录。

使用显式条件删除记录

在Kronos中，我们可以使用KPojo.delete().execute()方法用于删除数据库中的记录

空 where() 会读取当前 KPojo 对象中的可查询非空字段，并生成删除条件。by { ... } 用于选择参与匹配的对象字段，where { ... } 用于编写自定义条件。

delete().execute() 会删除当前表中所有符合框架策略的行。需要拒绝全表删除时，请启用 DataGuard。

写入标记列的 delete 行为见 📚逻辑删除。

kotlin

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

user.delete().where().execute()

Mysql

DELETE
FROM `user`
WHERE `id` = :id
  and `name` = :name
  and `age` = :age

PostgreSQL

DELETE
FROM "user"
WHERE "id" = :id
  and "name" = :name
  and "age" = :age

SQLite

DELETE
FROM `user`
WHERE `id` = :id
  and `name` = :name
  and `age` = :age

SQLServer

DELETE
FROM [user]
WHERE [id] = :id and [name] = :name and [age] = :age

Oracle

DELETE
FROM "user"
WHERE "id" = :id
  and "name" = :name
  and "age" = :age

by 设置删除条件

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

kotlin

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

user.delete().by { it.id }.execute()

Mysql

DELETE
FROM `user`
WHERE `id` = :id

PostgreSQL

DELETE
FROM "user"
WHERE "id" = :id

SQLite

DELETE
FROM "user"
WHERE "id" = :id

SQLServer

DELETE
FROM [user]
WHERE [id] = :id

Oracle

DELETE
FROM "user"
WHERE "id" = :id

where 设置删除条件

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

kotlin

val user: User = User(
    id = 1,
)

user.delete().where { it.id.eq && it.name like "Kronos%" and it.age > 18 }.execute()

Mysql

DELETE
FROM `user`
WHERE `id` = :id
  and `name` like :name
  and `age` > :ageMin

PostgreSQL

DELETE
FROM "user"
WHERE "id" = :id
  and "name" like :name
  and "age" > :ageMin

SQLite

DELETE
FROM "user"
WHERE "id" = :id
  and "name" like :name
  and "age" > :ageMin

SQLServer

DELETE
FROM [user]
WHERE [id] = :id and [name] like :name and [age] > :ageMin

Oracle

DELETE
FROM "user"
WHERE "id" = :id
  and "name" like :name
  and "age" > :ageMin

使用 IN 子查询筛选删除行

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

in

User()
    .delete()
    .where {
        it.id in Order()
            .select { order -> order.userId }
            .where { order -> order.status == 3 }
    }
    .execute()

in sql

DELETE FROM `user`
WHERE `user`.`id` IN (
    SELECT `user_id` AS `userId`
    FROM `order`
    WHERE `order`.`status` = :status
)

使用 EXISTS 筛选删除行

当关联行决定目标行是否删除时，可以使用exists(query)。子查询可以引用外层where接收者。

exists

User()
    .delete()
    .where { user ->
        exists(
            Order()
                .select()
                .where { order -> order.userId == user.id && order.status == 0 }
        )
    }
    .execute()

exists sql

DELETE FROM `user`
WHERE EXISTS (
    SELECT `id`, `user_id` AS `userId`, `status`
    FROM `order`
    WHERE `order`.`user_id` = `user`.`id`
      AND `order`.`status` = :status
)

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

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

kotlin

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

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

Mysql

DELETE
FROM `user`
WHERE `id` = :id
  and `name` = :name
  and `status` = :status
  and `status` > :statusMin

PostgreSQL

DELETE
FROM "user"
WHERE "id" = :id
  and "name" = :name
  and "status" = :status
  and "status" > :statusMin

SQLite

DELETE
FROM "user"
WHERE "id" = :id
  and "name" = :name
  and "status" = :status
  and "status" > :statusMin

SQLServer

DELETE
FROM [user]
WHERE [id] = :id
  and [name] = :name
  and [status] = :status
  and [status] > :statusMin

Oracle

DELETE
FROM "user"
WHERE "id" = :id
  and "name" = :name
  and "status" = :status
  and "status" > :statusMin

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

kotlin

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

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

Mysql

DELETE
FROM `user`
WHERE `id` = :id
  and `status` > :statusMin

PostgreSQL

DELETE
FROM "user"
WHERE "id" = :id
  and "status" > :statusMin

SQLite

DELETE
FROM "user"
WHERE "id" = :id
  and "status" > :statusMin

SQLServer

DELETE
FROM [user]
WHERE [id] = :id
  and [status] > :statusMin

Oracle

DELETE
FROM "user"
WHERE "id" = :id
  and "status" > :statusMin

patch 为自定义删除条件添加参数

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

kotlin

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

user.delete().where { it.eq && "status = :status".asSql() }.patch("status" to 1).execute()

Mysql

DELETE
FROM `user`
WHERE `id` = :id
  and status = :status

PostgreSQL

DELETE
FROM "user"
WHERE "id" = :id
  and status = :status

SQLite

DELETE
FROM `user`
WHERE `id` = :id
  and status = :status

SQLServer

DELETE
FROM [user]
WHERE [id] = :id
  and status = :status

Oracle

DELETE
FROM "user"
WHERE "id" = :id
  and status = :status

logic 逻辑删除

在Kronos中，我们可以使用logic方法设置逻辑删除，此时Kronos会生成逻辑删除的SQL语句。

逻辑删除的开启与字段设置设置请参考📚全局设置 - 逻辑删除策略、📚注解设置 - @LogicDelete表逻辑删除、📚注解设置 - @LogicDelete逻辑删除列。

kotlin

val user: User = User(
    id = 1,
)

user.delete().by { it.id }.logic().execute()

Mysql

UPDATE `user`
SET `deleted` = 1
WHERE `id` = :id

PostgreSQL

UPDATE "user"
SET "deleted" = 1
WHERE "id" = :id

SQLite

UPDATE `user`
SET `deleted` = 1
WHERE `id` = :id

SQLServer

UPDATE [user]
SET [deleted] = 1
WHERE [id] = :id

Oracle

UPDATE "user"
SET "deleted" = 1
WHERE "id" = :id

逻辑删除使用子查询条件时，Kronos 会保留子查询谓词，并追加逻辑删除谓词。

kotlin

User()
    .delete()
    .logic()
    .where {
        it.id in Order()
            .select { order -> order.userId }
            .where { order -> order.status == 5 }
    }
    .execute()

Mysql

UPDATE `user`
SET `deleted` = :deletedNew
WHERE `user`.`id` IN (
    SELECT `user_id` AS `userId`
    FROM `order`
    WHERE `order`.`status` = :status
)
AND `deleted` = 0

影响行数

execute方法返回的KronosOperationResult对象中包含了影响行数。

kotlin

val user: User = User(
    id = 1,
)

val (affectedRows) = user.delete().by { it.id }.execute()

批量删除记录

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

指定使用的数据源

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

kotlin

val customWrapper = CustomWrapper()

val user: User = User(
    id = 1,
)

user.delete().by { it.id }.execute(customWrapper)