Introducció

A continuació tens un exemple d'una execució d'una consulta:

database.from(Employees).select().forEach {
    row -> println(row[Employees.name])
}

El que fem és:

  1. Seleccionar una taula amb from -> retorna un objecte QuerySource.
  2. Crear una consulta amb el mètode select de QuerySource -> retorna un objecte Query.
  3. Cridar la funció d'extensió forEach que utilitza l'iterador de Query (moment en que s'executa la consulta SQL) per mostrar els noms dels treballadors.

from

from is an extension function of Database that represents the from clause of a SQL query.

It wraps the specific table as a QuerySource:

fun Database.from(table: BaseTable<*>): QuerySource

After we get a QuerySource object, we can call the select function to create a query, or we can continue to call innerJoin, leftJoin or other functions to join some tables.

Com ja saps de TODO(link sql:dql) encara que en una sentència de consulta sql el primer que s'escriu és select, la sentència sempre es comença a llegir i executar pel from i l'últim és el select.

Per tant, no t'ha d'estrenyar que aquí comencem pel from.

Query Objects

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

data class Query(val database: Database, val expression: QueryExpression) {
    
    val sql: String by lazy { ... }

    val rowSet: QueryRowSet by lazy { ... }

    val totalRecords: Int by lazy { ... }

    operator fun iterator(): Iterator<QueryRowSet> {
        return rowSet.iterator()
    }
}

Its constructor accepts two parameters: database is the database instance that this query is running on, and 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 overloads the iterator operator, that’s why we can iterate the results by a for-each loop.

Moreover, ktorm also provide some additional extension functions just like Iterable in the Kotlin standard lib, so we can also process the results via functions such as map, flatMap, etc. tal com veurem a TODO (link)

Here is an example:

data class Emp(val id: Int?, val name: String?, val salary: Long?)

val query = database.from(Employees).select()

query
    .map { row -> Emp(row[Employees.id], row[Employees.name], row[Employees.salary]) }
    .filter { it.salary > 1000 }
    .sortedBy { it.salary }
    .forEach { println(it.name) }

Please note: In the example above, all the work Ktorm does is just to generate a simple SQL select * from employee. The following .map { }.filter { }.sortedBy { }.forEach { } are just collection operations in memory.

There are some other useful properties in the Query class:

  • sql: Return the generated SQL string of this query, can be used to ensure whether the generated SQL is expected while debugging.

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

  • totalRecords: If the query doesn’t limits the results via offset and limit, return the size of the result set. Or if it does, return the total record count of the query ignoring the offset and limit parameters. Ktorm provides this property to support pagination, we can calculate page count through dividing it by our page size.

Extreure les dades

You might have noticed that the return type of Query.rowSet was not a normal ResultSet, but a QueryRowSet instead. That’s a special implementation provided by Ktorm.

Every JDBC user knows how to obtain query results from a ResultSet. We need a loop to iterate rows in it, calling the getter functions (such as getInt, getString, etc) to obtain the data of the specific column. A typical usage is based on a while loop: while (rs.netxt()) { ... }. Moreover, after finishing these works, we also have to call close function to release the resources.

QueryRowset provides additional features:

  • Available offline: It’s connection independent, it remains available after the connection closed, and it’s not necessary to be closed after being used. Ktorm creates QueryRowSet objects with all data being retrieved from the result set into memory, so we just need to wait for GC to collect them after they are not useful.

  • Indexed access operator: QueryRowSet overloads the indexed access operator, so we can use square brackets [] to obtain the value by giving a specific Column instance. It’s less error prone by the benefit of the compiler’s static checking. Also, we can still use getXxx functions in the ResultSet to obtain our results by labels or column indices.

A continuació tens un exemple d'accés a les dades mitjançant l'operador d'accés indexat:

for (row in database.from(Employees).select()) {
    val id: Int? = row[Employees.id]
    val name: String? = row[Employees.name]
    val salary: Long? = row[Employees.salary]

    println("$id, $name, $salary")
}

We can see that if the column’s type is Column<Int>, then the result’s type is Int?, and if the column’s type is Column<String>, the result type will be String?.

Pots veure que aquest és un accés segur perqué no em puc equivocar en els tipus:

Si per exemple, m'equivoco en el tipus de la variable salary:

for (row in database.from(Employees).select()) {
    val id: Int? = row[Employees.id]
    val name: String? = row[Employees.last]
    val salary: Double? = row[Employees.salary]

    println("$id, $name, $salary")
}

El compilador m'avisa d'aquests error:

TODO

En canvi, si utilitzo directament els mètodes de l'objecte ResultSetel compilador no pot verificar que el tipus sigui correcte:

//TODO
while (rs.next()) {
    ...
    val salary: Double? = rs.getDouble(2)
}

Com tampoc pot verificar que no m'hagi equivocat amb l'index d'accés.

A més, the types are not limited to the return types of getXxx functions in ResultSet, they can be any types corresponding to the column instances instead.

And additionally, there can be some necessary conversions on data, that depends on the column’s implementation of SqlType.

select

select is an extension function of QuerySource and accepts any number of columns and returns a new-created Query object which selects specific columns from the current query source.

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

The example below obtains employees’ ids and names via the select function:

val query = database.from(Employees).select(Employees.id, Employees.name)

Now we have a Query object, but no SQL has been executed yet.

We can:

  1. Chaining call where or other extension functions to modify it
  2. Iterate it by a for-each loop or any other way. While the query object is iterated, Ktorm will execute a generated SQL, then we can obtain results in the way we discussed above.

The generated SQL is given as follows:

select employee.id as employee_id, employee.name as employee_name 
from employee

Try to remove the arguments passed to the select function:

val query = database.from(Employees).select()

Then the generated SQL will be changed to select *:

select * 
from employee

You might have noticed that the parameter type of select function was ColumnDeclaring instead of Column.

So we can not only select normal columns from a table, but complex expressions and aggregation functions are also supported.

For instance, if we want to know the salary difference between the max and the min in a company, we can write a query like this:

database
    .from(Employees)
    .select(max(Employees.salary) - min(Employees.salary))
    .forEach { row -> println(row.getLong(1)) }

Here we use two aggregation functions, max and min, the return types of which are both AggregateExpression. Then subtracting the max by the min, we finally have a BinaryExpression, which is a subclass of ColumnDeclaring, so we can pass it to the select function.

Generated SQL:

select max(employee.salary) - min(employee.salary) 
from employee

We can see that the generated SQL is highly corresponding to our Kotlin code.

Ktorm provides many overloaded operators, that’s why we can use the minus operator in the query above.

Because of operator overloading, the minus operator here doesn’t perform an actual subtraction but being translated to a minus operator in SQL and executed in our database.

selectDistinct

selectDistinct is also an extension function of QuerySource. Just as its name implies, it will be translated to a select distinct statement in SQL, and its usage is totally the same with select function, so we won’t repeat it.

where

where is an extension function of Query class:

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

It’s an inline function that accepts a parameter of type () -> ColumnDeclaring<Boolean>, which is a closure function that returns a ColumnDeclaring<Boolean> as our filter condition.

The where function creates a new Query object with all properties being copied from the current query, but applying a new filter condition, the return value of the closure.

Typical usage:

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 easy too:

select employee.salary as employee_salary 
from employee 
where (employee.department_id = ?) and (employee.name like ?)

We can return any filter conditions in where closure, here we constructed one by operators eq, and and like. Kotlin provides an infix keyword, functions marked with it can be called using the [infix notation](https://kotlinlang.org/docs/reference/functions.html#infix-notation] (omitting the dot and the parentheses for the call), that’s how these operators work.

Ktorm’s built-in operators can be divided into two groups: those that is implemented by operator overloading, such as basic arithmetic operators; and those that is based on infix notations, such as and, or, eq, gt, lt, like, etc.

whereWithConditions

Sometimes, we need a variable number of filter conditions in our queries, those conditions are combined with and or or operator and each of them can be enabled or disabled depending on different conditions. Ktorm provides a convenient function whereWithConditions:

val query = database
    .from(Employees)
    .select(Employees.salary)
    .whereWithConditions {
        if (departmentId != null) {
            it += Employees.departmentId eq departmentId
        }
        if (managerId != null) {
            it += Employees.managerId eq managerId
        }
        if (name != null) {
            it += Employees.name like "%$name%"
        }
    }

groupBy/having

Both groupBy and having are extension functions for Query class, they provide aggregation support for Ktorm.

A usage example is shown below:

val t = Employees.aliased("t")
val query = database
    .from(t)
    .select(t.departmentId, avg(t.salary))
    .groupBy(t.departmentId)
    .having { avg(t.salary) gt 100 }

This query selects departments whose average salary is greater than 100, then returns the average salaries along with their department’s IDs.

The usage is similar to other extension functions like select and where, and the generated SQL is also simple and direct too:

select employee.department_id as employee_department_id, avg(employee.salary) 
from temployee 
group by employee.department_id 
having avg(employee.salary) > ?

Question: what will happen if we just add one column to the query above? Assuming if we want to select the employees’ names additionally:

val query = database
    .from(t)
    .select(t.departmentId, avg(t.salary), t.name)
    .groupBy(t.departmentId)
    .having { avg(t.salary) gt 100 }

The generated SQL will be changed to:

select t_employee.department_id as t_employee_department_id, avg(t_employee.salary), t_employee.name as t_employee_name 
from t_employee 
group by t_employee.department_id 
having avg(t_employee.salary) > ?

However, as any SQL users know, the generated SQL is wrong with syntax now, and it’s impossible to be executed in a database. That’s because the SQL’s grammar restricts that if we are using group by, every select column either comes from the group by clause or appears in an aggregation function.

So, that’s not Ktorm’s fault, we don’t understand SQL enough, Ktorm just translates our Koltin code to SQL trustily.

Note: Ktorm generates SQLs, but its design goal is never to replace SQL in Kotlin. Ktorm doesn’t mean to be an “automation” ORM framework that’s “large and complete”. Instead, one of its goals is to provide a set of flexible and convenient DSL for SQL by making full use of Kotlin’s excellent features. This requires their users to have a certain understanding of SQL because Ktorm just translates our DSL to SQL trustily, you have to take the responsibility of our SQL’s correctness and performance.

orderBy

orderBy is also an extension function for Query class, it’s corresponding to SQL’s order by keyword:

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

It can be seen that this function accepts a variable number of OrderByExpressions, that can be created by other two functions, asc and desc, naming by the keywords in SQL:

fun ColumnDeclaring<*>.asc(): OrderByExpression
fun ColumnDeclaring<*>.desc(): OrderByExpression

Typical usage is shown below. The query obtains all employees’ names, sorting them by their salaries descending:

val query = database
    .from(Employees)
    .select(Employees.name)
    .orderBy(Employees.salary.desc())

Similar to select, the orderBy function not only supports sorting by normal columns, but complex expressions are also OK.

The query below obtains departments’ IDs and their average salaries, and sorting them by their average salaries descending:

val t = Employees.aliased("t")
val query = database
    .from(t)
    .select(t.departmentId, avg(t.salary))
    .groupBy(t.departmentId)
    .orderBy(avg(t.salary).desc())

Generated SQL：

select t_employee.department_id as t_employee_department_id, avg(t_employee.salary) 
from t_employee 
group by t_employee.department_id 
order by avg(t_employee.salary) desc

limit

The SQL standard doesn’t say how to implement paging queries, so different databases provide different implementations on that. For example, MySQL uses limit m, n syntax for pagination; PostgreSQL uses limit m offset n syntax; Oracle doesn’t even provide any keyword, we need to limit our pages in where clause by rownum.

To hide the paging syntax’s differences among databases, Ktorm provides a limit function to support pagination:

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

limit is also an extension function for Query class, it accepts two parameters of int:

  • offset: the offset to the first returned record, starts from 0.
  • limit: max record numbers returned by the query.

Here is an example, this query obtains the first employee in the table:

val query = database.from(Employees).select().limit(0, 1)

When we are using the limit function, Ktorm will generate appropriate SQLs depending on the currently enabled dialect. If we don’t use any dialects, an exception might be thrown:

org.ktorm.database.DialectFeatureNotSupportedException: Pagination is not supported in Standard SQL.

This is OK, the SQL standard doesn’t say how to implement paging queries, so Ktorm is not able to generate the SQL for us. To avoid this exception, do not use limit, or enable a dialect.

##union/unionAll

Ktorm also supports to merge two or more query results, that’s the union and unionAll functions. The union function is corresponding to the union keyword in SQL, removing duplicated rows; The unionAll function is corresponding to the union all keyword, not removing duplicated rows.

Here is an example:

val query = database
    .from(Employees)
    .select(Employees.id)
    .union(
        database.from(Departments).select(Departments.id)
    )
    .unionAll(
        database.from(Departments).select(Departments.id)
    )
    .orderBy(Employees.id.desc())

Generated SQL：

(
  select t_employee.id as t_employee_id 
  from t_employee
) union (
  select t_department.id as t_department_id 
  from t_department
) union all (
  select t_department.id as t_department_id 
  from t_department
) 
order by t_employee_id desc

aliased

Ktorm provided a feature of column aliases, which allows us to assign aliases to the selected columns of a query and use them in subsequent clauses such as group by and having, just like the as keyword in SQL.

Here is an example. This query selects departments whose average salary is greater than 100, then returns the average salaries along with their department’s IDs.

val deptId = Employees.departmentId.aliased("dept_id")
val salaryAvg = avg(Employees.salary).aliased("salary_avg")

database
    .from(Employees)
    .select(deptId, salaryAvg)
    .groupBy(deptId)
    .having { salaryAvg gt 100.0 }
    .forEach { row ->
        println("${row[deptId]}:${row[salaryAvg]}")
    }

Generated SQL:

select t_employee.department_id as dept_id, avg(t_employee.salary) as salary_avg 
from t_employee 
group by dept_id 
having salary_avg > ?