SELECTクエリ

検索のためのクエリ

概要

SELECTクエリはQueryDslのfrom、withまたはselectを呼び出して構築します。

次のクエリはADDRESSテーブルを全件取得するSQLに対応します。

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

from

FROM句を指定する場合はfromを呼び出します。 fromにはエンティティメタモデルを指定します。

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

fromにはエンティティメタモデルと共にサブクエリを指定できます。エンティティメタモデルはサブクエリの結果に対応していなければいけません。

val e = Meta.employee
val t = Meta.employeeRank

val subquery = QueryDsl.from(e).select(
    e.employeeId,
    e.employeeName,
    rank().over {
        partitionBy(e.departmentId)
        orderBy(e.salary.desc())
    }
)

val query = QueryDsl.from(t, subquery)
    .where {
        t.rank eq 1
    }
    .orderBy(t.employeeId)
/*
select t0_.employee_id, t0_.employee_name, t0_.rank from (select t1_.employee_id as employee_id, t1_.employee_name as employee_name, rank() over(partition by t1_.department_id order by t1_.salary desc) as rank from employee as t1_) as t0_ where t0_.rank = ? order by t0_.employee_id asc
*/

上述のMeta.employeeRankに対応するエンティティ定義は次の通りです。

@KomapperEntity
data class EmployeeRank(
    @KomapperId
    val employeeId: Int,
    val employeeName: String,
    val rank: Int,
)

with

WITH句を指定する場合はwithを呼び出します。 with関数には、エンティティメタモデルとサブクエリを指定します。エンティティメタモデルはサブクエリの結果に対応していなければいけません。

val e = Meta.employee
val t = Meta.employeeRank

val subquery = QueryDsl.from(e).select(
    e.employeeId,
    e.employeeName,
    rank().over {
        partitionBy(e.departmentId)
        orderBy(e.salary.desc())
    }
)

val query = QueryDsl.with(t, subquery)
        .from(e)
        .innerJoin(t) { e.employeeId eq t.employeeId }
        .where { t.rank eq 1 }
        .orderBy(e.departmentId)
        .select(e.departmentId, e.employeeId, e.employeeName)
/*
with employee_rank (employee_id, employee_name, rank) as (select t0_.employee_id, t0_.employee_name, rank() over(partition by t0_.department_id order by t0_.salary desc) from employee as t0_) select t0_.department_id, t0_.employee_id, t0_.employee_name from employee as t0_ inner join employee_rank as t1_ on (t0_.employee_id = t1_.employee_id) where t1_.rank = ? order by t0_.department_id asc
*/

上述のMeta.employeeRankに対応するエンティティ定義は次の通りです。

@KomapperEntity
data class EmployeeRank(
    @KomapperId
    val employeeId: Int,
    val employeeName: String,
    val rank: Int,
)

withRecursive

WITH RECURSIVE句を指定する場合はwithRecursiveを呼び出します。 withRecursive関数には、エンティティメタモデルとサブクエリを指定します。エンティティメタモデルはサブクエリの結果に対応していなければいけません。

val t = Meta.t
val subquery =
    QueryDsl.select(literal(1)).unionAll(
    QueryDsl.from(t).where { t.n less 10 }.select(t.n + 1),
)
val query = QueryDsl.withRecursive(t, subquery).from(t).select(sum(t.n))
/*
with recursive t (n) as ((select 1) union all (select (t0_.n + ?) from t as t0_ where t0_.n < ?)) select sum(t0_.n) from t as t0_
*/

上述のMeta.tに対応するエンティティ定義は次の通りです。

@KomapperEntity
data class T(
    @KomapperId(virtual = true)
    val n: Int,
)

where

WHERE句を指定する場合はwhereを呼び出します。

val query: Query<List<Address>> = QueryDsl.from(a).where { a.addressId eq 1 }
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID = ?
*/

innerJoin

INNER JOINを行う場合はinnerJoinを呼び出します。

val query: Query<List<Address>> = QueryDsl.from(a).innerJoin(e) { a.addressId eq e.addressId }
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ inner join EMPLOYEE as t1_ on (t0_.ADDRESS_ID = t1_.ADDRESS_ID)
*/

leftJoin

LEFT OUTER JOINを行う場合はleftJoinを呼び出します。

val query: Query<List<Address>> = QueryDsl.from(a).leftJoin(e) { a.addressId eq e.addressId }
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ left outer join EMPLOYEE as t1_ on (t0_.ADDRESS_ID = t1_.ADDRESS_ID)
*/

forUpdate

FOR UPDATE句を指定する場合はforUpdateを呼び出します。

val query: Query<List<Address>> = QueryDsl.from(a).where { a.addressId eq 1 }.forUpdate()
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID = ? for update
*/

forUpdateに渡すラムダ式の中で、nowait、 skipLocked、 waitなどの関数を呼び出しLockオプションを指定できます。

val query: Query<List<Address>> = QueryDsl.from(a).where { a.addressId eq 1 }.forUpdate { nowait() }
/*
select t0_.address_id, t0_.street, t0_.version from address as t0_ where t0_.address_id = ? for update nowait
*/

val query: Query<List<Address>> = QueryDsl.from(a).where { a.addressId eq 1 }.forUpdate { skipLocked() }
/*
select t0_.address_id, t0_.street, t0_.version from address as t0_ where t0_.address_id = ? for update skip locked
*/

val query: Query<List<Address>> = QueryDsl.from(a).where { a.addressId eq 1 }.forUpdate { wait(1) }
/*
select t0_.address_id, t0_.street, t0_.version from address as t0_ where t0_.address_id = ? for update wait 1
*/

Warning

利用しているダイアレクトがLockオプションをサポートしない場合、クエリの実行時にUnsupportedOperationExceptionがスローされます。

forUpdateに渡すラムダ式の中でof関数を使うことでLock対象のテーブルを指定できます。

val a = Meta.address
val e = Meta.employee
val address: Address = db.runQuery {
    QueryDsl.from(a)
        .innerJoin(e) { a.addressId eq e.addressId }
        .where { a.addressId eq 10 }
        .forUpdate {
            of(a)
            nowait()
        }
        .first()
}
/*
select t0_.address_id, t0_.street, t0_.version from address as t0_ inner join employee as t1_ on (t0_.address_id = t1_.address_id) where t0_.address_id = ? for update of t0_ nowait
*/

Warning

利用しているダイアレクトがLock対象のテーブル指定をサポートしない場合、クエリの実行時にUnsupportedOperationExceptionがスローされます。

orderBy

ORDER BY句を指定する場合はorderByを呼び出します。

val query: Query<List<Adress>> = QueryDsl.from(a).orderBy(a.addressId)
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ order by t0_.ADDRESS_ID asc
*/

デフォルトでは昇順ですが降順を指定する場合はカラムをorderByに渡す前にカラムに対してdescを呼び出します。また、昇順を表すascを明示的に呼び出すことやカラムを複数指定することもできます。

val query: Query<List<Adress>> = QueryDsl.from(a).orderBy(a.addressId.desc(), a.street.asc())
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ order by t0_.ADDRESS_ID desc, t0_.STREET asc
*/

NULLのソート順序を制御するために、カラムに対してascNullsFirst、ascNullsLast、descNullsFirst、descNullsLastを呼び出すこともできます。

val query: Query<List<Employee>> = QueryDsl.from(e).orderBy(e.managerId.ascNullsFirst())
/*
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_ order by t0_.MANAGER_ID asc nulls first
 */

offset, limit

指定した位置から一部の行を取り出すにはoffsetやlimitを呼び出します。

val query: Query<List<Adress>> = QueryDsl.from(a).orderBy(a.addressId).offset(10).limit(3)
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ order by t0_.ADDRESS_ID asc offset ? rows fetch first ? rows only
*/

distinct

DISTINCTキーワードを指定するにはdistinctを呼び出します。

val query: Query<List<Department>> = QueryDsl.from(d).distinct().innerJoin(e) { d.departmentId eq e.departmentId }
/*
select distinct t0_.DEPARTMENT_ID, t0_.DEPARTMENT_NO, t0_.DEPARTMENT_NAME, t0_.LOCATION, t0_.VERSION from DEPARTMENT as t0_ inner join EMPLOYEE as t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID)
*/

select

射影を行うにはselectを呼び出します。

1つのカラムを射影する例です。

val query: Query<List<String?>> = QueryDsl.from(a)
    .where {
        a.addressId inList listOf(1, 2)
    }
    .orderBy(a.addressId)
    .select(a.street)
/*
select t0_.STREET from ADDRESS as t0_ where t0_.ADDRESS_ID in (?, ?) order by t0_.ADDRESS_ID asc
*/

2つのカラムを射影する例です。

val query: Query<List<Pair<Int?, String?>>> = QueryDsl.from(a)
    .where {
        a.addressId inList listOf(1, 2)
    }
    .orderBy(a.addressId)
    .select(a.addressId, a.street)
/*
select t0_.ADDRESS_ID, t0_.STREET from ADDRESS as t0_ where t0_.ADDRESS_ID in (?, ?) order by t0_.ADDRESS_ID asc
*/

3つのカラムを射影する例です。

val query: Query<List<Triple<Int?, String?, Int?>>> = QueryDsl.from(a)
    .where {
        a.addressId inList listOf(1, 2)
    }
    .orderBy(a.addressId)
    .select(a.addressId, a.street, a.version)
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID in (?, ?) order by t0_.ADDRESS_ID asc
*/

4つ以上のカラムを射影する例です。

val query: Query<List<Record>> = QueryDsl.from(a)
    .where {
        a.addressId inList listOf(1, 2)
    }
    .orderBy(a.addressId)
    .select(a.addressId, a.street, a.version, concat(a.street, " test"))
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION, (concat(t0_.STREET, ?)) from ADDRESS as t0_ where t0_.ADDRESS_ID in (?, ?) order by t0_.ADDRESS_ID asc
*/

val list: List<Record> = db.runQuery { query }
for (record: Record in list) {
  println(record[a.addressId])
  println(record[a.street])
  println(record[a.version])
  println(record[concat(a.street, " test")])
}

4つ以上のカラムを射影した場合、結果の値はRecordに含まれます。クエリのselectに指定したカラムをkeyにして値を取得できます。

selectNotNull

NULLでないことが確実なカラムを射影するにはselectNotNullを呼び出せます。

1つのカラムを射影する例です。

val query: Query<List<String>> = QueryDsl.from(a)
    .where {
        a.addressId inList listOf(1, 2)
    }
    .orderBy(a.addressId)
    .selectNotNull(a.street)
/*
select t0_.STREET from ADDRESS as t0_ where t0_.ADDRESS_ID in (?, ?) order by t0_.ADDRESS_ID asc
*/

queryの型がQuery<List<String>>であることに注目してください。これは、queryを実行して得られる値の型が<List<String>>であることを表しています。

なお、このケースでselectNotNullの代わりにselectを使うと、実行して得られる型は<List<String?>>です。

selectAsRecord

4つ未満のカラムの射影で結果をRecordとして受け取りたい場合はselectの代わりにselectAsRecordを呼び出します。

val query: Query<List<Record> = QueryDsl.from(a)
    .where {
        a.addressId inList listOf(1, 2)
    }
    .orderBy(a.addressId)
    .selectAsRecord(a.street)
/*
select t0_.STREET from ADDRESS as t0_ where t0_.ADDRESS_ID in (?, ?) order by t0_.ADDRESS_ID asc
*/

selectAsEntity

結果を射影して任意のエンティティとして受け取りたい場合はselectAsEntityを呼び出します。第一引数にはエンティティのメタモデル、第二引数以降には射影するプロパティを指定します。プロパティの順番や型はエンティティクラスのコンストラクタに合わせなければいけません。

次の例ではEMPLOYEEテーブルを検索していますが、結果をAddressエンティティとして受け取っています。

val query: Query<List<Address> = QueryDsl.from(e)
    .selectAsEntity(a, e.addressId, e.employeeName, e.version)
/*
select t0_.ADDRESS_ID, t0_.EMPLOYEE_NAME, t0_.VERSION from EMPLOYEE as t0_
*/

結果として受け取りたいエンティティクラスに@KomapperProjectionを付与している場合、専用の拡張関数を使って以下のように簡潔に記述できます。

val e = Meta.employee

val query: Query<List<Address>> = QueryDsl.from(e)
    .selectAsAddress(
        version = e.version,
        addressId = e.addressId,
        street = e.employeeName,
    )

名前つき引数を使えばプロパティを指定する順番は自由です。

having

HAVING句を指定するにはhavingを呼び出します。

val query: Query<List<Pair<Int?, Long?>>> = QueryDsl.from(e)
    .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
*/

Note

groupByの呼び出しがない場合、GROUP BY句はselect関数に渡された引数から推測されて生成されます。

groupBy

GROUP BY句を指定するにはgroupByを呼び出します。

val query: Query<List<Pair<Int?, Long?>>> = 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
*/

union

UNION演算を行うにはクエリをunionで連携します。

val q1: Query<List<Pair<Int?, String?>>> = QueryDsl.from(e).where { e.employeeId eq 1 }
    .select(e.employeeId alias "ID", e.employeeName alias "NAME")
val q2: Query<List<Pair<Int?, String?>>> = QueryDsl.from(a).where { a.addressId eq 2 }
  .select(a.addressId alias "ID", a.street alias "NAME")
val q3: Query<List<Pair<Int?, String?>>> = QueryDsl.from(d).where { d.departmentId eq 3 }
  .select(d.departmentId alias "ID", d.departmentName alias "NAME")
val query: Query<List<Pair<Int?, String?>>> = (q1 union q2 union q3).orderBy("ID", desc("NAME"))
/*
(select t0_.EMPLOYEE_ID as "ID", t0_.EMPLOYEE_NAME as "NAME" from EMPLOYEE as t0_ where t0_.EMPLOYEE_ID = ?) union (select t1_.ADDRESS_ID as "ID", t1_.STREET as "NAME" from ADDRESS as t1_ where t1_.ADDRESS_ID = ?) union (select t2_.DEPARTMENT_ID as "ID", t2_.DEPARTMENT_NAME as "NAME" from DEPARTMENT as t2_ where t2_.DEPARTMENT_ID = ?) order by "ID" asc, "NAME" desc
*/

Note

unionに加え、unionAll、except、intersectといった関数をセット演算子として利用できます。ただし、ダイアレクトがサポートしていない場合、クエリを実行した時点でUnsupportedOperationExceptionがスローされます。

first

最初の1件を返却するクエリであることを示すには最後にfirstを呼び出します。

val query: Query<Address> = QueryDsl.from(a).where { a.addressId eq 1 }.first()
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID = ?
*/

firstOrNull

最初の1件もしくはnullを返却するクエリであることを示すには最後にfirstOrNullを呼び出します。

val query: Query<Address?> = QueryDsl.from(a).where { a.addressId eq 1 }.firstOrNull()
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID = ?
*/

firstOrNull関数は、クエリの結果が空の場合にnullを返します。

single

必ず1件を返却するクエリであることを示すには最後にsingleを呼び出します。

val query: Query<Address> = QueryDsl.from(a).where { a.addressId eq 1 }.single()
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID = ?
*/

singleOrNull

1件もしくはnullを返却するクエリであることを示すには最後にsingleOrNullを呼び出します。

val query: Query<Address?> = QueryDsl.from(a).where { a.addressId eq 1 }.singleOrNull()
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID = ?
*/

singleOrNull関数は、クエリの結果が空もしくは2件以上の行を持つ場合にnullを返します。

collect

結果セットをkotlinx.coroutines.flow.Flowとして処理するには最後にcollectを呼び出します。

val query: Query<Unit> = QueryDsl.from(a).collect { flow: Flow<Address> -> flow.collect { println(it) } }
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_
*/

Note

collectを使うと、結果セットを全てメモリに読み込んだ後に処理するのではなく結果セットを1件ずつ読み込みながら処理することになります。したがって、メモリの使用効率を向上させられます。

include

JOINしたテーブルのカラムをSELECT句に含める場合はincludeを呼び出します。

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

val query: Query<EntityStore> = QueryDsl.from(a)
  .innerJoin(e) {
    a.addressId eq e.addressId
  }.innerJoin(d) {
    e.departmentId eq d.departmentId
  }.include(e, d)
/*
select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION, t1_.EMPLOYEE_ID, t1_.EMPLOYEE_NO, t1_.EMPLOYEE_NAME, t1_.MANAGER_ID, t1_.HIREDATE, t1_.SALARY, t1_.DEPARTMENT_ID, t1_.ADDRESS_ID, t1_.VERSION, t2_.DEPARTMENT_ID, t2_.DEPARTMENT_NO, t2_.DEPARTMENT_NAME, t2_.LOCATION, t2_.VERSION from ADDRESS as t0_ inner join EMPLOYEE as t1_ on (t0_.ADDRESS_ID = t1_.ADDRESS_ID) inner join DEPARTMENT as t2_ on (t1_.DEPARTMENT_ID = t2_.DEPARTMENT_ID)
*/

このクエリを実行した場合の戻り値は、SQLの結果セットから生成された複数のエンティティを保持する org.komapper.core.dsl.query.EntityStoreインスタンスです。

EntityStoreからエンティティの一覧をSetとして取得したり、エンティティの関連をMapとして取得したりが可能です。上述のqueryを実行してEntityStoreから一覧や関連を取り出す例を以下に示します。

val store: EntityStore = db.runQuery { query }

val addresses: Set<Address> = store[a]
val employees: Set<Employee> = store[e]
val departments: Set<Department> = store[d]

val departmentEmployees: Map<Department, Set<Employee>> = store.oneToMany(d, e)
val employeeDepartment: Map<Employee, Department?> = store.oneToOne(e, d)
val employeeAddress: Map<Employee, Address?> = store.oneToOne(e, a)

関連を表すMapのキーをエンティティのIDに変換して取得することもできます。

val departmentIdEmployees: Map<Int, Set<Employee>> = store.oneToManyById(d, e)

EntityStoreからオブジェクトを取り出す手続きを簡易化するAssociation APIも参照ください。

includeAll

JOINしたテーブル全てのカラムをSELECT句に含めたい場合は、includeAllを呼び出します。

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

val query: Query<EntityStore> = QueryDsl.from(a)
  .innerJoin(e) {
    a.addressId eq e.addressId
  }.innerJoin(d) {
    e.departmentId eq d.departmentId
  }.includeAll()
/*
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, t1_.ADDRESS_ID, t1_.STREET, t1_.VERSION, t2_.DEPARTMENT_ID, t2_.DEPARTMENT_NO, t2_.DEPARTMENT_NAME, t2_.LOCATION, t2_.VERSION from EMPLOYEE as t0_ inner join ADDRESS as t1_ on (t0_.ADDRESS_ID = t1_.ADDRESS_ID) inner join DEPARTMENT as t2_ on (t0_.DEPARTMENT_ID = t2_.DEPARTMENT_ID)
*/

これは include で示した例と同等です。

options

クエリの挙動をカスタマイズするにはoptionsを呼び出します。ラムダ式のパラメータはデフォルトのオプションを表します。変更したいプロパティを指定してcopyメソッドを呼び出してください。

val query: Query<List<Address>> = QueryDsl.from(a).options {
    it.copy(
      fetchSize = 100,
      queryTimeoutSeconds = 5
    )
}

指定可能なオプションには以下のものがあります。

allowMissingWhereClause: 空のWHERE句を認めるかどうかです。デフォルトはtrueです。
escapeSequence: LIKE句に指定されるエスケープシーケンスです。デフォルトはnullでDialectの値を使うことを示します。
fetchSize: フェッチサイズです。デフォルトはnullでドライバの値を使うことを示します。
maxRows: 最大行数です。デフォルトはnullでドライバの値を使うことを示します。
queryTimeoutSeconds: クエリタイムアウトの秒数です。デフォルトはnullでドライバの値を使うことを示します。
suppressLogging: SQLのログ出力を抑制するかどうかです。デフォルトはfalseです。

executionOptions の同名プロパティよりもこちらに明示的に設定した値が優先的に利用されます。

最終更新 December 30, 2023: Add the `@KomapperProjection` annotation and the `komapper.enableEntityProjection` option (c63480e)