Criteria API¶
Contents
Warning
The criteria API is an experimental feature.
Introduction¶
We provide our criteria API as a Kotlin library. You have to use the API from your kotlin program, but you can define your Entity classes both in Java and Kotlin.
We use the following Entity classes to show you some examples:
@Entity
public class Emp {
@Id public Integer employeeId;
public String employeeName;
public Integer managerId;
@Transient public Emp manager;
public Integer departmentId;
@Transient public Dept department;
@Version public Integer version;
}
@Entity
public class Dept {
@Id public Integer departmentId;
public String departmentName;
@Version public Integer version;
@Transient public List<Emp> employeeList = new ArrayList<>();
}
Note that the above classes are written in Java.
There are two kind of DSLs in the Criteria API:
- The Entityql DSL
- The Sql DSL
Both requires predefined Entity classes and the meta code generated by Doma’s annotation processors.
Build with Gradle¶
To use the Criteria API, write your build.gradle as follows:
plugins {
id "org.seasar.doma.compile" version "1.0.0"
}
kapt {
arguments {
arg("doma.criteria.enabled", true)
}
}
dependencies {
kapt "org.seasar.doma:doma-processor:2.31.0"
implementation "org.seasar.doma:doma-criteria:2.31.0"
}
Check points:
- Use the Doma Compile Plugin
- Use the kapt extension and make the
doma.criteria.enabled
option enabled - Depends on the the doma-processor and the doma-criteria artifact
Entityql DSL¶
The Entityql DSL can query and associate entities.
Write your query in a entityql
block as follows:
val query = entityql {
// write your query here
}
For example, to query Emp
entities, write as follows:
val query = entityql {
from(::Emp_) { e ->
where {
e.managerId ge 3
e.managerId le 6
}
}
}
The ::Emp_
is the reference to the meta class of the Emp
entity class.
The above query issues the following SQL statement:
select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.DEPARTMENT_ID, t0_.VERSION
from EMP t0_
where t0_.MANAGER_ID >= ? and t0_.MANAGER_ID <= ?
Entityql DSL Examples¶
or operator¶
val query = entityql {
from(::Emp_) { e ->
where {
e.managerId eq 1
or {
e.managerId eq 6
}
}
}
}
The above query issues the following SQL statement:
select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.DEPARTMENT_ID, t0_.VERSION
from EMP t0_
where t0_.MANAGER_ID = ? or (t0_.MANAGER_ID = ?)
in predicate¶
val query = entityql {
from(::Emp_) { e ->
where {
e.managerId `in` (listOf(1, 6))
}
}
}
The above query issues the following SQL statement:
select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.DEPARTMENT_ID, t0_.VERSION
from EMP t0_
where t0_.MANAGER_ID in (?, ?)
in predicate with subquery¶
val query = entityql {
from(::Emp_) { e ->
where {
e.managerId `in` {
from(::Emp_) { e2 ->
select(e2.employeeId)
}
}
}
}
}
The above query issues the following SQL statement:
select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.DEPARTMENT_ID, t0_.VERSION
from EMP t0_
where t0_.MANAGER_ID in (select t1_.EMPLOYEE_ID from EMP t1_)
join clause¶
You can use the following functions to issue JOIN:
- innerJoin
- leftJoin
left join¶
val query = entityql {
from(::Emp_) { e ->
val d = leftJoin(::Dept_) { d ->
e.departmentId eq d.departmentId
}
where {
d.departmentName eq "RESEARCH"
}
}
}
The above query issues the following SQL statement:
select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.DEPARTMENT_ID, t0_.VERSION
from EMP t0_ left outer join DEPT t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID)
where t1_.DEPARTMENT_NAME = ?
left join and association¶
When you invoke the innerJoin
or the leftJoin
function,
you can associate entities with the associate
function:
val query = entityql {
from(::Emp_) { e ->
val d = leftJoin(::Dept_) { d ->
e.departmentId eq d.departmentId
}
where {
e.employeeName eq "SMITH"
}
associate(e, d) { employee, department ->
employee.department = department
department.employeeList.add(employee)
}
}
}
The above query issues the following SQL statement:
select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.DEPARTMENT_ID, t0_.VERSION,
t1_.DEPARTMENT_ID, t1_.DEPARTMENT_NAME, t1_.VERSION
from EMP t0_left outer join DEPT t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID)
where t0_.EMPLOYEE_NAME = ?
self-join and association¶
We also support self-join as follows:
val query = entityql {
from(::Emp_) { e ->
val m = leftJoin(::Emp_) { m ->
e.managerId eq m.employeeId
}
associate(e, m) { employee, manager ->
employee.manager = manager
}
}
}
The above query issues the following SQL statement:
select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.DEPARTMENT_ID, t0_.VERSION,
t1_.EMPLOYEE_ID, t1_.EMPLOYEE_NAME, t1_.MANAGER_ID, t1_.DEPARTMENT_ID, t1_.VERSION
from EMP t0_ left outer join EMP t1_ on (t0_.MANAGER_ID = t1_.EMPLOYEE_ID)
limit and offset¶
val query = entityql {
from(::Emp_) { e ->
orderBy {
e.employeeId.asc()
}
limit(5)
offset(3)
}
}
The above query issues the following SQL statement:
select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.DEPARTMENT_ID, t0_.VERSION
from EMP t0_ order by t0_.EMPLOYEE_ID asc limit 5 offset 3
Sql DSL¶
The Sql DSL can issue more complex SQL statements rather than the Entityql DSL, but can’t associate entities.
Write your query in a sql
block as follows:
val query = sql {
// write your query here
}
For example, to query one column, write as follows:
val query = sql {
from(::Emp_) { e ->
where {
e.employeeId eq 1
}
select(e.employeeName)
}
}
Note that the Sql DSL requires a invocation of the select function in the last statement of the from block. The above query issues the following SQL statement:
select t0_.EMPLOYEE_NAME from EMP t0_ where t0_.EMPLOYEE_ID = ?
Sql DSL Examples¶
DTO mapping¶
data class MyDto(val name: String?, val id: Int?)
val query = sql {
from(::Emp_) { e ->
where {
e.employeeId eq 1
}
select(e.employeeName, e.departmentId) {
MyDto(it[e.employeeName], it[e.departmentId])
}
}
}
The above query issues the following SQL statement:
select t0_.EMPLOYEE_NAME, t0_.DEPARTMENT_ID from EMP t0_ where t0_.EMPLOYEE_ID = ?
aggregate function¶
val query = sql {
from(::Emp_) { e ->
val d = leftJoin(::Dept_) { d ->
e.departmentId eq d.departmentId
}
groupBy(d.departmentName)
having {
count(`*`) gt 2
}
orderBy {
count(`*`).desc()
}
select(d.departmentName, count(`*`), min(e.employeeName))
}
}
The above query issues the following SQL statement:
select t1_.DEPARTMENT_NAME, count(*), min(t0_.EMPLOYEE_NAME)
from EMP t0_
left outer join DEPT t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID)
group by t1_.DEPARTMENT_NAME
having count(*) > ?
order by count(*) desc
insert statement¶
val query = sql {
insert.into(::Dept_) { d ->
values {
it[d.departmentId] = 99
it[d.departmentName] = "MARKETING"
it[d.version] = 1
}
}
}
The above query issues the following SQL statement:
insert into DEPT (DEPARTMENT_ID, DEPARTMENT_NAME, VERSION) values (?, ?, ?)
delete statement¶
val query = sql {
delete.from(::Emp_) { e ->
where {
e.departmentId eq 1
}
}
}
The above query issues the following SQL statement:
delete from EMP t0_ where t0_.DEPARTMENT_ID = ?
update statement¶
val query = sql {
update(::Emp_) { e ->
set {
it[e.managerId] = 2
}
where {
e.departmentId eq 1
}
}
}
The above query issues the following SQL statement:
update EMP t0_ set t0_.MANAGER_ID = ? where t0_.DEPARTMENT_ID = ?