mongoTemplate的复杂组装条件查询

mongoTemplate不像SQL那么灵活，组装条件较为复杂。
如下演示了查询类似于
AND name = ‘张三’ OR age = 12 NOT birthday > 2024-12-31
这类结构的代码示例。

脑子里的范围图：

所有的AND锁定一个范围，再跟所有的OR组成的范围取并集，再剔除所有NOT的范围。

所以

如果没有AND，则表示全范围，那么OR也失去了意义，这时候只看NOT

想象一个场景帮助理解

我是一个女的，找对象的标准是必须是身高一米八以上的（AND），如果他身价一亿以上，那么不到一米八也可以（OR），不过再怎么样，脾气差的我不要（NOT）

以下是具体代码：

1.前端传参封装Req：

@Data
class CaseLibrarySearchReq {private Integer page;private Integer size;// 条件组合列表private List<SearchFilterItem> searchFilterItems;
}@Data
class SearchFilterItem {// 过滤项名字private String filterName;// 过滤项值private Object filterValue;// 逻辑连接词private String logicWord;
}

2.mongoTemplate的复杂组装条件查询：

/*** mongodb mongoTemplate的复杂组装条件查询*/
public Map<String, Object> search(CaseLibrarySearchReq caseLibrarySearchReq) {Map<String, Object> data = new HashMap<>();Integer page = caseLibrarySearchReq.getPage();Integer size = caseLibrarySearchReq.getSize();data.put("page", page);data.put("size", size);List<SearchFilterItem> searchFilterItems = caseLibrarySearchReq.getSearchFilterItems();// 必须要满足的条件List<Criteria> andCriteriaList = ListUtil.toList();// 可选的条件List<Criteria> orCriteriaList = ListUtil.toList();// 要排除的条件List<Criteria> notCriteriaList = ListUtil.toList();// 过滤出有效的搜索项，再根据逻辑关键词分组Map<String, List<SearchFilterItem>> validFilterItemsMap = searchFilterItems.stream().filter(item -> item.getFilterValue() != null && StrUtil.isNotBlank(item.getFilterValue().toString())).collect(Collectors.groupingBy(SearchFilterItem::getLogicWord));// 组装过滤项for (Map.Entry<String, List<SearchFilterItem>> entry : validFilterItemsMap.entrySet()) {String logicWord = entry.getKey();List<SearchFilterItem> validFilterItems = entry.getValue();for (SearchFilterItem item : validFilterItems) {String filterName = item.getFilterName();String filterValueStr = item.getFilterValue().toString();// 针对每种字段单独写处理逻辑switch (filterName) {case "case_number":// 精确匹配的字符串处理Criteria caseNumberCriteria = Criteria.where("case_number").is(filterValueStr);if ("AND".equals(logicWord)) {andCriteriaList.add(caseNumberCriteria);} else if ("OR".equals(logicWord)) {orCriteriaList.add(caseNumberCriteria);} else if ("NOT".equals(logicWord)) {notCriteriaList.add(caseNumberCriteria);}break;case "case_name":// 模糊匹配的字符串处理Pattern pattern1 = Pattern.compile("^.*" + filterValueStr + ".*$", Pattern.CASE_INSENSITIVE);Criteria caseNameCriteria = Criteria.where("case_name").regex(pattern1);if ("AND".equals(logicWord)) {andCriteriaList.add(caseNameCriteria);} else if ("OR".equals(logicWord)) {orCriteriaList.add(caseNameCriteria);} else if ("NOT".equals(logicWord)) {notCriteriaList.add(caseNameCriteria);}break;case "public_date":// 日期类型处理String[] split = filterValueStr.split(",");String startDate = split[0];String endDate = split[1];Criteria publicDateCriteria = new Criteria().and("public_date").gte(startDate).lte(endDate);if ("AND".equals(logicWord)) {andCriteriaList.add(publicDateCriteria);} else if ("OR".equals(logicWord)) {orCriteriaList.add(publicDateCriteria);} else if ("NOT".equals(logicWord)) {notCriteriaList.add(publicDateCriteria);}break;default:break;}}}// 至此，过滤项分类组装完毕Criteria finalCriteria = new Criteria();Criteria andOrcriteria;// 如果AND为空，则只看NOTif (CollUtil.isEmpty(andCriteriaList)) {if (CollUtil.isNotEmpty(notCriteriaList)) {finalCriteria = new Criteria().norOperator(notCriteriaList);// 上一行可能会报错，也许跟版本有关，参考下一行调试一下。下面的报错也是一样// finalCriteria = new Criteria().norOperator(notCriteriaList.toArray(new Criteria[notCriteriaList.size()]));}} else {// 组装and 和orList<Criteria> andOrCriteriaList = ListUtil.toList();andOrCriteriaList.add(new Criteria().andOperator(andCriteriaList));if (CollUtil.isNotEmpty(orCriteriaList)) {andOrCriteriaList.add(new Criteria().orOperator(orCriteriaList));}// 把and 和not合并andOrcriteria = new Criteria().orOperator(andOrCriteriaList);// 如果 notCriteriaList 不为空if (CollUtil.isNotEmpty(notCriteriaList)) {finalCriteria = new Criteria().andOperator(andOrcriteria,new Criteria().norOperator(notCriteriaList));} else {finalCriteria = andOrcriteria;}}Query queryCount = new Query();queryCount.addCriteria(finalCriteria);// 异步任务1 (查询数量，因为mongoTemplate的分页无法返回总数)CompletableFuture<Void> task1 = this.queryTotalCount(data, queryCount);// 分页参数     注意分页时，页码数是从0开始，所以要-1PageRequest pageRequest = PageRequest.of(page - 1, size);Query queryList = new Query();queryList.addCriteria(finalCriteria);queryList.with(pageRequest);// 排序字段Sort sort = Sort.by(Sort.Direction.DESC, "public_date");queryList.with(sort);// 异步任务2    （查询分页列表）CompletableFuture<Void> task2 = this.querySearchList(data, queryList);// 等待所有任务执行完成CompletableFuture.allOf(task1, task2).join();return data;
}

3.计算count：

/*** 计算count*/
public CompletableFuture<Void> caseLibraryCount(Map<String, Object> data, Query query) {return CompletableFuture.runAsync(() -> {try {long total = mongoTemplate.count(query, CaseLibrarySearchVO.class, "case_library");data.put("total", total);} catch (Exception e) {log.error("计算count失败", e);}});
}

4.查询列表：

/*** 查询列表*/
public CompletableFuture<Void> caseLibrarySearchList(Map<String, Object> data, Query query) {return CompletableFuture.runAsync(() -> {try {// 执行分页查询List<CaseLibrarySearchVO> caseLibraryEntityList = mongoTemplate.find(query, CaseLibrarySearchVO.class, "case_library");data.put("caseLibraryEntityList", caseLibraryEntityList);} catch (Exception e) {log.error("查询列表失败", e);}});
}