Criteria API

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 the map method. The object returned by the select method doesn’t have any execute 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.