Doma Shows how the Criteria API in 2.43.0 can represent SQL subqueries.
For an overview of Doma and the Criteria API, read the other articles in Introduction to Doma.
Here, the simplified code is used. 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.
Department.java
@Entity(metamodel = @Metamodel)
public class Department {
@Id Integer id;
String name;
}
Employee.java
@Entity(metamodel = @Metamodel)
public class Employee {
@Id Integer id;
String name;
@Column(name = "DEPARTMENT_ID")
Integer departmentId;
}
We will prepare ʻEmployeeRepository` and add some methods to this class to show an example.
public class EmployeeRepository {
private final Entityql entityql;
public EmployeeRepository(Config config) {
this.entityql = new Entityql(config);
}
}
Call the ʻin` method.
Note that the lambda expression you pass to the where in the subquery uses the c2 instance, which is passed as a parameter, instead of the outer c instance.
public List<Employee> selectByDepartmentName_in(String departmentName) {
Employee_ e = new Employee_();
Department_ d = new Department_();
return entityql
.from(e)
.where(
c ->
c.in(
e.departmentId,
c.from(d).where(c2 -> c2.eq(d.name, departmentName)).select(d.id)))
.fetch();
}
If the departmentName parameter is" SALES ", the generated SQL will look like this:
(Hereafter, the SQL with the bound value embedded is shown, but in reality, the SQL using the bind variable ? Is issued.)
select
t0_.id,
t0_.name,
t0_.DEPARTMENT_ID
from
Employee t0_
where
t0_.DEPARTMENT_ID in (
select
t1_.id
from
Department t1_
where
t1_.name = 'SALES'
)
If the department table has a compound key, you can also search by key combination by using Tuple2 as follows (however, the database to be used specifies multiple columns in the IN predicate format. Must be supported).
c.in(
new Tuple2<>(e.departmentId1, e.departmentId2),
c.from(d).where(c2 -> c2.eq(d.name, departmentName)).select(d.id1, d.id2)))
The notIn method, which is the equivalent of the NOT IN predicate, is also provided.
Call the ʻexists` method.
The precautions are the same as when using the IN predicate. Note that the lambda expression you pass to the where in the subquery uses the c2 instance, which is passed as a parameter, instead of the outer c instance.
public List<Employee> selectByDepartmentName_exists(String departmentName) {
Employee_ e = new Employee_();
Department_ d = new Department_();
return entityql
.from(e)
.where(
c ->
c.exists(
c.from(d)
.where(
c2 -> {
c2.eq(e.departmentId, d.id);
c2.eq(d.name, departmentName);
})))
.fetch();
}
If the departmentName parameter is" SALES ", the generated SQL will look like this:
select
t0_.id,
t0_.name,
t0_.DEPARTMENT_ID
from
Employee t0_
where
exists (
select
t1_.id,
t1_.name
from
Department t1_
where
t0_.DEPARTMENT_ID = t1_.id
and
t1_.name = 'SALES'
)
The notExists method, which is the equivalent of the NOT EXISTS predicate, is also provided.
Queries that are not subqueries but get equivalent search results can also be represented using joins. You don't have to worry about the precautions (lambda expression parameters) required when using IN predicates and EXISTS predicates, so if the results are the same, this method is recommended. This example calls the ʻinnerJoin` method.
public List<Employee> selectByDepartmentName_join(String departmentName) {
Employee_ e = new Employee_();
Department_ d = new Department_();
return entityql
.from(e)
.innerJoin(d, on -> on.eq(e.departmentId, d.id))
.where(c -> c.eq(d.name, departmentName))
.fetch();
}
If the departmentName parameter is" SALES ", the generated SQL will look like this:
select
t0_.id,
t0_.name,
t0_.DEPARTMENT_ID
from
Employee t0_
inner join
Department t1_
on
(t0_.DEPARTMENT_ID = t1_.id)
where
t1_.name = 'SALES'
Especially for subqueries, the code tends to get a little messy. If you are concerned about that point, you may consider writing in Kotlin. (Doma provides Criteria API for Kotlin) If you write a method equivalent to the above in Kotlin, it will be refreshing overall, and you will not have to worry about the parameters that are passed in the lambda expression.
Below is a code example when written in Kotlin, but if you are interested, please also refer to this project. I have a more complete working sample code.
fun selectByDepartmentName_in(departmentName: String): List<Employee> {
val e = Employee_()
val d = Department_()
return entityql
.from(e)
.where {
`in`(e.departmentId, from(d).where { eq(d.name, departmentName) }.select(d.id))
}
.fetch()
}
fun selectByDepartmentName_exists(departmentName: String): List<Employee> {
val e = Employee_()
val d = Department_()
return entityql
.from(e)
.where {
exists(
from(d).where {
eq(e.departmentId, d.id)
eq(d.name, departmentName)
}
)
}
.fetch()
}
fun selectByDepartmentName_join(departmentName: String): List<Employee> {
val e = Employee_()
val d = Department_()
return entityql
.from(e)
.innerJoin(d) { eq(e.departmentId, d.id) }
.where {
eq(d.name, departmentName)
}
.fetch()
}
Demonstrated how to represent subqueries in the Criteria API of Doma 2.43.0.
In this article, the Java code is formatted with google-java-format and the Kotlin code is formatted with ktlint, but you may want to customize the format a bit to improve the readability of the code. Personally, I have the impression that google-java-format formats a little more line breaks around lambda expressions.
Recommended Posts