クエリの式

クエリの構成要素を成す式

概要

本ページは以下の節から成ります。

宣言
比較演算子
論理演算子
算術演算子
数学関数
文字列関数
集約関数
ウィンドウ関数
条件式
スカラサブクエリ
リテラル
ユーザー定義の式

宣言

Query DSLでは、例えばwhere関数に検索条件を表すラムダ式を渡せます。

QueryDsl.from(a).where { a.addressId eq 1 }

KomapperではこのようなSQLの句に対応するようなラムダ式のことを宣言と呼びます。宣言は全てtypealiasとしてorg.komapper.core.dsl.expressionパッケージに定義されています。

Assignment宣言: VALUES句に相当するvalues関数やSET句に相当するset関数が受け取るラムダ式。typealiasはAssignmentDeclaration。
Having宣言: HAVING句に対応するhaving関数が受け取るラムダ式。typealiasはHavingDeclaration。
On宣言: ON句に対応するon関数が受け取るラムダ式。typealiasはOnDeclaration。
When宣言: WHEN句に対応するWhen関数が受け取るラムダ式。typealiasはWhenDeclaration。
Where宣言: WHERE句に対応するwhere関数が受け取るラムダ式。typealiasはWhereDeclaration。

これらの宣言は下記に示すように合成が可能です。

plus

+演算子を使うと、被演算子の宣言内部に持つ式を順番に実行するような新たな宣言を構築できます。

val w1: WhereDeclaration = {
    a.addressId eq 1
}
val w2: WhereDeclaration = {
    a.version eq 1
}
val w3: WhereDeclaration = w1 + w2 // +演算子の利用
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 = ?
*/

+演算子はすべての宣言で利用できます。

and

and関数を使うと、宣言をand演算子で連結する新たな宣言を構築できます。

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) // and関数の利用
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 = ?))
*/

and関数は、Having、When、Whereの宣言に対して適用できます。

or

or関数を使うと、宣言をor演算子で連結する新たな宣言を構築できます。

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) // or関数の利用
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 = ?)
*/

or関数は、Having、When、Whereの宣言に対して適用できます。

比較演算子

Having、On、When、Whereの宣言の中で利用できます。

演算子の引数にnullを渡した場合その演算子は評価されません。つまりSQLに変換されません。

val nullable: Int? = null
val query = QueryDsl.from(a).where { a.addressId eq nullable }

したがって、上記のqueryが実行された場合は次のSQLが発行されます。

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
*/

サブクエリも使えます。

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
*/

サブクエリも使えます。

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
*/

サブクエリも使えます。

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
*/

サブクエリも使えます。

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 ?)
*/

論理演算子

Having、On、When、Whereの宣言の中で利用できます。

and

宣言の中で式を並べるとAND演算子で連結されます。

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 < ?
*/

明示的にAND演算子を使いたい場合はand関数にラムダ式を渡します。

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

OR演算子で連結したい場合はor関数にラムダ式を渡します。

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

NOT演算子を使うにはnot関数にラムダ式を渡します。

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
*/

算術演算子

以下の演算子が使えます。

これらの演算子はorg.komapper.core.dsl.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 = ?
*/

数学関数

次の関数が使えます。

random

この関数はorg.komapper.core.dsl.operatorに定義されています。

random関数を使った例を次に示します。

QueryDsl.from(a).orderBy(random())
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ order by random() asc
*/

文字列関数

次の関数が使えます。

concat
substring
locate
lower
upper
trim
ltrim
rtrim

これらの関数はorg.komapper.core.dsl.operatorに定義されています。

concat関数を使った例を次に示します。

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 = ?
*/

集約関数

次の関数が使えます。

avg
count
sum
max
min

これらの関数はorg.komapper.core.dsl.operatorに定義されています。

呼び出して得られる式はhavingやselectで使われることを想定しています。

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

SQLのcount(*)に変換するにはcount関数を引数なしで呼び出します。

QueryDsl.from(a).select(count())
/*
select count(*) from ADDRESS as t0_
*/

count関数をカラムを指定して呼び出すこともできます。

QueryDsl.from(a).select(count(a.street))
/*
select count(t0_.STREET) from ADDRESS as t0_
*/

count distinct はcountDistinct関数で表現できます。

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_
*/

ウィンドウ関数

次の関数が使えます。

rowNumber
rank
denseRank
percentRank
cumeDist
ntile
lag
lead
firstValue
lastValue
nthValue

これらの関数はorg.komapper.core.dsl.operatorに定義されています。

rowRumber

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
*/

条件式

次の関数や式が使えます。

coalesce
case

これらはorg.komapper.core.dsl.operatorに定義されています。

coalesce

coalesce関数を使う例です。

QueryDsl.from(a).select(a.addressId, coalesce(a.street, literal("default")))
/*
select t0_.ADDRESS_ID, coalesce(t0_.STREET, 'default') from ADDRESS as t0_
*/

CASE式

CASE式を使うにはcaseを呼び出します。

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
*/

スカラサブクエリ

集約関数を使ってスカラを返すクエリはサブクエリとして他のクエリのselect関数に渡せます。

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
*/

リテラル

バインド変数を介さず直接値をリテラルとしてSQLに埋め込みたい場合はliteralまたはnullLiteralを呼び出します。

literal関数とnullLiteral関数はorg.komapper.core.dsl.operatorに定義されています。

literal関数がサポートする引数の型は以下のものです。

Boolean
Int
Long
String

使用例です。

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)
*/

nullリテラルを使うにはnullLiteral関数を呼び出します。

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)
*/

ユーザー定義の式

独自の比較演算子

独自の比較演算子は、org.komapper.core.dsl.operator.CriteriaContextをコンストラクタの引数に持つクラス内で定義してください。演算子に対応するSQLを生成する処理は、ラムダ関数としてCriteriaContextにaddします。

以下の例では、~演算子と!~演算子を定義します。

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

演算子を利用するには、WhereやHavingの宣言の中でextension関数を呼び出します。 extension関数の引数には上述のコンストラクタと演算子を呼び出すラムダ式を指定してください。

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
*/

独自のカラム式

独自のカラム式（例えば文字列関数など）は、org.komapper.core.dsl.expression.ColumnExpressionを返す関数として定義します。 ColumnExpressionは、org.komapper.core.dsl.operator.columnExpressionを呼び出すことで生成できます。 columnExpressionには、カラム式の型、カラム式を一意に特定するための情報、SQLを生成するラムダ式を渡します。

以下の例では、replace関数を定義します。

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(")")
    }
}

以下は、定義したreplace関数をクエリに組み込む例です。

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 = ?
*/

最終更新 March 8, 2024: Add the countDistinct function (c32c4ea)