mongo page query based on conditions
一、MongoTemplate 中 Aggregation 应用
- 使用Aggregation聚合查询
- 支持返回固定字段
- 支持分组计算(count)总数、(sum)求和、(avg)平均值、(max)最大值、(min)最小值等
public Page<Student> getListWithAggregation(StudentVO studentVO) { Sort sort = Sort.by(Sort.Direction.DESC, "createTime"); Pageable pageable = PageRequest.of(studentVO.getPageNum(), studentVO.getPageSize(), sort); Integer pageNum = studentVO.getPageNum(); Integer pageSize = studentVO.getPageSize(); List<AggregationOperation> operations = new ArrayList<>(); if (!StringUtils.isEmpty(studentVO.getName())) { Pattern pattern = Pattern.compile("^.*" + studentVO.getName() + ".*$", Pattern.CASE_INSENSITIVE); Criteria criteria = Criteria.where("name").regex(pattern);
operations.add(Aggregation.match(criteria)); } if (null != studentVO.getSex()) {
operations.add(Aggregation.match(Criteria.where("sex").is(studentVO.getSex())));
}
//获取满足添加的总页数
long totalCount = 0; if (null != operations && operations.size() > 0) {
Aggregation aggregationCount = Aggregation.newAggregation(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;
}
二、MongoTemplate 结合 BasicQuery 的集合应用
public Page<Student> getListWithBasicQuery(StudentVO studentVO) {
// 排序 Sort sort = Sort.by(Sort.Direction.DESC, "createTime"); Pageable pageable = PageRequest.of(studentVO.getPageNum(), studentVO.getPageSize(), sort); QueryBuilder queryBuilder = new QueryBuilder(); if (!StringUtils.isEmpty(studentVO.getName())) {
// 模糊查询 Pattern pattern = Pattern.compile("^.*" + studentVO.getName() + ".*$", Pattern.CASE_INSENSITIVE); queryBuilder.and("name").regex(pattern);
} if (studentVO.getSex() != null) {
queryBuilder.and("sex").is(studentVO.getSex()); } if (studentVO.getCreateTime() != null) {
queryBuilder.and("createTime").lessThanEquals(studentVO.getCreateTime()); } Query query = new BasicQuery(queryBuilder.get().toString());
//计算总数
long total = mongoTemplate.count(query, Student.class);
//查询结果集条件
BasicDBObject fieldsObject = new BasicDBObject();
fieldsObject.append("id", 1).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; }
三、MongoTemplate结合Example和Criteria 的集合查询应用
public Page<Student> getListWithExampleAndCriteria(StudentVO studentVO) { Sort sort = Sort.by(Sort.Direction.DESC, "createTime"); Pageable pageable = PageRequest.of(studentVO.getPageNum(), studentVO.getPageSize(), sort); Student student = new Student(); BeanUtils.copyProperties(studentVO, 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 (studentVO.getCreateTime() != null){
query.addCriteria(Criteria.where("createTime").lte(studentVO.getCreateTime()));
} //计算总数 long total = mongoTemplate.count(query, Student.class); //查询结果
List<Student> studentList = mongoTemplate.find(query.with(pageable), Student.class); Page<Student> stuPageList = new PageImpl(studentList, pageable, total);
return stuPageList; }
四、MongoTemplate结合Query 的集合查询应用
public Page<Student> getListWithCriteria(StudentVO studentVO) { Sort sort = Sort.by(Sort.Direction.DESC, "createTime"); Pageable pageable = PageRequest.of(studentVO.getPageNum(), studentVO.getPageSize(), sort); Query query = new Query(); if (!StringUtils.isEmpty(studentVO.getName())){ Pattern pattern = Pattern.compile("^.*" + studentVO.getName() + ".*$", Pattern.CASE_INSENSITIVE);
query.addCriteria(Criteria.where("name").regex(pattern)); } if (studentVO.getSex() != null){
query.addCriteria(Criteria.where("sex").is(studentVO.getSex())); } if (studentVO.getCreateTime() != null){
query.addCriteria(Criteria.where("createTime").lte(studentVO.getCreateTime()));
} //计算总数 long total = mongoTemplate.count(query, Student.class); //查询结果集 List<Student> studentList = mongoTemplate.find(query.with(pageable), Student.class); Page<Student> stuPageList = new PageImpl(studentList, pageable, total);
return stuPageList; }
五、MongoTemplate结合 ExampleMatcher 的集合查询应用
- 使用ExampleMatcher匹配器-----只支持字符串的模糊查询,其他类型是完全匹配
- Example封装实体类和匹配器
- 使用ExampleExecutor 接口中的findAll方法
public Page<Student> getListWithExample(StudentVO studentVO) { Sort sort = Sort.by(Sort.Direction.DESC, "createTime"); Pageable pageable = PageRequest.of(studentVO.getPageNum(), studentVO.getPageSize(), sort); Student student = new Student(); BeanUtils.copyProperties(studentVO, 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;
}