Query Introduction

Overview

Query construction and execution are separated. QueryDsl is responsible for constructing the query and Database is responsible for executing the query.

// construct a query
val query: Query<List<Address>> = QueryDsl.from(a)
// execute the query
val result: List<Address> = db.runQuery { query }

In the above example, a singleton QueryDSL is used, but QueryDSL can also be instantiated. Instantiation is useful when you want to share query options among queries generated from the same instance.

// instantiate a QueryDSL
val myDsl = QueryDsl(selectOptions = SelectOptions(queryTimeoutSeconds = 10))

// share the query options
val query1: Query<List<Address>> = myDsl.from(a)
val query2: Query<List<Employee>> = myDsl.from(e)

Prerequisites

The pages under Queries assume that the following entity definitions, mapping definitions, and variables exist.

data class Address(
    val addressId: Int,
    val street: String,
    val version: Int
)

data class Department(
  val departmentId: Int,
  val departmentNo: Int,
  val departmentName: String,
  val location: String,
  val version: Int,
)

data class Employee(
    val employeeId: Int,
    val employeeNo: Int,
    val employeeName: String,
    val managerId: Int?,
    val hiredate: LocalDate,
    val salary: BigDecimal,
    val departmentId: Int,
    val addressId: Int,
    val version: Int,
)

@KomapperEntityDef(Address::class)
data class AddressDef(
    @KomapperId @KomapperColumn(name = "ADDRESS_ID") val addressId: Nothing,
    @KomapperVersion val version: Nothing
)

@KomapperEntityDef(Department::class)
data class DepartmentDef(
  @KomapperId @KomapperColumn("DEPARTMENT_ID") val departmentId: Nothing,
  @KomapperColumn("DEPARTMENT_NO") val departmentNo: Nothing,
  @KomapperColumn("DEPARTMENT_NAME") val departmentName: Nothing,
  @KomapperVersion val version: Nothing,
)

@KomapperEntityDef(Employee::class)
data class EmployeeDef(
  @KomapperId @KomapperColumn("EMPLOYEE_ID") val employeeId: Nothing,
  @KomapperColumn("EMPLOYEE_NO") val employeeNo: Nothing,
  @KomapperColumn("EMPLOYEE_NAME") val employeeName: Nothing,
  @KomapperColumn("MANAGER_ID") val managerId: Nothing,
  @KomapperColumn("DEPARTMENT_ID") val departmentId: Nothing,
  @KomapperColumn("ADDRESS_ID") val addressId: Nothing,
  @KomapperVersion val version: Nothing,
)

val a = Meta.address
val d = Meta.department
val e = Meta.employee

Notes

In the example code of query construction, the Kotlin type is explicitly showed to make it easier to understand what kind of query is constructed, but it can actually be omitted.

Here is a typical example code:

val query: Query<List<Address>> = QueryDsl.from(a)
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_
*/

The SQL corresponding to the query is shown in the comment. Unless otherwise noted, the SQL is assumed to be generated by Dialect of the H2 Database Engine. Depending on the Dialect you use, different SQL may be generated.

Last modified July 13, 2024: Write QueryDSL as a single word (ef0459c)