Criteria API

Contents

• Criteria API
  • Introduction
  • Entityql DSL
  • NativeSql DSL
  • Where expression (Entityql, NativeSql)
  • Join expression
    • innerJoin (Entityql, NativeSql)
    • leftJoin (Entityql, NativeSql)
    • associate (Entityql)
  • 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
    • Delete statement (Entityql)
    • Delete statement (NativeSql)
  • Insert statement
    • Insert statement (Entityql)
    • Insert statement (NativeSql)
  • Update statement
    • Update statement (Entityql)
    • Update statement (NativeSql)
  • Tips
    • Execution in Dao (Entityql, NativeSql)
    • The use of the select method (NativeSql)
    • Debugging (Entityql, NativeSql)
  • 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 require 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 methods:

• from
• insert
• delete
• update

You can instantiate the Entityql class as follows:

Entityql entityql = new Entityql(config);

For example, to query Employee and Department entities and associate them, write as follows:

Employee_ e = new Employee_();
Department_ d = new Department_();

Listable<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.getResultList();

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 methods:

• from
• delete
• insert
• update

You can use instantiate the NativeSql class as follows:

NativeSql nativeSql = new NativeSql(config);

For example, to query two columns with GROUP BY and HAVING clauses, write as follows:

Employee_ e = new Employee_();
Department_ d = new Department_();

Listable<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.getResultList();

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_();

Listable<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.getResultList();

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_();

Listable<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.getResultList();

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_();

Listable<Employee> stmt =
    entityql.from(e).innerJoin(d, on -> on.eq(e.departmentId, d.departmentId));

List<Employee> list = stmt.getResultList();

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_();

Listable<Employee> stmt =
    entityql.from(e).leftJoin(d, on -> on.eq(e.departmentId, d.departmentId));

List<Employee> list = stmt.getResultList();

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_();

Listable<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.getResultList();

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_();

Listable<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.getResultList();

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_();

Listable<Salary> stmt =
    nativeSql.from(e).<Salary>select(sum(e.salary)).map(row -> row.get(sum(e.salary)));

List<Salary> list = stmt.getResultList();

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_();

Listable<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.getResultList();

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_();

Listable<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.getResultList();

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_();

Listable<Employee> stmt =
    entityql
        .from(e)
        .orderBy(
            c -> {
              c.asc(e.departmentId);
              c.desc(e.salary);
            });

List<Employee> list = stmt.getResultList();

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_();

Listable<Employee> stmt =
    nativeSql.from(e).limit(5).offset(3).orderBy(c -> c.asc(e.employeeNo));

List<Employee> list = stmt.getResultList();

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_();

Listable<Employee> stmt = nativeSql.from(e).where(c -> c.eq(e.employeeId, 1)).forUpdate();

List<Employee> list = stmt.getResultList();

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_();

Mappable<Tuple2<Integer, String>> stmt1 =
    nativeSql.from(e).select(e.employeeId, e.employeeName);
Mappable<Tuple2<Integer, String>> stmt2 =
    nativeSql.from(d).select(d.departmentId, d.departmentName);
Listable<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.getResultList();

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

Delete statement (Entityql)

Employee_ e = new Employee_();

Listable<Employee> select = entityql.from(e).where(c -> c.eq(e.employeeId, 5));
Employee employee = select.getSingleResult().orElseThrow(AssertionError::new);

Statement<Employee> delete = entityql.delete(e, employee);
Employee result = delete.execute();

The above query issues the following SQL statement:

delete from EMPLOYEE where EMPLOYEE_ID = ? and VERSION = ?

Batch Delete is also supported:

Employee_ e = new Employee_();

Listable<Employee> select =
    entityql.from(e).where(c -> c.in(e.employeeId, Arrays.asList(5, 6)));
List<Employee> employees = select.getResultList();

Statement<List<Employee>> delete = entityql.delete(e, employees);
List<Employee> results = delete.execute();

Delete statement (NativeSql)

Employee_ e = new Employee_();

Statement<Integer> stmt = nativeSql.delete(e).where(c -> c.ge(e.salary, new Salary("2000")));

int count = stmt.execute();

The above query issues the following SQL statement:

delete from EMPLOYEE t0_ where t0_.SALARY >= ?

Insert statement

Insert statement (Entityql)

Department_ d = new Department_();

Department department = new Department();
department.setDepartmentId(99);
department.setDepartmentNo(99);
department.setDepartmentName("aaa");
department.setLocation("bbb");

Statement<Department> insert = entityql.insert(d, department);
Department result = insert.execute();

The above query issues the following SQL statement:

insert into DEPARTMENT (DEPARTMENT_ID, DEPARTMENT_NO, DEPARTMENT_NAME, LOCATION, VERSION)
values (?, ?, ?, ?, ?)

Batch Insert is also supported:

Department_ d = new Department_();

Department department = new Department();
department.setDepartmentId(99);
department.setDepartmentNo(99);
department.setDepartmentName("aaa");
department.setLocation("bbb");

Department department2 = new Department();
department2.setDepartmentId(100);
department2.setDepartmentNo(100);
department2.setDepartmentName("ccc");
department2.setLocation("ddd");

List<Department> departments = Arrays.asList(department, department2);

Statement<List<Department>> insert = entityql.insert(d, departments);
List<Department> results = insert.execute();

Insert statement (NativeSql)

Department_ d = new Department_();

Statement<Integer> stmt =
    nativeSql
        .insert(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();

The above query issues the following SQL statement:

insert into DEPARTMENT (DEPARTMENT_ID, DEPARTMENT_NO, DEPARTMENT_NAME, LOCATION, VERSION)
values (?, ?, ?, ?, ?)

Update statement

Update statement (Entityql)

Employee_ e = new Employee_();

Listable<Employee> select = entityql.from(e).where(c -> c.eq(e.employeeId, 5));
Employee employee = select.execute().get(0);
employee.setEmployeeName("aaa");
employee.setSalary(new Salary("2000"));

Statement<Employee> update = entityql.update(e, employee);
Employee result = update.execute();

The above query issues the following SQL statement:

update EMPLOYEE set EMPLOYEE_NO = 7654, EMPLOYEE_NAME = 'aaa', MANAGER_ID = 6,
HIREDATE = '1981-09-28', SALARY = 2000, DEPARTMENT_ID = 3, ADDRESS_ID = 5, VERSION = 1 + 1
where EMPLOYEE_ID = 5 and VERSION = 1

Batch Update is also supported:

Employee_ e = new Employee_();

Listable<Employee> select =
    entityql.from(e).where(c -> c.in(e.employeeId, Arrays.asList(5, 6)));
List<Employee> employees = select.getResultList();
employees.forEach(it -> it.setEmployeeName("aaa"));

Statement<List<Employee>> update = entityql.update(e, employees);
List<Employee> results = update.execute();

Update statement (NativeSql)

Employee_ e = new Employee_();

Statement<Integer> 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();

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) {
    Entityql entityql = new Entityql(Config.get(this));

    Employee_ e = new Employee_();
    Listable<Employee> stmt = entityql.from(e).where(c -> c.eq(e.employeeId, id));
    return stmt.getSingleResult();
  }
}

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 the map method.

Employee_ e = new Employee_();

Listable<String> stmt =
    nativeSql.from(e).<String>select(e.employeeName).map(row -> row.get(e.employeeName));

List<Salary> list = stmt.getResultList();

Debugging (Entityql, NativeSql)

To know the SQL statement built by the DSLs, use the asSql method:

Department_ d = new Department_();

Listable<Department> stmt = entityql.from(d).where(c -> c.eq(d.departmentName, "SALES"));

Sql<?> sql = stmt.asSql();
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.

You can also get the Sql object by calling the peek method.

Department_ d = new Department_();

List<Department> list =
    entityql
        .from(d)
        .peek(System.out::println)
        .where(c -> c.eq(d.departmentName, "SALES"))
        .peek(System.out::println)
        .orderBy(c -> c.asc(d.location))
        .peek(sql -> System.out.println(sql.getFormattedSql()))
        .getResultList();

The above code prints as follows:

select t0_.DEPARTMENT_ID, t0_.DEPARTMENT_NO, t0_.DEPARTMENT_NAME, t0_.LOCATION, t0_.VERSION from DEPARTMENT t0_
select t0_.DEPARTMENT_ID, t0_.DEPARTMENT_NO, t0_.DEPARTMENT_NAME, t0_.LOCATION, t0_.VERSION from DEPARTMENT t0_ where t0_.DEPARTMENT_NAME = ?
select t0_.DEPARTMENT_ID, t0_.DEPARTMENT_NO, t0_.DEPARTMENT_NAME, t0_.LOCATION, t0_.VERSION from DEPARTMENT t0_ where t0_.DEPARTMENT_NAME = 'SALES' order by t0_.LOCATION asc

Sample projects

• simple-examples
• test-criteria