Getting Started with Doma-Dynamicly construct WHERE clauses with the Criteria API

Introduction

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.

Sample code used in this article

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

Automatic removal function of search conditions to compare with null

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.

When passing a non-null value to both min and max

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 <= ?

When passing non-null to min and null to max

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 >= ?

When passing null to min and non-null to max

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 <= ?

When passing null to both min and max

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_

Explicit conditional branching within a block

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 conclusion

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

Getting Started with Doma-Dynamicly construct WHERE clauses with the Criteria API
Getting Started with Doma-Using Subqueries with the Criteria API
Getting Started with Doma-Introduction to the Criteria API
Getting Started with Doma-Using Projection with the Criteira API
Getting Started with Doma-Using Joins with the Criteira API
Getting Started with Doma-Using Logical Operators such as AND and OR in the WHERE Clause of 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-Annotation Processing
Getting Started with Java Collection
Getting Started with JSP & Servlet
Getting Started with Java Basics
Getting Started with Spring Boot
Now is the time to get started with the Stream API
Getting Started with Ruby Modules
Summarize the main points of getting started with JPA learned with Hibernate
Getting Started with Java_Chapter 5_Practice Exercises 5_4
[Google Cloud] Getting Started with Docker
Getting started with Java lambda expressions
Getting Started with Docker with VS Code
Getting Started with Ruby for Java Engineers
Getting Started with Docker for Mac (Installation)
Getting Started with Parameterization Testing in JUnit
Getting Started with Java Starting from 0 Part 1
Getting Started with Ratpack (4)-Routing & Static Content
Getting Started with Language Server Protocol with LSP4J
Getting Started with Creating Resource Bundles with ListResoueceBundle