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

Dear 丶 2022-04-23 08:56 363阅读 0赞

最近改为不用手写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;
        }