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

はじめに

Doma 2.43.0のCriteria APIでSQLのサブクエリをどのように表現できるのかを示します。

DomaやCriteria APIの概要についてはDoma入門の他の記事もお読みください。

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

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

• https://github.com/domaframework/simple-examples/tree/master/dsl-style-java

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

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クラスを用意します。

Department.java

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

Employee.java

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

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

public class EmployeeRepository {

  private final Entityql entityql;

  public EmployeeRepository(Config config) {
    this.entityql = new Entityql(config);
  }
}

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

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

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

  public List<Employee> selectByDepartmentName_in(String departmentName) {
    Employee_ e = new Employee_();
    Department_ d = new Department_();
    return entityql
        .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を発行しています。）

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述語に複数カラムを指定する形式をサポートしている必要があります）。

    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インスタンスを使うことに注意してください。

  public List<Employee> selectByDepartmentName_exists(String departmentName) {
    Employee_ e = new Employee_();
    Department_ d = new Department_();
    return entityql
        .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は次のようなものになります。

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メソッドを呼び出します。

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

departmentNameパラメータが"SALES"の場合、生成される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'

Kotlinで書いた場合

サブクエリについて特に言えるのですが、少しコードが煩雑になってしまう傾向にあります。
その点が気になる方はKotlinで書くことを検討してみてもいいかもしれないです。（DomaはKotlin用のCriteria APIを提供しています）
上記と同等のメソッドをKotlinで書くとこうなり、全体的にさっぱりとする上に、ラムダ式で渡ってくるパラメータに関する注意点を気にしなくて良くなります。

下記にKotlinで書いた場合のコード例を示しますが、興味がある方はこちらのプロジェクトも参照ください。より完全な動くサンプルコードがあります。

• https://github.com/domaframework/simple-examples/tree/master/dsl-style-kotlin

IN述語を使った例

    fun selectByDepartmentName_in(departmentName: String): List<Employee> {
        val e = Employee_()
        val d = Department_()
        return entityql
            .from(e)
            .where {
                `in`(e.departmentId, from(d).where { eq(d.name, departmentName) }.select(d.id))
            }
            .fetch()
    }

EXISTS述語を使った例

    fun selectByDepartmentName_exists(departmentName: String): List<Employee> {
        val e = Employee_()
        val d = Department_()
        return entityql
            .from(e)
            .where {
                exists(
                    from(d).where {
                        eq(e.departmentId, d.id)
                        eq(d.name, departmentName)
                    }
                )
            }
            .fetch()
    }

JOIN句を使った例

    fun selectByDepartmentName_join(departmentName: String): List<Employee> {
        val e = Employee_()
        val d = Department_()
        return entityql
            .from(e)
            .innerJoin(d) { eq(e.departmentId, d.id) }
            .where {
                eq(d.name, departmentName)
            }
            .fetch()
    }

おわりに

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

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