MongoDB动态条件之分页查询

转自https://www.cnblogs.com/wslook/p/9275861.html

1. 继承MongoRepository

public interface StudentRepository extends MongoRepository<Student, String> {
    
}

• 1
• 2
• 3
• 4

2. 代码实现

• 使用ExampleMatcher匹配器-----只支持字符串的模糊查询，其他类型是完全匹配
• Example封装实体类和匹配器
• 使用QueryByExampleExecutor接口中的findAll方法

  public Page<Student> getListWithExample(StudentReqVO studentReqVO) {
      Sort sort = Sort.by(Sort.Direction.DESC, "createTime");
      Pageable pageable = PageRequest.of(studentReqVO.getPageNum(), studentReqVO.getPageSize(), sort);
  
      Student student = new Student();
      BeanUtils.copyProperties(studentReqVO, student);
  
      //创建匹配器，即如何使用查询条件
      ExampleMatcher matcher = ExampleMatcher.matching() //构建对象
              .withStringMatcher(ExampleMatcher.StringMatcher.CONTAINING) //改变默认字符串匹配方式：模糊查询
              .withIgnoreCase(true) //改变默认大小写忽略方式：忽略大小写
              .withMatcher("name", ExampleMatcher.GenericPropertyMatchers.contains()) //采用“包含匹配”的方式查询
              .withIgnorePaths("pageNum", "pageSize");  //忽略属性，不参与查询
  
      //创建实例
      Example<Student> example = Example.of(student, matcher);
      Page<Student> students = studentRepository.findAll(example, pageable);
  
      return students;
  }
  

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21

  缺点：

• 不支持过滤条件分组。即不支持过滤条件用 or(或) 来连接，所有的过滤条件，都是简单一层的用 and(并且) 连接
• 不支持两个值的范围查询，如时间范围的查询

二、MongoTemplate结合Query

实现一：使用Criteria封装查询条件

public Page<Student> getListWithCriteria(StudentReqVO studentReqVO) {
    Sort sort = Sort.by(Sort.Direction.DESC, "createTime");
    Pageable pageable = PageRequest.of(studentReqVO.getPageNum(), studentReqVO.getPageSize(), sort);

    Query query = new Query();

    //动态拼接查询条件
    if (!StringUtils.isEmpty(studentReqVO.getName())){
        Pattern pattern = Pattern.compile("^.*" + studentReqVO.getName() + ".*$", Pattern.CASE_INSENSITIVE);
        query.addCriteria(Criteria.where("name").regex(pattern));
    }

    if (studentReqVO.getSex() != null){
        query.addCriteria(Criteria.where("sex").is(studentReqVO.getSex()));
    }
    if (studentReqVO.getCreateTime() != null){
        query.addCriteria(Criteria.where("createTime").lte(studentReqVO.getCreateTime()));
    }

    //计算总数
    long total = mongoTemplate.count(query, Student.class);

    //查询结果集
    List<Student> studentList = mongoTemplate.find(query.with(pageable), Student.class);
    Page<Student> studentPage = new PageImpl(studentList, pageable, total);
    return studentPage;
}

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28

实现二：使用Example和Criteria封装查询条件

public Page<Student> getListWithExampleAndCriteria(StudentReqVO studentReqVO) {
    Sort sort = Sort.by(Sort.Direction.DESC, "createTime");
    Pageable pageable = PageRequest.of(studentReqVO.getPageNum(), studentReqVO.getPageSize(), sort);

    Student student = new Student();
    BeanUtils.copyProperties(studentReqVO, student);

    //创建匹配器，即如何使用查询条件
    ExampleMatcher matcher = ExampleMatcher.matching() //构建对象
            .withStringMatcher(ExampleMatcher.StringMatcher.CONTAINING) //改变默认字符串匹配方式：模糊查询
            .withIgnoreCase(true) //改变默认大小写忽略方式：忽略大小写
            .withMatcher("name", ExampleMatcher.GenericPropertyMatchers.contains()) //标题采用“包含匹配”的方式查询
            .withIgnorePaths("pageNum", "pageSize");  //忽略属性，不参与查询

    //创建实例
    Example<Student> example = Example.of(student, matcher);
    Query query = new Query(Criteria.byExample(example));
    if (studentReqVO.getCreateTime() != null){
        query.addCriteria(Criteria.where("createTime").lte(studentReqVO.getCreateTime()));
    }

    //计算总数
    long total = mongoTemplate.count(query, Student.class);

    //查询结果集
    List<Student> studentList = mongoTemplate.find(query.with(pageable), Student.class);
    Page<Student> studentPage = new PageImpl(studentList, pageable, total);
    return studentPage;
}

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30

缺点：

• 不支持返回固定字段

三、MongoTemplate结合BasicQuery

• BasicQuery是Query的子类
• 支持返回固定字段
• 这种方式查询速度很快，建议使用

• public Page<Student> getListWithBasicQuery(StudentReqVO studentReqVO) {
      Sort sort = Sort.by(Sort.Direction.DESC, "createTime");
      Pageable pageable = PageRequest.of(studentReqVO.getPageNum(), studentReqVO.getPageSize(), sort);
  
      QueryBuilder queryBuilder = new QueryBuilder();
  
      //动态拼接查询条件
      if (!StringUtils.isEmpty(studentReqVO.getName())) {
          Pattern pattern = Pattern.compile("^.*" + studentReqVO.getName() + ".*$", Pattern.CASE_INSENSITIVE);
          queryBuilder.and("name").regex(pattern);
      }
  
      if (studentReqVO.getSex() != null) {
          queryBuilder.and("sex").is(studentReqVO.getSex());
      }
      if (studentReqVO.getCreateTime() != null) {
          queryBuilder.and("createTime").lessThanEquals(studentReqVO.getCreateTime());
      }
  
      Query query = new BasicQuery(queryBuilder.get().toString());
      //计算总数
      long total = mongoTemplate.count(query, Student.class);
  
      //查询结果集条件
      BasicDBObject fieldsObject = new BasicDBObject();
      //id默认有值，可不指定
      fieldsObject.append("id", 1)    //1查询，返回数据中有值；0不查询，无值
                  .append("name", 1);
      query = new BasicQuery(queryBuilder.get().toString(), fieldsObject.toJson());
  
      //查询结果集
      List<Student> studentList = mongoTemplate.find(query.with(pageable), Student.class);
      Page<Student> studentPage = new PageImpl(studentList, pageable, total);
      return studentPage;
  }
  

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36

  四、MongoTemplate结合Aggregation

• 使用Aggregation聚合查询
• 支持返回固定字段
• 支持分组计算总数、求和、平均值、最大值、最小值等等
• 注：这个方法数据量大的时候查询很慢，他应该是先全部查询出来一个集合，然后在分页

• public Page<Student> getListWithAggregation(StudentReqVO studentReqVO) {
      Sort sort = Sort.by(Sort.Direction.DESC, "createTime");
      Pageable pageable = PageRequest.of(studentReqVO.getPageNum(), studentReqVO.getPageSize(), sort);
  
      Integer pageNum = studentReqVO.getPageNum();
      Integer pageSize = studentReqVO.getPageSize();
  
      List<AggregationOperation> operations = new ArrayList<>();
      if (!StringUtils.isEmpty(studentReqVO.getName())) {
          Pattern pattern = Pattern.compile("^.*" + studentReqVO.getName() + ".*$", Pattern.CASE_INSENSITIVE);
          Criteria criteria = Criteria.where("name").regex(pattern);
          operations.add(Aggregation.match(criteria));
      }
      if (null != studentReqVO.getSex()) {
          operations.add(Aggregation.match(Criteria.where("sex").is(studentReqVO.getSex())));
      }
      long totalCount = 0;
      //获取满足添加的总页数
      if (null != operations && operations.size() > 0) {
          Aggregation aggregationCount = Aggregation.newAggregation(operations);  //operations为空，会报错
          AggregationResults<Student> resultsCount = mongoTemplate.aggregate(aggregationCount, "student", Student.class);
          totalCount = resultsCount.getMappedResults().size();
      } else {
          List<Student> list = mongoTemplate.findAll(Student.class);
          totalCount = list.size();
      }
  
      operations.add(Aggregation.skip((long) pageNum * pageSize));
      operations.add(Aggregation.limit(pageSize));
      operations.add(Aggregation.sort(Sort.Direction.DESC, "createTime"));
      Aggregation aggregation = Aggregation.newAggregation(operations);
      AggregationResults<Student> results = mongoTemplate.aggregate(aggregation, "student", Student.class);
  
      //查询结果集
      Page<Student> studentPage = new PageImpl(results.getMappedResults(), pageable, totalCount);
      return studentPage;
  }