spring data jpa 动态查询(mysql)

cankao : https://stackoverflow.com/questions/45973070/spring-jpa-examplematcher-compare-date-condition

场景: 动态条件查询, (主要解决时间问题)

repository

public interface TranxlogRepository extends JpaRepository<Tranxlog, Long>, JpaSpecificationExecutor<Tranxlog>{ 
}

 

serviceImp:

public Specification<TranxLog> getSpecFromDatesAndExample(
  LocalDateTime from, LocalDateTime to, Example<TranxLog> example) {

    return (Specification<TranxLog>) (root, query, builder) -> {
         final List<Predicate> predicates = new ArrayList<>();

         if (from != null) {
            predicates.add(builder.greaterThan(root.get("dateField"), from));
         }
         if (to != null) {
            predicates.add(builder.lessThan(root.get("dateField"), to));
         }
         predicates.add(QueryByExamplePredicateBuilder.getPredicate(root, builder, example));

         return builder.and(predicates.toArray(new Predicate[predicates.size()]));
    }
};

..

public Page<TranxLog> findAllByConditions(TranxReportFormModel formModel, Pageable page) {
    ExampleMatcher matcher = ExampleMatcher.matching()
            .withNullHandler(ExampleMatcher.NullHandler.IGNORE)
            .withStringMatcher(ExampleMatcher.StringMatcher.CONTAINING)
            .withIgnoreCase()
            .withIgnoreNullValues();
    Example<TranxLog> example = Example.of(formModel.getTranxLog(), matcher);
    return tranxlogRepository.findAll(getSpecFromDatesAndExample(from, to, Example.of(formModel.getTranxLog(), matcher)), page);
}

 

方式2：

 

    select
        exchangera0_.currency as currency3_1_,
        exchangera0_.effectiveFromLoc as effectiv4_1_,
        exchangera0_.effectiveToLoc as effectiv5_1_,
        exchangera0_.exchangeRate as exchange6_1_,
        exchangera0_.localCountry as localcou7_1_,
        exchangera0_.localCurrency as localcur8_1_,
        exchangera0_.rateType as ratetype9_1_ 
    from
        ExchangeRate exchangera0_ 
    where
        exchangera0_.rateType=? 
        and exchangera0_.effectiveFromLoc<=? 
        and (
            exchangera0_.currency in (
                ? , ?
            )
        ) 
        and (
            exchangera0_.effectiveToLoc>=? 
            or exchangera0_.effectiveToLoc is null
        )

 

 

 

实现：

 

    private List<ExchangeRatePO> queryRateDataByDate(String effectiveDate, List<String> currency) {
        
        Specification<ExchangeRatePO> specification = (Specification<ExchangeRatePO>) (root, criteriaQuery, criteriaBuilder) -> {
            List<Predicate> predicates = new ArrayList<>();
            predicates.add(criteriaBuilder.equal(root.get("rateType"), "Sea"));
            predicates.add(criteriaBuilder.lessThanOrEqualTo(root.get("effectiveFromLoc"),effectiveDate));
//            predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get("effectiveToLoc"),effectiveDate));
            //process for effectiveToLoc
            List<Predicate> orPredicatesForEffectiveToLoc = new ArrayList<>(); //or 条件的 一定要放在一个集合中
            orPredicatesForEffectiveToLoc.add(criteriaBuilder.greaterThanOrEqualTo(root.get("effectiveToLoc"),effectiveDate));
            orPredicatesForEffectiveToLoc.add(criteriaBuilder.equal(root.get("effectiveToLoc"), criteriaBuilder.nullLiteral(String.class)));
            
            Path<Object> path = root.get("currency");
            CriteriaBuilder.In<Object> in = criteriaBuilder.in(path);
            in.value(currency);
            predicates.add(in); 
            predicates.add(criteriaBuilder.or(orPredicatesForEffectiveToLoc.toArray(new Predicate[orPredicatesForEffectiveToLoc.size()])));
            return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
        };
        return exchangeRateRepository.findAll(specification);