Doma入門 - Kotlin Criteria API チートシート

## 注意

このチートシートは**Kotlin向け**のCriteria APIを対象としています。
Java版のチートシートについては[Doma入門 - Criteria API チートシート](https://qiita.com/nakamura-to/items/04def9dc3d218d50c71f)を参照ください。

## 前提
[Doma](https://github.com/domaframework/doma)のバージョンは3.6.0です。
利用しているKotlinのバージョンは2.1.20です。

エンティティクラス`Employee`と`Department`が定義済みとします。

また、下記の変数が定義されているものとします。

```kotlin
val queryDsl = KQueryDsl(config)

val e = Employee_()
val d = Department_()
```

例示されるSQLは実際に生成されるものと異なる場合があります。

## 検索

### 全件検索

```kotlin
val list: List<Employee> = queryDsl.from(e).fetch()
// select * from employee t0_
```

### 1件検索

存在しなかったらnullを返す。

```kotlin
val employee: Employee = queryDsl.from(e).where { eq(e.id, 1) }.fetchOne()
// select * from employee t0_ where t0_.id = ?
```

### Sequence検索

メモリを圧迫せずに大量データを1件づつ処理する。

```kotlin
val names: String = queryDsl.from(e).mapSequence { seq -> seq.map { it.name }.joinToString() }
// select * from employee t0_
```

### 射影

結果をタプルクラスとして返す。

```kotlin
val list: List<Tuple2<String, Int>> = queryDsl.from(e).select(e.name, e.age).fetch()
// select t0_.name, t0_.age from employee t0_
```

結果をエンティティクラスとして返すには`projectTo`または`selectTo`を使う。`projectTo`は結果から重複したエンティティIDを削除するが、`selectTo`は削除しない。主キーはSELECT句に必ず含まれエンティティにもセットされる。

```kotlin
val list: List<Employee> = queryDsl.from(e).projectTo(e, e.name, e.age).fetch()
// select t0_.id, t0_.name, t0_.age from employee t0_
```

```kotlin
val list: List<Employee> = queryDsl.from(e).selectTo(e, e.name, e.age).fetch()
// select t0_.id, t0_.name, t0_.age from employee t0_
```

### ソート

```kotlin
List<Employee> list = queryDsl.from(e).orderBy {
  asc(e.name)
  desc(e.age)
}
.fetch()
// select * from employee t0_ order by t0_.name asc, t0_.age desc
```

### 重複行の除外

```kotlin
val list: List<String> = queryDsl.from(e).distinct().select(e.name).fetch()
// select distinct t0_.name from employee t0_
```
### Limit/Offset

```kotlin
val list: List<Employee> = queryDsl.from(e).limit(10).offset(3).fetch()
// select * from employee t0_ limit 10 offset 3
```

### 悲観的ロック

```kotlin
val list: List<Employee> = queryDsl.from(e).forUpdate().fetch()
// select * from employee t0_ for update
```

### 集約

集約関数としては、`org.seasar.doma.jdbc.criteria.expression.KExpressions`に定義された、`avg`、`count`、`countDistinct`、`max`、`min`、`sum`が使える。

```kotlin
val sum: Int = queryDsl.from(e).select(KExpressions.sum(e.age)).fetchOne()
// select sum(t0_.age) from employee t0_
```

#### グループ単位の集計

```kotlin
val list: List<Tuple2<Int, Long>> = queryDsl.from(e).groupBy(e.departmentId).select(e.departmentId, KExpressions.count()).fetch()
// select t0_.department_id, count(*) from employee t0_ group by t0_.department_id
```

`groupBy`メソッドを呼び出さない場合、`select`メソッドに指定したプロパティからGROUP BY句に必要なカラムを推測し自動で付与する。したがって、次のコードは上記と同等のSQLを生成する。

```kotlin
val list: List<Tuple2<Int, Long>> = queryDsl.from(e).select(e.departmentId, KExpressions.count()).fetch()
// select t0_.department_id, count(*) from employee t0_ group by t0_.department_id
```

#### グループ単位の集計結果に対する絞り込み

```kotlin
// 従業員数が3人より多い部署について、部署ごとの従業員数を求める
val list: List<Tuple2<Long, String>> =
  queryDsl
    .from(e)
    .innerJoin(d) { eq(e.departmentId, d.id) }
    .having { gt(KExpressions.count(), 3L) }
    .select(KExpressions.count(), d.name)
    .fetch()
// select count(*), t1_.name from employee t0_ inner join department t1_ on (t0_.department_id = t1_.id) group by t1_.name having count(*) > 3
```

### 結合
#### 内部結合

内部結合のみを行う。

```kotlin
val list: List<Employee> = queryDsl.from(e).innerJoin(d) { eq(e.departmentId, d.id) }.fetch()
// select t0_.* from employee t0_ inner join department t1_ on (t0_.department_id = t1_.id)
```

内部結合し関連エンティティも取得する。

```kotlin
val list: List<Employee> = queryDsl.from(e).innerJoin(d) { eq(e.departmentId, d.id) }.associate(e, d) { employee, department ->
  employee.department = department
  department.employees += employee
}
.fetch()
// select * from employee t0_ inner join department t1_ on (t0_.department_id = t1_.id)
```

#### 外部結合

外部結合のみを行う。

```kotlin
val list: List<Employee> = queryDsl.from(e).leftJoin(d) { eq(e.departmentId, d.id) }.fetch()
// select t0_.* from employee t0_ left outer join department t1_ on (t0_.department_id = t1_.id)
```

外部結合し関連エンティティも取得する。

```kotlin
val list: List<Employee> = queryDsl.from(e). leftJoin(d) { eq(e.departmentId, d.id) }.associate(e, d) { employee, department ->
  employee.department = department
  department.employees += employee
}
.fetch()
// select * from employee t0_ left outer join department t1_ on (t0_.department_id = t1_.id)
```

#### 自己結合

同じメタモデルの異なるインスタンスを使えば同じテーブル同士で結合（自己結合）できる。

```kotlin
val m = Employee_()

val list: List<Employee> = queryDsl.from(e).leftJoin(m) { eq(e.managerId, m.id) }.fetch()
// select t0_.* from employee t0_ left outer join employee t1_ on (t0_.manager_id = t1_.id)
```

関連エンティティの取得もできる。

```kotlin
val m = Employee_()

val list: List<Employee> = queryDsl.from(e).leftJoin(m) { eq(e.managerId, m.id) }.associate(e, m) { employee, manager ->
  employee.manager = manager
}
.fetch()
// select * from employee t0_ left outer join employee t1_ on (t0_.manager_id = t1_.id)
```

### UNION

```kotlin
val list: List<Tuple2<Int, String>> =
  queryDsl
    .from(e)
    .select(e.id, e.name)
    .union(queryDsl.from(d).select(d.id, d.name))
    .fetch()
// select t0_.id, t0_.name from employee t0_ union select t0_.id, t0_.name from department t0_
```

ソートをするには対象のカラムをindexで指定する。indexは1から始まる。

```kotlin
val list: List<Tuple2<Int, String>> =
  queryDsl
    .from(e)
    .select(e.id, e.name)
    .union(queryDsl.from(d).select(d.id, d.name))
    .orderBy { asc(2) }
    .fetch()
// (select t0_.id, t0_.name from employee t0_) union (select t0_.id, t0_.name from department t0_) order by 2 asc
```

UNION ALLもできる。

```kotlin
val list: List<Tuple2<Int, String>> =
  queryDsl
    .from(e)
    .select(e.id, e.name)
    .unionAll(queryDsl.from(d).select(d.id, d.name))
    .fetch()
// select t0_.id, t0_.name from employee t0_ union all select t0_.id, t0_.name from department t0_
```

## 追加

### 1件追加

```kotlin
val employee: Employee = ...
queryDsl.insert(e).single(employee).execute()
// insert into employee (id, name, age, version) values (?, ?, ?, ?)
```

### バッチ追加

```kotlin
val employees: List<Employee> = ...
queryDsl.insert(e).batch(employees).execute()
// insert into employee (id, name, age, version) values (?, ?, ?, ?)
```

### 検索結果を追加

同じデータ構造を持つ別テーブルに複数件を追加。

```kotlin
val da = Department_("DEPARTMENT_ARCHIVE")

queryDsl.insert(da).select { from(d).where { `in`(d.id, listOf(1, 2))} }.execute()
// insert into department_archive (id, name, version) select t0_.id, t0_.name, t0_.version from department t0_ where t0_.id in (1, 2)
```

## 更新

### 1件更新

```kotlin
val employee: Employee = ...
queryDsl.update(e).single(employee).execute()
// update employee set name = ?, age = ?, version = ? + 1 where id = ? and version = ?
```

### バッチ更新

```kotlin
val employees: List<Employee> = ...
queryDsl.update(e).batch(employees).execute()
// update employee set name = ?, age = ?, version = ? + 1 where id = ? and version = ?
```

### 特定条件に合致する複数件を更新

```kotlin
queryDsl
  .update(e)
  .set { value(e.departmentId, 3) }
  .where {
    eq(e.managerId, 3)
    lt(e.age, 30)
  }
  .execute()
// update employee t0_ set department_id = ? where t0_.manager_id = ? and t0_.age < ?
```

### SQL上の演算結果で更新

```kotlin
queryDsl
  .update(e)
  .set {
    value(e.name, KExpressions.concat("[", KExpressions.concat(e.name, "]")))
    value(e.age, KExpressions.add(e.age, 1))
  }
  .where { eq(e.id, 1) }
  .execute()
// update employee t0_ set name = concat(?, concat(t0_.name, ?)), age = (t0_.age + ?) where t0_.id = ?
```

## 削除

### 1件削除

```kotlin
val employee: Employee = ...
queryDsl.delete(e).single(employee).execute()
// delete from employee where id = ? and version = ? 
```

### バッチ削除

```kotlin
val employees: List<Employee> = ...
queryDsl.delete(e).batch(employees).execute()
// delete from employee where id = ? and version = ? 
```

### 特定条件に合致する複数件を削除

```kotlin
queryDsl.delete(e).where { ge(e.age, 50) }.execute()
// delete from employee t0_ where t0_.age >= ? 
```

## WHERE句に指定できる検索条件

### 比較演算
#### =

```kotlin
queryDsl.from(e).where { eq(e.age, 20) }.fetch()
// select * from employee t0_ where t0_.age = ?
```

#### <>

```kotlin
queryDsl.from(e).where { ne(e.age, 20) }.fetch()
// select * from employee t0_ where t0_.age <> ?
```

#### >

```kotlin
queryDsl.from(e).where { gt(e.age, 20) }.fetch()
// select * from employee t0_ where t0_.age > ?
```

#### >=

```kotlin
queryDsl.from(e).where { ge(e.age, 20) }.fetch()
// select * from employee t0_ where t0_.age >= ?
```

#### <

```kotlin
queryDsl.from(e).where { lt(e.age, 20) }.fetch()
// select * from employee t0_ where t0_.age < ?
```

#### <=

```kotlin
queryDsl.from(e).where { le(e.age, 20) }.fetch()
// select * from employee t0_ where t0_.age <= ?
```

#### IS NULL

```kotlin
queryDsl.from(e).where { isNull(e.age) }.fetch()
// select * from employee t0_ where t0_.age is null
```

#### IS NOT NULL

```kotlin
queryDsl.from(e).where { isNotNull(e.age) }.fetch()
// select * from employee t0_ where t0_.age is not null
```

#### = または IS NULL

`age`がnullでなければ = を生成。

```kotlin
queryDsl.from(e).where { eqOrIsNull(e.age, age) }.fetch()
// select * from employee t0_ where t0_.age = ?
```

`age`がnullならば IS NULLを生成。

```kotlin
queryDsl.from(e).where { eqOrIsNull(e.age, age) }.fetch()
// select * from employee t0_ where t0_.age is null
```
#### <> または IS NOT NULL

`age`がnullでなければ <> を生成。

```kotlin
queryDsl.from(e).where { neOrIsNotNull(e.age, age) }.fetch()
// select * from employee t0_ where t0_.age <> ?
```

`age`がnullならば IS NOT NULLを生成。

```kotlin
queryDsl.from(e).where { neOrIsNotNull(e.age, age) }.fetch()
// select * from employee t0_ where t0_.age is not null
```

#### LIKE

何の加工もしないLIKE述語。

```kotlin
queryDsl.from(e).where { like(e.name, "A%") }.fetch()
// select * from employee t0_ where t0_.name like ?
// select * from employee t0_ where t0_.name like 'A%' (バインドされた値つきSQL)
```

前方一致のためのLIKE述語。ワイルドカードはエスケープされる。

```kotlin
queryDsl.from(e).where { like(e.name, "A%", LikeOption.prefix()) }.fetch()
// select * from employee t0_ where t0_.name like ? escape '$'
// select * from employee t0_ where t0_.name like 'A$%%' escape '$'　(バインドされた値つきSQL)
```

中間一致のためのLIKE述語。ワイルドカードはエスケープされる。

```kotlin
queryDsl.from(e).where { like(e.name, "A%", LikeOption.infix()) }.fetch()
// select * from employee t0_ where t0_.name like ? escape '$'
// select * from employee t0_ where t0_.name like '%A$%%' escape '$' (バインドされた値つきSQL)
```

後方一致のためのLIKE述語。ワイルドカードはエスケープされる。

```kotlin
queryDsl.from(e).where { like(e.name, "A%", LikeOption.suffix()) }.fetch()
// select * from employee t0_ where t0_.name like ? escape '$'
// select * from employee t0_ where t0_.name like '%A$%' escape '$' (バインドされた値つきSQL)
```

#### NOT LIKE

何の加工もしないNOT LIKE述語。

```kotlin
queryDsl.from(e).where { notLike(e.name, "A%") }.fetch()
// select * from employee t0_ where t0_.name not like ?
// select * from employee t0_ where t0_.name not like 'A%' (バインドされた値つきSQL)
```

前方一致のためのNOT LIKE述語。ワイルドカードはエスケープされる。

```kotlin
queryDsl.from(e).where { notLike(e.name, "A%", LikeOption.prefix()) }.fetch()
// select * from employee t0_ where t0_.name not like ? escape '$'
// select * from employee t0_ where t0_.name not like 'A$%%' escape '$' (バインドされた値つきSQL)
```

中間一致のためのNOT LIKE述語。ワイルドカードはエスケープされる。

```kotlin
queryDsl.from(e).where { notLike(e.name, "A%", LikeOption.infix()) }.fetch()
// select * from employee t0_ where t0_.name not like ? escape '$'
// select * from employee t0_ where t0_.name not like '%A$%%' escape '$' (バインドされた値つきSQL)
```

後方一致のためのNOT LIKE述語。ワイルドカードはエスケープされる。

```kotlin
queryDsl.from(e).where { notLike(e.name, "A%", LikeOption.suffix()) }.fetch()
// select * from employee t0_ where t0_.name not like ? escape '$'
// select * from employee t0_ where t0_.name not like '%A$%' escape '$' (バインドされた値つきSQL)
```

#### BETWEEN

```kotlin
queryDsl.from(e).where { between(e.age, 20, 30) }.fetch()
// select * from employee t0_ where t0_.age between ? and ?
```

#### IN

シンプルなIN述語。

```kotlin
queryDsl.from(e).where { `in`(e.age, listOf(10, 20)) }.fetch()
// select * from employee t0_ where t0_.age in (?, ?)
```

タプルを使ったIN述語。

```kotlin
queryDsl.from(e).where { `in`(Tuple2(e.age, e.salary), listOf(Tuple2(10, 1000), Tuple2(20, 2000))) }.fetch()
// select * from employee t0_ where (t0_.age, t0_.salary) in ((?, ?), (?, ?))
```

サブクエリを使ったIN述語。

```kotlin
queryDsl.from(e).where{ `in`(e.departmentId, from(d).select(d.id)) }.fetch()
// select * from employee t0_ where t0_.department_id in (select t1_.id from department t1_)
```

#### NOT IN

シンプルなNOT IN述語。

```kotlin
queryDsl.from(e).where { notIn(e.age, listOf(10, 20)) }.fetch()
// select * from employee t0_ where t0_.age not in (?, ?)
```

タプルを使ったNOT IN述語。

```kotlin
queryDsl.from(e).where { notIn(Tuple2(e.age, e.salary), listOf(Tuple2(10, 1000), Tuple2(20, 2000))) }.fetch()
// select * from employee t0_ where (t0_.age, t0_.salary) not in ((?, ?), (?, ?))
```

サブクエリを使ったNOT IN述語。

```kotlin
queryDsl.from(e).where { notIn(e.departmentId, from(d).select(d.id)) }.fetch()
// select * from employee t0_ where t0_.department_id not in (select t1_.id from department t1_)
```

#### EXISTS

```kotlin
queryDsl.from(e).where { exists(from(d).where { eq(e.departmentId, d.id) } }.fetch()
// select * from employee t0_ where exists (select * from department t1_ where t0_.deparment_id = t1_.id)
```

### 論理演算

#### AND

```kotlin
queryDsl.from(e).where {
  eq(e.age, 20)
  ge(e.salary, 100000)
  lt(e.salary, 200000)
}
.fetch()
// select * from employee t0_ where t0_.age = ? and t0_.salary >= ? and t0_.salary < ?
```

#### OR

```kotlin
queryDsl.from(e).where {
  eq(e.age, 20)
  or {
    ge(e.salary, 100000)
    lt(e.salary, 200000)
  }
}
.fetch()
// select * from employee t0_ where t0_.age = ? or (t0_.salary >= ? and t0_.salary < ?)
```

#### NOT

```kotlin
queryDsl.from(e).where {
  eq(e.age, 20)
  not {
    ge(e.salary, 100000)
    lt(e.salary, 200000)
  }
}
.fetch()
// select * from employee t0_ where t0_.age = ? and not (t0_.salary >= ? and t0_.salary < ?)
```

## カラムに関する式

### リテラル

バインド変数を使わず、そのまま値をSQLに埋め込む。
`org.seasar.doma.jdbc.criteria.expression.KExpressions`の`litera`メソッドが受け入れる型のみをサポートしている。

```kotlin
val list: List<Employee> = queryDsl.from(e).where { eq(e.id, KExpressions.literal(10)) }.fetch()
// select * from employee t0_ where t0_.id = 10
```

### 算術演算

算術演算には、`org.seasar.doma.jdbc.criteria.expression.KExpressions`に定義された`add`、`sub`、`mul`、`div`、`mod`などが使える。

```kotlin
val list: List<String> = queryDsl.from(e).select(KExpressions.add(e.age, 10)).fetch()
// select (t0_.age + ?) from employee t0_
```

### 文字列関数

文字列関数には、`org.seasar.doma.jdbc.criteria.expression.KExpressions`に定義された`concat`、`lower`、`upper`、`trim`、`ltrim`、`rtrim`などが使える。

```kotlin
val list: List<String> = queryDsl.from(e).select(KExpressions.lower(e.name)).fetch()
// select lower(t0_.name) from employee t0_
```

### CASE式

```kotlin
val list: List<Tuple2<String, String>> =
  queryDsl
    .from(e)
    .select(
      e.name,
      KExpressions.when({
        lt(e.age, KExpressions.literal(10), KExpressions.literal("A"))
        lt(e.age, KExpressions.literal(20), KExpressions.literal("B"))
        lt(e.age, KExpressions.literal(30), KExpressions.literal("C"))
      },
      KExpressions.literal("D")))
      .fetch()
// select t0_.name, case when t0_.age < 10 then 'A' when t0_.age < 20 then 'B' when t0_.age < 30 then 'C' else 'D' end from EMPLOYEE t0_

```