Getting Started with Doma-Criteria API Cheat Sheet

Caution

This cheat sheet is for the ** Java ** Criteria API. For the Kotlin version of the cheat sheet, see Introduction to Doma-Kotlin Criteria API Cheat Sheet.

Premise

The version of Doma is 2.43.0. For an overview of the Criteria API, see Introduction to Doma. The Java version I'm using is 8.

Suppose the entity classes ʻEmployeeandDepartment` are defined.

In addition, it is assumed that the following variables are defined.

Entityql entityql = new Entityql(config);
Nativesql nativeSql = new NativeSql(config);

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

The example SQL may differ from what is actually generated.

Principle of proper use of Entityql and NativeSql

When using Entityql

• I want to get related entities
• I want to automatically generate a primary key when adding
• I want optimistic exclusive control by updating or deleting
• I want to perform batch processing with update processing
• I want to hook the update process with ʻEntityListener`
• There is no particular reason to use NativeSql as described below

When using NativeSql

• I want to search Stream
• Collect I want to search
• I want to aggregate using an aggregate function (I need to use HAVING or GROUP BY)
• I want to do UNION or UNION ALL
• I want to get an arbitrary column with a tuple class (Tuple2 etc.)
• I want to add using the search results of another table
• I want to update or delete by specifying arbitrary conditions
• I want to handle a table without a primary key
• I want to allow entities with duplicate primary keys when retrieving entities by search

Search

Search all

List<Employee> list = entityql.from(e).fetch();
// select * from employee t0_

1 search

Returns null if it does not exist.

Employee employee = entityql.from(e).where(c -> c.eq(e.id, 1)).fetchOne();
// select * from employee t0_ where t0_.id = ?

Returns ʻOptional.empty ()` if it does not exist.

Optional<Employee> employee = entityql.from(e).where(c -> c.eq(e.id, 1)).fetchOptional();
// select * from employee t0_ where t0_.id = ?

Stream search

Process large amounts of data one by one without squeezing memory.

String names = nativeSql.from(e).mapStream(stream -> 
  stream.map(Employee::getName).collect(Collectors.joining(","))
);
// select * from employee t0_

Collect search

Stream search shortcut.

Map<Integer, List<Employee>> map = nativeSql.from(e).collect(Collectors.groupingBy(Employee::getDepartmentId));
// select * from employee t0_

The above is equivalent to the code below.

Map<Integer, List<Employee>> map = nativeSql.from(e).mapStream(stream -> 
  stream.collect(Collectors.groupingBy(Employee::getDepartmentId))
);
// select * from employee t0_

projection

Returns the result as a tuple class.

List<Tuple2<String, Integer>> list = nativeSql.from(e).select(e.name, e.age).fetch();
// select t0_.name, t0_.age from employee t0_

Returns the result as an entity class. The primary key is always included in the SELECT clause and is also set in the entity.

List<Employee> list = entityql.from(e).selectTo(e, e.name, e.age).fetch();
// select t0_.id, t0_.name, t0_.age from employee t0_

sort

List<Employee> list = entityql.from(e).orderBy(c -> {
  c.asc(e.name);
  c.desc(e.age);
}).fetch();
// select * from employee t0_ order by t0_.name asc, t0_.age desc

Exclude duplicate rows

List<String> list = nativeSql.from(e).distinct().select(e.name).fetch();
// select distinct t0_.name from employee t0_

Limit/Offset

List<Employee> list = entityql.from(e).limit(10).offset(3).fetch();
// select * from employee t0_ limit 10 offset 3

Pessimistic lock

List<Employee> list = entityql.from(e).forUpdate().fetch();
// select * from employee t0_ for update

Aggregation

As an aggregate function, you can use ʻavg, count, countDistinct, max, min, sum defined in ʻorg.seasar.doma.jdbc.criteria.expression.Expressions. ..

Integer integer = nativeSql.from(e).select(Expressions.sum(e.age)).fetchOne();
// select sum(t0_.age) from employee t0_

Aggregation by group

List<Tuple2<Integer, Long>> list = nativeSql.from(e).groupBy(e.departmentId).select(e.departmentId, Expressions.count()).fetch();
// select t0_.department_id, count(*) from employee t0_ group by t0_.department_id

When the groupBy method is not called, the column required for the GROUP BY clause is inferred from the property specified in the select method and automatically assigned. Therefore, the following code will generate the same SQL as above.

List<Tuple2<Integer, Long>> list = nativeSql.from(e).select(e.departmentId, Expressions.count()).fetch();
// select t0_.department_id, count(*) from employee t0_ group by t0_.department_id

Narrowing down the aggregated results for each group

//Find the number of employees in each department for departments with more than 3 employees
List<Tuple2<Long, String>> list =
  nativeSql
    .from(e)
    .innerJoin(d, on -> on.eq(e.departmentId, d.id))
    .having(c -> c.gt(Expressions.count(), 3L))
    .select(Expressions.count(), d.name)
    .fetch();
// select count(*), t1_.name from employee t0_ inner join department t1_ on (t0_.department_id = t1_.id) group by t1_.name having count(*) > 3

Join

Inner join

Only inner join is done.

List<Employee> list = entityql.from(e).innerJoin(d, on -> on.eq(e.departmentId, d.id)).fetch();
// select t0_.* from employee t0_ inner join department t1_ on (t0_.department_id = t1_.id)

Inner join and get related entities.

List<Employee> list = entityql.from(e).innerJoin(d, on -> on.eq(e.departmentId, d.id)).associate(e, d, (employee, department) {
  employee.setDepartment(department);
  department.getEmployees().add(employee);
}).fetch();
// select * from employee t0_ inner join department t1_ on (t0_.department_id = t1_.id)

Outer join

Only outer join is done.

List<Employee> list = entityql.from(e).leftJoin(d, on -> on.eq(e.departmentId, d.id)).fetch();
// select t0_.* from employee t0_ left outer join department t1_ on (t0_.department_id = t1_.id)

Outer join and get related entities as well.

List<Employee> list = entityql.from(e). leftJoin(d, on -> on.eq(e.departmentId, d.id)).associate(e, d, (employee, department) {
  employee.setDepartment(department);
  department.getEmployees().add(employee);
}).fetch();
// select * from employee t0_ left outer join department t1_ on (t0_.department_id = t1_.id)

Self-bonding

You can join (self-join) the same tables using different instances of the same metamodel.

Employee_ m = new Employee_();

List<Employee> list = entityql.from(e).leftJoin(m, on -> on.eq(e.managerId, m.id)).fetch();
// select t0_.* from employee t0_ left outer join employee t1_ on (t0_.manager_id = t1_.id)

You can also get related entities.

Employee_ m = new Employee_();

List<Employee> list = entityql.from(e).leftJoin(m, on -> on.eq(e.managerId, m.id)).associate(e, m, (employee, manager) {
  employee.setManager(manager);
}).fetch();
// select * from employee t0_ left outer join employee t1_ on (t0_.manager_id = t1_.id)

UNION

List<Tuple2<Integer, String>> list =
  nativeSql
    .from(e)
    .select(e.id, e.name)
    .union(nativeSql.from(d).select(d.id, d.name))
    .fetch();
// select t0_.id, t0_.name from employee t0_ union select t0_.id, t0_.name from department t0_

To sort, specify the target column by index. index starts from 1.

List<Tuple2<Integer, String>> list =
  nativeSql
    .from(e)
    .select(e.id, e.name)
    .union(nativeSql.from(d).select(d.id, d.name))
    .orderBy(c -> c.asc(2))
    .fetch();
// (select t0_.id, t0_.name from employee t0_) union (select t0_.id, t0_.name from department t0_) order by 2 asc

You can also UNION ALL.

List<Tuple2<Integer, String>> list =
  nativeSql
    .from(e)
    .select(e.id, e.name)
    .unionAll(nativeSql.from(d).select(d.id, d.name))
    .fetch();
// select t0_.id, t0_.name from employee t0_ union all select t0_.id, t0_.name from department t0_

add to

1 added

Employee employee = ...;
entityql.insert(e, employee).execute();
// insert into employee (id, name, age, version) values (?, ?, ?, ?)

Add batch

List<Employee> employees = ...;
entityql.insert(e, employees).execute();
// insert into employee (id, name, age, version) values (?, ?, ?, ?)

Add search results

Added multiple items to another table with the same data structure.

Department_ da = new Department_("DEPARTMENT_ARCHIVE");

nativeSql.insert(da).select(c -> c.from(d).where(cc -> cc.in(d.id, Arrays.asList(1, 2)))).execute();
// insert into department_archive (id, name, version) select t0_.id, t0_.name, t0_.version from department t0_ where t0_.id in (1, 2)

update

1 update

Employee employee = ...;
entityql.update(e, employee).execute();
// update employee set name = ?, age = ?, version = ? + 1 where id = ? and version = ?

Batch update

List<Employee> employees = ...;
entityql.update(e, employees).execute();
// update employee set name = ?, age = ?, version = ? + 1 where id = ? and version = ?

Updated multiple items that match specific conditions

nativeSql
  .update(e)
  .set(c -> c.value(e.departmentId, 3))
  .where(
    c -> {
      c.eq(e.managerId, 3);
      c.lt(e.age, 30);
    })
    .execute();
// update employee t0_ set department_id = ? where t0_.manager_id = ? and t0_.age < ?

Updated with the calculation result on SQL

nativeSql
  .update(e)
  .set(c -> {
    c.value(e.name, Expressions.concat("[", Expressions.concat(e.name, "]")));
    c.value(e.age, Expressions.add(e.age, 1));
  })
  .where(c -> c.eq(e.id, 1))
  .execute();
// update employee t0_ set name = concat(?, concat(t0_.name, ?)), age = (t0_.age + ?) where t0_.id = ?

Delete

Delete 1

Employee employee = ...;
entityql.delete(e, employee).execute();
// delete from employee where id = ? and version = ? 

Batch delete

List<Employee> employees = ...;
entityql.delete(e, employees).execute();
// delete from employee where id = ? and version = ? 

Delete multiple items that match specific conditions

nativeSql.delete(e).where(c -> c.ge(e.age, 50)).execute();
// delete from employee t0_ where t0_.age >= ? 

Search conditions that can be specified in the WHERE clause

Comparison operation

=

entityql.from(e).where(c -> c.eq(e.age, 20)).fetch();
// select * from employee t0_ where t0_.age = ?

<>

entityql.from(e).where(c -> c.ne(e.age, 20)).fetch();
// select * from employee t0_ where t0_.age <> ?

entityql.from(e).where(c -> c.gt(e.age, 20)).fetch();
// select * from employee t0_ where t0_.age > ?

=

entityql.from(e).where(c -> c.ge(e.age, 20)).fetch();
// select * from employee t0_ where t0_.age >= ?

<

entityql.from(e).where(c -> c.lt(e.age, 20)).fetch();
// select * from employee t0_ where t0_.age < ?

<=

entityql.from(e).where(c -> c.le(e.age, 20)).fetch();
// select * from employee t0_ where t0_.age <= ?

IS NULL

entityql.from(e).where(c -> c.isNull(e.age)).fetch();
// select * from employee t0_ where t0_.age is null

IS NOT NULL

entityql.from(e).where(c -> c.isNotNull(e.age)).fetch();
// select * from employee t0_ where t0_.age is not null

= Or IS NULL

Generate = if ʻage` is not null.

entityql.from(e).where(c -> c.eqOrIsNull(e.age, age)).fetch();
// select * from employee t0_ where t0_.age = ?

Generate IS NULL if ʻage` is null.

entityql.from(e).where(c -> c.eqOrIsNull(e.age, age)).fetch();
// select * from employee t0_ where t0_.age is null

<> Or IS NOT NULL

Generate <> if ʻage` is not null.

entityql.from(e).where(c -> c.neOrIsNotNull(e.age, age)).fetch();
// select * from employee t0_ where t0_.age <> ?

Generate IS NOT NULL if ʻage` is null.

entityql.from(e).where(c -> c.neOrIsNotNull(e.age, age)).fetch();
// select * from employee t0_ where t0_.age is not null

LIKE

A LIKE predicate that does not process anything.

entityql.from(e).where(c -> c.like(e.name, "A%")).fetch();
// select * from employee t0_ where t0_.name like ?
// select * from employee t0_ where t0_.name like 'A%' (Bound valued SQL)

LIKE predicate for prefix match. Wildcards are escaped.

entityql.from(e).where(c -> c.like(e.name, "A%", LikeOption.prefix())).fetch();
// select * from employee t0_ where t0_.name like ? escape '$'
// select * from employee t0_ where t0_.name like 'A$%%' escape '$'　(Bound valued SQL)

LIKE predicate for intermediate match. Wildcards are escaped.

entityql.from(e).where(c -> c.like(e.name, "A%", LikeOption.infix())).fetch();
// select * from employee t0_ where t0_.name like ? escape '$'
// select * from employee t0_ where t0_.name like '%A$%%' escape '$' (Bound valued SQL)

LIKE predicate for suffix match. Wildcards are escaped.

entityql.from(e).where(c -> c.like(e.name, "A%", LikeOption.suffix())).fetch();
// select * from employee t0_ where t0_.name like ? escape '$'
// select * from employee t0_ where t0_.name like '%A$%' escape '$' (Bound valued SQL)

NOT LIKE

A NOT LIKE predicate that does not process anything.

entityql.from(e).where(c -> c.notLike(e.name, "A%")).fetch();
// select * from employee t0_ where t0_.name not like ?
// select * from employee t0_ where t0_.name not like 'A%' (Bound valued SQL)

NOT LIKE predicate for prefix match. Wildcards are escaped.

entityql.from(e).where(c -> c.notLike(e.name, "A%", LikeOption.prefix())).fetch();
// select * from employee t0_ where t0_.name not like ? escape '$'
// select * from employee t0_ where t0_.name not like 'A$%%' escape '$' (Bound valued SQL)

NOT LIKE predicate for intermediate match. Wildcards are escaped.

entityql.from(e).where(c -> c.notLike(e.name, "A%", LikeOption.infix())).fetch();
// select * from employee t0_ where t0_.name not like ? escape '$'
// select * from employee t0_ where t0_.name not like '%A$%%' escape '$' (Bound valued SQL)

NOT LIKE predicate for suffix match. Wildcards are escaped.

entityql.from(e).where(c -> c.notLike(e.name, "A%", LikeOption.suffix())).fetch();
// select * from employee t0_ where t0_.name not like ? escape '$'
// select * from employee t0_ where t0_.name not like '%A$%' escape '$' (Bound valued SQL)

BETWEEN

entityql.from(e).where(c -> c.between(e.age, 20, 30)).fetch();
// select * from employee t0_ where t0_.age between ? and ?

IN

A simple IN predicate.

entityql.from(e).where(c -> c.in(e.age, Arrays.asList(10, 20))).fetch();
// select * from employee t0_ where t0_.age in (?, ?)

IN predicate using tuples.

entityql.from(e).where(c -> c.in(new Tuple2(e.age, e.salary), Arrays.asList(new Tuple2(10, 1000), new Tuple2(20, 2000)))).fetch();
// select * from employee t0_ where (t0_.age, t0_.salary) in ((?, ?), (?, ?))

IN predicate with subquery.

entityql.from(e).where(c -> c.in(e.departmentId, c.from(d).select(d.id))).fetch();
// select * from employee t0_ where t0_.department_id in (select t1_.id from department t1_)

NOT IN

A simple NOT IN predicate.

entityql.from(e).where(c -> c.notIn(e.age, Arrays.asList(10, 20))).fetch();
// select * from employee t0_ where t0_.age not in (?, ?)

NOT IN predicate with tuples.

entityql.from(e).where(c -> c.notIn(new Tuple2(e.age, e.salary), Arrays.asList(new Tuple2(10, 1000), new Tuple2(20, 2000)))).fetch();
// select * from employee t0_ where (t0_.age, t0_.salary) not in ((?, ?), (?, ?))

NOT IN predicate with subquery.

entityql.from(e).where(c -> c.notIn(e.departmentId, c.from(d).select(d.id))).fetch();
// select * from employee t0_ where t0_.department_id not in (select t1_.id from department t1_)

EXISTS

entityql.from(e).where(c -> c.exists(c.from(d).where(c2 -> c2.eq(e.departmentId, d.id))).fetch();
// select * from employee t0_ where exists (select * from department t1_ where t0_.deparment_id = t1_.id)

Logical operation

AND

entityql.from(e).where(c -> {
  c.eq(e.age, 20);
  c.ge(e.salary, 100000);
  c.lt(e.salary, 200000);
}).fetch();
// select * from employee t0_ where t0_.age = ? and t0_.salary >= ? and t0_.salary < ?

OR

entityql.from(e).where(c -> {
  c.eq(e.age, 20);
  c.or(() -> {
    c.ge(e.salary, 100000);
    c.lt(e.salary, 200000);
  });
}).fetch();
// select * from employee t0_ where t0_.age = ? or (t0_.salary >= ? and t0_.salary < ?)

NOT

entityql.from(e).where(c -> {
  c.eq(e.age, 20);
  c.not(() -> {
    c.ge(e.salary, 100000);
    c.lt(e.salary, 200000);
  });
}).fetch();
// select * from employee t0_ where t0_.age = ? and not (t0_.salary >= ? and t0_.salary < ?)

Expressions for columns

literal

Embed the value in SQL as it is without using bind variables. Only the types accepted by the litera method of ʻorg.seasar.doma.jdbc.criteria.expression.Expressions` are supported.

List<Employee> list = entityql.from(e).where(c -> c.eq(e.id, Expressions.literal(10))).fetch();
// select * from employee t0_ where t0_.id = 10

Arithmetic operations

Arithmetic operations can use ʻadd, sub, mul, div, mod, etc. defined in ʻorg.seasar.doma.jdbc.criteria.expression.Expressions.

List<String> list = nativeSql.from(e).select(Expressions.add(e.age, 10)).fetch();
// select (t0_.age + ?) from employee t0_

String function

String functions include concat, lower, ʻupper, trim, ltrim, rtrim defined in ʻorg.seasar.doma.jdbc.criteria.expression.Expressions. Can be used.

List<String> list = nativeSql.from(e).select(Expressions.lower(e.name)).fetch();
// select lower(t0_.name) from employee t0_

CASE expression

List<Tuple2<String, String>> list =
  nativeSql
    .from(e)
    .select(
      e.name,
      Expressions.when(
        c -> {
          c.lt(e.age, Expressions.literal(10), Expressions.literal("A"));
          c.lt(e.age, Expressions.literal(20), Expressions.literal("B"));
          c.lt(e.age, Expressions.literal(30), Expressions.literal("C"));
      },
      Expressions.literal("D")))
      .fetch();
// select t0_.name, case when t0_.age < 10 then 'A' when t0_.age < 20 then 'B' when t0_.age < 30 then 'C' else 'D' end from EMPLOYEE t0_