springboot JPA 基本查询和Specification查询

 

0 背景

   springboot JPA提供了查询数据的接口，使用起来简洁且强大。以下主要从基本查询和Specification查询（实现更复杂的查询）两方面展开。

1 基本查询

   1.1  JpaRepository使用

           只需继承JpaRepository接口，便可以使用基本的查询，具体方法可自行查看。

@Repository
public interface AIRepository extends JpaRepository<AI, String>{
}

   1.2 提供的方法（部分）

    <S extends T> S save(S entity);	
    Optional<T> findById(ID id);
    long count();
    void deleteById(ID id);
    List<T> findAll();
    List<T> findAll(Sort sort);
    Page<T> findAll(Pageable pageable);

       除此之外，也可在自己定义的AIRepository接口中创建查询

@Repository
public interface AIRepository extends JpaRepository<AI, String>{

    List<Person> findByEmailAddressAndLastname(EmailAddress emailAddress, String lastname);
    List<Person> findDistinctPeopleByLastnameOrFirstname(String lastname, String firstname);
    List<Person> findByLastnameOrderByFirstnameAsc(String lastname);

    //使用 Pageable, Slice 和 Sort来查询
    Page<User> findByLastname(String lastname, Pageable pageable);

    Slice<User> findByLastname(String lastname, Pageable pageable);

    List<User> findByLastname(String lastname, Sort sort);

    //用Top和First查询限制结果大小
    User findFirstByOrderByLastnameAsc();

    User findTopByOrderByAgeDesc();

    Page<User> queryFirst10ByLastname(String lastname, Pageable pageable);

    Slice<User> findTop3ByLastname(String lastname, Pageable pageable);

    List<User> findFirst10ByLastname(String lastname, Sort sort);
    
    //查询存在
    boolean existsByProjectId(String projectId);

}

2 Specification查询

     2.1 JpaSpecificationExecutor使用

            需要继承JpaSpecificationExecutor接口，具体方法可自行查看。

@Repository
public interface AIRepository extends JpaRepository<AI, String>, JpaSpecificationExecutor<AI> {
}

2.2 提供的方法

    List<T> findAll(@Nullable Specification<T> spec);
    Page<T> findAll(@Nullable Specification<T> spec, Pageable pageable);

2.3 Specification接口

public interface Specification<T> {
  Predicate toPredicate(Root<T> root, CriteriaQuery<?> query,
            CriteriaBuilder builder);
}

public class CustomerSpecs {

  public static Specification<Customer> isLongTermCustomer() {
    return new Specification<Customer>() {
      public Predicate toPredicate(Root<Customer> root, CriteriaQuery<?> query,
            CriteriaBuilder builder) {

         LocalDate date = new LocalDate().minusYears(2);
         return builder.lessThan(root.get(Customer_.createdAt), date);
      }
    };
  }
  //等同于以上
  public static Specification<Customer> isLongTermCustomer() {
    return (root, query, builder) -> {
         LocalDate date = new LocalDate().minusYears(2);
         return builder.lessThan(root.get(Customer_.createdAt), date);
      }
    };
  }

  public static Specification<Customer> hasSalesOfMoreThan(MonetaryAmount value) {
    return new Specification<Customer>() {
      public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query,
            CriteriaBuilder builder) {

         // build query here
      }
    };
  }

}

List<Customer> customers = customerRepository.findAll(isLongTermCustomer());

这里实现一个specification的查询

    //join
    public static Specification<ApprovalRecord> queryProcessRecord(String approvalId) {
        return (root, query, cb) -> {
            Join<ApprovalProcess, Approval> join = root.join(ApprovalRecord_.approvalProcess).join(ApprovalProcess_.approval);
            Predicate idPredicate = cb.equal(join.get(Approval_.id), approvalId);
            Predicate statusPredicate = cb.equal(root.get(ApprovalRecord_.status), ApprovalRecordType.PENDING);
            return cb.and(idPredicate, statusPredicate);
        };
    }
    
    //and
    public static Specification<IssueConclusion> queryConclusionByIssueId(String issueId) {
        return (root, query, cb) -> {
            Predicate ownPredicate = cb.and(cb.equal(root.get(IssueConclusion_.issue).get(Issue_.id), issueId),
                cb.equal(root.get(IssueConclusion_.status), IssueConclusionStatus.WAIT_AUDIT));
            query.where(ownPredicate).orderBy(cb.desc(root.get(IssueConclusion_.lastModifiedDate)));
            return ownPredicate;
//          return query.getRestriction();
        };
    }

    //根据关键字进行查询，按照sort升序返回
    public static Specification<Case> queryCaseList(String queryString){
        return (root, query, cb)->{
            Predicate ownPredicate = cb.equal(root.get(Case_.deleteFlag), false);
            query.distinct(true);
            if (!StringUtils.isEmpty(queryString)){
                Predicate keyWordsPredicate = cb.or(cb.like(root.get(Case_.caseName), '%' + queryString + '%'));
                ownPredicate = cb.and(ownPredicate, keyWordsPredicate);
            }
            query.orderBy(cb.asc(root.get(Case_.caseSort)));
            return ownPredicate;
        };
    }

    // add  实现关键字搜索
    public static Specification<IssueConclusion> queryConclusionByStatus(IssueConclusionStatus status, String queryString) {
        return (root, query, cb) -> {
            List<Predicate> predicates = new ArrayList<>();
            predicates.add(cb.equal(root.get(IssueConclusion_.createdBy), SecurityUtils.getCurrentUserLogin().get()));
            if (status != null) {
                predicates.add(cb.equal(root.get(IssueConclusion_.status), status));
            }
            if (!StringUtils.isEmpty(queryString)) {
                predicates.add(cb.or(cb.like(root.get(IssueConclusion_.id), '%' + queryString + '%')
                    ,cb.like(root.get(IssueConclusion_.issue).get(Issue_.title), '%' + queryString + '%')));
            }
            query.where(predicates.toArray(new Predicate[predicates.size()])).orderBy(cb.desc(root.get(IssueConclusion_.lastModifiedDate)));
   //       return cb.and(predicates.toArray(new Predicate[predicates.size()]))
            return query.getRestriction();
        };
    }