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。
3 則留言:
最近我也在玩spring boot,前陣子有看了些JPA資訊,有個部份滿好奇的,JPA對於查詢語法的JOIN、子查詢支援性如何?
就我目前了解JPA是介面層,底層應該是MyBatis(我沒記錯的話…),另外有一套是Hibernate,兩種各有各的優缺點,但就JPA部份我沒有使用與實作過,只有看過一些簡單範例。
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)
對啊,好久沒來留言了,最近有看你發的幾篇jpa文章,非常謝謝你提供資訊以及相關連結。
張貼留言