Spring Data JPA使用JPA Criteria API查詢的簡單範例如下。
範例環境:
- Java 8
- Spring Boot 2.3.2.RELEASE
- Spring Data JPA
- H2 database
- Lombok
Employee
為JPA entity類。
Employee
package com.abc.demo.entity;
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;
// getters and setters
// hashCode() and equals()
}
在Spring componentEmployeeDao
類注入EntityManager
實例並使用Criteria API查詢如下。
EmployeeDao
package com.abc.demo.dao;
import com.abc.demo.entity.Employee;
import org.springframework.stereotype.Repository;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.*;
import java.util.List;
@Repository
public class EmployeeDao {
@PersistenceContext
private EntityManager em;
public List<Employee> findByNameAndAgeGreaterThan(String name, int age) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Employee> cq = cb.createQuery(Employee.class); // select employee
Root<Employee> root = cq.from(Employee.class); // from employee
Predicate predName = cb.equal(root.get("name"), name);
Predicate predAge = cb.gt(root.get("age"), age);
cq.where(predName, predAge); // where name = :name and age = :age
Order orderByIdDesc = cb.desc(root.get("id"));
cq.orderBy(orderByIdDesc); // order by id desc
TypedQuery<Employee> tq = em.createQuery(cq);
return tq.getResultList();
}
}
上面從EntityManager.getCriteriaBuilder
取得CriteriaBuilder
來建構CriteriaQuery
。
呼叫CriteriaBuilder.createQuery(Class<T> resultClass)
傳入查詢結果對應的類別參數。
CriteriaQuery.from(Class<X> entityClass)
產生的query Root
相當於JPQL的from
即查詢的起點。
建立查詢條件Predicate
時,以Root.get(String attributeName)
傳入entity欄位名稱取得條件欄位做為CriteriaBuilder
建立where條件的參數。一個Predicate
相當於一where個條件。
簡單整理如下:
EntityManager
:管理entity生命週期及對持久層進行CRUD操作。CriteriaBuilder
:建構各種查詢條件如大於,小於,等於,不等於,like,邏輯運算 and,or,排序 asc,desc等。CriteriaQuery
:組成頂層的查詢語句如select,from,where,order by,group by,having,distinct。Root
:獲取entity的條件欄位。Predicate
:where條件,由CriteriaBuilder
產生。Order
:排序條件,由CriteriaBuilder
產生。
上面criteria query的寫法可稍改成以下比較fluent的寫法。
EmployeeDao
@Repository
public class EmployeeDao {
@PersistenceContext
private EntityManager em;
public List<Employee> findByNameAndAgeGreaterThan(String name, int age) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Employee> cq = cb.createQuery(Employee.class);
Root<Employee> root = cq.from(Employee.class);
cq.select(root)
.where(
cb.and(
cb.equal(root.get("name"), name),
cb.gt(root.get("age"), age)
)
)
.orderBy(cb.asc(root.get("id")));
TypedQuery<Employee> tq = em.createQuery(cq);
return tq.getResultList();
}
}
執行產生的sql如下:
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=?
and employee0_.age>20
order by employee0_.id desc
除了以上用EntityManager
的方式,Spring Data JPA還提供JpaSpecificationExecutor
及Specification
介面可直接以Repository輸入Criteria條件。
在Employee
的Repository介面EmployeeRepository
另外繼承JpaSpecificationExecutor
介面,此介面提供輸入Specification
參數的各查詢方法。
EmployeeRepository
package com.abc.demo.repository;
import com.abc.demo.entity.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;
@Repository
public interface EmployeeRepository extends
JpaRepository<Employee, Long>,
JpaSpecificationExecutor<Employee> {
}
在EmployeeDao
中改用EmployeeRepository.findAll(Specification<T> spec)
輸入Criteria組成的Specification
實例參數來查詢。
EmployeeDao
package com.abc.demo.dao;
import com.abc.demo.entity.Employee;
import com.abc.demo.repository.EmployeeRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Repository;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.util.List;
@Repository
public class EmployeeDao {
@Autowired
private EmployeeRepository employeeRepository;
public List<Employee> findByNameAndAgeGreaterThan(String name, int age) {
return employeeRepository.findAll(new Specification<Employee>() { // provide instance of Specification
@Override
public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {
Predicate predicate = cb.and(
cb.equal(root.get("name"), name),
cb.greaterThan(root.get("age"), age));
cq.orderBy(cb.desc(root.get("id")));
return predicate;
}
});
// lambda
// return employeeRepository.findAll((root, cq, cb) -> {
// Predicate predicate = cb.and(
// cb.equal(root.get("name"), name),
// cb.greaterThan(root.get("age"), age));
// cq.orderBy(cb.desc(root.get("id")));
// return predicate;
// });
}
}
1 則留言:
很棒的講解
張貼留言