MongoTemplate之分组分页复合条件查询

た入场券 2022-05-17 11:05 836阅读 0赞

ongodbsql 文档地址: https://docs.mongodb.com/manual/reference/operator/update/

废话不多说,直接上代码

情景一:分组查询

public Page<CourseDetail> listCourseDetails(QueryCourseDetailModel queryModel) {
    
            //条件一 where gradeId in (集合) and mark=true
            Criteria criteria = Criteria.where("gradeId").in(gradeIds).and("mark").is(true);
    
            if (null != status && status.size() > 0) {
                Criteria criteriaChild = new Criteria();
                //条件二  criteriaChild 条件查询 ( teacherCommentStatus = XX or parentsReplyTeacherStatus =XX  )
                criteriaChild.orOperator(Criteria.where("teacherCommentStatus").in(status), Criteria.where("parentsReplyTeacherStatus").is(replayStatus));
            }
    
            //拼接最后语句 where gradeId in (集合) and mark=true  and ( teacherCommentStatus = XX or parentsReplyTeacherStatus =XX  )
            criteria.andOperator(criteriaChild);
    
            //分组条件
            GroupBy groupBy = new GroupBy("gradeId")
                    .initialDocument("{ count: 0 }")
                    .reduceFunction("function (doc,pre){pre.count +=1 ;}");
    
            //使用 mongoTemplate.group 分组查询
            GroupByResults groupByResults = mongoTemplate.
                    group(criteria, "homework", groupBy, Homework.class);
    
            //获取结果
            BasicDBList list = (BasicDBList) groupByResults.getRawResults().get("retval");
            list.stream().map(map -> {
                BasicDBObject obj = (BasicDBObject) map;
                ......
                return obj;
            }).collect(Collectors.toList());
    
            return list;
    
            }

情景二: 分页查询

public Page<CourseDetail> listCourseDetails(QueryCourseDetailModel queryModel) {
    
            //创建排序模板Sort
            Sort sort = new Sort(Sort.Direction.DESC, "creationDate");
    
            //创建分页模板Pageable 
            Pageable pageable = new PageRequest(queryModel.getPageStart(), queryModel.getPageSize(), sort);
    
            //创建查询条件对象 
            Query query = new Query();
    
            //条件gradeId =XX
            Criteria criteria = Criteria.where("gradeId").is(queryModel.getGradeId());
    
            //条件 and time > XX 
            if (!StringUtil.isEmpty(queryModel.getStartCourseTime())) {
                criteria.and("time").gt(queryModel.getStartCourseTime());
            }
            //条件 and time < XX if(! StringUtil.isEmpty(queryModel.getEndCourseTime())){
            criteria.and("time").lt(queryModel.getEndCourseTime());
    
            query.addCriteria(criteria);
    
            //mongoTemplate.count计算总数 
            long total = mongoTemplate.count(query, CourseDetail.class);
    
            // mongoTemplate.find 查询结果集
            List<CourseDetail> items = mongoTemplate.find(query.with(pageable), CourseDetail.class);
            items.forEach(courseDetail -> {
                courseDetail.setTeacherUnReplaySize(homeworkRepository.countByGradeIdAndParentsReplyTeacherStatusAndCourseStartTimeAndMarkTrue(courseDetail.getGradeId(), PARENTS_REPLY_TEACHER_STATUS.UNANSWERED, courseDetail.getCourseStartTime().getTime()));
            });
            
            return new PageImpl(items, pageable, total);
            
            }