When constructing a SQL WHERE clause, you need to concatenate with logical operators such as AND and OR to specify multiple conditions. This article shows how such logical operator concatenation can be represented by the Criteria API in Doma 2.43.0.
Concatenation of logical operators is a simple function to express in SQL, but how to realize it as a programming language is a problem to be considered. It is characteristic that Doma can be expressed using a lambda expression.
For an overview of Doma and the Criteria API, read the other articles in Introduction to Doma.
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;
}
We will prepare ʻEmployeeRepositoryand rewrite theselect` method of this class to show some examples.
public class EmployeeRepository {
private final Entityql entityql;
public EmployeeRepository(Config config) {
this.entityql = new Entityql(config);
}
public List<Employee> select() {
//Here is a code example rewritten in various ways
}
}
If you list the search conditions (ʻeq (..., ...) `in this example), they will be implicitly concatenated by AND.
Employee_ e = new Employee_();
return entityql.from(e).where(c -> {
c.eq(e.id, 1);
c.eq(e.name, "aaa");
c.eq(e.age, 20);
}).fetch();
The generated SQL looks 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_.age, t0_.version from Employee t0_ where t0_.id = 1 and t0_.name = 'aaa' and t0_.age = 20
In the previous example, the method corresponding to AND was not called, but it is also possible to explicitly call the method ʻand corresponding to AND as follows. The ʻand method takes a lambda expression.
Employee_ e = new Employee_();
return entityql.from(e).where(c -> {
c.eq(e.id, 1);
c.and(() -> c.eq(e.name, "aaa"));
c.and(() -> c.eq(e.age, 20));
}).fetch();
The generated SQL will be as follows, and the same SQL as in the previous example will be generated (however, there are differences such as the presence or absence of parentheses).
select t0_.id, t0_.name, t0_.age, t0_.version from Employee t0_ where t0_.id = 1 and (t0_.name = 'aaa') and (t0_.age = 20)
To concatenate with an OR, call the ʻor method as follows: The ʻor method takes a lambda expression.
Employee_ e = new Employee_();
return entityql.from(e).where(c -> {
c.eq(e.id, 1);
c.or(() -> {
c.eq(e.name, "aaa");
c.eq(e.age, 20);
});
}).fetch();
The generated SQL looks like this:
select t0_.id, t0_.name, t0_.age, t0_.version from Employee t0_ where t0_.id = 1 or (t0_.name = 'aaa' and t0_.age = 20)
Similar to the previous example, but what kind of SQL does the following code generate?
Employee_ e = new Employee_();
return entityql.from(e).where(c -> {
c.or(() -> {
c.eq(e.name, "aaa");
c.eq(e.age, 20);
});
c.eq(e.id, 1);
}).fetch();
You might think that an extra OR is generated immediately after the WHERE clause, but Doma automatically removes the AND and OR at the beginning of the WHERE clause, so the following SQL is generated.
select t0_.id, t0_.name, t0_.age, t0_.version from Employee t0_ where (t0_.name = 'aaa' and t0_.age = 20) and t0_.id = 1
This function is useful when the necessity of AND or OR is not statically determined due to a conditional branch in the WHERE clause.
Employee_ e = new Employee_();
return entityql.from(e).where(c -> {
if (Some condition) {
c.or(() -> {
c.eq(e.name, "aaa");
c.eq(e.age, 20);
});
}
if (Some condition 2) {
c.or(() -> {
c.eq(e.name, "bbb");
c.eq(e.age, 30);
});
}
}).fetch();
You can nest any number of ʻand and ʻor without any restrictions.
Employee_ e = new Employee_();
return entityql.from(e).where(c -> {
c.eq(e.id, 1);
c.or(() -> {
c.eq(e.name, "aaa");
c.eq(e.age, 20);
c.or(() -> {
c.eq(e.name, "bbb");
c.eq(e.age, 30);
});
});
}).fetch();
The above code will generate the following SQL:
select t0_.id, t0_.name, t0_.age, t0_.version from Employee t0_ where t0_.id = 1 or (t0_.name = 'aaa' and t0_.age = 20 or (t0_.name = 'bbb' and t0_.age = 30))
We have shown how logical operator concatenation can be represented in Doma's Criteria API.
As mentioned at the beginning, Doma's Criteira API expresses the concatenation of logical operators as a lambda expression. There is a method chain as another expression method, but I personally think that the method chain has a weakness. It is vulnerable to conditional branching. If there is a conditional branch, the chain will break and the complexity will increase at once. This is because you need to store the object to be chained in a variable in order to connect the chain. On the other hand, the method using a lambda expression such as Doma's Criteria API is simple and highly readable because there is no difference in writing style depending on whether conditional branching is included or not (just because it is surrounded by if blocks). I think.
Recommended Posts