English | 简体中文

api-docs / org.ktorm.dsl / Query

Query

class Query (source code)

Query is an abstraction of query operations and the core class of Ktorm’s query DSL.

The constructor of this class accepts two parameters: database is the database instance that this query
is running on; expression is the abstract representation of the executing SQL statement. Usually, we don’t
use the constructor to create Query objects but use the database.from(..).select(..) syntax instead.

Query provides a built-in iterator, so we can iterate the results by a for-each loop:

for (row in database.from(Employees).select()) {
println(row[Employees.name])
}

Moreover, there are many extension functions that can help us easily process the query results, such as
Query.map, Query.flatMap, Query.associate, Query.fold, etc. With the help of these functions, we can
obtain rows from a query just like it’s a common Kotlin collection.

Query objects are immutable. Query DSL functions are provided as its extension functions normally. We can
chaining call these functions to modify them and create new query objects. Here is a simple example:

val query = database
.from(Employees)
.select(Employees.salary)
.where { (Employees.departmentId eq 1) and (Employees.name like "%vince%") }

Easy to know that the query obtains the salary of an employee named vince in department 1. The generated
SQL is obviously:

select t_employee.salary as t_employee_salary
from t_employee
where (t_employee.department_id = ?) and (t_employee.name like ?)

More usages can be found in the documentations of those DSL functions.

Constructors

NameSummary

<init>

Query(database: Database, expression: QueryExpression)

Query is an abstraction of query operations and the core class of Ktorm’s query DSL.

Properties

NameSummary

database

val database: Database

the Database instance that this query is running on.

expression

val expression: QueryExpression

the underlying SQL expression of this query object.

rowSet

val rowSet: QueryRowSet

The ResultSet object of this query, lazy initialized after first access, obtained from the database by
executing the generated SQL.

sql

val sql: String

The executable SQL string of this query.

totalRecords

val totalRecords: Int

The total record count of this query ignoring the pagination params.

Functions

NameSummary

iterator

operator fun iterator(): Iterator<QueryRowSet>

Return an iterator over the rows of this query.

withExpression

fun withExpression(expression: QueryExpression): Query

Return a copy of this Query with the expression modified.

Extension Functions

NameSummary

asIterable

fun Query.asIterable(): Iterable<QueryRowSet>

Wrap this query as Iterable.

associate

fun <K, V> Query.associate(
    transform: (row: QueryRowSet) -> Pair<K, V>
): Map<K, V>

Return a Map containing key-value pairs provided by transform function applied to rows of the query.

associateBy

fun <K, V> Query.associateBy(
    keySelector: (row: QueryRowSet) -> K,
    valueTransform: (row: QueryRowSet) -> V
): Map<K, V>

Return a Map containing the values provided by valueTransform and indexed by keySelector functions applied to
rows of the query.

associateByTo

fun <K, V, M : MutableMap<in K, in V>> Query.associateByTo(
    destination: M,
    keySelector: (row: QueryRowSet) -> K,
    valueTransform: (row: QueryRowSet) -> V
): M

Populate and return the destination mutable map with key-value pairs, where key is provided by the keySelector
function and value is provided by the valueTransform function applied to rows of the query.

associateTo

fun <K, V, M : MutableMap<in K, in V>> Query.associateTo(
    destination: M,
    transform: (row: QueryRowSet) -> Pair<K, V>
): M

Populate and return the destination mutable map with key-value pairs provided by transform function applied to
each row of the query.

eq

infix fun <T : Any> T.eq(
    expr: ColumnDeclaring<T>
): BinaryExpression<Boolean>

Equal operator, translated to = in SQL.

flatMap

fun <R> Query.flatMap(
    transform: (row: QueryRowSet) -> Iterable<R>
): List<R>

Return a single list of all elements yielded from results of transform function being invoked on each row
of the query.

flatMapIndexed

fun <R> Query.flatMapIndexed(
    transform: (index: Int, row: QueryRowSet) -> Iterable<R>
): List<R>

Return a single list of all elements yielded from results of transform function being invoked on each row
and its index in the query.

flatMapIndexedTo

fun <R, C : MutableCollection<in R>> Query.flatMapIndexedTo(
    destination: C,
    transform: (index: Int, row: QueryRowSet) -> Iterable<R>
): C

Append all elements yielded from results of transform function being invoked on each row and its index
in the query, to the given destination.

flatMapTo

fun <R, C : MutableCollection<in R>> Query.flatMapTo(
    destination: C,
    transform: (row: QueryRowSet) -> Iterable<R>
): C

Append all elements yielded from results of transform function being invoked on each row of the query,
to the given destination.

fold

fun <R> Query.fold(
    initial: R,
    operation: (acc: R, row: QueryRowSet) -> R
): R

Accumulate value starting with initial value and applying operation to current accumulator value and each row.

foldIndexed

fun <R> Query.foldIndexed(
    initial: R,
    operation: (index: Int, acc: R, row: QueryRowSet) -> R
): R

Accumulate value starting with initial value and applying operation to current accumulator value and each row
with its index in the original query results.

forEach

fun Query.forEach(action: (row: QueryRowSet) -> Unit): Unit

Perform the given action on each row of the query.

forEachIndexed

fun Query.forEachIndexed(
    action: (index: Int, row: QueryRowSet) -> Unit
): Unit

Perform the given action on each row of the query, providing sequential index with the row.

groupBy

fun Query.groupBy(
    columns: Collection<ColumnDeclaring<*>>
): Query

fun Query.groupBy(vararg columns: ColumnDeclaring<*>): Query

Specify the group by clause of this query using the given columns or expressions.

having

fun Query.having(condition: ColumnDeclaring<Boolean>): Query

Specify the having clause of this query using the given condition expression.

fun Query.having(
    condition: () -> ColumnDeclaring<Boolean>
): Query

Specify the having clause of this query using the expression returned by the given callback function.

insertTo

fun Query.insertTo(
    table: BaseTable<*>,
    vararg columns: Column<*>
): Int

Insert the current Query‘s results into the given table, useful when transfer data from a table to another table.

joinTo

fun <A : Appendable> Query.joinTo(
    buffer: A,
    separator: CharSequence = ", ",
    prefix: CharSequence = "",
    postfix: CharSequence = "",
    limit: Int = -1,
    truncated: CharSequence = "...",
    transform: (row: QueryRowSet) -> CharSequence
): A

Append the string from all rows separated using separator and using the given prefix and postfix if supplied.

joinToString

fun Query.joinToString(
    separator: CharSequence = ", ",
    prefix: CharSequence = "",
    postfix: CharSequence = "",
    limit: Int = -1,
    truncated: CharSequence = "...",
    transform: (row: QueryRowSet) -> CharSequence
): String

Create a string from all rows separated using separator and using the given prefix and postfix if supplied.

limit

fun Query.limit(n: Int): Query

Specify the pagination limit parameter of this query.

fun Query.limit(offset: Int?, limit: Int?): Query

Specify the pagination parameters of this query.

locking

fun Query.locking(
    mode: LockingMode,
    tables: List<BaseTable<*>> = emptyList(),
    wait: LockingWait = WAIT
): Query

Specify the locking clause of this query, an example generated SQL could be:

locking

fun Query.locking(
    mode: LockingMode,
    tables: List<BaseTable<*>> = emptyList(),
    wait: LockingWait = WAIT
): Query

Specify the locking clause of this query, an example generated SQL could be:

map

fun <R> Query.map(
    transform: (row: QueryRowSet) -> R
): List<R>

Return a list containing the results of applying the given transform function to each row of the query.

mapIndexed

fun <R> Query.mapIndexed(
    transform: (index: Int, row: QueryRowSet) -> R
): List<R>

Return a list containing the results of applying the given transform function to each row and its index.

mapIndexedNotNull

fun <R : Any> Query.mapIndexedNotNull(
    transform: (index: Int, row: QueryRowSet) -> R?
): List<R>

Return a list containing only the non-null results of applying the given transform function to each row
and its index.

mapIndexedNotNullTo

fun <R : Any, C : MutableCollection<in R>> Query.mapIndexedNotNullTo(
    destination: C,
    transform: (index: Int, row: QueryRowSet) -> R?
): C

Apply the given transform function the each row and its index and append only the non-null results to
the given destination.

mapIndexedTo

fun <R, C : MutableCollection<in R>> Query.mapIndexedTo(
    destination: C,
    transform: (index: Int, row: QueryRowSet) -> R
): C

Apply the given transform function the each row and its index and append the results to the given destination.

mapNotNull

fun <R : Any> Query.mapNotNull(
    transform: (row: QueryRowSet) -> R?
): List<R>

Return a list containing only the non-null results of applying the given transform function to each row of
the query.

mapNotNullTo

fun <R : Any, C : MutableCollection<in R>> Query.mapNotNullTo(
    destination: C,
    transform: (row: QueryRowSet) -> R?
): C

Apply the given transform function to each row of the query and append only the non-null results to
the given destination.

mapTo

fun <R, C : MutableCollection<in R>> Query.mapTo(
    destination: C,
    transform: (row: QueryRowSet) -> R
): C

Apply the given transform function to each row of the query and append the results to the given destination.

neq

infix fun <T : Any> T.neq(
    expr: ColumnDeclaring<T>
): BinaryExpression<Boolean>

Not-equal operator, translated to <> in SQL.

notEq

infix fun <T : Any> T.notEq(
    expr: ColumnDeclaring<T>
): BinaryExpression<Boolean>

Not-equal operator, translated to <> in SQL.

offset

fun Query.offset(n: Int): Query

Specify the pagination offset parameter of this query.

orderBy

fun Query.orderBy(
    orders: Collection<OrderByExpression>
): Query

fun Query.orderBy(vararg orders: OrderByExpression): Query

Specify the order by clause of this query using the given order-by expressions.

union

fun Query.union(right: Query): Query

Union this query with the given one, corresponding to the union keyword in SQL.

unionAll

fun Query.unionAll(right: Query): Query

Union this query with the given one, corresponding to the union all keyword in SQL.

where

fun Query.where(condition: ColumnDeclaring<Boolean>): Query

Specify the where clause of this query using the given condition expression.

fun Query.where(
    condition: () -> ColumnDeclaring<Boolean>
): Query

Specify the where clause of this query using the expression returned by the given callback function.

whereWithConditions

fun Query.whereWithConditions(
    block: (MutableList<ColumnDeclaring<Boolean>>) -> Unit
): Query

Create a mutable list, then add filter conditions to the list in the given callback function, finally combine
them with the and operator and set the combined condition as the where clause of this query.

whereWithOrConditions

fun Query.whereWithOrConditions(
    block: (MutableList<ColumnDeclaring<Boolean>>) -> Unit
): Query

Create a mutable list, then add filter conditions to the list in the given callback function, finally combine
them with the or operator and set the combined condition as the where clause of this query.

withIndex

Return a lazy Iterable that wraps each row of the query into an IndexedValue containing the index of
that row and the row itself.