SpringBoot中使用spring data jpa 的Specification方法封装查询条件

 

最近改为不用手写sql执行语句时,execute执行后,发现用jpa封装查询条件时,尤其是封装分页查询时,更是烦...

 

更是在封装like条件时,业务更奇葩的时,根据所有的字段进行模糊查询....所以写了个方法,感觉很冗余,但还没有找到更好的方法,望看到的大佬批评指正.

    /**
     * @param faceInfoParamEntity
     * @return 根据查询条件查询人脸抓拍库的所有数据
     * @Author Young
     * @Description
     * @Date 13:54 2019/1/11
     **/
    public Future<Page> getFaceInfoAll(FaceInfoParamEntity faceInfoParamEntity, List pids) {
        //封装spection条件
        Specification<IdapFaceResultEntity> querySpecifi = faceResultParam2Specification(faceInfoParamEntity, pids, faceInfoParamEntity.getDevices(), faceInfoParamEntity.getStartTime(), faceInfoParamEntity.getEndTime());
        //根据封装查询条件,数据分页和startTime降序排序
        Page resultAll = faceInfoReponsitory.findAll(querySpecifi, new PageRequest((faceInfoParamEntity.getStart() - 1) / faceInfoParamEntity.getLength(), faceInfoParamEntity.getLength()
            , new Sort(Sort.Direction.DESC, "startTime")));
        return new AsyncResult<>(resultAll);
    }

 

/**
     * @param faceResultParamEntity
     * @param pids                  是null时,则是封装查询目标库的条件;否则是封装查询抓拍库的条件
     * @return org.springframework.data.jpa.domain.Specification
     * @Author Young
     * @Description //封装Face的查询条件
     * @Date 15:40 2019/1/9
     * 条件有:
     * pid:
     * And
     * labels中的所有动态传参的条件查询(labels中字段,字段的type对应数据库字段,value对应值的集合)
     * And
     * 所有search的value值;
     **/
    private Specification faceResultParam2Specification(FaceResultParamEntity faceResultParamEntity, List pids, String[] devices, String startTime, String endTime) {

        Specification<IdapFaceResultEntity> querySpecifi = new Specification<IdapFaceResultEntity>() {
            @Override
            public Predicate toPredicate(Root<IdapFaceResultEntity> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicatesAnd = new ArrayList<>();
                /*
                * 人脸目标库,pid值唯一; 人脸pid是一个数组
                * labels,相同,
                * search 值相同
                *       人脸抓拍库中有start_time的查询区间
                *       人脸抓拍库中有devices的in条件
                *
                * */
                //处理人脸抓拍库
                if (null != pids) {
                    //封装pids in 查询
                    log.debug("封装人脸抓拍库的查询参数...");
                    if (pids.size() > 0) {
                        Expression<String> exp = root.<String>get("pid");
                        predicatesAnd.add(exp.in(pids));
                    }
                    //devices in 查询
                    if (devices.length > 0) {
                        Expression<String> exp = root.<String>get("netbarWacode");
                        predicatesAnd.add(exp.in(devices));
                    }
                    //startTime between 查询
                    if (!"".equals(startTime) && !"".equals(endTime)) {
                        predicatesAnd.add(criteriaBuilder.greaterThanOrEqualTo(root.get("startTime").as(String.class), startTime));
                        predicatesAnd.add(criteriaBuilder.lessThanOrEqualTo(root.get("startTime").as(String.class), endTime));
                    }
                }
                //处理pid的值
                if (null == pids && !"".equals(faceResultParamEntity.getPid())) {
                    log.debug("添加pid的条件查询,pid="+faceResultParamEntity.getPid());
                    predicatesAnd.add(criteriaBuilder.equal(root.get("pid"), faceResultParamEntity.getPid()));
                }
                //处理labels中的值
                int labelsLength = faceResultParamEntity.getLabels().length;
                if (labelsLength != 0) {
                    log.debug("添加labels的条件查询,labels的长度是:"+labelsLength);
                    for (Labels label : faceResultParamEntity.getLabels()) {
                        Expression<String> exp = root.<String>get(label.getType());
                        predicatesAnd.add(exp.in(label.getValue()));
                    }
                }
                Predicate predicateAnd = criteriaBuilder.and(predicatesAnd.toArray(new Predicate[predicatesAnd.size()]));
                //处理search中的值       所有字段进行like模糊查询
                List<Predicate> predicatesOr = new ArrayList<>();
                String likeValue = faceResultParamEntity.getSearch().getValue();
                if (!"".equals(likeValue)) {
                    log.debug("封装模糊查询....");
                    predicatesOr.add(criteriaBuilder.like(root.get(Constants.FACERESULT_SEARCH_AGE), "%" + likeValue + "%"));
                    predicatesOr.add(criteriaBuilder.like(root.get(Constants.FACERESULT_SEARCH_QUALITY_SCORE), "%" + likeValue + "%"));
                    predicatesOr.add(criteriaBuilder.like(root.get(Constants.FACERESULT_SEARCH_NETBAR_WACODE), "%" + likeValue + "%"));
                    predicatesOr.add(criteriaBuilder.like(root.get(Constants.FACERESULT_SEARCH_DEVICE_NAME), "%" + likeValue + "%"));
                    predicatesOr.add(criteriaBuilder.like(root.get(Constants.FACERESULT_SEARCH_SITE_ADDRESS), "%" + likeValue + "%"));
                    predicatesOr.add(criteriaBuilder.like(root.get(Constants.FACERESULT_SEARCH_GENDER), "%" + likeValue + "%"));
                    predicatesOr.add(criteriaBuilder.like(root.get(Constants.FACERESULT_SEARCH_RACE), "%" + likeValue + "%"));
                    predicatesOr.add(criteriaBuilder.like(root.get(Constants.FACERESULT_SEARCH_BEARD), "%" + likeValue + "%"));
                    predicatesOr.add(criteriaBuilder.like(root.get(Constants.FACERESULT_SEARCH_EYE_OPEN), "%" + likeValue + "%"));
                    predicatesOr.add(criteriaBuilder.like(root.get(Constants.FACERESULT_SEARCH_EYE_GLASS), "%" + likeValue + "%"));
                    predicatesOr.add(criteriaBuilder.like(root.get(Constants.FACERESULT_SEARCH_SUN_GLASS), "%" + likeValue + "%"));
                    predicatesOr.add(criteriaBuilder.like(root.get(Constants.FACERESULT_SEARCH_MASK), "%" + likeValue + "%"));
                    predicatesOr.add(criteriaBuilder.like(root.get(Constants.FACERESULT_SEARCH_MOUTH_OPEN), "%" + likeValue + "%"));
                    predicatesOr.add(criteriaBuilder.like(root.get(Constants.FACERESULT_SEARCH_SMILE), "%" + likeValue + "%"));
                    Predicate predicateOr = criteriaBuilder.or(predicatesOr.toArray(new Predicate[predicatesOr.size()]));
                    criteriaBuilder.and(predicateOr);
                }
                return criteriaBuilder.and(predicateAnd);
            }
        };
        return querySpecifi;
    }