注意
このチートシートはJava向けのCriteria APIを対象としています。
Kotlin版のチートシートについてはDoma入門 - Kotlin Criteria API チートシートを参照ください。
前提
Domaのバージョンは2.43.0です。
Criteria APIの概要についてはDoma入門を参照してください。
利用しているJavaのバージョンは8です。
エンティティクラスEmployeeとDepartmentが定義済みとします。
また、下記の変数が定義されているものとします。
Entityql entityql = new Entityql(config);
Nativesql nativeSql = new NativeSql(config);
Employee_ e = new Employee_();
Department_ d = new Department_();
例示されるSQLは実際に生成されるものと異なる場合があります。
EntityqlとNativeSqlの使い分けの原則
Entityqlを使うとき
- 関連エンティティを取得したい
- 追加時に主キーを自動生成したい
- 更新や削除で楽観的排他制御をしたい
- 更新系処理でバッチ処理したい
- 更新系処理を
EntityListenerでフックしたい - 下記に述べるようなNativeSqlを使う理由が特にない
NativeSqlを使うとき
- Stream検索したい
- Collect検索したい
- 集約関数を使って集計したい（HAVINGやGROUP BYを使う必要がある）
- UNIONやUNION ALLをしたい
- 任意のカラムをタプルクラス（
Tuple2など）で取得したい - 別テーブルの検索結果を使って追加したい
- 任意の条件を指定して更新や削除をしたい
- 主キーのないテーブルを扱いたい
- 検索でエンティティを取得する際、主キーが重複するエンティティを許容したい
検索
全件検索
List<Employee> list = entityql.from(e).fetch();
// select * from employee t0_
1件検索
存在しなかったらnullを返す。
Employee employee = entityql.from(e).where(c -> c.eq(e.id, 1)).fetchOne();
// select * from employee t0_ where t0_.id = ?
存在しなかったらOptional.empty()を返す。
Optional<Employee> employee = entityql.from(e).where(c -> c.eq(e.id, 1)).fetchOptional();
// select * from employee t0_ where t0_.id = ?
Stream検索
メモリを圧迫せずに大量データを1件づつ処理する。
String names = nativeSql.from(e).mapStream(stream ->
stream.map(Employee::getName).collect(Collectors.joining(","))
);
// select * from employee t0_
Collect検索
Stream検索のショートカット。
Map<Integer, List<Employee>> map = nativeSql.from(e).collect(Collectors.groupingBy(Employee::getDepartmentId));
// select * from employee t0_
上記は以下のコードと同等。
Map<Integer, List<Employee>> map = nativeSql.from(e).mapStream(stream ->
stream.collect(Collectors.groupingBy(Employee::getDepartmentId))
);
// select * from employee t0_
射影
結果をタプルクラスとして返す。
List<Tuple2<String, Integer>> list = nativeSql.from(e).select(e.name, e.age).fetch();
// select t0_.name, t0_.age from employee t0_
結果をエンティティクラスとして返す。主キーはSELECT句に必ず含まれエンティティにもセットされる。
List<Employee> list = entityql.from(e).selectTo(e, e.name, e.age).fetch();
// select t0_.id, t0_.name, t0_.age from employee t0_
ソート
List<Employee> list = entityql.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
重複行の除外
List<String> list = nativeSql.from(e).distinct().select(e.name).fetch();
// select distinct t0_.name from employee t0_
Limit/Offset
List<Employee> list = entityql.from(e).limit(10).offset(3).fetch();
// select * from employee t0_ limit 10 offset 3
悲観的ロック
List<Employee> list = entityql.from(e).forUpdate().fetch();
// select * from employee t0_ for update
集約
集約関数としては、org.seasar.doma.jdbc.criteria.expression.Expressionsに定義された、avg、count、countDistinct、max、min、sumが使える。
Integer integer = nativeSql.from(e).select(Expressions.sum(e.age)).fetchOne();
// select sum(t0_.age) from employee t0_
グループ単位の集計
List<Tuple2<Integer, Long>> list = nativeSql.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を生成する。
List<Tuple2<Integer, Long>> list = nativeSql.from(e).select(e.departmentId, Expressions.count()).fetch();
// select t0_.department_id, count(*) from employee t0_ group by t0_.department_id
グループ単位の集計結果に対する絞り込み
// 従業員数が3人より多い部署について、部署ごとの従業員数を求める
List<Tuple2<Long, String>> list =
nativeSql
.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
結合
内部結合
内部結合のみを行う。
List<Employee> list = entityql.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)
内部結合し関連エンティティも取得する。
List<Employee> list = entityql.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)
外部結合
外部結合のみを行う。
List<Employee> list = entityql.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)
外部結合し関連エンティティも取得する。
List<Employee> list = entityql.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)
自己結合
同じメタモデルの異なるインスタンスを使えば同じテーブル同士で結合（自己結合）できる。
Employee_ m = new Employee_();
List<Employee> list = entityql.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)
関連エンティティの取得もできる。
Employee_ m = new Employee_();
List<Employee> list = entityql.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
List<Tuple2<Integer, String>> list =
nativeSql
.from(e)
.select(e.id, e.name)
.union(nativeSql.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から始まる。
List<Tuple2<Integer, String>> list =
nativeSql
.from(e)
.select(e.id, e.name)
.union(nativeSql.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もできる。
List<Tuple2<Integer, String>> list =
nativeSql
.from(e)
.select(e.id, e.name)
.unionAll(nativeSql.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件追加
Employee employee = ...;
entityql.insert(e, employee).execute();
// insert into employee (id, name, age, version) values (?, ?, ?, ?)
バッチ追加
List<Employee> employees = ...;
entityql.insert(e, employees).execute();
// insert into employee (id, name, age, version) values (?, ?, ?, ?)
検索結果を追加
同じデータ構造を持つ別テーブルに複数件を追加。
Department_ da = new Department_("DEPARTMENT_ARCHIVE");
nativeSql.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件更新
Employee employee = ...;
entityql.update(e, employee).execute();
// update employee set name = ?, age = ?, version = ? + 1 where id = ? and version = ?
バッチ更新
List<Employee> employees = ...;
entityql.update(e, employees).execute();
// update employee set name = ?, age = ?, version = ? + 1 where id = ? and version = ?
特定条件に合致する複数件を更新
nativeSql
.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上の演算結果で更新
nativeSql
.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件削除
Employee employee = ...;
entityql.delete(e, employee).execute();
// delete from employee where id = ? and version = ?
バッチ削除
List<Employee> employees = ...;
entityql.delete(e, employees).execute();
// delete from employee where id = ? and version = ?
特定条件に合致する複数件を削除
nativeSql.delete(e).where(c -> c.ge(e.age, 50)).execute();
// delete from employee t0_ where t0_.age >= ?
WHERE句に指定できる検索条件
比較演算
=
entityql.from(e).where(c -> c.eq(e.age, 20)).fetch();
// select * from employee t0_ where t0_.age = ?
<>
entityql.from(e).where(c -> c.ne(e.age, 20)).fetch();
// select * from employee t0_ where t0_.age <> ?
>
entityql.from(e).where(c -> c.gt(e.age, 20)).fetch();
// select * from employee t0_ where t0_.age > ?
>=
entityql.from(e).where(c -> c.ge(e.age, 20)).fetch();
// select * from employee t0_ where t0_.age >= ?
<
entityql.from(e).where(c -> c.lt(e.age, 20)).fetch();
// select * from employee t0_ where t0_.age < ?
<=
entityql.from(e).where(c -> c.le(e.age, 20)).fetch();
// select * from employee t0_ where t0_.age <= ?
IS NULL
entityql.from(e).where(c -> c.isNull(e.age)).fetch();
// select * from employee t0_ where t0_.age is null
IS NOT NULL
entityql.from(e).where(c -> c.isNotNull(e.age)).fetch();
// select * from employee t0_ where t0_.age is not null
= または IS NULL
ageがnullでなければ = を生成。
entityql.from(e).where(c -> c.eqOrIsNull(e.age, age)).fetch();
// select * from employee t0_ where t0_.age = ?
ageがnullならば IS NULLを生成。
entityql.from(e).where(c -> c.eqOrIsNull(e.age, age)).fetch();
// select * from employee t0_ where t0_.age is null
<> または IS NOT NULL
ageがnullでなければ <> を生成。
entityql.from(e).where(c -> c.neOrIsNotNull(e.age, age)).fetch();
// select * from employee t0_ where t0_.age <> ?
ageがnullならば IS NOT NULLを生成。
entityql.from(e).where(c -> c.neOrIsNotNull(e.age, age)).fetch();
// select * from employee t0_ where t0_.age is not null
LIKE
何の加工もしないLIKE述語。
entityql.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述語。ワイルドカードはエスケープされる。
entityql.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述語。ワイルドカードはエスケープされる。
entityql.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述語。ワイルドカードはエスケープされる。
entityql.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述語。
entityql.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述語。ワイルドカードはエスケープされる。
entityql.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述語。ワイルドカードはエスケープされる。
entityql.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述語。ワイルドカードはエスケープされる。
entityql.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
entityql.from(e).where(c -> c.between(e.age, 20, 30)).fetch();
// select * from employee t0_ where t0_.age between ? and ?
IN
シンプルなIN述語。
entityql.from(e).where(c -> c.in(e.age, Arrays.asList(10, 20))).fetch();
// select * from employee t0_ where t0_.age in (?, ?)
タプルを使ったIN述語。
entityql.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述語。
entityql.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述語。
entityql.from(e).where(c -> c.notIn(e.age, Arrays.asList(10, 20))).fetch();
// select * from employee t0_ where t0_.age not in (?, ?)
タプルを使ったNOT IN述語。
entityql.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述語。
entityql.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
entityql.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
entityql.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
entityql.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
entityql.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メソッドが受け入れる型のみをサポートしている。
List<Employee> list = entityql.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などが使える。
List<String> list = nativeSql.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などが使える。
List<String> list = nativeSql.from(e).select(Expressions.lower(e.name)).fetch();
// select lower(t0_.name) from employee t0_
CASE式
List<Tuple2<String, String>> list =
nativeSql
.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_