Version v1.17 of the documentation is no longer actively maintained. The site that you are currently viewing is an archived snapshot. For up-to-date documentation, see the latest version.
Query Expressions
Overview
This page covers the components of an expression, including declarations, operators, and functions.
- Declarations
- Comparison operators
- Logical operators
- Arithmetic operators
- Mathematical functions
- String functions
- Aggregate functions
- Window functions
- Conditional expressions
- Scalar subqueries
- literals
- User-defined expressions
Declarations
In the Query DSL, for example, you can pass a lambda expression representing
the search criteria to the where
function.
QueryDsl.from(a).where { a.addressId eq 1 }
We call such lambda expressions declarations.
All declarations are defined as typealias in the org.komapper.core.dsl.expression
package.
- AssignmentDeclaration
- Used with the
values
andset
functions. - HavingDeclaration
- Used with the
having
function. - OnDeclaration
- Used with the
on
function. - WhenDeclaration
- Used with the
When
function. - WhereDeclaration
- Used with the
where
function.
These declarations are composable.
plus
The +
operator constructs a new declaration that executes its operands in sequence:
val w1: WhereDeclaration = {
a.addressId eq 1
}
val w2: WhereDeclaration = {
a.version eq 1
}
val w3: WhereDeclaration = w1 + w2 // Use of the `+` operator
val query: Query<List<Address>> = QueryDsl.from(a).where(w3)
val list: List<Address> = db.runQuery { query }
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID = ? and t0_.VERSION = ?
*/
The +
operator is available in all declarations.
and
The and
function constructs a new declaration that concatenates its receiver and argument with the AND predicate:
val w1: WhereDeclaration = {
a.addressId eq 1
}
val w2: WhereDeclaration = {
a.version eq 1
or { a.version eq 2 }
}
val w3: WhereDeclaration = w1.and(w2) // Use of the `and` function
val query: Query<List<Address>> = QueryDsl.from(a).where(w3)
val list: List<Address> = db.runQuery { query }
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID = ? and (t0_.VERSION = ? or (t0_.VERSION = ?))
*/
The and
function can be applied to Having, When, and Where declarations.
or
The or
function constructs a new declaration that concatenates its receiver and argument with the OR predicate:
val w1: WhereDeclaration = {
a.addressId eq 1
}
val w2: WhereDeclaration = {
a.version eq 1
a.street eq "STREET 1"
}
val w3: WhereDeclaration = w1.or(w2) // Use of the `or` function
val query: Query<List<Address>> = QueryDsl.from(a).where(w3)
val list: List<Address> = db.runQuery { query }
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID = ? or (t0_.VERSION = ? and t0_.STREET = ?)
*/
The or
function can be applied to Having, When, and Where declarations.
Comparison operators
Comparison operators are available in the Having, On, When, and Where Declarations.
If null
is passed as an argument to a comparison operator, the operator is not evaluated.
That is, the corresponding SQL will not be generated:
val nullable: Int? = null
val query = QueryDsl.from(a).where { a.addressId eq nullable }
Thus, when the above query
is executed, the following SQL will be issued:
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_
eq
QueryDsl.from(a).where { a.addressId eq 1 }
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID = ?
*/
notEq
QueryDsl.from(a).where { a.addressId notEq 1 }
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID <> ?
*/
less
QueryDsl.from(a).where { a.addressId less 1 }
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID < ?
*/
lessEq
QueryDsl.from(a).where { a.addressId lessEq 1 }
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID <= ?
*/
greater
QueryDsl.from(a).where { a.addressId greater 1 }
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID > ?
*/
greaterEq
QueryDsl.from(a).where { a.addressId greaterEq 1 }
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID >= ?
*/
isNull
QueryDsl.from(e).where { e.managerId.isNull() }
/*
select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE, t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION from EMPLOYEE as t0_ where t0_.MANAGER_ID is null
*/
isNotNull
QueryDsl.from(e).where { e.managerId.isNotNull() }
/*
select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE, t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION from EMPLOYEE as t0_ where t0_.MANAGER_ID is not null
*/
like
QueryDsl.from(a).where { a.street like "STREET 1_" }.orderBy(a.addressId)
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.STREET like ? escape ? order by t0_.ADDRESS_ID asc
*/
notLike
QueryDsl.from(a).where { a.street notLike "STREET 1_" }.orderBy(a.addressId)
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.STREET not like ? escape ? order by t0_.ADDRESS_ID asc
*/
startsWith
QueryDsl.from(a).where { a.street startsWith "STREET 1" }.orderBy(a.addressId)
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.STREET like ? escape ? order by t0_.ADDRESS_ID asc
*/
notStartsWith
QueryDsl.from(a).where { a.street notStartsWith "STREET 1" }.orderBy(a.addressId)
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.STREET not like ? escape ? order by t0_.ADDRESS_ID asc
*/
contains
QueryDsl.from(a).where { a.street contains "T 1" }.orderBy(a.addressId)
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.STREET like ? escape ? order by t0_.ADDRESS_ID asc
*/
notContains
QueryDsl.from(a).where { a.street notContains "T 1" }.orderBy(a.addressId)
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.STREET not like ? escape ? order by t0_.ADDRESS_ID asc
*/
endsWith
QueryDsl.from(a).where { a.street endsWith "1" }.orderBy(a.addressId)
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.STREET like ? escape ? order by t0_.ADDRESS_ID asc
*/
notEndsWith
QueryDsl.from(a).where { a.street notEndsWith "1" }.orderBy(a.addressId)
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.STREET not like ? escape ? order by t0_.ADDRESS_ID asc
*/
between
QueryDsl.from(a).where { a.addressId between 5..10 }.orderBy(a.addressId)
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID between ? and ? order by t0_.ADDRESS_ID asc
*/
notBetween
QueryDsl.from(a).where { a.addressId notBetween 5..10 }.orderBy(a.addressId)
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID not between ? and ? order by t0_.ADDRESS_ID asc
*/
inList
QueryDsl.from(a).where { a.addressId inList listOf(9, 10) }.orderBy(a.addressId.desc())
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID in (?, ?) order by t0_.ADDRESS_ID desc
*/
The inList
operator also accepts a subquery.
QueryDsl.from(e).where {
e.addressId inList {
QueryDsl.from(a)
.where {
e.addressId eq a.addressId
e.employeeName like "%S%"
}.select(a.addressId)
}
}
/*
select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE, t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION from EMPLOYEE as t0_ where t0_.ADDRESS_ID in (select t1_.ADDRESS_ID from ADDRESS as t1_ where t0_.ADDRESS_ID = t1_.ADDRESS_ID and t0_.EMPLOYEE_NAME like ? escape ?)
*/
notInList
QueryDsl.from(a).where { a.addressId notInList (1..9).toList() }.orderBy(a.addressId)
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID not in (?, ?, ?, ?, ?, ?, ?, ?, ?) order by t0_.ADDRESS_ID asc
*/
The notInList
operator also accepts a subquery.
QueryDsl.from(e).where {
e.addressId notInList {
QueryDsl.from(a).where {
e.addressId eq a.addressId
e.employeeName like "%S%"
}.select(a.addressId)
}
}
/*
select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE, t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION from EMPLOYEE as t0_ where t0_.ADDRESS_ID not in (select t1_.ADDRESS_ID from ADDRESS as t1_ where t0_.ADDRESS_ID = t1_.ADDRESS_ID and t0_.EMPLOYEE_NAME like ? escape ?)
*/
inList2
QueryDsl.from(a).where { a.addressId to a.version inList2 listOf(9 to 1, 10 to 1) }.orderBy(a.addressId.desc())
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where (t0_.ADDRESS_ID, t0_.VERSION) in ((?, ?), (?, ?)) order by t0_.ADDRESS_ID desc
*/
The inList2
operator also accepts a subquery.
QueryDsl.from(e).where {
e.addressId to e.version inList2 {
QueryDsl.from(a)
.where {
e.addressId eq a.addressId
e.employeeName like "%S%"
}.select(a.addressId, a.version)
}
}
/*
select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE, t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION from EMPLOYEE as t0_ where (t0_.ADDRESS_ID, t0_.VERSION) in (select t1_.ADDRESS_ID, t1_.VERSION from ADDRESS as t1_ where t0_.ADDRESS_ID = t1_.ADDRESS_ID and t0_.EMPLOYEE_NAME like ? escape ?)
*/
notInList2
QueryDsl.from(a).where { a.addressId to a.version notInList2 listOf(9 to 1, 10 to 1) }.orderBy(a.addressId)
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where (t0_.ADDRESS_ID, t0_.VERSION) not in ((?, ?), (?, ?)) order by t0_.ADDRESS_ID asc
*/
The notInList2
operator also accepts a subquery.
QueryDsl.from(e).where {
e.addressId to e.version notInList2 {
QueryDsl.from(a).where {
e.addressId eq a.addressId
e.employeeName like "%S%"
}.select(a.addressId, a.version)
}
}
/*
select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE, t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION from EMPLOYEE as t0_ where (t0_.ADDRESS_ID, t0_.VERSION) not in (select t1_.ADDRESS_ID, t1_.VERSION from ADDRESS as t1_ where t0_.ADDRESS_ID = t1_.ADDRESS_ID and t0_.EMPLOYEE_NAME like ? escape ?)
*/
exists
QueryDsl.from(e).where {
exists {
QueryDsl.from(a).where {
e.addressId eq a.addressId
e.employeeName like "%S%"
}
}
}
/*
select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE, t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION from EMPLOYEE as t0_ where exists (select t1_.ADDRESS_ID, t1_.STREET, t1_.VERSION from ADDRESS as t1_ where t0_.ADDRESS_ID = t1_.ADDRESS_ID and t0_.EMPLOYEE_NAME like ? escape ?)
*/
notExists
QueryDsl.from(e).where {
notExists {
QueryDsl.from(a).where {
e.addressId eq a.addressId
e.employeeName like "%S%"
}
}
}
/*
select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE, t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION from EMPLOYEE as t0_ where not exists (select t1_.ADDRESS_ID, t1_.STREET, t1_.VERSION from ADDRESS as t1_ where t0_.ADDRESS_ID = t1_.ADDRESS_ID and t0_.EMPLOYEE_NAME like ? escape ?)
*/
Logical operators
Logical operators are available in the Having, On, When, and Where Declarations.
and
Expressions in the declaration are implicitly concatenated using the AND operator.
QueryDsl.from(a).where {
a.addressId greater 1
a.street startsWith "S"
a.version less 100
}
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID > ? and t0_.STREET like ? escape ? and t0_.VERSION < ?
*/
To explicitly concatenate expression using the AND operator, pass a lambda expression to the and
function.
QueryDsl.from(a).where {
a.addressId greater 1
and {
a.street startsWith "S"
a.version less 100
}
}
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID > ? and (t0_.STREET like ? escape ? and t0_.VERSION < ?)
*/
or
To concatenate expressions using the OR operator, pass a lambda expression to the or
function.
QueryDsl.from(a).where {
a.addressId greater 1
or {
a.street startsWith "S"
a.version less 100
}
}
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID > ? or (t0_.STREET like ? escape ? and t0_.VERSION < ?)
*/
not
To use the NOT operator, pass a lambda expression to the not
function.
QueryDsl.from(a).where {
a.addressId greater 5
not {
a.addressId greaterEq 10
}
}.orderBy(a.addressId)
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID > ? and not (t0_.ADDRESS_ID >= ?) order by t0_.ADDRESS_ID asc
*/
Arithmetic operators
The following operators are available as arithmetic operators:
+
-
*
/
%
These operators are defined in org.komapper.core.dsl.operator
.
The following is an example of using the +
operator:
QueryDsl.update(a).set {
a.version eq (a.version + 10)
}.where {
a.addressId eq 1
}
/*
update ADDRESS as t0_ set VERSION = (t0_.VERSION + ?) where t0_.ADDRESS_ID = ?
*/
Mathematical functions
The following function is available:
- random
This function is defined in org.komapper.core.dsl.operator
.
The following is an example of using the random
function:
QueryDsl.from(a).orderBy(random())
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ order by random() asc
*/
String functions
The following functions are available as string functions:
- concat
- substring
- locate
- lower
- upper
- trim
- ltrim
- rtrim
These functions are defined in org.komapper.core.dsl.operator
.
The following is an example of using the concat
function:
QueryDsl.update(a).set {
a.street eq (concat(concat("[", a.street), "]"))
}.where {
a.addressId eq 1
}
/*
update ADDRESS as t0_ set STREET = (concat((concat(?, t0_.STREET)), ?)) where t0_.ADDRESS_ID = ?
*/
Aggregate functions
The following functions are available as aggregate functions:
- avg
- count
- sum
- max
- min
These functions are defined in org.komapper.core.dsl.operator
.
The expression obtained by the aggregate function call is intended to be used with the having
or select
function:
QueryDsl.from(e)
.groupBy(e.departmentId)
.having {
count(e.employeeId) greaterEq 4L
}
.orderBy(e.departmentId)
.select(e.departmentId, count(e.employeeId))
/*
select t0_.DEPARTMENT_ID, count(t0_.EMPLOYEE_ID) from EMPLOYEE as t0_ group by t0_.DEPARTMENT_ID having count(t0_.EMPLOYEE_ID) >= ? order by t0_.DEPARTMENT_ID asc
*/
avg
QueryDsl.from(a).select(avg(a.addressId))
/*
select avg(t0_.ADDRESS_ID) from ADDRESS as t0_
*/
count
To generate a SQL count(*)
, call the count
function with no arguments:
QueryDsl.from(a).select(count())
/*
select count(*) from ADDRESS as t0_
*/
It is possible to pass a metamodel property to the count
function:
QueryDsl.from(a).select(count(a.street))
/*
select count(t0_.STREET) from ADDRESS as t0_
*/
You can represent “count distinct” with the countDistinct
function:
QueryDsl.from(a).select(countDistinct(a.street))
/*
select count(distinct t0_.STREET) from ADDRESS as t0_
*/
sum
QueryDsl.from(a).select(sum(a.addressId))
/*
select sum(t0_.ADDRESS_ID) from ADDRESS as t0_
*/
max
QueryDsl.from(a).select(max(a.addressId))
/*
select max(t0_.ADDRESS_ID) from ADDRESS as t0_
*/
min
QueryDsl.from(a).select(min(a.addressId))
/*
select min(t0_.ADDRESS_ID) from ADDRESS as t0_
*/
Window Functions
The following functions are available:
- rowNumber
- rank
- denseRank
- percentRank
- cumeDist
- ntile
- lag
- lead
- firstValue
- lastValue
- nthValue
These functions are defined in org.komapper.core.dsl.operator
.
rowNumber
QueryDsl.from(e)
.orderBy(e.departmentId)
.selectNotNull(e.departmentId, rowNumber().over { orderBy(e.departmentId) })
/*
select t0_.department_id, row_number() over(order by t0_.department_id asc) from employee as t0_ order by t0_.department_id asc
*/
rank
QueryDsl.from(e)
.orderBy(e.departmentId)
.selectNotNull(e.departmentId, rank().over { orderBy(e.departmentId) })
/*
select t0_.department_id, rank() over(order by t0_.department_id asc) from employee as t0_ order by t0_.department_id asc
*/
denseRank
QueryDsl.from(e)
.orderBy(e.departmentId)
.selectNotNull(e.departmentId, denseRank().over { orderBy(e.departmentId) })
/*
select t0_.department_id, dense_rank() over(order by t0_.department_id asc) from employee as t0_ order by t0_.department_id asc
*/
percentRank
QueryDsl.from(e)
.orderBy(e.departmentId)
.selectNotNull(e.departmentId, percentRank().over { orderBy(e.departmentId) })
/*
select t0_.department_id, percent_rank() over(order by t0_.department_id asc) from employee as t0_ order by t0_.department_id asc
*/
cumeDist
QueryDsl.from(e)
.orderBy(e.departmentId)
.selectNotNull(e.departmentId, cumeDist().over { orderBy(e.departmentId) })
/*
select t0_.department_id, cume_dist() over(order by t0_.department_id asc) from employee as t0_ order by t0_.department_id asc
*/
ntile
QueryDsl.from(e)
.orderBy(e.departmentId)
.selectNotNull(e.departmentId, ntile(5).over { orderBy(e.departmentId) })
/*
select t0_.department_id, ntile(5) over(order by t0_.department_id asc) from employee as t0_ order by t0_.department_id asc
*/
lag
val c1 = d.departmentId
val c2 = lag(d.departmentId).over { orderBy(d.departmentId) }
val c3 = lag(d.departmentId, 2).over { orderBy(d.departmentId) }
val c4 = lag(d.departmentId, 2, literal(-1)).over { orderBy(d.departmentId) }
QueryDsl.from(d)
.orderBy(d.departmentId)
.select(c1, c2, c3, c4)
/*
select t0_.department_id, lag(t0_.department_id) over(order by t0_.department_id asc), lag(t0_.department_id, 2) over(order by t0_.department_id asc), lag(t0_.department_id, 2, -1) over(order by t0_.department_id asc) from department as t0_ order by t0_.department_id asc
*/
lead
val d = Meta.department
val c1 = d.departmentId
val c2 = lead(d.departmentId).over { orderBy(d.departmentId) }
val c3 = lead(d.departmentId, 2).over { orderBy(d.departmentId) }
val c4 = lead(d.departmentId, 2, literal(-1)).over { orderBy(d.departmentId) }
QueryDsl.from(d)
.orderBy(d.departmentId)
.select(c1, c2, c3, c4)
/*
select t0_.department_id, lead(t0_.department_id) over(order by t0_.department_id asc), lead(t0_.department_id, 2) over(order by t0_.department_id asc), lead(t0_.department_id, 2, -1) over(order by t0_.department_id asc) from department as t0_ order by t0_.department_id asc
*/
firstValue
val d = Meta.department
val c1 = d.departmentId
val c2 = firstValue(d.departmentId).over { orderBy(d.departmentId) }
val c3 = firstValue(d.departmentId).over {
orderBy(d.departmentId)
rows(preceding(1))
}
QueryDsl.from(d)
.orderBy(d.departmentId)
.select(c1, c2, c3)
/*
select t0_.department_id, first_value(t0_.department_id) over(order by t0_.department_id asc), first_value(t0_.department_id) over(order by t0_.department_id asc rows 1 preceding) from department as t0_ order by t0_.department_id asc
*/
lastValue
val d = Meta.department
val c1 = d.departmentId
val c2 = lastValue(d.departmentId).over {
orderBy(d.departmentId)
rows(unboundedPreceding, unboundedFollowing)
}
val c3 = lastValue(d.departmentId).over {
orderBy(d.departmentId)
rows(currentRow, following(1))
}
QueryDsl.from(d)
.orderBy(d.departmentId)
.select(c1, c2, c3)
/*
select t0_.department_id, last_value(t0_.department_id) over(order by t0_.department_id asc rows between unbounded preceding and unbounded following), last_value(t0_.department_id) over(order by t0_.department_id asc rows between current row and 1 following) from department as t0_ order by t0_.department_id asc
*/
nthValue
val d = Meta.department
val c1 = d.departmentId
val c2 = nthValue(d.departmentId, 2).over {
orderBy(d.departmentId)
}
val c3 = nthValue(d.departmentId, 2).over {
orderBy(d.departmentId)
rows(preceding(2))
}
QueryDsl.from(d)
.orderBy(d.departmentId)
.select(c1, c2, c3)
/*
select t0_.department_id, nth_value(t0_.department_id, 2) over(order by t0_.department_id asc), nth_value(t0_.department_id, 2) over(order by t0_.department_id asc rows 2 preceding) from department as t0_ order by t0_.department_id asc
*/
Conditional expression
The following functions and expressions are available as conditional expressions:
- coalesce
- case
These are defined in org.komapper.core.dsl.operator
.
coalesce
The following is an example of using the coalesce
function:
QueryDsl.from(a).select(a.addressId, coalesce(a.street, literal("default")))
/*
select t0_.ADDRESS_ID, coalesce(t0_.STREET, 'default') from ADDRESS as t0_
*/
CASE expressions
To use a CASE expression, call the case
function:
val caseExpression = case(
When(
{
a.street eq "STREET 2"
a.addressId greater 1
},
literal("HIT")
)
) { literal("NO HIT") }
val list: List<Pair<String?, String?>> = db.runQuery {
QueryDsl.from(a).where { a.addressId inList listOf(1, 2, 3) }
.orderBy(a.addressId)
.select(a.street, caseExpression)
}
/*
select t0_.street, case when t0_.street = ? and t0_.address_id > ? then 'HIT' else 'NO HIT' end from address as t0_ where t0_.address_id in (?, ?, ?) order by t0_.address_id asc
*/
Scalar subqueries
A query that returns a scalar using an aggregate function is a scalar subquery.
The scalar subquery can be passed to the select
function of another query:
val subquery = QueryDsl.from(e).where { d.departmentId eq e.departmentId }.select(count())
val query = QueryDsl.from(d)
.orderBy(d.departmentId)
.select(d.departmentName, subquery)
/*
select t0_.department_name, (select count(*) from employee as t1_ where t0_.department_id = t1_.department_id) from department as t0_ order by t0_.department_id asc
*/
Literals
To embed a value directly into SQL as a literal without binding variable, call the literal
function or nullLiteral
function.
These functions are defined in org.komapper.core.dsl.operator
.
The literal
function supports the following argument types:
- Boolean
- Int
- Long
- String
Here is an example of literal function usage:
QueryDsl.insert(a).values {
a.addressId eq 100
a.street eq literal("STREET 100")
a.version eq literal(100)
}
/*
insert into ADDRESS (ADDRESS_ID, STREET, VERSION) values (?, 'STREET 100', 100)
*/
To use a null literal, call the nullLiteral
function:
QueryDsl.insert(a).values {
a.addressId eq 100
a.street eq nullLiteral(String::class)
a.version eq nullLiteral(Long::class)
}
/*
insert into ADDRESS (ADDRESS_ID, STREET, VERSION) values (?, null, null)
*/
User-defined expressions
Custom comparison operators
Define custom comparison operators within a class that takes
org.komapper.core.dsl.operator.CriteriaContext
as a constructor argument.
The logic to generate SQL corresponding to the operator is added to CriteriaContext as a lambda function.
In the example below, the ~
and !~
operators are defined:
class MyExtension(private val context: CriteriaContext) {
infix fun <T : Any> ColumnExpression<T, String>.`~`(pattern: T?) {
if (pattern == null) return
val o1 = Operand.Column(this)
val o2 = Operand.Argument(this, pattern)
context.add {
visit(o1)
append(" ~ ")
visit(o2)
}
}
infix fun <T : Any> ColumnExpression<T, String>.`!~`(pattern: T?) {
if (pattern == null) return
val o1 = Operand.Column(this)
val o2 = Operand.Argument(this, pattern)
context.add {
visit(o1)
append(" !~ ")
visit(o2)
}
}
}
To use the operator, call the extension
function within declarations like Where or Having.
Specify the above constructor and a lambda expression to invoke the operator as arguments to the extension
function.
You can use the ~
and !~
operators in your query as follows:
QueryDsl.from(e).where {
e.salary greaterEq BigDecimal(1000)
extension(::MyExtension) {
e.employeeName `~` "S"
e.employeeName `!~` "T"
}
}.orderBy(e.employeeName)
/*
select
t0_.EMPLOYEE_ID,
t0_.EMPLOYEE_NO,
t0_.EMPLOYEE_NAME,
t0_.MANAGER_ID,
t0_.HIREDATE,
t0_.SALARY,
t0_.DEPARTMENT_ID,
t0_.ADDRESS_ID,
t0_.VERSION
from
EMPLOYEE as t0_
where
t0_.SALARY >= ?
t0_.EMPLOYEE_NAME ~ ?
t0_.EMPLOYEE_NAME !~ ?
order by
t0_.EMPLOYEE_NAME
*/
Custom column expressions
Define custom column expressions as Kotlin functions that return
org.komapper.core.dsl.expression.ColumnExpression
.
You can create ColumnExpression
by calling org.komapper.core.dsl.operator.columnExpression
.
Pass the type of the column expression, information to uniquely identify the column expression,
and a lambda expression to generate SQL to columnExpression
.
In the example below, the replace
function is defined:
private fun <T: Any> replace(
expression: ColumnExpression<T, String>,
from: T,
to: T,
): ColumnExpression<T, String> {
val name = "replace"
val o1 = Operand.Column(expression)
val o2 = Operand.Argument(expression, from)
val o3 = Operand.Argument(expression, to)
return columnExpression(expression, name, listOf(o1, o2, o3)) {
append("$name(")
visit(o1)
append(", ")
visit(o2)
append(", ")
visit(o3)
append(")")
}
}
You can use the replace
function in your query as follows:
QueryDsl.from(a)
.where { a.addressId eq 1 }
.select(replace(a.street, "STREET", "St.")).first()
/*
select replace(t0_.STREET, ?, ?) from ADDRESS as t0_ where t0_.ADDRESS_ID = ?
*/