There are several evaluation items for the database access library, but I think that how easy it is to assemble dynamic SQL is a high priority item. It's easy to write static SQL, but when it comes to dynamic SQL, it's easy to get into trouble. Especially in the case of business applications, there are multiple search items on the screen, and if it is specified, it is included in the search conditions and if it is not specified, it is not included, but depending on the function provided by the library, the application code Is full of conditional branches, which reduces readability.
This article introduces the ability to dynamically construct SQL WHERE clauses using the Doma 2.43.0 Criteria API, and how Doma solves the above problems. Indicates whether it can be resolved.
For an overview of Doma and the Criteria API, also read Introduction to Doma-Introduction to the Criteria API.
The database has only one employee table that represents employees.
schema.sql
create table employee (
id integer not null primary key,
name varchar(255) not null,
age integer not null,
version integer not null);
Prepare the ʻEmployee` class as the entity class corresponding to the employee table.
Employee.java
@Entity(metamodel = @Metamodel)
public class Employee {
@Id
public Integer id;
public String name;
public Integer age;
@Version public Integer version;
}
Prepare ʻEmployee Repositoryas Repository to search employees by age. When searching, it is assumed that the upper and lower age limits can be specified as options (if specified, it will be included in the search condition, if not specified, it will not be included), and this function will be implemented by theselectByAgeRange` method.
EmployeeRepository.java
public class EmployeeRepository {
private final Entityql entityql;
public EmployeeRepository(Config config) {
this.entityql = new Entityql(config);
}
public List<Employee> selectByAgeRange(Integer min, Integer max) {
//The focus of this article is how to implement this with the Criteria API.
}
}
Doma's Criteria API has the ability to automatically exclude a search condition from the WHERE clause when you specify a search condition to compare with null. Therefore, if you use this function, you do not need to describe conditional branching in the implementation of selectByAgeRange. It can be implemented as follows:
EmployeeRepository.Part of java
public List<Employee> selectByAgeRange(Integer min, Integer max) {
Employee_ e = new Employee_();
return entityql
.from(e)
.where(
c -> {
c.ge(e.age, min);
c.le(e.age, max);
})
.fetch();
}
Below, we'll look at the SQL that is generated when you call this method in some pattern.
Hereafter, repository represents an instance of the ʻEmployeeRepository` class.
List<Employee> list = repository.selectByAgeRange(30, 40);
The SQL generated at this time is as follows. The search conditions that specify the upper and lower age limits appear properly in the WHERE clause.
select t0_.id, t0_.name, t0_.age, t0_.version from Employee t0_ where t0_.age >= ? and t0_.age <= ?
List<Employee> list = repository.selectByAgeRange(30, null);
The search condition that specifies the upper limit does not appear in the WHERE clause.
select t0_.id, t0_.name, t0_.age, t0_.version from Employee t0_ where t0_.age >= ?
List<Employee> list = repository.selectByAgeRange(null, 40);
This time, the search condition that specifies the lower limit does not appear in the WHERE clause.
select t0_.id, t0_.name, t0_.age, t0_.version from Employee t0_ where t0_.age <= ?
List<Employee> list = repository.selectByAgeRange(null, null);
You can predict what the result will be. Yes, neither the search criteria that specify the upper and lower limits will appear.
select t0_.id, t0_.name, t0_.age, t0_.version from Employee t0_
So how should you write if you want to see non-null values and decide whether to include them in your search criteria? For example, suppose there is a requirement that when the lower limit of age is 0 or less, it is not included in the condition (only when it is greater than 0, it is included in the condition). In that case, you can write an explicit conditional branch as follows.
EmployeeRepository.Part of java
public List<Employee> selectByAgeRange(Integer min, Integer max) {
Employee_ e = new Employee_();
return entityql
.from(e)
.where(
c -> {
if (min != null && min > 0) {
c.ge(e.age, min);
}
c.le(e.age, max);
})
.fetch();
}
I'm just conditional branching in a lambda expression block that I pass to the where method. Unless the condition is evaluated, it will not be included in the search condition.
List<Employee> list = repository.selectByAgeRange(-1, 40);
If you call it as above, you will see that the following SQL is generated and the search condition that specifies the lower bound does not appear in the WHERE clause.
select t0_.id, t0_.name, t0_.age, t0_.version from Employee t0_ where t0_.age <= ?
In this article, I showed you how to use Doma's Criteria API to concisely construct dynamic WHERE clauses in SQL.
It's not exactly the same as shown here, but similar code can be found in the project below and can be run and tried.
Recommended Posts