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

## 注意

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

## 前提

[Doma](https://github.com/domaframework/doma)のバージョンは3.6.0です。
Criteria APIの概要については[Doma入門](https://qiita.com/nakamura-to/items/deaff05ba530317f3cf8)を参照してください。
利用しているJavaのバージョンは17です。

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

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

```java
QueryDsl queryDsl = new QueryDsl(config);

Employee_ e = new Employee_();
Department_ d = new Department_();
```

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

## 検索

### 全件検索

```java
List<Employee> list = queryDsl.from(e).fetch();
// select * from employee t0_
```

### 1件検索

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

```java
Employee employee = queryDsl.from(e).where(c -> c.eq(e.id, 1)).fetchOne();
// select * from employee t0_ where t0_.id = ?
```

存在しなかったら`Optional.empty()`を返す。

```java
Optional<Employee> employee = queryDsl.from(e).where(c -> c.eq(e.id, 1)).fetchOptional();
// select * from employee t0_ where t0_.id = ?
```

### Stream検索

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

```java
String names = queryDsl.from(e).mapStream(stream -> 
  stream.map(Employee::getName).collect(Collectors.joining(","))
);
// select * from employee t0_
```

`Stream` を戻り値にすることもできるが、その場合、 `close` が必要。

```java
String names;
try (Stream<Employee> stream = queryDsl.from(e).openStream()) {
    names = stream.map(Employee::getName).collect(Collectors.joining(","))
}
// select * from employee t0_
```

### Collect検索

Stream検索のショートカット。

```java
Map<Integer, List<Employee>> map = queryDsl.from(e).collect(Collectors.groupingBy(Employee::getDepartmentId));
// select * from employee t0_
```

上記は以下のコードと同等。

```java
Map<Integer, List<Employee>> map = queryDsl.from(e).mapStream(stream -> 
  stream.collect(Collectors.groupingBy(Employee::getDepartmentId))
);
// select * from employee t0_
```

### 射影

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

```java
List<Tuple2<String, Integer>> list = queryDsl.from(e).select(e.name, e.age).fetch();
// select t0_.name, t0_.age from employee t0_
```

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

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

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

### ソート

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

### 重複行の除外

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

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

### 悲観的ロック

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

### 集約

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

```java
Integer integer = queryDsl.from(e).select(Expressions.sum(e.age)).fetchOne();
// select sum(t0_.age) from employee t0_
```

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

```java
List<Tuple2<Integer, Long>> list = queryDsl.from(e).groupBy(e.departmentId).select(e.departmentId, Expressions.count()).fetch();
// select t0_.department_id, count(*) from employee t0_ group by t0_.department_id
```

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

```java
List<Tuple2<Integer, Long>> list = queryDsl.from(e).select(e.departmentId, Expressions.count()).fetch();
// select t0_.department_id, count(*) from employee t0_ group by t0_.department_id
```

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

```java
// 従業員数が3人より多い部署について、部署ごとの従業員数を求める
List<Tuple2<Long, String>> list =
  queryDsl
    .from(e)
    .innerJoin(d, on -> on.eq(e.departmentId, d.id))
    .having(c -> c.gt(Expressions.count(), 3L))
    .select(Expressions.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
```

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

内部結合のみを行う。

```java
List<Employee> list = queryDsl.from(e).innerJoin(d, on -> on.eq(e.departmentId, d.id)).fetch();
// select t0_.* from employee t0_ inner join department t1_ on (t0_.department_id = t1_.id)
```

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

```java
List<Employee> list = queryDsl.from(e).innerJoin(d, on -> on.eq(e.departmentId, d.id)).associate(e, d, (employee, department) -> {
  employee.setDepartment(department);
  department.getEmployees().add(employee);
}).fetch();
// select * from employee t0_ inner join department t1_ on (t0_.department_id = t1_.id)
```

#### 外部結合

外部結合のみを行う。

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

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

```java
List<Employee> list = queryDsl.from(e). leftJoin(d, on -> on.eq(e.departmentId, d.id)).associate(e, d, (employee, department) {
  employee.setDepartment(department);
  department.getEmployees().add(employee);
}).fetch();
// select * from employee t0_ left outer join department t1_ on (t0_.department_id = t1_.id)
```

#### 自己結合

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

```java
Employee_ m = new Employee_();

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

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

```java
Employee_ m = new Employee_();

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

### UNION

```java
List<Tuple2<Integer, String>> list =
  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から始まる。

```java
List<Tuple2<Integer, String>> list =
  queryDsl
    .from(e)
    .select(e.id, e.name)
    .union(queryDsl.from(d).select(d.id, d.name))
    .orderBy(c -> c.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もできる。

```java
List<Tuple2<Integer, String>> list =
  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件追加

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

### バッチ追加

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

### 複数行追加

```java
List<Employee> employees = ...;
queryDsl.insert(e).multi(employees).execute();
// insert into employee (id, name, age, version) values (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?)
```

### 追加または更新

```java
Employee employee = ...;
queryDsl.insert(e).single(employee).onDuplicateKeyUpdate().execute();
// insert into employee as target (id, name, age, version) values (?, ?, ?, ?) on conflict (id) do update set name = excluded.name, age = excluded.age, version = excluded.version
```

`onDuplicateKeyUpdate()` はバッチ追加や複数行追加でも利用可能。

### 追加または無視

```java
Employee employee = ...;
queryDsl.insert(e).single(employee).onDuplicateKeyIgnore().execute();
// insert into employee as target (id, name, age, version) values (?, ?, ?, ?) on conflict (id) do nothing
```

`onDuplicateKeyIgnore()` はバッチ追加や複数行追加でも利用可能。


### 検索結果を追加

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

```java
Department_ da = new Department_("DEPARTMENT_ARCHIVE");

queryDsl.insert(da).select(c -> c.from(d).where(cc -> cc.in(d.id, Arrays.asList(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件更新

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

### バッチ更新

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

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

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

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

```java
queryDsl
  .update(e)
  .set(c -> {
    c.value(e.name, Expressions.concat("[", Expressions.concat(e.name, "]")));
    c.value(e.age, Expressions.add(e.age, 1));
  })
  .where(c -> c.eq(e.id, 1))
  .execute();
// update employee t0_ set name = concat(?, concat(t0_.name, ?)), age = (t0_.age + ?) where t0_.id = ?
```

## 削除

### 1件削除

```java
Employee employee = ...;
queryDsl.delete(e).single(employee).execute();
// delete from employee where id = ? and version = ? 
```

### バッチ削除

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

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

```java
queryDsl.delete(e).where(c -> c.ge(e.age, 50)).execute();
// delete from employee t0_ where t0_.age >= ? 
```

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

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

```java
queryDsl.from(e).where(c -> c.eq(e.age, 20)).fetch();
// select * from employee t0_ where t0_.age = ?
```

#### <>

```java
queryDsl.from(e).where(c -> c.ne(e.age, 20)).fetch();
// select * from employee t0_ where t0_.age <> ?
```

#### >

```java
queryDsl.from(e).where(c -> c.gt(e.age, 20)).fetch();
// select * from employee t0_ where t0_.age > ?
```

#### >=

```java
queryDsl.from(e).where(c -> c.ge(e.age, 20)).fetch();
// select * from employee t0_ where t0_.age >= ?
```

#### <

```java
queryDsl.from(e).where(c -> c.lt(e.age, 20)).fetch();
// select * from employee t0_ where t0_.age < ?
```

#### <=

```java
queryDsl.from(e).where(c -> c.le(e.age, 20)).fetch();
// select * from employee t0_ where t0_.age <= ?
```

#### IS NULL

```java
queryDsl.from(e).where(c -> c.isNull(e.age)).fetch();
// select * from employee t0_ where t0_.age is null
```

#### IS NOT NULL

```java
queryDsl.from(e).where(c -> c.isNotNull(e.age)).fetch();
// select * from employee t0_ where t0_.age is not null
```

#### = または IS NULL

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

```java
queryDsl.from(e).where(c -> c.eqOrIsNull(e.age, age)).fetch();
// select * from employee t0_ where t0_.age = ?
```

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

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

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

```java
queryDsl.from(e).where(c -> c.neOrIsNotNull(e.age, age)).fetch();
// select * from employee t0_ where t0_.age <> ?
```

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

```java
queryDsl.from(e).where(c -> c.neOrIsNotNull(e.age, age)).fetch();
// select * from employee t0_ where t0_.age is not null
```

#### LIKE

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

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

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

```java
queryDsl.from(e).where(c -> c.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述語。ワイルドカードはエスケープされる。

```java
queryDsl.from(e).where(c -> c.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述語。ワイルドカードはエスケープされる。

```java
queryDsl.from(e).where(c -> c.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述語。

```java
queryDsl.from(e).where(c -> c.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述語。ワイルドカードはエスケープされる。

```java
queryDsl.from(e).where(c -> c.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述語。ワイルドカードはエスケープされる。

```java
queryDsl.from(e).where(c -> c.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述語。ワイルドカードはエスケープされる。

```java
queryDsl.from(e).where(c -> c.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

```java
queryDsl.from(e).where(c -> c.between(e.age, 20, 30)).fetch();
// select * from employee t0_ where t0_.age between ? and ?
```

#### IN

シンプルなIN述語。

```java
queryDsl.from(e).where(c -> c.in(e.age, Arrays.asList(10, 20))).fetch();
// select * from employee t0_ where t0_.age in (?, ?)
```

タプルを使ったIN述語。

```java
queryDsl.from(e).where(c -> c.in(new Tuple2(e.age, e.salary), Arrays.asList(new Tuple2(10, 1000), new Tuple2(20, 2000)))).fetch();
// select * from employee t0_ where (t0_.age, t0_.salary) in ((?, ?), (?, ?))
```

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

```java
queryDsl.from(e).where(c -> c.in(e.departmentId, c.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述語。

```java
queryDsl.from(e).where(c -> c.notIn(e.age, Arrays.asList(10, 20))).fetch();
// select * from employee t0_ where t0_.age not in (?, ?)
```

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

```java
queryDsl.from(e).where(c -> c.notIn(new Tuple2(e.age, e.salary), Arrays.asList(new Tuple2(10, 1000), new Tuple2(20, 2000)))).fetch();
// select * from employee t0_ where (t0_.age, t0_.salary) not in ((?, ?), (?, ?))
```

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

```java
queryDsl.from(e).where(c -> c.notIn(e.departmentId, c.from(d).select(d.id))).fetch();
// select * from employee t0_ where t0_.department_id not in (select t1_.id from department t1_)
```

#### EXISTS

```java
queryDsl.from(e).where(c -> c.exists(c.from(d).where(c2 -> c2.eq(e.departmentId, d.id))).fetch();
// select * from employee t0_ where exists (select * from department t1_ where t0_.deparment_id = t1_.id)
```

### 論理演算

#### AND

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

#### OR

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

#### NOT

```java
queryDsl.from(e).where(c -> {
  c.eq(e.age, 20);
  c.not(() -> {
    c.ge(e.salary, 100000);
    c.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.Expressions`の`litera`メソッドが受け入れる型のみをサポートしている。

```java
List<Employee> list = queryDsl.from(e).where(c -> c.eq(e.id, Expressions.literal(10))).fetch();
// select * from employee t0_ where t0_.id = 10
```

### 算術演算

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

```java
List<String> list = queryDsl.from(e).select(Expressions.add(e.age, 10)).fetch();
// select (t0_.age + ?) from employee t0_
```

### 文字列関数

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

```java
List<String> list = queryDsl.from(e).select(Expressions.lower(e.name)).fetch();
// select lower(t0_.name) from employee t0_
```

### CASE式

```java
List<Tuple2<String, String>> list =
  queryDsl
    .from(e)
    .select(
      e.name,
      Expressions.when(
        c -> {
          c.lt(e.age, Expressions.literal(10), Expressions.literal("A"));
          c.lt(e.age, Expressions.literal(20), Expressions.literal("B"));
          c.lt(e.age, Expressions.literal(30), Expressions.literal("C"));
      },
      Expressions.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_

```