網頁

2021/6/20

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)的固定條件改為動態條件如下。當輸入的nameage參數為空時不加入判斷,透過組合不同的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


3 則留言:

  1. 最近我也在玩spring boot,前陣子有看了些JPA資訊,有個部份滿好奇的,JPA對於查詢語法的JOIN、子查詢支援性如何?

    就我目前了解JPA是介面層,底層應該是MyBatis(我沒記錯的話…),另外有一套是Hibernate,兩種各有各的優缺點,但就JPA部份我沒有使用與實作過,只有看過一些簡單範例。

    回覆刪除
  2. JAVA吉他手好久不見。

    JPA是介面層沒錯,底層的實作主力為Hibernate,還有些小眾沒人用的EclipseLink。MyBatis和JPA沒有關係

    JPA設定好entity的關聯也可以JPQL做join查詢,不過個人認為太複雜的join還是原生來的方便,JPA沒設定好會把不需要的資料都查出來影響效能。總之就是我覺得JPA做join及複雜查詢,子查詢不好用

    而Spring Data JPA是在Hibernate上的一層封裝,如果您在用Spring Boot可直接引入Spring Data JPA進行CRUD操作,有興趣可看我寫得以下幾篇文章:
    1.Spring Boot 2 + Spring Data JPA + MySQL 8 簡單範例(
    https://matthung0807.blogspot.com/2019/05/spring-boot-2-spring-data-jpa-mysql-8.html)
    2.Hibernate JPA 雙向一對多/多對一設定 Bidirectional One To Many / Many To One(https://matthung0807.blogspot.com/2018/06/jpa-bidirectional-onetomanymanytoone.html)
    3.JPA JPQL查詢語法(https://matthung0807.blogspot.com/2018/06/jpa-jpql.html)
    4.Spring Data JPA JPQL JOIN 簡單範例(https://matthung0807.blogspot.com/2020/11/spring-data-jpa-jpql-join.html)

    回覆刪除
  3. 對啊,好久沒來留言了,最近有看你發的幾篇jpa文章,非常謝謝你提供資訊以及相關連結。

    回覆刪除