Criteria API¶
Contents
- Criteria API
- Introduction
- Entityql DSL
- NativeSql DSL
- Where expression (Entityql, NativeSql)
- Join expression
- Aggregate Functions (NativeSql)
- Group by expression (NativeSql)
- Having expression (NativeSql)
- Order by expression (Entityql, NativeSql)
- Limit and Offset expression (Entityql, NativeSql)
- For Update expression (Entityql, NativeSql)
- Union expression (NativeSql)
- Delete statement (NativeSql)
- Insert statement (NativeSql)
- Update statement (NativeSql)
- Tips
- Sample projects
Warning
The criteria API is an experimental feature.
Introduction¶
There are two kind of DSLs in the Criteria API:
- The Entityql DSL
- The NativeSql DSL
Both requires predefined Entity classes and the meta classes generated by Doma’s annotation processors.
We use the following Entity classes to show you some examples:
@Entity
public class Employee {
@Id private Integer employeeId;
private Integer employeeNo;
private String employeeName;
private Integer managerId;
private EmployeeInfo employeeInfo;
private Salary salary;
private Integer departmentId;
private Integer addressId;
@Version private Integer version;
@Transient private Department department;
@Transient private Employee manager;
@Transient private Address address;
// getter and setter
}
@Entity
public class Department {
@Id Integer departmentId;
Integer departmentNo;
String departmentName;
String location;
@Version Integer version;
@OriginalStates Department originalStates;
@Transient List<Employee> employeeList = new ArrayList<>();
// getter and setter
}
Each of the above classes has a corresponding meta class - Employee_
and Department_
.
These meta classes allow you to make your query typesafe.
You can customize the name of the meta classes by specifying annotation processor options. See Annotation processing and check the doma.criteria.prefix and the doma.criteria.suffix options.
Entityql DSL¶
The Entityql DSL can query and associate entities.
The entry point is the org.seasar.doma.jdbc.criteria.Entityql
class.
This class has the following static method:
- from
You can use it as follows:
Entityql.from(e)
For example, to query Employee
and Department
entities and associate them, write as follows:
Employee_ e = new Employee_();
Department_ d = new Department_();
SelectStatement<Employee> stmt =
Entityql.from(e)
.innerJoin(d, on -> on.eq(e.departmentId, d.departmentId))
.where(c -> c.eq(d.departmentName, "SALES"))
.associate(
e,
d,
(employee, department) -> {
employee.setDepartment(department);
department.getEmployeeList().add(employee);
});
List<Employee> list = stmt.execute(config);
The above query issues the following SQL statement:
select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID,
t0_.HIREDATE, t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION,
t1_.DEPARTMENT_ID, t1_.DEPARTMENT_NO, t1_.DEPARTMENT_NAME, t1_.LOCATION, t1_.VERSION
from EMPLOYEE t0_ inner join DEPARTMENT t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID)
where t1_.DEPARTMENT_NAME = ?
NativeSql DSL¶
The NativeSql DSL can issue more complex SQL statements rather than the Entityql DSL. But note that the NativeSql DSL doesn’t support to associate entities.
The entry point is the org.seasar.doma.jdbc.criteria.NativeSql
class.
This class has the following static methods:
- from
- delete.from
- insert.into
- update
You can use them as follows:
NativeSql.from(e)
NativeSql.delete.from(e)
NativeSql.insert.into(e)
NativeSql.update(e)
For example, to query two columns with GROUP BY and HAVING clauses, write as follows:
Employee_ e = new Employee_();
Department_ d = new Department_();
SelectStatement<Tuple2<Long, String>> stmt =
NativeSql.from(e)
.innerJoin(d, on -> on.eq(e.departmentId, d.departmentId))
.groupBy(d.departmentName)
.having(c -> c.gt(count(), 3L))
.orderBy(c -> c.asc(count()))
.<Tuple2<Long, String>>select(count(), d.departmentName)
.map(
row -> {
Long first = row.get(count());
String second = row.get(d.departmentName);
return new Tuple2<>(first, second);
});
List<Tuple2<Long, String>> list = stmt.execute(config);
The above query issues the following SQL statement:
select count(*), t1_.DEPARTMENT_NAME from EMPLOYEE t0_
inner join DEPARTMENT t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID)
group by t1_.DEPARTMENT_NAME
having count(*) > ?
order by count(*) asc
Where expression (Entityql, NativeSql)¶
We support the following operators and predicates:
- eq - (=)
- ne - (<>)
- ge - (>=)
- gt - (>)
- le - (<=)
- lt - (<)
- isNull - (is null)
- isNotNull - (is not null)
- like
- notLike - (not like)
- between
- in
- notIn - (not in)
- exists
- notExists - (not exists)
We also support the following logical operators:
- and
- or
- not
Employee_ e = new Employee_();
SelectStatement<Employee> stmt =
Entityql.from(e)
.where(
c -> {
c.eq(e.departmentId, 2);
c.isNotNull(e.managerId);
c.or(
() -> {
c.gt(e.salary, new Salary("1000"));
c.lt(e.salary, new Salary("2000"));
});
});
List<Employee> list = stmt.execute(config);
The above query issues the following SQL statement:
select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE,
t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION
from EMPLOYEE t0_
where t0_.DEPARTMENT_ID = ? and t0_.MANAGER_ID is not null or (t0_.SALARY > ? and t0_.SALARY < ?)
You can write a subquery as follows:
Employee_ e = new Employee_();
Employee_ e2 = new Employee_();
SelectStatement<Employee> stmt =
Entityql.from(e)
.where(c -> c.in(e.employeeId, c.from(e2).select(e2.managerId)))
.orderBy(c -> c.asc(e.employeeId));
List<Employee> list = stmt.execute(config);
The above query issues the following SQL statement:
select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE,
t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION
from EMPLOYEE t0_
where t0_.EMPLOYEE_ID in (select t1_.MANAGER_ID from EMPLOYEE t1_)
order by t0_.EMPLOYEE_ID asc
Join expression¶
We support the following expressions:
- innerJoin - (inner join)
- leftJoin - (left outer join)
innerJoin (Entityql, NativeSql)¶
Employee_ e = new Employee_();
Department_ d = new Department_();
SelectStatement<Employee> stmt =
Entityql.from(e).innerJoin(d, on -> on.eq(e.departmentId, d.departmentId));
List<Employee> list = stmt.execute(config);
The above query issues the following SQL statement:
select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE,
t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION
from EMPLOYEE t0_
inner join DEPARTMENT t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID)
leftJoin (Entityql, NativeSql)¶
Employee_ e = new Employee_();
Department_ d = new Department_();
SelectStatement<Employee> stmt =
Entityql.from(e).leftJoin(d, on -> on.eq(e.departmentId, d.departmentId));
List<Employee> list = stmt.execute(config);
The above query issues the following SQL statement:
select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE,
t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION
from EMPLOYEE t0_
left outer join DEPARTMENT t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID)
associate (Entityql)¶
You can associate entities with the associate
operation in the Entityql DSL.
You have to use the associate
operation with join expression.
Employee_ e = new Employee_();
Department_ d = new Department_();
SelectStatement<Employee> stmt =
Entityql.from(e)
.innerJoin(d, on -> on.eq(e.departmentId, d.departmentId))
.where(c -> c.eq(d.departmentName, "SALES"))
.associate(
e,
d,
(employee, department) -> {
employee.setDepartment(department);
department.getEmployeeList().add(employee);
});
List<Employee> list = stmt.execute(config);
The above query issues the following SQL statement:
select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID,
t0_.HIREDATE, t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION,
t1_.DEPARTMENT_ID, t1_.DEPARTMENT_NO, t1_.DEPARTMENT_NAME, t1_.LOCATION, t1_.VERSION
from EMPLOYEE t0_ inner join DEPARTMENT t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID)
where t1_.DEPARTMENT_NAME = ?
You can associate many entities:
Employee_ e = new Employee_();
Department_ d = new Department_();
Address_ a = new Address_();
SelectStatement<Employee> stmt =
Entityql.from(e)
.innerJoin(d, on -> on.eq(e.departmentId, d.departmentId))
.innerJoin(a, on -> on.eq(e.addressId, a.addressId))
.where(c -> c.eq(d.departmentName, "SALES"))
.associate(
e,
d,
(employee, department) -> {
employee.setDepartment(department);
department.getEmployeeList().add(employee);
})
.associate(e, a, (employee, address) -> employee.setAddress(address));
List<Employee> list = stmt.execute(config);
Aggregate Functions (NativeSql)¶
We support the following aggregate functions:
- avg(property)
- count()
- count(property)
- max(property)
- min(property)
- sum(property)
These are defined in the org.seasar.doma.jdbc.criteria.AggregateFunctions
class.
Use them with static import.
For example, you can pass the sum
function to the select method:
Employee_ e = new Employee_();
SelectStatement<Salary> stmt =
NativeSql.from(e).<Salary>select(sum(e.salary)).map(row -> row.get(sum(e.salary)));
List<Salary> list = stmt.execute(config);
Note that you have to specify a type argument to the select method.
The above query issues the following SQL statement:
select sum(t0_.SALARY) from EMPLOYEE t0_
Group by expression (NativeSql)¶
Employee_ e = new Employee_();
SelectStatement<Tuple2<Integer, Long>> stmt =
NativeSql.from(e)
.groupBy(e.departmentId)
.<Tuple2<Integer, Long>>select(e.departmentId, count())
.map(
row -> {
Integer id = row.get(e.departmentId);
Long count = row.get(count());
return new Tuple2<>(id, count);
});
List<Tuple2<Integer, Long>> list = stmt.execute(config);
The above query issues the following SQL statement:
select t0_.DEPARTMENT_ID, count(*) from EMPLOYEE t0_ group by t0_.DEPARTMENT_ID
Having expression (NativeSql)¶
We support the following operators:
- eq - (=)
- ne - (<>)
- ge - (>=)
- gt - (>)
- le - (<=)
- lt - (<)
We also support the following logical operators:
- and
- or
- not
Employee_ e = new Employee_();
Department_ d = new Department_();
SelectStatement<Tuple2<Long, String>> stmt =
NativeSql.from(e)
.innerJoin(d, on -> on.eq(e.departmentId, d.departmentId))
.groupBy(d.departmentName)
.having(
c -> {
c.gt(count(), 3L);
c.or(() -> c.le(min(e.salary), new Salary("2000")));
})
.orderBy(c -> c.asc(count()))
.<Tuple2<Long, String>>select(count(), d.departmentName)
.map(
row -> {
Long first = row.get(count());
String second = row.get(d.departmentName);
return new Tuple2<>(first, second);
});
List<Tuple2<Long, String>> list = stmt.execute(config);
The above query issues the following SQL statement:
select count(*), t1_.DEPARTMENT_NAME
from EMPLOYEE t0_
inner join DEPARTMENT t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID)
group by t1_.DEPARTMENT_NAME having count(*) > ? or (min(t0_.SALARY) <= ?)
order by count(*) asc
Order by expression (Entityql, NativeSql)¶
We support the following order operations:
- asc
- desc
Employee_ e = new Employee_();
SelectStatement<Employee> stmt = Entityql.from(e).orderBy(c -> {
c.asc(e.departmentId);
c.desc(e.salary);
});
List<Employee> list = stmt.execute(config);
The above query issues the following SQL statement:
select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE,
t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION
from EMPLOYEE t0_
order by t0_.DEPARTMENT_ID asc, t0_.SALARY desc
Limit and Offset expression (Entityql, NativeSql)¶
Employee_ e = new Employee_();
SelectStatement<Employee> stmt =
NativeSql.from(e).limit(5).offset(3).orderBy(c -> c.asc(e.employeeNo));
List<Employee> list = stmt.execute(config);
The above query issues the following SQL statement:
select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE,
t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION
from EMPLOYEE t0_
order by t0_.EMPLOYEE_NO asc
limit 5 offset 3
For Update expression (Entityql, NativeSql)¶
Employee_ e = new Employee_();
SelectStatement<Employee> stmt =
NativeSql.from(e).where(c -> c.eq(e.employeeId, 1)).forUpdate();
List<Employee> list = stmt.execute(config);
The above query issues the following SQL statement:
select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE,
t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION
from EMPLOYEE t0_
where t0_.EMPLOYEE_ID = ?
for update
Union expression (NativeSql)¶
We support the following expressions:
- union
- unionAll - (union all)
Employee_ e = new Employee_();
Department_ d = new Department_();
SetOperand<Tuple2<Integer, String>> stmt1 =
NativeSql.from(e).select(e.employeeId, e.employeeName);
SetOperand<Tuple2<Integer, String>> stmt2 =
NativeSql.from(d).select(d.departmentId, d.departmentName);
SelectStatement<Tuple2<Integer, String>> stmt3 =
stmt1
.union(stmt2)
.map(
row -> {
Integer id = row.get(e.employeeId);
String name = row.get(e.employeeName);
return new Tuple2<>(id, name);
});
List<Tuple2<Integer, String>> list = stmt3.execute(config);
The above query issues the following SQL statement:
select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NAME from EMPLOYEE t0_
union
select t0_.DEPARTMENT_ID, t0_.DEPARTMENT_NAME from DEPARTMENT t0_
Delete statement (NativeSql)¶
Employee_ e = new Employee_();
DeleteStatement stmt = NativeSql.delete.from(e).where(c -> c.ge(e.salary, new Salary("2000")));
int count = stmt.execute(config);
The above query issues the following SQL statement:
delete from EMPLOYEE t0_ where t0_.SALARY >= ?
Insert statement (NativeSql)¶
Department_ d = new Department_();
InsertStatement stmt =
NativeSql.insert
.into(d)
.values(
c -> {
c.value(d.departmentId, 99);
c.value(d.departmentNo, 99);
c.value(d.departmentName, "aaa");
c.value(d.location, "bbb");
c.value(d.version, 1);
});
int count = stmt.execute(config);
The above query issues the following SQL statement:
insert into DEPARTMENT (DEPARTMENT_ID, DEPARTMENT_NO, DEPARTMENT_NAME, LOCATION, VERSION)
values (?, ?, ?, ?, ?)
Update statement (NativeSql)¶
Employee_ e = new Employee_();
UpdateStatement stmt =
NativeSql.update(e)
.set(c -> c.value(e.departmentId, 3))
.where(
c -> {
c.isNotNull(e.managerId);
c.ge(e.salary, new Salary("2000"));
});
int count = stmt.execute(config);
The above query issues the following SQL statement:
update EMPLOYEE t0_ set t0_.DEPARTMENT_ID = ?
where t0_.MANAGER_ID is not null and t0_.SALARY >= ?
Tips¶
Execution in Dao (Entityql, NativeSql)¶
It is useful to execute DSLs in the default method of the Dao interface.
To get a config
object, call Config.get(this)
in the default method as follows:
@Dao
public interface EmployeeDao {
default Optional<Employee> selectById(Integer id) {
Employee_ e = new Employee_();
List<Employee> list =
Entityql.from(e)
.where(c -> c.eq(e.employeeId, id))
.execute(Config.get(this));
return list.stream().findFirst();
}
}
The use of the select method (NativeSql)¶
Be careful of the following points when you use the select
method:
- Specify a type argument to the
select
method. - Use the
select
method in combination with themap
method. The object returned by theselect
method doesn’t have anyexecute
methods.
Employee_ e = new Employee_();
SelectStatement<Salary> stmt =
NativeSql.from(e).<Salary>select(sum(e.salary)).map(row -> row.get(sum(e.salary)));
List<Salary> list = stmt.execute(config);
Debugging (Entityql, NativeSql)¶
To know the SQL statement built by the DSLs, use the asSql
method instead of the execute
method:
Department_ d = new Department_();
SelectStatement<Department> stmt = Entityql.from(d).where(c -> c.eq(d.departmentName, "SALES"));
Sql<?> sql = stmt.asSql(config);
System.out.printf("Raw SQL : %s\n", sql.getRawSql());
System.out.printf("Formatted SQL: %s\n", sql.getFormattedSql());
The above code prints as follows:
Raw SQL : select t0_.DEPARTMENT_ID, t0_.DEPARTMENT_NO, t0_.DEPARTMENT_NAME, t0_.LOCATION, t0_.VERSION from DEPARTMENT t0_ where t0_.DEPARTMENT_NAME = ?
Formatted SQL: select t0_.DEPARTMENT_ID, t0_.DEPARTMENT_NO, t0_.DEPARTMENT_NAME, t0_.LOCATION, t0_.VERSION from DEPARTMENT t0_ where t0_.DEPARTMENT_NAME = 'SALES'
The asSql
method doesn’t issue the SQL statement to your Database.
It only builds the SQL statement and return it as an Sql
object.