mybaties-plus实现复杂的多分组多条件且支持多字段和json字段多属性的查询
实现方案说明
- 简单场景使用map集合接收参数，以BaseEntity为例（BaseEntity开源框架中很常见）
@TableField(exist = false)private Map<String, Object> params = new HashMap<>();
- 复杂场景使用嵌套分组方案实现复杂查询，将BaseEntity实体下新增如下字段（新增是为了兼容params）
@TableField(exist = false)private QueryParameter queryParameter;
- 实际上好的框架应该代码优雅并保持绝的的低耦合、通用性和扩展性，所以分三步构建查询条件：
3.1 使用Wrappers.lambdaQuery()构建实体相关的LambdaQueryWrapper实列，以实现基本查询
3.2 使用LambdaQueryWrapper实列构建params的较复杂的支持多字段和json字段多属性的查询语句
3.3 继续使用LambdaQueryWrapper实列构建复杂的多分组多条件且支持多字段和json字段多属性的查询语句
支持常见的操作符（比较运算符和逻辑运算符）
- 在构建查询条件时，常见的操作符（比较运算符和逻辑运算符）可以非常丰富，以下是完整的常用操作符分类整理，本工具实现了其中大部分：
- 基础比较运算符
| 操作符 | SQL 等价 | 说明 | 示例 |
|--------------|-------------------|--------------------------|-------------------------------|
|eq|=| 等于 |age = 18|
|ne|!=或<>| 不等于 |status != 0|
|gt|>| 大于 |price > 100|
|lt|<| 小于 |score < 60|
|ge|>=| 大于等于 |quantity >= 10|
|le|<=| 小于等于 |age <= 30|
- 模糊匹配与字符串操作
| 操作符 | SQL 等价 | 说明 | 示例 |
|----------------|------------------------|--------------------------|-------------------------------|
|like|LIKE| 模糊匹配 |name LIKE '%张%'|
|notLike|NOT LIKE| 模糊不匹配 |title NOT LIKE '%test%'|
|startsWith|LIKE 'value%'| 开头匹配 |code LIKE 'A00%'|
|endsWith|LIKE '%value'| 结尾匹配 |path LIKE '%/home'|
|contains|LIKE '%value%'| 包含（同like） |desc LIKE '%重要%'|
|in|IN (...)| 在列表中 |id IN (1, 2, 3)|
|notIn|NOT IN (...)| 不在列表中 |status NOT IN (0, 1)|
- 空值判断
| 操作符 | SQL 等价 | 说明 | 示例 |
|--------------|-------------------|--------------------------|-------------------------------|
|isNull|IS NULL| 为 NULL |address IS NULL|
|notNull|IS NOT NULL| 不为 NULL |phone IS NOT NULL|
|isEmpty|= ''或IS NULL| 空字符串或 NULL |comment = '' OR comment IS NULL|
|isNotEmpty|!= ''且IS NOT NULL| 非空且不为 NULL |comment != '' AND comment IS NOT NULL|
- 范围查询
| 操作符 | SQL 等价 | 说明 | 示例 |
|--------------|------------------------|--------------------------|-------------------------------|
|between|BETWEEN x AND y| 介于范围 |age BETWEEN 18 AND 30|
|notBetween|NOT BETWEEN x AND y| 不在范围内 |price NOT BETWEEN 50 AND 100|
- 集合操作
| 操作符 | SQL 等价 | 说明 | 示例 |
|--------------|------------------------|--------------------------|-------------------------------|
|in|IN (...)| 在集合内 |id IN (1, 2, 3)|
|notIn|NOT IN (...)| 不在集合内 |category NOT IN ('A', 'B')|
- JSON/数组字段操作
（适用于 MySQL、PostgreSQL 等支持 JSON 的数据库）
| 操作符 | SQL 等价 | 说明 | 示例 |
|----------------|-----------------------------------|--------------------------|-------------------------------|
|jsonContains|JSON_CONTAINS(field, 'value')| JSON 包含值 |tags JSON_CONTAINS '"VIP"'|
|jsonExists|JSON_EXISTS(field, '$.path')| JSON 路径存在 |profile JSON_EXISTS '$.address'|
- 逻辑组合运算符
| 操作符 | 说明 | 示例 |
|--------------|--------------------------|-------------------------------|
|AND| 逻辑与（默认） |status = 1 AND price > 100|
|OR| 逻辑或 |name LIKE '%张%' OR age < 18|
|NOT| 逻辑非 |NOT (deleted = 1)|
- 特殊场景操作符
| 操作符 | 说明 | 示例 |
|----------------|--------------------------|-------------------------------|
|regexp| 正则匹配（数据库支持时） |name REGEXP '^A.*'|
|exists| 子查询存在 |EXISTS (SELECT 1 FROM orders)|
|notExists| 子查询不存在 |NOT EXISTS (SELECT 1 FROM logs)|
- 示例（JSON 请求格式）
{"conditions": [{ "field": "age", "operator": "ge", "value": 18 },{ "field": "name", "operator": "like", "value": "张%" },{ "logic": "OR","conditions": [{ "field": "vip", "operator": "eq", "value": true },{ "field": "points", "operator": "ge", "value": 1000 }]}]
}
简单场景使用map集合接收参数，如BaseEntity.java
package com.xxx.entity;import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.TableField;
import com.fasterxml.jackson.annotation.JsonIgnore;
import com.fasterxml.jackson.annotation.JsonInclude;
import lombok.Data;import java.io.Serial;
import java.io.Serializable;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;/*** Entity基类*/
@Data
public class BaseEntity implements Serializable {@Serialprivate static final long serialVersionUID = 1L;// 其他需求字段/*** 请求参数*/@JsonInclude(JsonInclude.Include.NON_EMPTY)@TableField(exist = false)private Map<String, Object> params = new HashMap<>();/*** 请求参数，支持分组*/@JsonInclude(JsonInclude.Include.NON_EMPTY)@TableField(exist = false)private QueryParameter queryParameter;}
新建一个条件嵌套参数QueryParameter.java
- 该类使用说明：
- 同一级别的多个条件conditions以及多个组groups之间由该级别的logic属性决定连接关系
- 每个组内group的多个子条件group.conditions和子组group.groups被视为平级，由组的group.logic属性决定连接关系
- 嵌套组自动添加括号，非嵌套条件不添加额外括号。如果是OR链接可能会由多个括号，因为OR链接时每个部分都会调用wrapper.or(part)
package com.xxx.entity;import lombok.Data;import java.util.List;@Data
public class QueryParameter {/*** 逻辑操作符（AND/OR/NOT）*/private String logic;/*** 条件列表*/private List<Condition> conditions;/*** 嵌套的逻辑组*/private List<QueryParameter> groups;@Datapublic final static class Condition {/*** 字段名*/private String field;/*** 操作符（eq/ne/gt/ge/lt/le/like等）*/private String operator;/*** 值*/private Object value;}
}
新建LambdaQuery构建工具类MixedQueryWrapper.java
package org.dromara.product.util;import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.dromara.common.mybatis.core.domain.QueryParameter;
import org.springframework.util.CollectionUtils;import java.lang.reflect.Field;
import java.util.*;
import java.util.function.Consumer;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;/*** 查询条件构建工具类* 支持实体字段和JSON属性的混合查询*/
@Slf4j
public class MixedQueryWrapper {private static final Pattern PARAM_PATTERN = Pattern.compile("^(gt|lt|eq|ge|le|contains|like)([A-Z].*)$");private static final Map<String, OperatorProcessor> OPERATOR_HANDLERS = new HashMap<>();private static final Pattern RANGE_PATTERN = Pattern.compile("^(.+)~(.+)$");// 操作符处理器接口@FunctionalInterfaceinterface OperatorProcessor {void apply(LambdaQueryWrapper<?> lqw, String column, Object value) throws IllegalArgumentException;}static {// ========== 基础比较运算符 ==========putOperator("eq", (lqw, c, v) -> lqw.apply(c + " = {0}", v));putOperator("ne", (lqw, c, v) -> lqw.apply(c + " != {0}", v));putOperator("gt", (lqw, c, v) -> lqw.apply(c + " > {0}", v));putOperator("lt", (lqw, c, v) -> lqw.apply(c + " < {0}", v));putOperator("ge", (lqw, c, v) -> lqw.apply(c + " >= {0}", v));putOperator("le", (lqw, c, v) -> lqw.apply(c + " <= {0}", v));// ========== 模糊匹配运算符 ==========putOperator("like", (lqw, c, v) -> lqw.apply(c + " LIKE {0}", "%" + v + "%"));putOperator("notLike", (lqw, c, v) -> lqw.apply(c + " NOT LIKE {0}", "%" + v + "%"));putOperator("startsWith", (lqw, c, v) -> lqw.apply(c + " LIKE {0}", v + "%"));putOperator("endsWith", (lqw, c, v) -> lqw.apply(c + " LIKE {0}", "%" + v));putOperator("contains", (lqw, c, v) -> lqw.apply(c + " LIKE {0}", "%" + v + "%"));// ========== 范围查询运算符 ==========putOperator("between", (lqw, c, v) -> {if (v instanceof String && RANGE_PATTERN.matcher((String) v).matches()) {String[] range = ((String) v).split("~");lqw.apply(c + " BETWEEN {0} AND {1}", range[0], range[1]);} else if (v instanceof Object[] range && range.length == 2) {lqw.apply(c + " BETWEEN {0} AND {1}", range[0], range[1]);} else {throw new IllegalArgumentException("Between operator requires 'min~max' format or 2-element array");}});// ========== 集合运算符 ==========putOperator("in", (lqw, c, v) -> {if (v instanceof Iterable<?> iterable) {String placeholders = String.join(",", Collections.nCopies(size(iterable), "?"));lqw.apply(c + " IN (" + placeholders + ")", toArray(iterable));} else if (v instanceof Object[] array) {String placeholders = String.join(",", Collections.nCopies(array.length, "?"));lqw.apply(c + " IN (" + placeholders + ")", array);} else {throw new IllegalArgumentException("IN operator requires collection or array");}});putOperator("notIn", (lqw, c, v) -> {if (v instanceof Iterable<?> iterable) {String placeholders = String.join(",", Collections.nCopies(size(iterable), "?"));lqw.apply(c + " NOT IN (" + placeholders + ")", toArray(iterable));} else if (v instanceof Object[] array) {String placeholders = String.join(",", Collections.nCopies(array.length, "?"));lqw.apply(c + " NOT IN (" + placeholders + ")", array);} else {throw new IllegalArgumentException("NOT IN operator requires collection or array");}});// ========== 空值判断运算符 ==========putOperator("isNull", (lqw, column, value) -> lqw.apply("{0} IS NULL", column));putOperator("isNotNull", (lqw, column, value) -> lqw.apply("{0} IS NOT NULL", column));putOperator("isEmpty", (lqw, c, v) -> lqw.apply(c + " IS NULL OR " + c + " = ''"));putOperator("isNotEmpty", (lqw, c, v) -> lqw.apply(c + " IS NOT NULL AND " + c + " != ''"));// ========== JSON/特殊类型运算符 ==========putOperator("jsonContains", (lqw, c, v) -> lqw.apply("JSON_CONTAINS(" + c + ", {0})", v));putOperator("jsonExists", (lqw, c, v) -> lqw.apply("JSON_EXISTS(" + c + ", {0})", v));}private static void putOperator(String key, OperatorProcessor processor) {OPERATOR_HANDLERS.put(key.toLowerCase(), processor);// 添加别名switch (key) {case "eq":OPERATOR_HANDLERS.put("=", processor);break;case "ne":OPERATOR_HANDLERS.put("!=", processor);break;case "gt":OPERATOR_HANDLERS.put(">", processor);break;case "lt":OPERATOR_HANDLERS.put("<", processor);break;case "ge":OPERATOR_HANDLERS.put(">=", processor);break;case "le":OPERATOR_HANDLERS.put("<=", processor);break;}}private static int size(Iterable<?> iterable) {if (iterable instanceof Collection) {return ((Collection<?>) iterable).size();}int size = 0;for (Object ignored : iterable) {size++;}return size;}private static Object[] toArray(Iterable<?> iterable) {if (iterable instanceof Collection) {return ((Collection<?>) iterable).toArray();}List<Object> list = new ArrayList<>();for (Object item : iterable) {list.add(item);}return list.toArray();}public static <T> LambdaQueryWrapper<T> buildMixedQueryWrapper(Class<T> entityClass,String jsonFieldName,QueryParameter queryParameter,Set<String> knownPropKeys,LambdaQueryWrapper<T> lqw) {if (queryParameter == null || (CollectionUtils.isEmpty(queryParameter.getConditions())&& CollectionUtils.isEmpty(queryParameter.getGroups()))) {return lqw;}// 处理当前层条件Consumer<LambdaQueryWrapper<T>> currentConsumer = wrapper -> {String currentLogic = StringUtils.defaultIfBlank(queryParameter.getLogic(), "AND");boolean isOrLogic = "OR".equalsIgnoreCase(currentLogic);// 1. 处理直接条件if (!CollectionUtils.isEmpty(queryParameter.getConditions())) {if (isOrLogic) {// OR逻辑时，所有条件用OR连接for (QueryParameter.Condition condition : queryParameter.getConditions()) {wrapper.or(w -> addCondition(w, entityClass, jsonFieldName, condition, knownPropKeys));}} else {// AND逻辑时，直接添加条件for (QueryParameter.Condition condition : queryParameter.getConditions()) {addCondition(wrapper, entityClass, jsonFieldName, condition, knownPropKeys);}}}// 2. 处理嵌套组if (!CollectionUtils.isEmpty(queryParameter.getGroups())) {for (QueryParameter group : queryParameter.getGroups()) {String groupLogic = StringUtils.defaultIfBlank(group.getLogic(), "AND");boolean isGroupOr = "OR".equalsIgnoreCase(groupLogic);if (isOrLogic) {// 当前层是OR逻辑时，每个组用OR连接wrapper.or(groupWrapper ->processGroup(groupWrapper, entityClass, jsonFieldName, group, knownPropKeys, isGroupOr));} else {// 当前层是AND逻辑时，每个组用AND连接wrapper.and(groupWrapper ->processGroup(groupWrapper, entityClass, jsonFieldName, group, knownPropKeys, isGroupOr));}}}};currentConsumer.accept(lqw);return lqw;}private static <T> void processGroup(LambdaQueryWrapper<T> wrapper,Class<T> entityClass,String jsonFieldName,QueryParameter group,Set<String> knownPropKeys,boolean isGroupOr) {// 处理组内条件if (!CollectionUtils.isEmpty(group.getConditions())) {for (QueryParameter.Condition condition : group.getConditions()) {if (isGroupOr) {wrapper.or(w -> addCondition(w, entityClass, jsonFieldName, condition, knownPropKeys));} else {addCondition(wrapper, entityClass, jsonFieldName, condition, knownPropKeys);}}}// 递归处理嵌套组if (!CollectionUtils.isEmpty(group.getGroups())) {for (QueryParameter subGroup : group.getGroups()) {String subLogic = StringUtils.defaultIfBlank(subGroup.getLogic(), "AND");boolean isSubOr = "OR".equalsIgnoreCase(subLogic);if (isGroupOr) {wrapper.or(subWrapper ->processGroup(subWrapper, entityClass, jsonFieldName, subGroup, knownPropKeys, isSubOr));} else {wrapper.and(subWrapper ->processGroup(subWrapper, entityClass, jsonFieldName, subGroup, knownPropKeys, isSubOr));}}}}private static <T> void addCondition(LambdaQueryWrapper<T> wrapper, Class<T> entityClass, String jsonFieldName, QueryParameter.Condition condition, Set<String> knownPropKeys) {Set<String> entityFields = Arrays.stream(entityClass.getDeclaredFields()).map(Field::getName).map(StringUtils::uncapitalize).collect(Collectors.toSet());if (entityFields.contains(condition.getField())) {handleEntityFieldQuery(wrapper, entityClass, condition.getOperator(), condition.getField(), condition.getValue());} else if (knownPropKeys.contains(condition.getField())) {handleJsonPropertyQuery(wrapper, jsonFieldName, condition.getOperator(), condition.getField(), condition.getValue());}}/*** 单条件处理（保持兼容），针对*/public static <T> LambdaQueryWrapper<T> buildMixedQueryWrapper(Class<T> entityClass, String jsonFieldName, Map<String, Object> params, Set<String> knownPropKeys, LambdaQueryWrapper<T> lqw) {// 获取参数Mapif (params == null || params.isEmpty()) {return lqw;}// 获取实体类的所有字段名(小写驼峰)Set<String> entityFields = Arrays.stream(entityClass.getDeclaredFields()).map(Field::getName).map(StringUtils::uncapitalize).collect(Collectors.toSet());// 处理查询参数params.forEach((paramKey, paramValue) -> {if (paramValue == null || StringUtils.isBlank(paramValue.toString())) {return;}Matcher matcher = PARAM_PATTERN.matcher(paramKey);if (matcher.matches()) {String operator = matcher.group(1);String fieldName = StringUtils.uncapitalize(matcher.group(2));// 判断是实体字段还是JSON属性if (entityFields.contains(fieldName)) {handleEntityFieldQuery(lqw, entityClass, operator, fieldName, paramValue);} else if (knownPropKeys.contains(fieldName)) {handleJsonPropertyQuery(lqw, jsonFieldName, operator, fieldName, paramValue);}}});return lqw;}/*** 处理实体字段查询*/private static <T> void handleEntityFieldQuery(LambdaQueryWrapper<T> lqw, Class<T> entityClass, String operator, String fieldName, Object value) {if (StringUtils.isBlank(fieldName) || value == null) {return;}// 获取字段的数据库列名（假设使用 MyBatis-Plus 的 @TableField 注解）String columnName = getColumnName(entityClass, fieldName);if (columnName == null) {return;}OperatorProcessor handler = OPERATOR_HANDLERS.get(operator.toLowerCase());if (handler != null) {try {handler.apply(lqw, columnName, value);} catch (Exception e) {log.error("Apply operator failed: {}{}{}", columnName, operator, value, e);}}}/*** 处理JSON属性查询*/private static <T> void handleJsonPropertyQuery(LambdaQueryWrapper<T> lqw, String jsonFieldName,String operator, String propKey, Object value) {//String jsonPath = String.format("$.%s", propKey);String jsonExtract = String.format("JSON_EXTRACT(%s, '$.%s')", jsonFieldName, propKey);switch (operator) {case "eq":lqw.apply(jsonExtract + " = {0}", value);break;case "gt":lqw.apply(jsonExtract + " > {0}", value);break;case "lt":lqw.apply(jsonExtract + " < {0}", value);break;case "ge":lqw.apply(jsonExtract + " >= {0}", value);break;case "le":lqw.apply(jsonExtract + " <= {0}", value);break;case "contains":if (value.toString().contains("~")) {String[] range = value.toString().split("~");if (range.length == 2) {lqw.apply(jsonExtract + " BETWEEN {0} AND {1}", range[0], range[1]);}}break;case "like":lqw.apply("JSON_UNQUOTE(" + jsonExtract + ") LIKE {0}", "%" + value + "%");break;}}private static <T> String getColumnName(Class<T> entityClass, String fieldName) {try {Field field = entityClass.getDeclaredField(fieldName);TableField tableField = field.getAnnotation(TableField.class);// 如果字段有 @TableField 注解，并且指定了列名，则使用它if (tableField != null && StringUtils.isNotBlank(tableField.value())) {return tableField.value();}// 否则默认使用字段名（驼峰转下划线）return camelToUnderline(fieldName);} catch (NoSuchFieldException e) {log.error("Field not found: {}", fieldName, e);}return null;}private static String camelToUnderline(String camelCase) {if (StringUtils.isEmpty(camelCase)) {return camelCase;}// 使用正则匹配大写字母，并在前面加下划线，然后转小写return camelCase.replaceAll("([A-Z])", "_$1").toLowerCase();}}
如何使用呢
后端service
// propertyData是json字段，knownPropKeys是json对应的key集合， bo.getParams()是map参数集合，bo.getQueryParameter()是更复杂的参数LambdaQueryWrapper<Product> lqw = Wrappers.lambdaQuery();// 1 使用*Wrappers.lambdaQuery()*构建实体相关的*LambdaQueryWrapper*实列，以实现基本查询lqw.orderByAsc(Product::getId);lqw.eq(StringUtils.isNotBlank(bo.getProductPropertyTemplate()), Product::getProductPropertyTemplate, bo.getProductPropertyTemplate());// 2 使用*LambdaQueryWrapper*实列构建*params*的较复杂的支持多字段和json字段多属性的查询语句lqw = MixedQueryWrapper.buildMixedQueryWrapper(Product.class, "propertyData", bo.getParams(), knownPropKeys, lqw);// 3 继续使用*LambdaQueryWrapper*实列构建复杂的多分组多条件且支持多字段和json字段多属性的查询语句lqw = MixedQueryWrapper.buildMixedQueryWrapper(Product.class, "propertyData", bo.getQueryParameter(), knownPropKeys, lqw);
前端参数示例
- 请求示例：/product/list?pageNum=1&pageSize=10&queryParameter%5Blogic%5D=AND&queryParameter%5Bconditions%5D=*****
- 参数如下：
queryParameter.logic: AND
queryParameter.conditions[0].field: productPropertyTemplate
queryParameter.conditions[0].operator: eq
queryParameter.conditions[0].value: 432
queryParameter.conditions[1].field: originalProductId
queryParameter.conditions[1].operator: eq
queryParameter.conditions[1].value: 6456
queryParameter.groups[0].logic: OR
queryParameter.groups[0].conditions[0].field: nameSpec
queryParameter.groups[0].conditions[0].operator: like
queryParameter.groups[0].conditions[0].value: cs
queryParameter.groups[0].conditions[1].field: originalProductId
queryParameter.groups[0].conditions[1].operator: eq
queryParameter.groups[0].conditions[1].value: 44
queryParameter.groups[1].logic: AND
queryParameter.groups[1].conditions[0].field: nameSpec
queryParameter.groups[1].conditions[0].operator: eq
queryParameter.groups[1].conditions[0].value: dd
queryParameter.groups[1].conditions[1].field: status
queryParameter.groups[1].conditions[1].operator: startsWith
queryParameter.groups[1].conditions[1].value: 4
//queryParameter.groups[1].groups[0]....
- API请求如果是GET接口可能需要改造一下
export const listProduct = (query?: ProductQuery): AxiosPromise<ProductVO[]> => {// 处理 queryParameter 参数const params = new URLSearchParams();// 添加基础参数params.append('pageNum', query?.pageNum?.toString() || '1');params.append('pageSize', query?.pageSize?.toString() || '10');// 添加 queryParameter 参数if (query?.queryParameter) {const qp = query.queryParameter;params.append('queryParameter.logic', qp.logic || 'AND');// 处理 conditionsqp.conditions?.forEach((condition, index) => {params.append(`queryParameter.conditions[${index}].field`, condition.field);params.append(`queryParameter.conditions[${index}].operator`, condition.operator);params.append(`queryParameter.conditions[${index}].value`, condition.value);});// 处理 groupsqp.groups?.forEach((group, gIndex) => {params.append(`queryParameter.groups[${gIndex}].logic`, group.logic);group.conditions?.forEach((condition, cIndex) => {params.append(`queryParameter.groups[${gIndex}].conditions[${cIndex}].field`, condition.field);params.append(`queryParameter.groups[${gIndex}].conditions[${cIndex}].operator`, condition.operator);params.append(`queryParameter.groups[${gIndex}].conditions[${cIndex}].value`, condition.value);});});}return request({url: '/product/product/list',method: 'get',params: params});
};
生成SQL示例
SELECT COUNT(*) AS total
FROM product
WHERE (product_property_template = '432' AND original_product_id = '6456' AND (name_spec LIKE '%cs%' OR original_product_id = '44') AND (name_spec = 'dd' AND status LIKE '4%')
)
![[C++]内存管理：串顺序存储的内存回收](http://pic.xiahunao.cn/yaotu/[C++]内存管理：串顺序存储的内存回收)
