Getting Started with Doma-Using Joins with the Criteira API

Introduction

Doma Describes how to use joins with the 2.43.0 Criteria API.

Doma's Criteira API supports two patterns: "simply join" and "join and get related entities". The latter is a bit like JPA's join fetch in terms of implications.

For an overview of Doma and the Criteria API, read the other articles in Introduction to Doma.

Sample code used in this article

Here is a simplified code. See the project below for the complete sample code.

The database has a department table that represents departments and an employee table that represents employees.

schema.sql


create table department (
    id integer not null primary key,
    name varchar(255) not null);

create table employee (
    id integer not null primary key,
    name varchar(255) not null,
    department_id integer);

Prepare the Department class corresponding to the department table and the Employee class corresponding to the employee table. Annotate @Transient with the employees property in the Department class and the department property in the Employee class so that they can represent bidirectional relationships. @Transient indicates that the property should not be mapped to a database column.

Employee.java


@Entity(metamodel = @Metamodel)
public class Department {
  @Id Integer id;

  String name;

  @Transient List<Employee> employees = new ArrayList<>();

  // getter, setter
}

Department.java


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

  @Id
  Integer id;

  String name;

  @Column(name = "DEPARTMENT_ID")
  Integer departmentId;

  @Transient Department department;

  // getter, setter
}

We will prepare ʻEmployeeRepository` and add a method to this class to show an example.

EmployeeRepository.java


public class EmployeeRepository {

  private final Entityql entityql;

  public EmployeeRepository(Config config) {
    this.entityql = new Entityql(config);
  }
}

Simple join

I wrote an example of a simple join in a previous article [here](https://qiita.com/nakamura-to/items/77d9e56aff890d7757b3#%E5%90%8C%E7%AD%89%E3%81% AE% E6% A4% 9C% E7% B4% A2% E7% B5% 90% E6% 9E% 9C% E3% 82% 92join% E5% 8F% A5% E3% 82% 92% E4% BD% BF% Please refer to E3% 81% A3% E3% 81% A6% E5% AE% 9F% E7% 8F% BE).

Since a simple join does not get the related object, it is not necessary to define the property as @Transient, but depending on the situation, you can choose not to get the related object, so I think you should define the basics.

Join to get related entities

To get the related entity, call the leftJoin or ʻinnerJoin method and then the ʻassociate method. ʻAssociate` method is passed a lambda expression to associate.

  public List<Employee> selectAllWithAssociation() {
    Employee_ e = new Employee_();
    Department_ d = new Department_();
    return 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();
  }

The lambda expression passed to the ʻassociate` method is called for the number of all employees. The SQL that is executed looks like this:

select
    t0_.id,
    t0_.name,
    t0_.DEPARTMENT_ID,
    t1_.id,
    t1_.name
from
    Employee t0_
left outer join
    Department t1_
on
    (t0_.DEPARTMENT_ID = t1_.id)

You can see that the select list contains both columns for the employee table and the department table. Since it is acquired from SQL 1, the N + 1 problem does not occur.

In the above example, we only read the leftJoin and ʻassociate` methods once each, but both can be called multiple times in a single query, as in the sample code below.

  public Order selectOrder(int orderId) {
    var order_ = new Order_();
    var orderStatus_ = new OrderStatus_();
    var orderLineItem_ = new OrderLineItem_();
    var item_ = new Item_();
    var inventory_ = new Inventory_();
    var product_ = new Product_();

    return entityql
        .from(order_)
        .innerJoin(orderStatus_, on -> on.eq(order_.orderId, orderStatus_.orderId))
        .leftJoin(orderLineItem_, on -> on.eq(order_.orderId, orderLineItem_.orderId))
        .leftJoin(item_, on -> on.eq(orderLineItem_.itemId, item_.itemId))
        .innerJoin(inventory_, on -> on.eq(item_.itemId, inventory_.itemId))
        .innerJoin(product_, on -> on.eq(item_.productId, product_.productId))
        .where(c -> c.eq(order_.orderId, orderId))
        .associate(order_, orderStatus_, Order::setOrderStatus)
        .associate(order_, orderLineItem_, Order::addLineItem)
        .associate(orderLineItem_, item_, OrderLineItem::setItem)
        .associate(item_, inventory_, Item::setInventory)
        .associate(item_, product_, Item::setProduct)
        .fetchOne();
  }

This example is a sample app spring-boot-jpetstore part -jpetstore / blob / f44df4c6289934d3b5d74ebd1c0172186cb138ef / src / main / java / sample / repository / OrderRepository.java # L25-L47 )is.

You can get it by tracing any number of related destinations without any particular restrictions. For example, in the sample code above, you can get related entities by following Order → OrderLineItem → Item → Product. However, please note that the amount of data acquired by SQL tends to be large.

Reuse of association code

If you are writing a query like the one above, you may be concerned about duplication of the associated code (the lambda expression you pass to the ʻassociate` method). In that case, it's a good idea to express the lambda expression as a class.

  public List<Employee> selectAllWithAssociation() {
    Employee_ e = new Employee_();
    Department_ d = new Department_();
    return entityql
        .from(e)
        .leftJoin(d, on -> on.eq(e.departmentId, d.id))
        .associate(e, d, new Employees_Department())
        .fetch();
  }

ʻThe lambda expression of the associate method is just BiConsumer`, so you can create an implementation class.

public class Employees_Department implements BiConsumer<Employee, Department> {

  @Override
  public void accept(Employee employee, Department department) {
    employee.setDepartment(department);
    department.getEmployees().add(employee);
  }
}

in conclusion

I showed you how to use joins in Doma's Criteria API.

Doma does not require static annotations such as @ OneToMany to represent associations in entity classes like JPA. Therefore, it is necessary to explicitly describe the association process in the query that uses the Criteria API, but flexible description is possible for each query.

In the example in this article, I used @Transient to represent related entities in the properties of the entity because it is the most common method, but depending on your preference, the entity obtained by the lambda expression of the ʻassociate` method is completely different. It is also possible to have it in the object of.

Recommended Posts

Getting Started with Doma-Using Joins with the Criteira API
Getting Started with Doma-Using Projection with the Criteira API
Getting Started with Doma-Using Subqueries with the Criteria API
Getting Started with Doma-Introduction to the Criteria API
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 Doma-Criteria API Cheat Sheet
Getting started with the JVM's GC mechanism
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 Logical Operators such as AND and OR in the WHERE Clause of the Criteria API
Getting Started with Doma-Annotation Processing
Getting Started with Java Collection
Getting Started with JSP & Servlet
Getting Started with Java Basics
Getting Started with Spring Boot
Getting Started with Ruby Modules
Now is the time to get started with the Stream API
Getting started with Java lambda expressions
Getting Started with Docker with VS Code
Returning to the beginning, getting started with Java ② Control statements, loop statements
Summarize the main points of getting started with JPA learned with Hibernate
Getting Started with Ruby for Java Engineers
Getting Started with Docker for Mac (Installation)
Getting Started with Parameterization Testing in JUnit
Getting Started with Ratpack (4)-Routing & Static Content
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
Links & memos for getting started with Java (for myself)
Getting Started with Java 1 Putting together similar things
Getting started with Kotlin to send to Java developers
I tried Getting Started with Gradle on Heroku
Going back to the beginning and getting started with Java ① Data types and access modifiers
Getting started with Java programs using Visual Studio Code
"Experience" reactive programming with NoSQL (touch the Couchbase Reactive API)
Getting Started with Legacy Java Engineers (Stream + Lambda Expression)
Get started with serverless Java with the lightweight framework Micronaut!