查询

本章将介绍如何查询数据库中的记录。

`select` 查询记录

在Kronos中，我们可以使用KPojo.select()方法用于查询数据库中的记录。

字段列表、alias、生成投影对象和 join 投影示例见 📚投影。

kotlin

val users: List<User> = User().select().toList()

Mysql

SELECT `id`, `name`, `age`
FROM `user`

PostgreSQL

SELECT "id", "name", "age"
FROM "user"

SQLite

SELECT "id", "name", "age"
FROM "user"

SQLServer

SELECT [id], [name], [age]
FROM [user]

Oracle

SELECT "id", "name", "age"
FROM "user"

`select` 指定查询字段

select方法用于指定查询字段，此时Kronos会根据select方法设置的字段生成查询字段语句。

Kronos支持直接传入字符串作为查询字段。

使用[]书写多个字段，alias方法用于设置字段别名。

字段投影、alias、生成结果形态和 DTO 消费方式见 📚投影。派生查询源、标量子查询、谓词子查询和窗口函数结果过滤见 📚子查询。

kotlin

val rows: List<Map<String, Any?>> = User()
    .select {
        [it.id, it.name.alias("username"), "count(*) as total", "1"]
    }
    .toMapList()

Mysql

SELECT `id`, `name` AS `username`, count(*) AS total, 1
FROM `user`

PostgreSQL

SELECT "id", "name" AS "username", count(*) AS total, 1
FROM "user"

SQLite

SELECT "id", `name` AS `username`, count(*) AS total, 1
FROM `user`

SQLServer

SELECT [id], [name] AS [username], count (*) AS total, 1
FROM [user]

Oracle

SELECT "id", "name" AS "username", count(*) AS total, 1
FROM "user"

查询全部字段、排除部分列

在显式投影中返回 it 会展开当前 KPojo 的全部数据库列；it 可以直接返回，也可以放进 []。使用 - 可以从完整投影中排除列。

select { it }、select { [it] } 和 select { listOf(it) } 都和 select() 一样返回源 KPojo 类型。只要排除字段或再追加一个投影项，就会返回投影结果。

val allDirect = User().select { it }.toList()
val allInList = User().select { [it] }.toList()
val withoutId = User().select { it - it.id }.toList()
val withoutIdInList = User().select { [it - it.id] }.toList()

val withAlias = User()
    .select { [it, it.id.alias("sourceId")] }
    .toList()

[] 里也可以放 it - ... 项，例如 [it - it.id, "count(*) as total"] 或 [it - [it.id, it.age], it.id.alias("sourceId")]。生成结果属性见 📚投影。

kotlin

val rows: List<Map<String, Any?>> = User()
    .select { [it - it.id, "count(*) as total"] }
    .toMapList()

Mysql

SELECT `name`, `age`, count(*) AS total
FROM `user`

PostgreSQL

SELECT "name", "age", count(*) AS total
FROM "user"

SQLite

SELECT "name", "age", count(*) AS total
FROM "user"

SQLServer

SELECT [name], [age], count (*) AS total
FROM [user]

Oracle

SELECT "name", "age", count(*) AS total
FROM "user"

使用子查询和生成投影

Select 字段可以包含标量子查询，where 可以通过 in、exists、any、all 和 row-value tuple 条件消费 selectable subquery。

kotlin

val users = User()
    .select { user ->
        [
            user.id,
            user.name,
            Order()
                .select { order -> order.status }
                .where { order -> order.userId == user.id }
                .limit(1)
                .alias("lastOrderStatus")
        ]
    }
    .where { user ->
        user.id in Order()
            .select { order -> order.userId }
            .where { order -> order.status == 1 }
    }
    .toList()

Mysql

SELECT `id`,
       `name`,
       (SELECT `status`
        FROM `order`
        WHERE `order`.`user_id` = `user`.`id`
        LIMIT 1) AS `lastOrderStatus`
FROM `user`
WHERE `user`.`id` IN (
    SELECT `user_id` AS `userId`
    FROM `order`
    WHERE `order`.`status` = :status
)

生成投影可以由无参 toList() 或 first() 直接返回。需要通过下一层查询筛选 selected 字段或 alias 时，使用 filter。

kotlin

val nameLengths = User()
    .select { [it.id, f.length(it.name).alias("nameLength")] }

val rows = nameLengths
    .filter { it.nameLength > 8 }
    .toList()

生成投影消费方式见 📚投影。派生查询源、标量子查询、谓词子查询和窗口函数结果过滤见 📚子查询。

使用 `where`() 根据对象值生成查询条件

空 where() 会读取当前 KPojo 对象中的可查询非空字段，并生成等值查询条件。

select().toList() 按当前表读取数据。空 where() 和 by { ... } 会读取对象字段值；where { ... } 使用你在 lambda 中写出的条件。

kotlin

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

val listOfUser: List<User> = user.select().where().toList()

Mysql

SELECT `id`, `name`, `age`
FROM `user`
WHERE `name` = :name

PostgreSQL

SELECT "id", "name", "age"
FROM "user"
WHERE "name" = :name

SQLite

SELECT "id", "name", "age"
FROM "user"
WHERE "name" = :name

SQLServer

SELECT [id], [name], [age]
FROM [user]
WHERE [name] = :name

Oracle

SELECT "id", "name", "age"
FROM "user"
WHERE "name" = :name

若只想使用对象中的部分字段参与匹配，使用 by { ... } 选择字段。

`by` 设置查询条件

by方法用于设置查询条件，此时Kronos会根据by方法设置的字段生成查询条件语句。

kotlin

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

val kronos: User? = user.select().by { it.id }.firstOrNull()

Mysql

SELECT `id`, `name`, `age`
FROM `user`
WHERE `id` = :id

PostgreSQL

SELECT "id", "name", "age"
FROM "user"
WHERE "id" = :id

SQLite

SELECT "id", "name", "age"
FROM "user"
WHERE "id" = :id

SQLServer

SELECT [id], [name], [age]
FROM [user]
WHERE [id] = :id

Oracle

SELECT "id", "name", "age"
FROM "user"
WHERE "id" = :id

多个条件字段使用[]书写：

kotlin

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

val kronos: User? = user.select().by { [it.id, it.name] }.firstOrNull()

Mysql

SELECT `id`, `name`, `age`
FROM `user`
WHERE `id` = :id and `name` = :name

PostgreSQL

SELECT "id", "name", "age"
FROM "user"
WHERE "id" = :id and "name" = :name

SQLite

SELECT "id", "name", "age"
FROM "user"
WHERE "id" = :id and "name" = :name

SQLServer

SELECT [id], [name], [age]
FROM [user]
WHERE [id] = :id and [name] = :name

Oracle

SELECT "id", "name", "age"
FROM "user"
WHERE "id" = :id and "name" = :name

`where` 设置查询条件

where() 按对象值生成 query-by-example 条件，where { ... } 按 lambda 表达式生成 📚Criteria条件语句。

field in query、exists(query)、any(query)、all(query) 和 row-value tuple IN 等子查询谓词见 📚子查询。

kotlin

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

val kronos: User? = user.select().where { it.id }.firstOrNull()

val listOfUser: List<User> = user.select()
    .where { it.id > 1 && it.age < 20 }
    .toList()

Mysql

SELECT `id`, `name`, `age`
FROM `user`
WHERE `id` = :id

SELECT `id`, `name`, `age`
FROM `user`
WHERE `id` > :id
  and `age` < :age

PostgreSQL

SELECT "id", "name", "age"
FROM "user"
WHERE "id" = :id

SELECT "id", "name", "age"
FROM "user"
WHERE "id" > :id
  and "age" < :age

SQLite

SELECT "id", "name", "age"
FROM "user"
WHERE "id" = :id

SELECT "id", "name", "age"
FROM "user"
WHERE "id" > :id
  and "age" < :age

SQLServer

SELECT [id], [name], [age]
FROM [user]
WHERE [id] = :id

SELECT [id], [name], [age]
FROM [user]
WHERE [id] > :id and [age] < :age

Oracle

SELECT "id", "name", "age"
FROM "user"
WHERE "id" = :id

SELECT "id", "name", "age"
FROM "user"
WHERE "id" > :id
  and "age" < :age

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

kotlin

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

user.select().where { it.eq && it.status > 1 }.firstOrNull()

Mysql

SELECT `id`, `name`, `age`
FROM `user`
WHERE `id` = :id
  and `name` = :name
  and `status` = :status
  and `status` > :statusMin

PostgreSQL

SELECT "id", "name", "age"
FROM "user"
WHERE "id" = :id
  and "name" = :name
  and "status" = :status
  and "status" > :statusMin

SQLite

SELECT "id", "name", "age"
FROM "user"
WHERE "id" = :id
  and "name" = :name
  and "status" = :status
  and "status" > :statusMin

SQLServer

SELECT [id], [name], [age]
FROM [user]
WHERE [id] = :id
  and [name] = :name
  and [status] = :status
  and [status] > :statusMin

Oracle

SELECT "id", "name", "age"
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.select().where { (it - it.name).eq && it.status == 1 }.firstOrNull()

Mysql

SELECT `id`, `name`, `age`
FROM `user`
WHERE `id` = :id
  and `status` = :status

PostgreSQL

SELECT "id", "name", "age"
FROM "user"
WHERE "id" = :id
  and "status" = :status

SQLite

SELECT "id", "name", "age"
FROM "user"
WHERE "id" = :id
  and "status" = :status

SQLServer

SELECT [id], [name], [age]
FROM [user]
WHERE [id] = :id
  and [status] = :status

Oracle

SELECT "id", "name", "age"
FROM "user"
WHERE "id" = :id
  and "status" = :status

`patch` 为自定义查询条件添加参数

参数:

参数	说明	类型	默认值
*`pairs`	vararg，自定义查询条件的参数	`Pair<String, Any?>`	`-`

当where条件内包含自定义Sql如：where { "id = :id".asSql() }时，可以使用patch方法为自定义查询条件添加参数。

kotlin

val user = User().select()
    .where { "id = :id".asSql() }
    .patch("id" to 1)
    .firstOrNull()

Mysql

SELECT `id`, `name`, `age`
FROM `user`
WHERE id = :id

PostgreSQL

SELECT "id", "name", "age"
FROM "user"
WHERE id = :id

SQLite

SELECT "id", "name", "age"
FROM "user"
WHERE id = :id

SQLServer

SELECT [id], [name], [age]
FROM [user]
WHERE id = :id

Oracle

SELECT "id", "name", "age"
FROM "user"
WHERE id = :id

`orderBy` 设置排序条件

orderBy方法用于设置排序条件，此时Kronos会根据orderBy方法设置的字段生成排序条件语句。

排序、limit、page、withTotal、groupBy、having 和聚合的集中说明见 📚排序、分页与聚合。

使用asc方法设置升序排序，使用desc方法设置降序排序。

当不设置排序方法时，默认为升序排序，如：orderBy { it.id }等同于orderBy { it.id.asc() }

kotlin

val listOfUser: List<User> = User().select()
    .orderBy { [it.id.desc(), it.name.asc()] }
    .toList()

Mysql

SELECT `id`, `name`, `age`
FROM `user`
ORDER BY `id` DESC, `name` ASC

PostgreSQL

SELECT "id", "name", "age"
FROM "user"
ORDER BY "id" DESC, "name" ASC

SQLite

SELECT "id", "name", "age"
FROM "user"
ORDER BY "id" DESC, "name" ASC

SQLServer

SELECT [id], [name], [age]
FROM [user]
ORDER BY [id] DESC, [name] ASC

Oracle

SELECT "id", "name", "age"
FROM "user"
ORDER BY "id" DESC, "name" ASC

selected alias 和标量子查询也可以作为排序项。

kotlin

val rows = User()
    .select { [it.id, f.length(it.name).alias("nameLength")] }
    .orderBy { it.nameLength.desc() }
    .toList()

Mysql

SELECT `id`, LENGTH(`name`) AS `nameLength`
FROM `user`
ORDER BY `nameLength` DESC

标量子查询排序和派生来源分页见 📚子查询。

`groupBy`, `having` 设置分组和聚合条件

groupBy方法用于设置分组条件，having方法用于设置聚合条件。

如需根据多个字段分组，使用 []，例如 groupBy { [it.age, it.status] }。

聚合投影进入下一层查询后作为 generated field 使用的规则见 📚子查询。

kotlin

val listOfUser: List<User> = User().select()
    .groupBy { it.age }
    .having { it.age > 18 }
    .toList()

Mysql

SELECT `id`, `name`, `age`
FROM `user`
GROUP BY `age`
HAVING `age` > :age

PostgreSQL

SELECT "id", "name", "age"
FROM "user"
GROUP BY "age"
HAVING "age" > :age

SQLite

SELECT "id", "name", "age"
FROM "user"
GROUP BY "age"
HAVING "age" > :age

SQLServer

SELECT [id], [name], [age]
FROM [user]
GROUP BY [age]
HAVING [age] > :age

Oracle

SELECT "id", "name", "age"
FROM "user"
GROUP BY "age"
HAVING "age" > :age

`limit` 设置查询记录数

limit方法用于设置查询记录数，此时Kronos会根据limit方法设置的记录数生成查询条件语句。

kotlin

val listOfUser: List<User> = User().select()
    .limit(10)
    .toList()

Mysql

SELECT `id`, `name`, `age`
FROM `user` LIMIT 10

PostgreSQL

SELECT "id", "name", "age"
FROM "user" LIMIT 10

SQLite

SELECT "id", "name", "age"
FROM "user" LIMIT 10

SQLServer

SELECT [id], [name], [age]
FROM [user]
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Oracle

SELECT "id", "name", "age"
FROM "user"
WHERE ROWNUM <= 10

`lock` 设置查询时行锁

lock方法用于设置查询时行锁，此时Kronos会根据lock方法设置的锁类型进行锁的添加。

默认情况下，Kronos 会使用 SqlLock.Update()。可以通过传入 SqlLock.Share() 改用共享锁。

如需使用乐观锁，请参考📚进阶用法 - 加锁机制。

kotlin

val listOfUser: List<User> = User().select()
    .lock() // SqlLock.Update()
    .toList()

val listOfUser: List<User> = User().select()
    .lock(SqlLock.Share())
    .toList()

Mysql

SELECT `id`, `name`, `age`
FROM `user` FOR UPDATE

SELECT `id`, `name`, `age`
FROM `user` LOCK IN SHARE MODE

PostgreSQL

SELECT "id", "name", "age"
FROM "user" FOR UPDATE

SELECT "id", "name", "age"
FROM "user" FOR SHARE

SQLite

#不支持对Sqlite添加行锁功能因为Sqlite本身没有行锁功能

SQLServer

SELECT [id], [name], [age]
FROM [user]
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY ROWLOCK

Oracle

SELECT "id", "name", "age"
FROM "user" FOR UPDATE(NOWAIT)

SELECT "id", "name", "age"
FROM "user" LOCK IN SHARE MODE

`page`、`withTotal` 设置分页查询

page(pageIndex, pageSize) 创建 offset page，页码从 1 开始。需要总数时，在 page(...) 后调用 withTotal()。

在不同的数据库中，分页查询的语法有所不同，Kronos会根据不同的数据库生成相应的分页查询语句。

带总数形式返回 PageResult<T>。

kotlin

val page = User().select()
    .page(1, 10)
    .withTotal()
    .toList()

val total = page.total
val listOfUser = page.records
val totalPages = page.totalPages

Mysql

SELECT `id`, `name`, `age`
FROM `user` LIMIT 10
OFFSET 0

PostgreSQL

SELECT "id", "name", "age"
FROM "user" LIMIT 10
OFFSET 0

SQLite

SELECT "id", "name", "age"
FROM "user" LIMIT 10
OFFSET 0

SQLServer

SELECT [id], [name], [age]
FROM [user]
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Oracle

SELECT "id", "name", "age"
FROM "user"
WHERE ROWNUM <= 10

生成投影可以通过派生查询筛选，再继续分页。

kotlin

val nameLengths = User()
    .select { [it.id, f.length(it.name).alias("nameLength")] }

val page = nameLengths
    .filter { it.nameLength > 8 }
    .page(1, 10)
    .withTotal()
    .toList()

val rows = page.records

Mysql

SELECT `q`.`id`, `q`.`nameLength`
FROM (
    SELECT `id`, LENGTH(`name`) AS `nameLength`
    FROM `user`
) AS `q`
WHERE `q`.`nameLength` > :nameLength
LIMIT 10
OFFSET 0

投影可见性和总数查询 SQL 形态见 📚子查询。

`db` 跨库查询

db方法用于跨库查询，此时Kronos会根据db方法的参数设置查询的数据库。

kotlin

val listOfUser: List<User> = User()
    .db("user_database")
    .select { [it.id, it.username] }
    .toList()

// 或者db方法可以直接在select后面调用
// val listOfUser: List<User> = User()
//            .select { [it.id, it.username] }
//            .db("user_database")
//            .toList()

Mysql

SELECT `id`, `username`
FROM `user_database`.`user`

PostgreSQL

SELECT "id", "username"
FROM "user_database"."user"

SQLite

#不支持Sqlite跨库查询功能因为Sqlite进行跨库查询需要使用ATTACH命令将另一个数据库附加到当前数据库中
，
然后使用SELECT语句来跨数据库查询数据

SQLServer

SELECT [id], [username]
FROM [user_database].[user]

Oracle

#不支持Oracle跨库查询功能因为Oracle进行跨库查询需要配置dblink并以此为基础进行查询

结果方法

在 select 查询链末尾使用结果方法决定返回形态。

kotlin

val users: List<User> = User()
    .select()
    .toList()

val row: User? = User()
    .select()
    .where { it.id == 1 }
    .firstOrNull()

val mapRows: List<Map<String, Any?>> = User()
    .select { [it.id, it.name] }
    .toMapList()

结果形态、可空单行查询、生成投影返回值、分页总数和自定义 wrapper 见 📚结果方法。

`single` 添加单行限制

查询链需要在终端方法前添加单行限制时，使用 single()。

kotlin

val user: User = User().select().single().first()

Mysql

SELECT `id`, `name`, `age`
FROM `user` LIMIT 1

级联查询

级联查询通过分步骤读取关联数据。常见流程是先读取主表记录，再根据这些主表结果读取子表记录。

详细 API 见📚进阶用法 - 级联查询。

使用指定的数据源

在Kronos中，我们可以使用指定的数据源查询数据库中的记录。

kotlin

val customWrapper = CustomWrapper()

val listOfUser: List<User> = User().select().toList(customWrapper)

查询

select 查询记录

kotlin

Mysql

PostgreSQL

SQLite

SQLServer

Oracle

select 指定查询字段

kotlin

Mysql

PostgreSQL

SQLite

SQLServer

Oracle

查询全部字段、排除部分列

kotlin

Mysql

PostgreSQL

SQLite

SQLServer

Oracle

使用子查询和生成投影

kotlin

Mysql

kotlin

使用 where() 根据对象值生成查询条件

kotlin

Mysql

PostgreSQL

SQLite

SQLServer

Oracle

by 设置查询条件

kotlin

Mysql

PostgreSQL

SQLite

SQLServer

Oracle

kotlin

Mysql

PostgreSQL

SQLite

SQLServer

Oracle

where 设置查询条件

kotlin

Mysql

PostgreSQL

SQLite

SQLServer

Oracle

kotlin

Mysql

PostgreSQL

SQLite

SQLServer

Oracle

kotlin

Mysql

PostgreSQL

SQLite

SQLServer

Oracle

patch 为自定义查询条件添加参数

kotlin

Mysql

PostgreSQL

SQLite

SQLServer

Oracle

orderBy 设置排序条件

kotlin

Mysql

PostgreSQL

SQLite

SQLServer

Oracle

kotlin

`select` 查询记录

`select` 指定查询字段

使用 `where`() 根据对象值生成查询条件

`by` 设置查询条件

`where` 设置查询条件

`patch` 为自定义查询条件添加参数

`orderBy` 设置排序条件

`groupBy`, `having` 设置分组和聚合条件

`limit` 设置查询记录数

`lock` 设置查询时行锁

`page`、`withTotal` 设置分页查询

`db` 跨库查询

`single` 添加单行限制