網頁

▼

2021/6/17

Spring Data JPA Criteria 查詢簡單範例

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 則留言: