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.
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);
}
}
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.
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.
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);
}
}
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