注意
このチートシートはKotlin向けのCriteria APIを対象としています。
Java版のチートシートについてはDoma入門 - Criteria API チートシートを参照ください。
前提
Domaのバージョンは2.43.0です。
利用しているKotlinのバージョンは1.4.10です。
エンティティクラスEmployeeとDepartmentが定義済みとします。
また、下記の変数が定義されているものとします。
val entityql = KEntityql(config)
val nativeSql = KNativeSql(config)
val e = Employee_()
val d = Department_()
例示されるSQLは実際に生成されるものと異なる場合があります。
KEntityqlとKNativeSqlの使い分けの原則
KEntityqlを使うとき
- 関連エンティティを取得したい
- 追加時に主キーを自動生成したい
- 更新や削除で楽観的排他制御をしたい
- 更新系処理でバッチ処理したい
- 更新系処理を
EntityListenerでフックしたい - 下記に述べるようなKNativeSqlを使う理由が特にない
KNativeSqlを使うとき
- Sequence検索したい
- 集約関数を使って集計したい（HAVINGやGROUP BYを使う必要がある）
- UNIONやUNION ALLをしたい
- 任意のカラムをタプルクラス（
Tuple2など）で取得したい - 別テーブルの検索結果を使って追加したい
- 任意の条件を指定して更新や削除をしたい
- 主キーのないテーブルを扱いたい
- 検索でエンティティを取得する際、主キーが重複するエンティティを許容したい
検索
全件検索
val list: List<Employee> = entityql.from(e).fetch()
// select * from employee t0_
1件検索
存在しなかったらnullを返す。
val employee: Employee = entityql.from(e).where { eq(e.id, 1) }.fetchOne()
// select * from employee t0_ where t0_.id = ?
Sequence検索
メモリを圧迫せずに大量データを1件づつ処理する。
val names: String = nativeSql.from(e).mapSequence { seq -> seq.map { it.name }.joinToString() }
// select * from employee t0_
射影
結果をタプルクラスとして返す。
val list: List<Tuple2<String, Int>> = nativeSql.from(e).select(e.name, e.age).fetch()
// select t0_.name, t0_.age from employee t0_
結果をエンティティクラスとして返す。主キーはSELECT句に必ず含まれエンティティにもセットされる。
val list: List<Employee> = 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 {
asc(e.name)
desc(e.age)
}
.fetch()
// select * from employee t0_ order by t0_.name asc, t0_.age desc
重複行の除外
val list: List<String> = nativeSql.from(e).distinct().select(e.name).fetch()
// select distinct t0_.name from employee t0_
Limit/Offset
val list: List<Employee> = entityql.from(e).limit(10).offset(3).fetch()
// select * from employee t0_ limit 10 offset 3
悲観的ロック
val list: List<Employee> = entityql.from(e).forUpdate().fetch()
// select * from employee t0_ for update
集約
集約関数としては、org.seasar.doma.jdbc.criteria.expression.KExpressionsに定義された、avg、count、countDistinct、max、min、sumが使える。
val sum: Int = nativeSql.from(e).select(KExpressions.sum(e.age)).fetchOne()
// select sum(t0_.age) from employee t0_
グループ単位の集計
val list: List<Tuple2<Int, Long>> = nativeSql.from(e).groupBy(e.departmentId).select(e.departmentId, KExpressions.count()).fetch()
// select t0_.department_id, count(*) from employee t0_ group by t0_.department_id
groupByメソッドを呼び出さない場合、selectメソッドに指定したプロパティからGROUP BY句に必要なカラムを推測し自動で付与する。したがって、次のコードは上記と同等のSQLを生成する。
val list: List<Tuple2<Int, Long>> = nativeSql.from(e).select(e.departmentId, KExpressions.count()).fetch()
// select t0_.department_id, count(*) from employee t0_ group by t0_.department_id
グループ単位の集計結果に対する絞り込み
// 従業員数が3人より多い部署について、部署ごとの従業員数を求める
val list: List<Tuple2<Long, String>> =
nativeSql
.from(e)
.innerJoin(d) { eq(e.departmentId, d.id) }
.having { gt(KExpressions.count(), 3L) }
.select(KExpressions.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
結合
内部結合
内部結合のみを行う。
val list: List<Employee> = entityql.from(e).innerJoin(d) { eq(e.departmentId, d.id) }.fetch()
// select t0_.* from employee t0_ inner join department t1_ on (t0_.department_id = t1_.id)
内部結合し関連エンティティも取得する。
val list: List<Employee> = entityql.from(e).innerJoin(d) { eq(e.departmentId, d.id) }.associate(e, d) { employee, department ->
employee.department = department
department.employees += employee
}
.fetch()
// select * from employee t0_ inner join department t1_ on (t0_.department_id = t1_.id)
外部結合
外部結合のみを行う。
val list: List<Employee> = entityql.from(e).leftJoin(d) { eq(e.departmentId, d.id) }.fetch()
// select t0_.* from employee t0_ left outer join department t1_ on (t0_.department_id = t1_.id)
外部結合し関連エンティティも取得する。
val list: List<Employee> = entityql.from(e). leftJoin(d) { eq(e.departmentId, d.id) }.associate(e, d) { employee, department ->
employee.department = department
department.employees += employee
}
.fetch()
// select * from employee t0_ left outer join department t1_ on (t0_.department_id = t1_.id)
自己結合
同じメタモデルの異なるインスタンスを使えば同じテーブル同士で結合（自己結合）できる。
val m = Employee_()
val list: List<Employee> = entityql.from(e).leftJoin(m) { eq(e.managerId, m.id) }.fetch()
// select t0_.* from employee t0_ left outer join employee t1_ on (t0_.manager_id = t1_.id)
関連エンティティの取得もできる。
val m = Employee_()
val list: List<Employee> = entityql.from(e).leftJoin(m) { eq(e.managerId, m.id) }.associate(e, m) { employee, manager ->
employee.manager = manager
}
.fetch()
// select * from employee t0_ left outer join employee t1_ on (t0_.manager_id = t1_.id)
UNION
val list: List<Tuple2<Int, String>> =
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から始まる。
val list: List<Tuple2<Int, String>> =
nativeSql
.from(e)
.select(e.id, e.name)
.union(nativeSql.from(d).select(d.id, d.name))
.orderBy { 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もできる。
val list: List<Tuple2<Int, String>> =
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件追加
val employee: Employee = ...
entityql.insert(e, employee).execute()
// insert into employee (id, name, age, version) values (?, ?, ?, ?)
バッチ追加
val employees: List<Employee> = ...
entityql.insert(e, employees).execute()
// insert into employee (id, name, age, version) values (?, ?, ?, ?)
検索結果を追加
同じデータ構造を持つ別テーブルに複数件を追加。
val da = Department_("DEPARTMENT_ARCHIVE")
nativeSql.insert(da).select { from(d).where { `in`(d.id, listOf(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件更新
val employee: Employee = ...
entityql.update(e, employee).execute()
// update employee set name = ?, age = ?, version = ? + 1 where id = ? and version = ?
バッチ更新
val employees: List<Employee> = ...
entityql.update(e, employees).execute()
// update employee set name = ?, age = ?, version = ? + 1 where id = ? and version = ?
特定条件に合致する複数件を更新
nativeSql
.update(e)
.set { value(e.departmentId, 3) }
.where {
eq(e.managerId, 3)
lt(e.age, 30)
}
.execute()
// update employee t0_ set department_id = ? where t0_.manager_id = ? and t0_.age < ?
SQL上の演算結果で更新
nativeSql
.update(e)
.set {
value(e.name, KExpressions.concat("[", KExpressions.concat(e.name, "]")))
value(e.age, KExpressions.add(e.age, 1))
}
.where { eq(e.id, 1) }
.execute()
// update employee t0_ set name = concat(?, concat(t0_.name, ?)), age = (t0_.age + ?) where t0_.id = ?
削除
1件削除
val employee: Employee = ...
entityql.delete(e, employee).execute()
// delete from employee where id = ? and version = ?
バッチ削除
val employees: List<Employee> = ...
entityql.delete(e, employees).execute()
// delete from employee where id = ? and version = ?
特定条件に合致する複数件を削除
nativeSql.delete(e).where { ge(e.age, 50) }.execute()
// delete from employee t0_ where t0_.age >= ?
WHERE句に指定できる検索条件
比較演算
=
entityql.from(e).where { eq(e.age, 20) }.fetch()
// select * from employee t0_ where t0_.age = ?
<>
entityql.from(e).where { ne(e.age, 20) }.fetch()
// select * from employee t0_ where t0_.age <> ?
>
entityql.from(e).where { gt(e.age, 20) }.fetch()
// select * from employee t0_ where t0_.age > ?
>=
entityql.from(e).where { ge(e.age, 20) }.fetch()
// select * from employee t0_ where t0_.age >= ?
<
entityql.from(e).where { lt(e.age, 20) }.fetch()
// select * from employee t0_ where t0_.age < ?
<=
entityql.from(e).where { le(e.age, 20) }.fetch()
// select * from employee t0_ where t0_.age <= ?
IS NULL
entityql.from(e).where { isNull(e.age) }.fetch()
// select * from employee t0_ where t0_.age is null
IS NOT NULL
entityql.from(e).where { isNotNull(e.age) }.fetch()
// select * from employee t0_ where t0_.age is not null
= または IS NULL
ageがnullでなければ = を生成。
entityql.from(e).where { eqOrIsNull(e.age, age) }.fetch()
// select * from employee t0_ where t0_.age = ?
ageがnullならば IS NULLを生成。
entityql.from(e).where { eqOrIsNull(e.age, age) }.fetch()
// select * from employee t0_ where t0_.age is null
<> または IS NOT NULL
ageがnullでなければ <> を生成。
entityql.from(e).where { neOrIsNotNull(e.age, age) }.fetch()
// select * from employee t0_ where t0_.age <> ?
ageがnullならば IS NOT NULLを生成。
entityql.from(e).where { neOrIsNotNull(e.age, age) }.fetch()
// select * from employee t0_ where t0_.age is not null
LIKE
何の加工もしないLIKE述語。
entityql.from(e).where { 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 { 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 { 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 { 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 { 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 { 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 { 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 { 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 { between(e.age, 20, 30) }.fetch()
// select * from employee t0_ where t0_.age between ? and ?
IN
シンプルなIN述語。
entityql.from(e).where { `in`(e.age, listOf(10, 20)) }.fetch()
// select * from employee t0_ where t0_.age in (?, ?)
タプルを使ったIN述語。
entityql.from(e).where { `in`(Tuple2(e.age, e.salary), listOf(Tuple2(10, 1000), Tuple2(20, 2000))) }.fetch()
// select * from employee t0_ where (t0_.age, t0_.salary) in ((?, ?), (?, ?))
サブクエリを使ったIN述語。
entityql.from(e).where{ `in`(e.departmentId, 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 { notIn(e.age, listOf(10, 20)) }.fetch()
// select * from employee t0_ where t0_.age not in (?, ?)
タプルを使ったNOT IN述語。
entityql.from(e).where { notIn(Tuple2(e.age, e.salary), listOf(Tuple2(10, 1000), Tuple2(20, 2000))) }.fetch()
// select * from employee t0_ where (t0_.age, t0_.salary) not in ((?, ?), (?, ?))
サブクエリを使ったNOT IN述語。
entityql.from(e).where { notIn(e.departmentId, 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 { exists(from(d).where { 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 {
eq(e.age, 20)
ge(e.salary, 100000)
lt(e.salary, 200000)
}
.fetch()
// select * from employee t0_ where t0_.age = ? and t0_.salary >= ? and t0_.salary < ?
OR
entityql.from(e).where {
eq(e.age, 20)
or {
ge(e.salary, 100000)
lt(e.salary, 200000)
}
}
.fetch()
// select * from employee t0_ where t0_.age = ? or (t0_.salary >= ? and t0_.salary < ?)
NOT
entityql.from(e).where {
eq(e.age, 20)
not {
ge(e.salary, 100000)
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.KExpressionsのliteraメソッドが受け入れる型のみをサポートしている。
val list: List<Employee> = entityql.from(e).where { eq(e.id, KExpressions.literal(10)) }.fetch()
// select * from employee t0_ where t0_.id = 10
算術演算
算術演算には、org.seasar.doma.jdbc.criteria.expression.KExpressionsに定義されたadd、sub、mul、div、modなどが使える。
val list: List<String> = nativeSql.from(e).select(KExpressions.add(e.age, 10)).fetch()
// select (t0_.age + ?) from employee t0_
文字列関数
文字列関数には、org.seasar.doma.jdbc.criteria.expression.KExpressionsに定義されたconcat、lower、upper、trim、ltrim、rtrimなどが使える。
val list: List<String> = nativeSql.from(e).select(KExpressions.lower(e.name)).fetch()
// select lower(t0_.name) from employee t0_
CASE式
val list: List<Tuple2<String, String>> =
nativeSql
.from(e)
.select(
e.name,
KExpressions.when({
lt(e.age, KExpressions.literal(10), KExpressions.literal("A"))
lt(e.age, KExpressions.literal(20), KExpressions.literal("B"))
lt(e.age, KExpressions.literal(30), KExpressions.literal("C"))
},
KExpressions.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_