MongoDB動態條件之分頁查詢

標籤：ini   json   HERE   time()   構建   exe   bye   擷取   ext   

一、使用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;
}

 

MongoDB動態條件之分頁查詢