Spring Data JPA Specification 動態條件查詢 dynamic query

Spring Data JPA的Specification動態查詢使用方式如下。

Spring Data JPA一般簡單的查詢多使用Query Method 方法名稱查詢，但有時只是幾個where條件的差異就必須撰寫多個query method。例如兩個條件是否加入判斷就可以有三個query method。

List<Employee> findByName(String name);
List<Employee> findByAgeGreaterThan(int age);
List<Employee> findByNameAndAgeGreaterThan(String name, int age);

此時就可以利用Specification查詢來依輸入參數決定是否加上where條件，又稱動態查詢(dynamic SQL query)。

Specification查詢是基於Criteria API，參考「Spring Data JPA Criteria 查詢簡單範例」了解Criteria及Specification的基本用法。

範例環境：

• Java 8
• Spring Boot 2.3.2.RELEASE
• Spring Data JPA
• H2 database
• Lombok

在entity類Employee新增兩個回傳Specification實例的靜態方法nameEquals()及ageGreaterThan()如下，分別為判斷姓名(name)等於的條件及年齡(age)大於的條件。

package com.abc.demo.entity;

import org.springframework.data.jpa.domain.Specification;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import java.io.Serializable;

@Entity
public class Employee implements Serializable {
    private static final Long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    private Long departmentId;

    private String name;

    private Integer age;

    /**
     * 姓名等於
     * @param name 名稱
     * @return 名稱等於spec
     */
    public static Specification<Employee> nameEquals(String name) {
        return (root, query, builder) ->
                builder.equal(root.get("name"), name);

    }
    
    /**
     * 年齡大於
     * @param age 年齡
     * @return 年齡大於spec
     */
    public static Specification<Employee> ageGreaterThan(int age) {
        return (root, query, builder) ->
                builder.greaterThan(root.get("age"), age);
    }

    // getters and setters
    // hashCode() and equals()
}

把範例中的EmployeeDao.findByNameAndAgeGreaterThan(String name, int age)的固定條件改為動態條件如下。當輸入的name或age參數為空時不加入判斷，透過組合不同的Specification來達成動態查詢。。

EmployeeDao

package com.abc.demo.dao;

import com.abc.demo.entity.Employee;
import com.abc.demo.repository.EmployeeRepository;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class EmployeeDao {

    @Autowired
    private EmployeeRepository employeeRepository;

    public List<Employee> findByNameAndAgeGreaterThan(String name, Integer age) {

        Specification<Employee> spec = Specification.where(null);
        if (StringUtils.isNotEmpty(name)) {
            spec = spec.and(Employee.nameEquals(name));
        }
        if (age != null) {
            spec = spec.and(Employee.ageGreaterThan(age));
        }

        return employeeRepository.findAll(spec);
    }
}

測試

EMPLOYEE資料表目前有以下資料。

+----+---------------+------+-----+
| ID | DEPARTMENT_ID | NAME | AGE |
+----+---------------+------+-----+
|  1 |             1 | John |  22 |
|  2 |             1 | Mary |  25 |
|  3 |             2 | Andy |  33 |
+----+---------------+------+-----+

在EmployeeDaoTests測試EmployeeDao.findByNameAndAgeGreaterThan(String name, Integer age)輸入不同參數的效果。

EmployeeDaoTests

package com.abc.demo.dao;

import com.abc.demo.entity.Employee;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
public class EmployeeDaoTests {

    @Autowired
    private EmployeeDao employeeDao;

    @Test
    public void findByNameAndAgeGreaterThan_nameIsEmptyAndAgeIsNull() {
        // select employee0_.id as id1_1_, employee0_.age as age2_1_, employee0_.department_id as departme3_1_, employee0_.name as name4_1_ from employee employee0_
        List<Employee> employeeList = employeeDao.findByNameAndAgeGreaterThan("", null);
        Assertions.assertEquals(3, employeeList.size());
    }

    @Test
    public void findByNameAndAgeGreaterThan_nameIsEmpty() {
        // select employee0_.id as id1_1_, employee0_.age as age2_1_, employee0_.department_id as departme3_1_, employee0_.name as name4_1_ from employee employee0_ where employee0_.age>30
        List<Employee> employeeList = employeeDao.findByNameAndAgeGreaterThan("", 30);
        Assertions.assertEquals(1, employeeList.size());
    }

    @Test
    public void findByNameAndAgeGreaterThan_ageIsNull() {
        // select employee0_.id as id1_1_, employee0_.age as age2_1_, employee0_.department_id as departme3_1_, employee0_.name as name4_1_ from employee employee0_ where employee0_.name=?
        List<Employee> employeeList = employeeDao.findByNameAndAgeGreaterThan("John", null);
        Assertions.assertEquals(1, employeeList.size());
    }

    @Test
    public void findByNameAndAgeGreaterThan_nameNotEmptyAndAgeNotNull() {
        // select employee0_.id as id1_1_, employee0_.age as age2_1_, employee0_.department_id as departme3_1_, employee0_.name as name4_1_ from employee employee0_ where employee0_.age>20 and employee0_.name=?
        List<Employee> employeeList = employeeDao.findByNameAndAgeGreaterThan("Andy", 20);
        Assertions.assertEquals(1, employeeList.size());
    }
}

參考github。

• Spring Data JPA - Reference Documentation - Specifications
• Stack Overflow - How to initialize Specification of Spring Data JPA?