JPA「ページング」「AND」「OR」「サブクエリ」

//            
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 specification = new Specification() {

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

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

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


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

        predicatesAndList.add(predicateFreeCouponId);


        if(!StringUtils.isEmpty(selectFreeCouponByFreeCouponIdParam.getCondition()))
        {
            //   
            Subquery subquery = criteriaQuery.subquery(ThirdStoreInfo.class);

            Root 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 example = new Specification() {

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

                List 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();
            }
};