spring-data-jpa Specification构建动态sql

1.持久层继承JpaSpecificationExecutor<T>

@Repository
public interface DailyBillingRepository extends PagingAndSortingRepository<DailyBilling, Long>, JpaSpecificationExecutor<DailyBilling> {

}

2.JpaSpecificationExecutor 提供的方法：

public interface JpaSpecificationExecutor<T> {
 
    T findOne(Specification<T> spec);
 
    List<T> findAll(Specification<T> spec);
 
    Page<T> findAll(Specification<T> spec, Pageable pageable);
 
    List<T> findAll(Specification<T> spec, Sort sort);
 
    long count(Specification<T> spec);
}

调用 ：

 public Page<DailyBilling> getList(Integer page, Integer rows,String beginTime,String endTime,String result){
        //Specification创建动态sql
        //Root<ExportSubsidyPersonnel> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder
        Specification<DailyBilling> specification= (root, query, cb) -> {
            Predicate predicate = cb.conjunction();
 
            if (StringHelper.isNotBlank(beginTime) && StringHelper.isNotBlank(endTime)){
                // sql ==》 billData between beginTime and endTime
                predicate.getExpressions().add(
                        cb.between(root.<Date>get("billData"), DateHelper.getDate(beginTime), DateHelper.getDate(endTime))
                );
            }
 
            if (StringHelper.isNotBlank(result) && !result.equals("2")){
                // sql ==》 differenceAmount = 0  或者 differenceAmount <> 0
                Predicate p = null;
                if ("1".equals(result)){
                    p = cb.equal(root.<BigDecimal>get("differenceAmount"),BigDecimal.ZERO);
                }
                if ("0".equals(result)){
                    p = cb.notEqual(root.<BigDecimal>get("differenceAmount"),BigDecimal.ZERO);
                }
                predicate.getExpressions().add(p);
            }
            return predicate;
        };
 
        return dailyBillingRepository.findAll(specification,new PageRequest(page,rows));
    }

CriteriaBuilder接口提供了操作sql的基本语句，可直接调用。