Doma入門 - Criteria APIでサブクエリを使う

## はじめに

[Doma](https://github.com/domaframework/doma) 3.6.0のCriteria APIでSQLのサブクエリをどのように表現できるのかを示します。

DomaやCriteria APIの概要については[Doma入門](https://qiita.com/nakamura-to/items/deaff05ba530317f3cf8)の他の記事もお読みください。

## この記事で使うサンプルコード

ここでは、簡略化したコードで説明します。
完全なサンプルコードをについては下記のプロジェクトを参照してください。

- https://github.com/domaframework/simple-examples

データベースには部署を表すdepartmentテーブルと従業員を表すemployeeテーブルがあります。

```sql:schema.sql
create table department (
    id integer not null primary key,
    name varchar(255) not null);

create table employee (
    id integer not null primary key,
    name varchar(255) not null,
    department_id integer);
```

departmentテーブルに対応するDepartmentクラスとemployeeテーブルに対応するEmployeeクラスを用意します。

```java:Department.java
@Entity(metamodel = @Metamodel)
public class Department {
  @Id Integer id;
  String name;
}
```

```java:Employee.java
@Entity(metamodel = @Metamodel)
public class Employee {
  @Id Integer id;
  String name;
  @Column(name = "DEPARTMENT_ID")
  Integer departmentId;
}
```

`EmployeeRepository`を用意し、このクラスにいくつかメソッドを追加して例を示していきます。

```java
public class EmployeeRepository {

  private final QueryDsl queryDsl;

  public EmployeeRepository(Config config) {
    this.queryDsl = new QueryDsl(config);
  }
}
```

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

`in`メソッドを呼び出します。

サブクエリの`where`に渡すラムダ式では外側の`c`インスタンスではなくパラメータで渡ってくる`c2`インスタンスを使うことに注意してください。

```java
  public List<Employee> selectByDepartmentName_in(String departmentName) {
    Employee_ e = new Employee_();
    Department_ d = new Department_();
    return queryDsl
        .from(e)
        .where(
            c ->
                c.in(
                    e.departmentId,
                    c.from(d).where(c2 -> c2.eq(d.name, departmentName)).select(d.id)))
        .fetch();
  }
```

`departmentName`パラメータが"SALES"の場合、生成されるSQLは次のようなものになります。
（以降ではバインドされた値を埋め込んだ状態のSQLを示しますが、実際にはバインド変数の`?`を使ったSQLを発行しています。）

```sql
select
    t0_.id,
    t0_.name,
    t0_.DEPARTMENT_ID
from
    Employee t0_ 
where 
    t0_.DEPARTMENT_ID in (
        select 
            t1_.id
        from
            Department t1_
        where
            t1_.name = 'SALES'
    )
```

もしdepartmentテーブルが複合キーを持っていたらですが、次のように`Tuple2`を使うことでキーの組み合わせで検索することもできます（ただし、利用するデータベースがIN述語に複数カラムを指定する形式をサポートしている必要があります）。

```java
    c.in(
        new Tuple2<>(e.departmentId1, e.departmentId2),
        c.from(d).where(c2 -> c2.eq(d.name, departmentName)).select(d.id1, d.id2)))
```

NOT IN述語に相当する`notIn`メソッドも提供されています。

## EXISTS述語を使ったサブクエリ

`exists`メソッドを呼び出します。

注意点はIN述語を使う場合と同じです。サブクエリの`where`に渡すラムダ式では外側の`c`インスタンスではなくパラメータで渡ってくる`c2`インスタンスを使うことに注意してください。


```java
  public List<Employee> selectByDepartmentName_exists(String departmentName) {
    Employee_ e = new Employee_();
    Department_ d = new Department_();
    return queryDsl
        .from(e)
        .where(
            c ->
                c.exists(
                    c.from(d)
                        .where(
                            c2 -> {
                              c2.eq(e.departmentId, d.id);
                              c2.eq(d.name, departmentName);
                            })))
        .fetch();
  }
```

`departmentName`パラメータが"SALES"の場合、生成されるSQLは次のようなものになります。

```sql
select
    t0_.id,
    t0_.name,
    t0_.DEPARTMENT_ID
from
    Employee t0_
where
    exists (
        select
            t1_.id,
            t1_.name
        from
            Department t1_
        where
            t0_.DEPARTMENT_ID = t1_.id 
            and
            t1_.name = 'SALES'
    )
```

NOT EXISTS述語に相当する`notExists`メソッドも提供されています。

## 同等の検索結果をJOIN句を使って実現

サブクエリではありませんが、同等の検索結果を得るクエリは結合を使っても表現できます。IN述語やEXISTS述語を使うときに必要な注意点（ラムダ式のパラメータ）を気にする必要がないので、結果が同じになるのであればこちらの方法をお勧めします。
この例では`innerJoin`メソッドを呼び出します。

```java
  public List<Employee> selectByDepartmentName_join(String departmentName) {
    Employee_ e = new Employee_();
    Department_ d = new Department_();
    return queryDsl
        .from(e)
        .innerJoin(d, on -> on.eq(e.departmentId, d.id))
        .where(c -> c.eq(d.name, departmentName))
        .fetch();
  }
```

`departmentName`パラメータが"SALES"の場合、生成されるSQLは次のようなものになります。

```sql
select
    t0_.id,
    t0_.name,
    t0_.DEPARTMENT_ID 
from
    Employee t0_
inner join
    Department t1_
on 
    (t0_.DEPARTMENT_ID = t1_.id)
where
    t1_.name = 'SALES'
```

## おわりに

Doma 3.6.0のCriteria APIでサブクエリを表現する方法を示しました。

この記事ではJavaのコードはgoogle-java-format、Kotlinのコードはktlintでフォーマットしていますが、コードの可読性を上げるために少しフォーマットの仕方をカスタマイズしても良いかもしれません。個人的にはgoogle-java-formatはラムダ式周りで少し改行が多めのフォーマットをするような印象があります。