//设置分页 和 多属性排序
Sort sort = new Sort(Sort.Direction.DESC, "userFreeCouponGetTime");

Sort sort = new Sort(Sort.Direction.DESC, "userFreeCouponGetTime").and(new Sort(Sort.Direction.ASC, "rangeMileage"));

Pageable pageable = PageRequest.of(Integer.valueOf(xxx.getPageNo()) - 1, Integer.valueOf(xxx.getPageSize()),sort);


//查询条件 freeCouponId 和 condition   (condition不为空时 查询)
//( condition 模糊查询两个字段 USER_ID 和 STORE_NAME ）
//(STORE_NAME 在另一张表不使用及联 使用子查询)

// 原SQL
SELECT xxx FROM FREE_COUPON_VERIFICATION FCV

WHERE 

FCV.FREE_COUPON_ID = #{freeCouponId} 

AND 
( 

    (FCV.USER_ID like CONCAT('%',#{condition},'%') 
    
    OR 
    
    ( EXISTS  (SELECT * FROM STORE S WHERE S.STORE_ID = FCV.STORE_ID AND S.STORE_NAME LIKE CONCAT('%',#{condition},'%') ) 

)


//JPA 实现
Specification<FreeCouponVerificationRecord> specification = new Specification<FreeCouponVerificationRecord>() {

    @Override
    public Predicate toPredicate(Root<FreeCouponVerificationRecord> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {

                //and条件
        List<Predicate> predicatesAndList = new ArrayList<>();

                //or条件
        List<Predicate> predicatesOrList = new ArrayList<>();


        Predicate predicateFreeCouponId = criteriaBuilder.equal(root.get("freeCouponId").as(String.class), selectFreeCouponByFreeCouponIdParam.getFreeCouponId());

        predicatesAndList.add(predicateFreeCouponId);


        if(!StringUtils.isEmpty(selectFreeCouponByFreeCouponIdParam.getCondition()))
        {
            //子查询
            Subquery<ThirdStoreInfo> subquery = criteriaQuery.subquery(ThirdStoreInfo.class);

            Root<ThirdStoreInfo> thirdStoreInfoRoot = subquery.from(ThirdStoreInfo.class);

            //子查询与主语句条件
            Predicate predicateStoreId = criteriaBuilder.equal(thirdStoreInfoRoot.get("storeId").as(String.class), root.get("storeId"));

            Predicate predicateCouponId = criteriaBuilder.like(thirdStoreInfoRoot.get("storeName").as(String.class), "%" + selectFreeCouponByFreeCouponIdParam.getCondition() + "%");

            Predicate where = criteriaBuilder.and(predicateStoreId, predicateCouponId);

            subquery.where(where);


            //子查询 加入条件
            Predicate predicateThirdStoreInfo = criteriaBuilder.exists(subquery.select(thirdStoreInfoRoot));

            predicatesOrList.add(predicateThirdStoreInfo);

            Predicate predicateUserId = criteriaBuilder.like(root.get("userId").as(String.class), "%" + selectFreeCouponByFreeCouponIdParam.getCondition() + "%");

            predicatesOrList.add(predicateUserId);
        }


        //设置 and  和  or

        Predicate[] predicatesOr = new Predicate[predicatesOrList.size()];

        Predicate or = criteriaBuilder.or(predicatesOrList.toArray(predicatesOr));


        Predicate[] predicatesAnd = new Predicate[predicatesAndList.size()];

        Predicate and = criteriaBuilder.and(predicatesAndList.toArray(predicatesAnd));

        //
        if(!StringUtils.isEmpty(selectFreeCouponByFreeCouponIdParam.getCondition()))
        {
            criteriaQuery.where(and,or);
        }
        else
        {
            criteriaQuery.where(and);
        }

        //criteriaQuery.orderBy(criteriaBuilder.desc(root.get("xxxx")));

        return criteriaQuery.getRestriction();
    }
};

普通模糊查询

//设置条件查询
Specification<Province> example = new Specification<Province>() {

            @Override
            public Predicate toPredicate(Root<Province> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {

                List<Predicate> predicatesAndList = new ArrayList<>();

                if (!StringUtils.isEmpty(getProvinceParam.getProvinceName()))
                {
                    Predicate predicateName = criteriaBuilder.like(root.get("provinceName").as(String.class), "%" + getProvinceParam.getProvinceName() + "%");

                    predicatesAndList.add(predicateName);
                }

                Predicate predicateDelFlag = criteriaBuilder.equal(root.get("del_flag").as(String.class), "1");

                predicatesAndList.add(predicateDelFlag);

                Predicate[] predicatesAnd = new Predicate[predicatesAndList.size()];

                Predicate and = criteriaBuilder.and(predicatesAndList.toArray(predicatesAnd));

                criteriaQuery.where(and);

                return criteriaQuery.getRestriction();
            }
};