Select

This chapter describes how to select records from the database.

select Select records

In Kronos, we can use KPojo.select() method to query database records.

Field lists, aliases, generated projection objects, and join projection examples are covered in 📚Projection.

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 Specify query fields

The select method is used to specify the query fields. At this time, Kronos will generate query field statements according to the fields set by the select method.

It's supported to use String as a custom query field.

Multiple fields are written with [] and the alias method is used to set field aliases.

For field projection, aliases, generated result shapes, and DTO consumption, see 📚Projection. For derived query sources, scalar subqueries, predicate subqueries, and window-result filtering, see 📚Subqueries.

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"

Query all fields, exclude some columns

Returning it from an explicit projection expands every database column of the current KPojo. It can be returned directly or placed inside []. Use - to exclude columns from that full projection.

select { it }, select { [it] }, and select { listOf(it) } keep the same result type as select(). Once you exclude fields or add another item, Kronos returns a projection result.

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()

[] can also contain it - ... items, such as [it - it.id, "count(*) as total"] or [it - [it.id, it.age], it.id.alias("sourceId")]. See 📚Projection for generated result properties.

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"

Use subqueries and generated projections

Select fields can contain scalar subqueries, and where can consume selectable subqueries with in, exists, any, all, and row-value tuple conditions.

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
)

Generated projections can be returned directly by no-argument toList() or first(). Use filter when a selected field or alias should be filtered through the next query layer.

kotlin

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

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

Generated projection consumption is covered in 📚Projection. Derived query sources, scalar subqueries, predicate subqueries, and window-result filtering are covered in 📚Subqueries.

Generate query conditions from object values with where()

An empty where() reads queryable non-null fields from the current KPojo object and generates equality conditions.

select().toList() reads from the current table. Empty where() and by { ... } read object property values; where { ... } uses the lambda condition you write.

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

Use by { ... } when only selected object fields should participate in the match.

by Set query condition

The by method is used to set the query condition, at which point Kronos generates a query condition statement based on the fields set by the by method.

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

Use [] to select multiple condition fields:

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 Set the query conditions

where() generates query-by-example conditions from object values, and where { ... } generates a 📚conditional expression from the lambda.

Subquery predicates such as field in query, exists(query), any(query), all(query), and row-value tuple IN are covered in 📚Subqueries.

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

Inside an explicit where block, .eq can expand the current KPojo object's field values into equality conditions, and you can combine those conditions with other expressions.

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 provides the minus operator - to exclude fields from the explicit .eq expansion.

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 Add parameters to custom query conditions

Parameters

namedescriptiontypedefault
*pairsvararg，Parameters for customizing query conditionsPair<String, Any?>-

The patch method can be used to add parameters to a custom query condition when the where condition contains a custom Sql such as where { "id = :id".asSql() }.

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 Set sorting conditions

The orderBy method is used to set the sort condition, at which point Kronos generates a sort condition statement based on the fields set by the orderBy method.

For the focused guide to sorting, limit, page, withTotal, groupBy, having, and aggregates, see 📚Sorting, Pagination, and Aggregation.

Use the asc method to set ascending sorting and the desc method to set descending sorting.

When no sort method is set, the default is ascending, e.g. orderBy { it.id } is equivalent to 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 aliases and scalar subqueries can also be used as sort items.

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

Scalar subquery sorting and derived-source paging are covered in 📚Subqueries.

groupBy, having Set grouping and aggregation conditions

The groupBy method is used to set grouping conditions and the having method is used to set aggregation conditions.

Use [] when grouping by multiple fields, such as groupBy { [it.age, it.status] }.

For aggregate projections that become generated fields in the next query layer, see 📚Subqueries.

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 Specify the maximum number of records to query

The limit method is used to set the maximum number of records to query, at which point Kronos generates a limit condition statement based on the number set by the limit method.

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 Set row lock

The lock method is used to set a row lock on a query, at which point Kronos will add a lock based on the lock type set by the lock method.

By default, Kronos will use Exclusive Lock (SqlLock.Update()) from SqlLock, which can be changed to Shared Lock from SqlLock by passing the parameter (SqlLock.Share()).

For optimistic locks, see 📚Advanced Usage - Optimistic locking is a mutation strategy.

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

# Don't support adding row locks to Sqlite because Sqlite itself doesn't have row locks.

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 Query paging

page(pageIndex, pageSize) creates an offset page; page indexes start from 1. Add withTotal() after page(...) when the result also needs a total count.

In different databases, paging queries have different syntaxes, Kronos generates paging queries based on different databases.

The total-count form returns 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

Generated projections can be filtered through a derived query and then paged.

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

For projection visibility and total-count SQL shape, see 📚Subqueries.

db Specify the database name(cross-database join)

The db method is used for cross-database queries, when Kronos sets the database to query based on the parameters of the db method.

kotlin

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

// Or the db method can be called directly after the 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 cross-database querying is not supported because Sqlite cross-database querying requires using the ATTACH command to attach another database to the current database, and then using the SELECT statement to query the data across databases.

SQLServer

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

Oracle

#Oracle cross-library query function is not supported because Oracle cross-library query needs to be configured with dblink and query based on it.

Result methods

Use a terminal result method at the end of a select chain to choose the returned shape.

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()

Result shapes, nullable single-row methods, generated projection results, pagination totals, and custom wrappers are covered in 📚Result Methods.

single Add a single-row limit

Use single() when a query chain should add a one-row limit before the terminal method.

kotlin

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

Mysql

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

Cascading query

Cascading query loads related data through separate query steps. A common flow is to load parent rows first, then load child rows from those parent results.

The detailed cascade select API is covered in 📚advanced usage - Cascading queries.

Specify the data source to be used

In Kronos, we can use the specified data source to query the records in the database.

kotlin

val customWrapper = CustomWrapper()

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