[JAVA] 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

When using NativeSql

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_

Recommended Posts

Getting Started with Doma-Criteria API Cheat Sheet
Getting Started with DBUnit
Getting Started with Ruby
Getting Started with Swift
Getting Started with Docker
Getting Started with Doma-Transactions
Getting Started with Doma-Using Projection with the Criteira API
Getting Started with Doma-Using Subqueries with the Criteria API
Getting Started with Doma-Using Joins with the Criteira API
Getting Started with Doma-Introduction to the Criteria API
Getting Started with Doma-Annotation Processing
Getting Started with Java Collection
Java Stream API cheat sheet
Getting Started with JSP & Servlet
Getting Started with Java Basics
Getting Started with Spring Boot
Getting Started with Ruby Modules
Getting Started with Doma-Dynamicly construct WHERE clauses with the Criteria API
Getting Started with Reactive Streams and the JDK 9 Flow API
Getting Started with Java_Chapter 5_Practice Exercises 5_4
[Google Cloud] Getting Started with Docker
Getting started with Java lambda expressions
Getting Started with Docker with VS Code
Getting Started with Ruby for Java Engineers
Getting Started with Docker for Mac (Installation)
Getting Started with Parameterization Testing in JUnit
Getting Started with Java Starting from 0 Part 1
Getting Started with Ratpack (4)-Routing & Static Content
Getting started with the JVM's GC mechanism
Getting Started with Language Server Protocol with LSP4J
Getting Started with Creating Resource Bundles with ListResoueceBundle
Getting Started with Java_Chapter 8_About Instances and Classes
Java cheat sheet
Links & memos for getting started with Java (for myself)
JMeter cheat sheet
Getting Started with Java 1 Putting together similar things
Getting started with Kotlin to send to Java developers
Kotlin cheat sheet
[Docker cheat sheet]
I tried Getting Started with Gradle on Heroku
Getting started with Java programs using Visual Studio Code
Getting Started with Legacy Java Engineers (Stream + Lambda Expression)
Mockito + PowerMock cheat sheet
Compatible with Android 10 (API 29)
Eclipse Collections cheat sheet
Rails Tutorial cheat sheet
Spring Boot2 cheat sheet
SCSS notation cheat sheet
Oreshiki docker-compose cheat sheet
Get started with Gradle
Docker command cheat sheet
Proceed with Rust official documentation on Docker container (1. Getting started)
Getting started with Java and creating an AsciiDoc editor with JavaFX
Now is the time to get started with the Stream API
Getting Started with GitHub Container Registry instead of Docker Hub