はじめに
Doma 2.43.0のCriteria APIでSQLのサブクエリをどのように表現できるのかを示します。
DomaやCriteria APIの概要についてはDoma入門の他の記事もお読みください。
この記事で使うサンプルコード
ここでは、簡略化したコードで説明します。
完全なサンプルコードをについては下記のプロジェクトを参照してください。
データベースには部署を表すdepartmentテーブルと従業員を表すemployeeテーブルがあります。
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クラスを用意します。
@Entity(metamodel = @Metamodel)
public class Department {
@Id Integer id;
String name;
}
@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で書いた場合のコード例を示しますが、興味がある方はこちらのプロジェクトも参照ください。より完全な動くサンプルコードがあります。
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はラムダ式周りで少し改行が多めのフォーマットをするような印象があります。