SQL - Query

#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) }