Criteria API

Contents

Introduction

Note

In Kotlin environment, use Kotlin specific DSLs instead of the following DSLs. See Kotlin specific Criteria API.

There are two kinds of DSLs in the Criteria API:

  • The Entityql DSL
  • The NativeSql DSL

Both requires predefined Entity classes and metamodel classes.

We use the following Entity classes to show you some examples:

@Entity(metamodel = @Metamodel)
public class Employee {

  @Id private Integer employeeId;
  private Integer employeeNo;
  private String employeeName;
  private Integer managerId;
  private LocalDate hiredate;
  private Salary salary;
  private Integer departmentId;
  private Integer addressId;
  @Version private Integer version;
  @OriginalStates private Employee states;
  @Transient private Department department;
  @Transient private Employee manager;
  @Transient private Address address;

  // getters and setters
}
@Entity(metamodel = @Metamodel)
public class Department {

  @Id private Integer departmentId;
  private Integer departmentNo;
  private String departmentName;
  private String location;
  @Version private Integer version;
  @OriginalStates private Department originalStates;
  @Transient private List<Employee> employeeList = new ArrayList<>();

  // getters and setters
}
@Entity(immutable = true, metamodel = @Metamodel)
@Table(name = "EMPLOYEE")
public class Emp {

  @Id private final Integer employeeId;
  private final Integer employeeNo;
  private final String employeeName;
  private final Integer managerId;
  private final LocalDate hiredate;
  private final Salary salary;
  private final Integer departmentId;
  private final Integer addressId;
  @Version private final Integer version;
  @Transient private final Dept department;
  @Transient private final Emp manager;

  // constructor and getters
}
@Entity(immutable = true, metamodel = @Metamodel)
@Table(name = "DEPARTMENT")
public class Dept {

  @Id private final Integer departmentId;
  private final Integer departmentNo;
  private final String departmentName;
  private final String location;
  @Version private final Integer version;

  // constructor and getters
}

Note that the above classes are annotated with @Entity(metamodel = @Metamodel). The metamodel = @Metamodel indicates that the annotated entity has a corresponding metamodel class generated by Doma’s annotation processor .

In our examples, the metamodel classes are Employee_, Department_, Emp_ and Dept_. These metamodels allow you to make your query typesafe.

You can customize the name of the metamodels by the Metamodel annotation elements.

To customize all metamodels in bulk, you can use the annotation processor options. See Annotation processing and check the following options:

  • doma.metamodel.enabled
  • doma.metamodel.prefix
  • doma.metamodel.suffix

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

List<Employee> list =
    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);
            })
        .fetch();

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

List<Tuple2<Long, String>> list =
    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()))
        .select(count(), d.departmentName)
        .fetch();

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

The difference between two DSLs

The biggest difference between two DSLs is that the Entityql DSL removes duplicated data from the fetched results, but the NativeSQL DSL doesn’t.

See the following example:

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

// (1) Use Entityql DSL
List<Department> list1 =
    entityql.from(d).innerJoin(e, on -> on.eq(d.departmentId, e.departmentId)).fetch();

// (2) Use NativeSql DSL
List<Department> list2 =
    nativeSql.from(d).innerJoin(e, on -> on.eq(d.departmentId, e.departmentId)).fetch();

System.out.println(list1.size()); //  3
System.out.println(list2.size()); // 14

Both (1) and (2) issue the same SQL statement as follows:

select t0_.DEPARTMENT_ID, t0_.DEPARTMENT_NO, t0_.DEPARTMENT_NAME, t0_.LOCATION,t0_.VERSION
from DEPARTMENT t0_
inner join EMPLOYEE t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID)

The ResultSet contains 14 rows, but the Entityql DSL returns only 3 rows because it removes duplicate Department entities. The Entityql DSL uses the id properties to know the uniqueness of the entities.

On the other hand, the NativeSql DSL returns the data as it retrieves from the database. It puts results into entity objects, but it handles them as the plain DTOs.

Select statement

Select settings (Entityql, NativeSql)

We support the following settings:

  • allowEmptyWhere
  • comment
  • fetchSize
  • maxRows
  • queryTimeout
  • sqlLogType

They are all optional. You can apply them as follows:

Employee_ e = new Employee_();

List<Employee> list = entityql.from(e, settings -> {
  settings.setAllowEmptyWhere(false);
  settings.setComment("all employees");
  settings.setFetchSize(100);
  settings.setMaxRows(100);
  settings.setSqlLogType(SqlLogType.RAW);
  settings.setQueryTimeout(1000);
}).fetch();

Fetching (Entityql, NativeSql)

Both Entityql DSL and NativeSql DSL support the following methods to fetch data from a database:

  • fetch
  • fetchOne
  • fetchOptional
  • stream
Employee_ e = new Employee_();

// The fetch method returns results as a list.
List<Employee> list =
    entityql.from(e).fetch();

// The fetchOne method returns a single result. The result may be null.
Employee employee =
    entityql.from(e).where(c -> c.eq(e.employeeId, 1)).fetchOne();

// The fetchOptional method returns a single result as an optional object.
Optional<Employee> optional =
    entityql.from(e).where(c -> c.eq(e.employeeId, 1)).fetchOptional();

// The stream method returns results as a stream.
// The following code is equivalent to "entityql.from(e).fetch().stream()"
Stream<Employee> stream =
    entityql.from(e).stream();

Streaming (NativeSql)

The NativeSql DSL supports the following methods:

  • mapStream
  • collect
Employee_ e = new Employee_();

// The mapStream method handles a stream.
Map<Integer, List<Employee>> map =
    nativeSql
        .from(e)
        .mapStream(stream -> stream.collect(groupingBy(Employee::getDepartmentId)));

// The collect method is a shortcut of the mapStream method.
// The following code does the same thing with the above.
Map<Integer, List<Employee>> map2 =
    nativeSql.from(e).collect(groupingBy(Employee::getDepartmentId));

These methods handle the stream that wraps a JDBC ResultSet. So they are useful to process a large ResultSet effectively.

Select expression

Entity selection (Entityql, NativeSql)

By default, the result entity type is the same as the one specified at the from method. See the following code:

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

List<Employee> list =
    entityql
        .from(e)
        .innerJoin(d, on -> on.eq(e.departmentId, d.departmentId))
        .fetch();

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)

To choose a joined entity type as the result entity type, call the select method as follows:

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

List<Department> list =
    entityql
        .from(e)
        .innerJoin(d, on -> on.eq(e.departmentId, d.departmentId))
        .select(d)
        .fetch();

The above query issues the following SQL statement:

select 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)

Multiple entity selection (NativeSql)

You can specify multiple entity types and fetch them as a tuple as follows:

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

List<Tuple2<Department, Employee>> list =
    nativeSql
        .from(d)
        .leftJoin(e, on -> on.eq(d.departmentId, e.departmentId))
        .where(c -> c.eq(d.departmentId, 4))
        .select(d, e)
        .fetch();

The above query issues the following SQL statement:

select t0_.DEPARTMENT_ID, t0_.DEPARTMENT_NO, t0_.DEPARTMENT_NAME, t0_.LOCATION,
t0_.VERSION, t1_.EMPLOYEE_ID, t1_.EMPLOYEE_NO, t1_.EMPLOYEE_NAME, t1_.MANAGER_ID,
t1_.HIREDATE, t1_.SALARY, t1_.DEPARTMENT_ID, t1_.ADDRESS_ID, t1_.VERSION
from DEPARTMENT t0_ left outer join EMPLOYEE t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID)
where t0_.DEPARTMENT_ID = ?

The entity included in the tuple may be null when the all properties of the entity are null.

Column projection (NativeSql)

To project columns, use the select method:

To project one column, pass one property to the select method as follows:

Employee_ e = new Employee_();

List<String> list = nativeSql.from(e).select(e.employeeName).fetch();

The above query issues the following SQL statement:

select t0_.EMPLOYEE_NAME from EMPLOYEE t0_

To project two or more columns, pass two or more properties to the select method as follows:

Employee_ e = new Employee_();

List<Tuple2<String, Integer>> list =
    nativeSql.from(e).select(e.employeeName, e.employeeNo).fetch();

The above query issues the following SQL statement:

select t0_.EMPLOYEE_NAME, t0_.EMPLOYEE_NO from EMPLOYEE t0_

Up to 9 numbers, the column results are held by Tuple2 to Tuple9. For more than 9 numbers, the results are held by Row.

Column projection and mapping (Entityql, NativeSql)

To project columns and map them to an entity, use the selectTo method as follows:

Employee_ e = new Employee_();

List<Employee> list = entityql.from(e).selectTo(e, e.employeeName).fetch();

The above query issues the following SQL statement:

select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NAME from EMPLOYEE t0_

Note that the select clause of the above SQL statement contains the primary key “EMPLOYEE_ID”. The selectTo method always includes the id properties of the entity, even if you don’t.

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)

Note

If the right hand operand is null, the WHERE or the HAVING clause doesn’t include the operator. See WhereDeclaration and HavingDeclaration javadoc for more details.

We also support the following utility operators:

  • eqOrIsNull - (“=” or “is null”)
  • neOrIsNotNull - (“<>” or “is not null”)

We also support the following logical operators:

  • and
  • or
  • not
Employee_ e = new Employee_();

List<Employee> list =
    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"));
                  });
            })
        .fetch();

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

List<Employee> list =
    entityql
        .from(e)
        .where(c -> c.in(e.employeeId, c.from(e2).select(e2.managerId)))
        .orderBy(c -> c.asc(e.employeeId))
        .fetch();

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

Dynamic where expression (Entityql, NativeSql)

A where expression uses only evaluated operators to build a WHERE clause.

When every operators are not evaluated in a where expression, the built statement doesn’t have any WHERE clause.

As well as, when every operators are not evaluated in a logical operator expression, the built statement doesn’t have the logical operator expression.

For example, suppose that a where expression contains a conditional expression as follows:

Employee_ e = new Employee_();

List<Employee> list =
    entityql
        .from(e)
        .where(
            c -> {
              c.eq(e.departmentId, 1);
              if (enableNameCondition) {
                c.like(e.employeeName, name);
              }
            })
        .fetch();

In the case that the enableNameCondition variable is false, the like expression is ignored. 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 = ?

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

List<Employee> list =
    entityql.from(e).innerJoin(d, on -> on.eq(e.departmentId, d.departmentId)).fetch();

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

List<Employee> list =
    entityql.from(e).leftJoin(d, on -> on.eq(e.departmentId, d.departmentId)).fetch();

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)

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

List<Employee> list =
    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);
            })
        .fetch();

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

List<Employee> list =
    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::setAddress)
        .fetch();

association for immutable entities (Entityql)

You can associate immutable entities with the associateWith operation in the Entityql DSL. You have to use the associateWith operation with join expression.

Emp_ e = new Emp_();
Emp_ m = new Emp_();
Dept_ d = new Dept_();

List<Emp> list =
    entityql
        .from(e)
        .innerJoin(d, on -> on.eq(e.departmentId, d.departmentId))
        .leftJoin(m, on -> on.eq(e.managerId, m.employeeId))
        .where(c -> c.eq(d.departmentName, "SALES"))
        .associateWith(e, d, Emp::withDept)
        .associateWith(e, m, Emp::withManager)
        .fetch();

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,
t2_.EMPLOYEE_ID, t2_.EMPLOYEE_NO, t2_.EMPLOYEE_NAME, t2_.MANAGER_ID, t2_.HIREDATE,
t2_.SALARY, t2_.DEPARTMENT_ID, t2_.ADDRESS_ID, t2_.VERSION
from EMPLOYEE t0_
inner join DEPARTMENT t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID)
left outer join EMPLOYEE t2_ on (t0_.MANAGER_ID = t2_.EMPLOYEE_ID)
where t1_.DEPARTMENT_NAME = ?

Dynamic join expression (Entityql, NativeSql)

A join expression uses only evaluated operators to build a JOIN clause.

When every operators are not evaluated in a join expression, the built statement doesn’t have any JOIN clause.

For example, suppose that a join expression contains a conditional expression as follows:

Employee_ e = new Employee_();
Employee_ e2 = new Employee_();

List<Employee> list =
    entityql
        .from(e)
        .innerJoin(
            e2,
            on -> {
              if (join) {
                on.eq(e.managerId, e2.employeeId);
              }
            })
        .fetch();

In the case that the join variable is false, the on expression is ignored. 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_

Dynamic association (Entityql)

When you use the above dynamic join expression, the association must be optional. To do it, pass the result of AssociationOption.optional() to the associate method:

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

List<Employee> list =
    entityql
        .from(e)
        .innerJoin(
            d,
            on -> {
              if (join) {
                on.eq(e.departmentId, d.departmentId);
              }
            })
        .associate(
            e,
            d,
            (employee, department) -> {
              employee.setDepartment(department);
              department.getEmployeeList().add(employee);
            },
            AssociationOption.optional())
        .fetch();

Aggregate Functions (NativeSql)

We support the following aggregate functions:

  • avg(property)
  • avgAsDouble(property)
  • count()
  • count(property)
  • countDistinct(property)
  • max(property)
  • min(property)
  • sum(property)

These are defined in the org.seasar.doma.jdbc.criteria.expression.Expressions class. Use them with static import.

For example, you can pass the sum function to the select method:

Employee_ e = new Employee_();

Salary salary = nativeSql.from(e).select(sum(e.salary)).fetchOne();

The above query issues the following SQL statement:

select sum(t0_.SALARY) from EMPLOYEE t0_

Group by expression (NativeSql)

Employee_ e = new Employee_();

List<Tuple2<Integer, Long>> list =
    nativeSql.from(e).groupBy(e.departmentId).select(e.departmentId, count()).fetch();

The above query issues the following SQL statement:

select t0_.DEPARTMENT_ID, count(*) from EMPLOYEE t0_ group by t0_.DEPARTMENT_ID

When you don’t specify a group by expression, the expression is inferred from the select expression automatically. So the following code issue the same SQL statement above:

Employee_ e = new Employee_();

List<Tuple2<Integer, Long>> list =
    nativeSql.from(e).select(e.departmentId, count()).fetch();

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

List<Tuple2<Long, String>> list =
    nativeSql
        .from(e)
        .innerJoin(d, on -> on.eq(e.departmentId, d.departmentId))
        .having(c -> c.gt(count(), 3L))
        .orderBy(c -> c.asc(count()))
        .select(count(), d.departmentName)
        .fetch();

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

Dynamic having expression (NativeSql)

A having expression uses only evaluated operators to build a HAVING clause.

When every operators are not evaluated in a having expression, the built statement doesn’t have any HAVING clause.

As well as, when every operators are not evaluated in a logical operator expression, the built statement doesn’t have the logical operator expression.

Order by expression (Entityql, NativeSql)

We support the following order operations:

  • asc
  • desc
Employee_ e = new Employee_();

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

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

Dynamic order by expression (NativeSql)

An order by expression uses only evaluated operators to build an ORDER BY clause.

When every operators are not evaluated in a order by expression, the built statement doesn’t have any ORDER BY clause.

Distinct expression (Entityql, NativeSql)

List<Department> list =
        nativeSql
                .from(d)
                .distinct()
                .leftJoin(e, on -> on.eq(d.departmentId, e.departmentId))
                .fetch();

The above query issues the following SQL statement:

select distinct t0_.DEPARTMENT_ID, t0_.DEPARTMENT_NO, t0_.DEPARTMENT_NAME,
t0_.LOCATION, t0_.VERSION
from DEPARTMENT t0_
left outer join EMPLOYEE t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID)

Limit and Offset expression (Entityql, NativeSql)

Employee_ e = new Employee_();

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

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
offset 3 rows fetch first 5 rows only

Dynamic Limit and Offset expression (Entityql, NativeSql)

A limit expressions uses only non-null value to build a FETCH FIRST clause. When the value is null ,the built statement doesn’t have any FETCH FIRST clause.

As well as, an offset expressions uses only non-null value to build a OFFSET clause. When the value is null ,the built statement doesn’t have any OFFSET clause.

For Update expression (Entityql, NativeSql)

Employee_ e = new Employee_();

List<Employee> list = nativeSql.from(e).where(c -> c.eq(e.employeeId, 1)).forUpdate().fetch();

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

List<Tuple2<Integer, String>> list =
    nativeSql
        .from(e)
        .select(e.employeeId, e.employeeName)
        .union(nativeSql.from(d)
        .select(d.departmentId, d.departmentName))
        .fetch();

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_

The order by expression with index is supported:

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

List<Tuple2<Integer, String>> list =
    nativeSql
        .from(e)
        .select(e.employeeId, e.employeeName)
        .union(nativeSql.from(d)
        .select(d.departmentId, d.departmentName))
        .orderBy(c -> c.asc(2))
        .fetch();

Delete statement

For the specification of the where expression, see Where expression (Entityql, NativeSql). The same rule is applied to delete statements.

Delete settings (Entityql, NativeSql)

We support the following settings:

  • allowEmptyWhere
  • batchSize
  • comment
  • ignoreVersion
  • queryTimeout
  • sqlLogType
  • suppressOptimisticLockException

They are all optional.

You can apply them as follows:

Employee_ e = new Employee_();

int count = nativeSql.delete(e, settings -> {
  settings.setAllowEmptyWhere(true);
  settings.setBatchSize(20);
  settings.setComment("delete all");
  settings.setIgnoreVersion(true);
  settings.setQueryTimeout(1000);
  settings.setSqlLogType(SqlLogType.RAW);
  settings.setSuppressOptimisticLockException(true);
}).execute();

Note

If you want to build a delete statement without a WHERE clause, you have to enable the allowEmptyWhere setting.

Delete statement (Entityql)

Employee_ e = new Employee_();

Employee employee = entityql.from(e).where(c -> c.eq(e.employeeId, 5)).fetchOne();

Result<Employee> result = entityql.delete(e, employee).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_();

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

BatchResult<Employee> result = entityql.delete(e, employees).execute();

The execute method may throw following exceptions:

  • OptimisticLockException: if the entity has a version property and an update count is 0

Delete statement (NativeSql)

Employee_ e = new Employee_();

int count = nativeSql.delete(e).where(c -> c.ge(e.salary, new Salary("2000"))).execute();

The above query issues the following SQL statement:

delete from EMPLOYEE t0_ where t0_.SALARY >= ?

Insert statement

Insert settings (Entityql, NativeSql)

We support the following settings:

  • comment
  • queryTimeout
  • sqlLogType
  • batchSize
  • excludeNull

They are all optional.

You can apply them as follows:

Department_ d = new Department_();

int count =
    nativeSql
        .insert(d, settings -> {
            settings.setComment("insert department");
            settings.setQueryTimeout(1000);
            settings.setSqlLogType(SqlLogType.RAW);
            settings.setBatchSize(20);
            settings.excludeNull(true);
        })
        .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);
            })
        .execute();

Insert statement (Entityql)

Department_ d = new Department_();

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

Result<Department> result = entityql.insert(d, department).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 = ...;
Department department2 = ...;
List<Department> departments = Arrays.asList(department, department2);

BatchResult<Department> result = entityql.insert(d, departments).execute();

The execute method may throw following exceptions:

  • UniqueConstraintException: if an unique constraint is violated

Insert statement (NativeSql)

Department_ d = new Department_();

int count =
    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);
            })
        .execute();

The above query issues the following SQL statement:

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

The execute method may throw following exceptions:

  • UniqueConstraintException: if an unique constraint is violated

We also support the INSERT SELECT syntax as follows:

Department_ da = new Department_("DEPARTMENT_ARCHIVE");
Department_ d = new Department_();

int count =
    nativeSql
        .insert(da)
        .select(c -> c.from(d).where(cc -> cc.in(d.departmentId, Arrays.asList(1, 2))))
        .execute();

The above query issues the following SQL statement:

insert into DEPARTMENT_ARCHIVE (DEPARTMENT_ID, DEPARTMENT_NO, DEPARTMENT_NAME,
LOCATION, VERSION) select t0_.DEPARTMENT_ID, t0_.DEPARTMENT_NO, t0_.DEPARTMENT_NAME,
t0_.LOCATION, t0_.VERSION from DEPARTMENT t0_ where t0_.DEPARTMENT_ID in (?, ?)

Update statement

For the specification of the where expression, see Where expression (Entityql, NativeSql). The same rule is applied to update statements.

Update settings (Entityql, NativeSql)

We support the following settings:

  • allowEmptyWhere
  • batchSize
  • comment
  • ignoreVersion
  • queryTimeout
  • sqlLogType
  • suppressOptimisticLockException
  • excludeNull

They are all optional.

You can apply them as follows:

Employee_ e = new Employee_();

int count = nativeSql.update(e, settings -> {
  settings.setAllowEmptyWhere(true);
  settings.setBatchSize(20);
  settings.setComment("update all");
  settings.setIgnoreVersion(true);
  settings.setQueryTimeout(1000);
  settings.setSqlLogType(SqlLogType.RAW);
  settings.setSuppressOptimisticLockException(true);
  settings.excludeNull(true);
}).set(c -> {
  c.value(e.employeeName, "aaa");
}).execute();

Note

If you want to build a update statement without a WHERE clause, you have to enable the allowEmptyWhere setting.

Update statement (Entityql)

Employee_ e = new Employee_();

Employee employee = entityql.from(e).where(c -> c.eq(e.employeeId, 5)).fetchOne();
employee.setEmployeeName("aaa");
employee.setSalary(new Salary("2000"));

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

The above query issues the following SQL statement:

update EMPLOYEE set EMPLOYEE_NAME = ?, SALARY = ?, VERSION = ? + 1
where EMPLOYEE_ID = ? and VERSION = ?

Batch Update is also supported:

Employee_ e = new Employee_();

Employee employee = ...;
Employee employee2 = ...;
List<Employee> departments = Arrays.asList(employee, employee2);

BatchResult<Employee> result = entityql.update(e, employees).execute();

The execute method may throw following exceptions:

  • OptimisticLockException: if the entity has a version property and an update count is 0
  • UniqueConstraintException: if an unique constraint is violated

Update statement (NativeSql)

Employee_ e = new Employee_();

int count =
    nativeSql
        .update(e)
        .set(c -> c.value(e.departmentId, 3))
        .where(
            c -> {
              c.isNotNull(e.managerId);
              c.ge(e.salary, new Salary("2000"));
            })
        .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 >= ?

The execute method may throw following exceptions:

  • UniqueConstraintException: if an unique constraint is violated

Property expressions (Entityql, NativeSql)

All expression methods are defined in the org.seasar.doma.jdbc.criteria.expression.Expressions class.

Use them with static import.

Arithmetic expressions

We provide the following methods:

  • add - (+)
  • sub - (-)
  • mul - (*)
  • div - (/)
  • mod - (%)

You can use the add method as follows:

Employee_ e = new Employee_();

int count =
    nativeSql
        .update(e)
        .set(c -> c.value(e.version, add(e.version, 10)))
        .where(c -> c.eq(e.employeeId, 1))
        .execute();

The above query issues the following SQL statement:

update EMPLOYEE t0_
set t0_.VERSION = (t0_.VERSION + ?)
where t0_.EMPLOYEE_ID = ?

String functions

We provide the following method:

  • concat
  • lower
  • upper
  • trim
  • ltrim
  • rtrim

You can use the concat method as follows:

Employee_ e = new Employee_();

int count =
    nativeSql
        .update(e)
        .set(c -> c.value(e.employeeName, concat("[", concat(e.employeeName, "]"))))
        .where(c -> c.eq(e.employeeId, 1))
        .execute();

The above query issues the following SQL statement:

update EMPLOYEE t0_
set t0_.EMPLOYEE_NAME = concat(?, concat(t0_.EMPLOYEE_NAME, ?))
where t0_.EMPLOYEE_ID = ?

Literal expression

We provide the following method:

  • literal (for all basic data types)

You can use the literal method as follows:

Employee employee = entityql.from(e).where(c -> c.eq(e.employeeId, literal(1))).fetchOne();

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 = 1

Note

Note that the literal expressions are not recognized as bind variables.

Case expression

We support the following method:

  • when

You can use the when method as follows:

Employee_ e = new Employee_();

List<String> list =
    nativeSql
        .from(e)
        .select(
            when(
                c -> {
                  c.eq(e.employeeName, literal("SMITH"), lower(e.employeeName));
                  c.eq(e.employeeName, literal("KING"), lower(e.employeeName));
                },
                literal("_")))
        .fetch();

The above query issues the following SQL statement:

select case
        when t0_.EMPLOYEE_NAME = 'SMITH' then lower(t0_.EMPLOYEE_NAME)
        when t0_.EMPLOYEE_NAME = 'KING' then lower(t0_.EMPLOYEE_NAME)
        else '_' end
from EMPLOYEE t0_

Subquery select expression

We support the following method:

  • select

You can use the select method as follows:

Employee_ e = new Employee_();

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

SelectExpression<Salary> subSelect =
    select(
        c ->
            c.from(e2)
                .innerJoin(d, on -> on.eq(e2.departmentId, d.departmentId))
                .where(cc -> cc.eq(e.departmentId, d.departmentId))
                .groupBy(d.departmentId)
                .select(max(e2.salary)));

int count =
    nativeSql
        .update(e)
        .set(c -> c.value(e.salary, subSelect))
        .where(c -> c.eq(e.employeeId, 1))
        .execute();

The above query issues the following SQL statement:

update EMPLOYEE t0_
set t0_.SALARY = (
    select max(t1_.SALARY)
    from EMPLOYEE t1_
    inner join DEPARTMENT t2_ on (t1_.DEPARTMENT_ID = t2_.DEPARTMENT_ID)
    where t0_.DEPARTMENT_ID = t2_.DEPARTMENT_ID group by t2_.DEPARTMENT_ID
)
where t0_.EMPLOYEE_ID = ?

Scopes (Entityql, NativeSql)

Scoping allow you to specify commonly-used query conditions.

To define a simple scope, create the class which has a method annotated with @Scope:

public class DepartmentScope {
    @Scope
    public Consumer<WhereDeclaration> onlyTokyo(Department_ d) {
        return c -> c.eq(d.location, "Tokyo");
    }
}

To enable the scope, specify the above class in the scopes element of @Metamodel:

@Entity(metamodel = @Metamodel(scopes = { DepartmentScope.class }))
public class Department { ... }

Now the metamodel Department_ has a onlyTokyo method. You can use it as follows:

Department_ d = new Department_();

List<Department> list = entityql.from(d).where(d.onlyTokyo()).fetch();

The above query issues the following SQL statement:

select t0_.DEPARTMENT_ID, t0_.DEPARTMENT_NO, t0_.DEPARTMENT_NAME, t0_.LOCATION, t0_.VERSION from DEPARTMENT t0_
where t0_.LOCATION = ?

When you want to combine other query conditions with scopes, compose them using the andThen method:

Department_ d = new Department_();

List<Department> list = entityql.from(d).where(d.onlyTokyo().andThen(c -> c.gt(d.departmentNo, 50))).fetch();

You can define several scopes in a class as follows:

public class DepartmentScope {
    @Scope
    public Consumer<WhereDeclaration> onlyTokyo(Department_ d) {
        return c -> c.eq(d.location, "Tokyo");
    }

    @Scope
    public Consumer<WhereDeclaration> locationStartsWith(Department_ d, String prefix) {
        return c -> c.like(d.location, prefix, LikeOption.prefix());
    }

    @Scope
    public Consumer<OrderByNameDeclaration> sortByNo(Department_ d) {
        return c -> c.asc(d.departmentNo);
    }
}

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_();
    return entityql.from(e).where(c -> c.eq(e.employeeId, id)).fetchOptional();
  }
}

Overwriting the table name (Entityql, NativeSql)

A metamodel constructor accepts the qualified table name and the metamodel overwrites its table name.

It is useful to handle two tables that have the same data structure:

Department_ da = new Department_("DEPARTMENT_ARCHIVE");
Department_ d = new Department_();

int count =
    nativeSql
        .insert(da)
        .select(c -> c.from(d))
        .execute();
insert into DEPARTMENT_ARCHIVE (DEPARTMENT_ID, DEPARTMENT_NO, DEPARTMENT_NAME,
LOCATION, VERSION) select t0_.DEPARTMENT_ID, t0_.DEPARTMENT_NO, t0_.DEPARTMENT_NAME,
t0_.LOCATION, t0_.VERSION from DEPARTMENT t0_

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<String> locations = nativeSql
        .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()))
        .select(d.location)
        .peek(sql -> System.out.println(sql.getFormattedSql()))
        .fetch();

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
select t0_.LOCATION from DEPARTMENT t0_ where t0_.DEPARTMENT_NAME = 'SALES' order by t0_.LOCATION asc