w88官网网址w88官网网址


w88.com优德官网

MongoDB动态条件之分页查询

一、使用QueryByExampleExecutor

1. 继承MongoRepository

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

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;}

缺点:

不支持过滤条件分组。即不支持过滤条件用 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;}

 

实现二:使用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;}

缺点:

不支持返回固定字段

三、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;

 

四、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;}

 

 

欢迎阅读本文章: 何鑫

优德88客服

w88.com优德官网