利用mybatis-plus和mybatis-plus-join实现列表自定义查询条件
前言
主要是通过给param和结果集的字段增加注解，再使用工具类将类的字段与sql的字段做匹配映射。然后通过MPJLambdaQueryWrapper自动生成sql。
使用mybatis-plus-join主要是考虑到会有联表查询的情况，原生的mybatis-plus还不支持联表查询
一、引入包
<dependency> <groupId>com.github.yulichang</groupId> <artifactId>mybatis-plus-join-boot-starter</artifactId> <version>1.5.4</version> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> </dependency>二、定义查询条件涉及的一些变量和枚举
import io.swagger.annotations.ApiModelProperty; import lombok.Data; import lombok.Getter; import java.util.Arrays; /** * 过滤条件 */ @Data public class FilterCondition { @ApiModelProperty(value = "字段名") private String field; @ApiModelProperty(value = "操作符") private Integer op = 2; @ApiModelProperty(value = "值") private String value; @ApiModelProperty(value = "逻辑关系") private Integer rl = 0; /** * 操作符枚举 */ @Getter public enum Operator { GT(1, "大于"), EQ(2, "等于"), LTE(3, "小于等于"), LT(4, "小于"), NE(5, "不等于"), STARTS(6, "开头是"), CONTAINS(7, "包含"), ENDS(8, "结尾是"), EMPTY(9, "为空"), NOT_EMPTY(10, "非空"),GTE(11, "大于等于"); private int code; private String desc; Operator(int code, String desc) { this.code = code; this.desc = desc; } public static Operator fromCode(int code) { return Arrays.stream(values()) .filter(op -> op.code == code) .findFirst() .orElse(EQ); } } public Operator getOperator() { return Operator.fromCode(op); } public ConditionRelation getRelation() { return rl == 1 ? ConditionRelation.AND : ConditionRelation.OR; } /** * 逻辑关系枚举 */ public enum ConditionRelation { AND, OR }/** * 排序条件 */ @Data public class SortCondition { @ApiModelProperty(value = "排序字段") private String field; @ApiModelProperty(value = "排序方式:true为升序,false为降序") private Boolean order; }/** * 字段类型枚举 字符串 数值 日期 字典 */ public enum FieldTypeEnum { STRING, NUMBER, DATE, DICT }/** * 元数据类 */ @Data @AllArgsConstructor public class FieldMeta { //字段名称 private String fieldName; //字段类型 private FieldTypeEnum fieldType; // 字典项类型标识 private String dictType; // 默认日期格式 private String dateFormat = "yyyy-MM-dd HH:mm:ss"; // 是否Having条件 private Boolean isHaving; }三、定义入参和出参使用的注解
/** * 字段元数据注解 */ @Target({ElementType.FIELD}) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface FieldMeta { // 表 Class<?> tableClass() default Object.class; // 字段名称 实际sql中查询的字段名 例如 select t.name from a as t where t.name ='123'; 如果该字段是对应t.name 那么fileName的值就是t.name String fieldName(); // 字段类型 FieldTypeEnum fieldType(); // 字典类型 String dictType() default ""; // 日期格式 String dateFormat() default ""; // 是否json字段 boolean isJson() default false; // 是否having条件 boolean isHaving() default false; }/** * 字段结果集注解 */ @Target({ElementType.FIELD}) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface FieldMetaRsp { // 字段名称 String fieldName(); // 是否json字段 boolean isJson() default false; //是否聚合字段 boolean isGroupBy() default true; }这两个注解主要用户param和结果集的字段
示例：
/** * 示例请求参数 */ @EqualsAndHashCode(callSuper = true) @Data public class ExampleReq extends PageParam implements Serializable { @FieldMeta(fieldName = "t.code", fieldType = FieldTypeEnum.STRING) @ApiModelProperty(value = "编码") private String code; @FieldMeta(fieldName = "t.name", fieldType = FieldTypeEnum.STRING, isJson = true) @ApiModelProperty(value = "名称") private String name; @FieldMeta(fieldName = "t.state", fieldType = FieldTypeEnum.DICT) @ApiModelProperty(value = "状态") private String state; @FieldMeta(fieldName = "t.create_time", fieldType = FieldTypeEnum.DATE, dateFormat = "yyyy-MM-dd") @ApiModelProperty(value = "创建时间") private String createTime; @ApiModelProperty(value = "过滤条件") private List<FilterCondition> filterConditions; @ApiModelProperty(value = "排序字段") private List<SortCondition> sortConditions; }/** * 示例返回数据模型 */ @Data @ApiModel(value = "ExampleVO", description = "示例返回数据模型") public class PerformanceInterviewImproveBookDTO{ @FieldMetaRsp(fieldName = "t.id") @ApiModelProperty(value = "示例id") private String id; @FieldMetaRsp(fieldName = "t.name") @ApiModelProperty(value = "名称") private String taskName; @FieldMetaRsp(fieldName = "t.create_by") @ApiModelProperty(value = "创建人") private String createBy; @FieldMetaRsp(fieldName = "t.create_time") @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8") @ApiModelProperty(value = "创建时间") private Date createTime; @FieldMetaRsp(fieldName = "t.update_time") @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8") private Date updateTime; @FieldMetaRsp(fieldName = "t.update_by") @ApiModelProperty(value = "更新人") private String updateBy; @FieldMetaRsp(fieldName = "t.title") @ApiModelProperty(value = "标题") private String title; }filterConditions和sortConditions的示例值
{ "filterConditions":[ { "field":"create_date", "op":1, "value":"2025-01-01 00:00:00", "rl":0 }, { "field":"update_date", "op":4, "value":"2025-07-01 23:59:59", "rl":0 } ], "sortConditions":[ { "field":"name", "order":false } ] }四、定义字段名称转换工具类
import cn.hutool.core.util.StrUtil; import cn.iiot.myth.starter.orm.bean.FieldMeta; import cn.iiot.myth.starter.orm.builder.QueryWrapperBuilder; import cn.iiot.myth.starter.orm.constants.FieldTypeEnum; import java.lang.reflect.Field; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; /** * 字段名称转换工具类 */ public class FieldMetaConverter { /** * 获取类字段与元数据字段映射关系map */ public static Map<String, FieldMeta> buildFieldMetaMap(Class<?> clazz) { Map<String, FieldMeta> result = new HashMap<>(); // 从当前类向上遍历到顶层父类（不包括 Object） Class<?> currentClass = clazz; while (currentClass != null && !currentClass.equals(Object.class)) { for (Field field : currentClass.getDeclaredFields()) { if (field.isAnnotationPresent(cn.iiot.myth.starter.orm.annotation.FieldMeta.class)) { FieldMeta annotation = field.getAnnotation(FieldMeta.class); String fieldName = annotation.fieldName(); FieldTypeEnum fieldType = annotation.fieldType(); String dictType = StrUtil.isEmpty(annotation.dictType()) ? null : annotation.dictType(); String dateFormat = StrUtil.isEmpty(annotation.dateFormat()) ? null : annotation.dateFormat(); Boolean isHaving = annotation.isHaving(); FieldMeta meta = new FieldMeta(fieldName, fieldType, dictType, dateFormat,isHaving); result.put(field.getName(), meta); } } currentClass = currentClass.getSuperclass(); } return result; } /** * 获取需要查询的字段名数组 */ public static String[] generateFieldSelectSql(Class<?> voClass) { List<String> sqlFields = new ArrayList<>(); for (Field field : voClass.getDeclaredFields()) { if (field.isAnnotationPresent(FieldMetaRsp.class)) { FieldMetaRsp annotation = field.getAnnotation(FieldMetaRsp.class); String dbField = annotation.fieldName(); String alias = field.getName(); sqlFields.add(dbField + " AS '" + alias + "'"); } } return sqlFields.toArray(new String[0]); } /** * 获取类字段与元数据字段映射关系map */ public static Map<String, FieldMeta> buildFieldMetaMapI18n(Class<?> clazz,String languageCode) { Map<String, FieldMeta> result = new HashMap<>(); // 从当前类向上遍历到顶层父类（不包括 Object） Class<?> currentClass = clazz; while (currentClass != null && !currentClass.equals(Object.class)) { for (Field field : currentClass.getDeclaredFields()) { if (field.isAnnotationPresent(FieldMeta.class)) { FieldMeta annotation = field.getAnnotation(FieldMeta.class); String fieldName = annotation.fieldName(); boolean isJson = annotation.isJson(); if(isJson){ fieldName = QueryWrapperBuilder.getI18nJsonFieldName(fieldName,languageCode); } FieldTypeEnum fieldType = annotation.fieldType(); String dictType = StrUtil.isEmpty(annotation.dictType()) ? null : annotation.dictType(); String dateFormat = StrUtil.isEmpty(annotation.dateFormat()) ? null : annotation.dateFormat(); Boolean isHaving = annotation.isHaving(); FieldMeta meta = new FieldMeta(fieldName, fieldType, dictType, dateFormat,isHaving); result.put(field.getName(), meta); } } currentClass = currentClass.getSuperclass(); } return result; } /** * 获取需要查询的字段名数组 含国际化 */ public static String[] generateFieldSelectSqlI18n(Class<?> voClass,String languageCode) { List<String> sqlFields = new ArrayList<>(); for (Field field : voClass.getDeclaredFields()) { if (field.isAnnotationPresent(FieldMetaRsp.class)) { FieldMetaRsp annotation = field.getAnnotation(FieldMetaRsp.class); String dbField = annotation.fieldName(); boolean isJson = annotation.isJson(); if(isJson){ dbField = QueryWrapperBuilder.getI18nJsonFieldName(dbField,languageCode); } String alias = field.getName(); sqlFields.add(dbField + " AS '" + alias + "'"); } } return sqlFields.toArray(new String[0]); } /** * 获取需要查询的字段名list */ public static List<String> generateFieldGroupBy(Class<?> voClass) { List<String> sqlFields = new ArrayList<>(); for (Field field : voClass.getDeclaredFields()) { if (field.isAnnotationPresent(FieldMetaRsp.class)) { FieldMetaRsp annotation = field.getAnnotation(FieldMetaRsp.class); String dbField = annotation.fieldName(); boolean isGroupBy = annotation.isGroupBy(); if(isGroupBy){ sqlFields.add(dbField); } } } return sqlFields; } }五、sql构建器
这里主要根据传入的自定义条件集合和排序条件集合，通过QueryWrapper进行封装。
会先根据字段进行分组，同一个字段的分到一组，组间条件根据传入的逻辑符。
每个查询条件根据字段类型及运算符的不同，路由到对应的处理方式
此处涉及到的所谓国际化字段，是指数据库中数据类型使用json，例如某个表的name字段，其中存的值如{ZH:中文,EN:英文}，就可以通过JSON_EXTRACT(name,'$.ZH')获取中文字段值。
在自定义条件查询中多个字段之间的查询条件逻辑关系用的且，可以自行根据需求进行调整。
@Component public class QueryWrapperBuilder<T> { public MPJLambdaWrapper<T> buildWrapper(List<SortCondition> sorts, List<FilterCondition> filters, Map<String,FieldMeta> fieldMetaMap) { MPJLambdaWrapper<T> wrapper = new MPJLambdaWrapper<>(); // 处理排序条件 processSorts(wrapper, sorts,fieldMetaMap); // 处理过滤条件 processFilters(wrapper, filters,fieldMetaMap); return wrapper; } private void processSorts(MPJLambdaWrapper<T> wrapper, List<SortCondition> sorts, Map<String,FieldMeta> fieldMetaMap) { if (CollUtil.isEmpty(sorts)) return; for (SortCondition sort : sorts) { FieldMeta meta = fieldMetaMap.get(sort.getField()); if (Boolean.TRUE.equals(sort.getOrder())) { wrapper.orderByDesc(meta.getFieldName()); } else { wrapper.orderByAsc(meta.getFieldName()); } } } private void processFilters(MPJLambdaWrapper<T> wrapper, List<FilterCondition> filters, Map<String,FieldMeta> fieldMetaMap) { if (CollUtil.isEmpty(filters)) return; // 按字段分组 Map<String, List<FilterCondition>> groupedFilters = filters.stream() .collect(Collectors.groupingBy(FilterCondition::getField)); groupedFilters.forEach((field, conditions) -> { FilterCondition first = conditions.get(0); FieldMeta firstMeta = fieldMetaMap.get(first.getField()); if(firstMeta.getIsHaving()){ // having String havingCondition = handleHavingCondition(wrapper, first, fieldMetaMap.get(first.getField())); for (int i = 1; i < conditions.size(); i++) { FilterCondition condition = conditions.get(i); FieldMeta meta = fieldMetaMap.get(condition.getField()); if(Objects.isNull(meta)){ continue; } if (condition.getRelation() == FilterCondition.ConditionRelation.AND) { havingCondition += StrUtil.format(" AND {}", handleHavingCondition(wrapper, condition, meta)); } else { havingCondition += StrUtil.format(" OR {}", handleHavingCondition(wrapper, condition, meta)); } } havingCondition = StrUtil.format("({})", havingCondition); wrapper.having(havingCondition); }else{ wrapper.and(conditionWrapper ->{ // 处理第一个条件 handleCondition(conditionWrapper, first, firstMeta); // 处理后续条件 for (int i = 1; i < conditions.size(); i++) { FilterCondition condition = conditions.get(i); FieldMeta meta = fieldMetaMap.get(condition.getField()); if(Objects.isNull(meta)){ continue; } if (condition.getRelation() == FilterCondition.ConditionRelation.AND) { conditionWrapper.and(qw -> handleCondition(qw, condition, meta)); } else { conditionWrapper.or(qw -> handleCondition(qw, condition, meta)); } } }); } }); } private String handleHavingCondition(MPJLambdaWrapper<T> wrapper, FilterCondition condition, FieldMeta meta){ FilterCondition.Operator op = condition.getOperator(); String field = meta.getFieldName(); String value = condition.getValue(); String havingCondition = null; switch (op) { case GT: // > havingCondition = StrUtil.format("{} > {}", field, value); break; case EQ: // = havingCondition = StrUtil.format("{} = {}", field, value); break; case LTE: // <= havingCondition = StrUtil.format("{} <= {}", field, value); break; case LT: // < havingCondition = StrUtil.format("{} < {}", field, value); break; case GTE: // >= havingCondition = StrUtil.format("{} >= {}", field, value); break; case NE: // != havingCondition = StrUtil.format("{} != {}", field, value); break; case EMPTY: // 为空 havingCondition = StrUtil.format("{} IS NULL", field); break; case NOT_EMPTY: // 非空 havingCondition = StrUtil.format("{} IS NOT NULL", field); break; case STARTS: havingCondition = StrUtil.format("{} LIKE '{}%'", field, value); break; case CONTAINS: havingCondition = StrUtil.format("{} LIKE '%{}%'", field, value); break; case ENDS: havingCondition = StrUtil.format("{} LIKE '%{}'", field, value); break; default: throw new UnsupportedOperationException( "不支持的操作符: " + op.name()); } return havingCondition; } private void handleCondition(MPJLambdaWrapper<T> wrapper, FilterCondition condition, FieldMeta meta) { // 根据字段类型路由处理 switch (meta.getFieldType()) { case DATE: handleDateCondition(wrapper, condition, meta); break; case NUMBER: handleNumberCondition(wrapper, condition,meta); break; case DICT: handleDictCondition(wrapper, condition, meta); break; case STRING: default: handleStringCondition(wrapper, condition, meta); } } private void handleDateCondition(MPJLambdaWrapper<T> wrapper, FilterCondition condition, FieldMeta meta) { // 日期类型只支持部分操作符 FilterCondition.Operator op = condition.getOperator(); String field = meta.getFieldName(); // 转换为日期对象 DateTime dateValue = null; if(StrUtil.isNotBlank(condition.getValue())){ dateValue = parseDate(condition.getValue(), meta.getDateFormat()); } if(StrUtil.equals(meta.getDateFormat(), DatePattern.NORM_DATE_PATTERN)){ switch (op) { case GT: // > wrapper.gt(field, DateUtil.endOfDay(dateValue)); break; case EQ: // = wrapper.apply(StrUtil.join(StrUtil.EMPTY,"DATE(",field,") = DATE({0})"), dateValue); break; case LTE: // <= wrapper.le(field, DateUtil.endOfDay(dateValue)); break; case LT: // < wrapper.lt(field, DateUtil.beginOfDay(dateValue)); break; case GTE: wrapper.ge(field, DateUtil.beginOfDay(dateValue)); case EMPTY: // 为空 wrapper.isNull(field); break; case NOT_EMPTY: // 非空 wrapper.isNotNull(field); break; default: throw new UnsupportedOperationException( "日期字段不支持操作符: " + op.name()); } }else{ switch (op) { case GT: // > wrapper.gt(field, dateValue); break; case EQ: // = wrapper.apply("DATE({0}) = DATE({1})", field, dateValue); break; case LTE: // <= wrapper.le(field, dateValue); break; case LT: // < wrapper.lt(field, dateValue); break; case GTE: wrapper.ge(field, dateValue); case EMPTY: // 为空 wrapper.isNull(field); break; case NOT_EMPTY: // 非空 wrapper.isNotNull(field); break; default: throw new UnsupportedOperationException( "日期字段不支持操作符: " + op.name()); } } } private DateTime parseDate(String value, String format) { try { DateTimeFormatter formatter = DateTimeFormatter.ofPattern(format); return DateUtil.parse(value,formatter); } catch (Exception e) { throw new IllegalArgumentException("日期格式错误: " + value); } } private void handleNumberCondition(MPJLambdaWrapper<T> wrapper, FilterCondition condition, FieldMeta meta) { // 数字类型处理 FilterCondition.Operator op = condition.getOperator(); String field = meta.getFieldName(); BigDecimal numberValue = BigDecimal.ZERO; if(StrUtil.isNotBlank(condition.getValue())){ numberValue = parseNumber(condition.getValue()); } else if (!Objects.equals(op, FilterCondition.Operator.NOT_EMPTY) && !Objects.equals(op, FilterCondition.Operator.EMPTY)) { throw new IllegalArgumentException("无效的数字值: " + condition.getValue()); } switch (op) { case GT: wrapper.gt(field, numberValue); break; case EQ: wrapper.eq(field, numberValue); break; case LTE: wrapper.le(field, numberValue); break; case LT: wrapper.lt(field, numberValue); break; case NE: wrapper.ne(field, numberValue); break; case GTE: wrapper.ge(field, numberValue); break; case EMPTY: wrapper.isNull(field); break; case NOT_EMPTY: wrapper.isNotNull(field); break; default: throw new UnsupportedOperationException( "数值字段不支持操作符: " + op.name()); } } private BigDecimal parseNumber(String value) { try { return new BigDecimal(value); } catch (Exception e) { throw new IllegalArgumentException("无效的数字值: " + value); } } private void handleStringCondition(MPJLambdaWrapper<T> wrapper, FilterCondition condition, FieldMeta meta) { // 字符串类型处理 FilterCondition.Operator op = condition.getOperator(); String field = meta.getFieldName(); String value = condition.getValue(); switch (op) { case EQ: wrapper.eq(field, value); break; case NE: wrapper.ne(field, value); break; case STARTS: wrapper.likeRight(field, value); break; case CONTAINS: wrapper.like(field, value); break; case ENDS: wrapper.likeLeft(field, value); break; case EMPTY: wrapper.isNull(field); break; case NOT_EMPTY: wrapper.isNotNull(field); break; default: throw new UnsupportedOperationException( "字符串字段不支持操作符: " + op.name()); } } private void handleDictCondition(MPJLambdaWrapper<T> wrapper, FilterCondition condition, FieldMeta meta) { // 字典项处理（前端传入逗号分隔的字典值） FilterCondition.Operator op = condition.getOperator(); String field = meta.getFieldName(); if (op == FilterCondition.Operator.EMPTY) { wrapper.isNull(field); return; } if (op == FilterCondition.Operator.NOT_EMPTY) { wrapper.isNotNull(field); return; } // 拆分为字典值集合 String[] dictValues = condition.getValue().split(","); Set<String> actualValues = new HashSet<>(Arrays.asList(dictValues)); if (CollUtil.isEmpty(actualValues)) { throw new IllegalArgumentException("字典转换失败: " + meta.getDictType()); } if (op == FilterCondition.Operator.EQ || op == FilterCondition.Operator.CONTAINS) { wrapper.in(field, actualValues); } else if (op == FilterCondition.Operator.NE) { wrapper.notIn(field, actualValues); } else { throw new UnsupportedOperationException( "字典字段不支持操作符: " + op.name()); } } /** * 获取国际化字段名 * @param field * @param languageCode * @return */ public static String getI18nJsonFieldName(String field,String languageCode){ String i18nField = StrUtil.join(StrUtil.EMPTY,"JSON_UNQUOTE(IFNULL(JSON_EXTRACT(" ,field,",concat('$.','",languageCode,"')),IFNULL(JSON_EXTRACT(" ,field,",'$.EN'),JSON_EXTRACT(",field,",'$.ZH'))))"); return i18nField; } /** * 获取国际化字段名(去前后空格) * @param field * @param languageCode * @return */ public static String getI18nJsonFieldNameTrim(String field,String languageCode){ String i18nField = StrUtil.join(StrUtil.EMPTY,"TRIM(JSON_UNQUOTE(IFNULL(JSON_EXTRACT(" ,field,",concat('$.','",languageCode,"')),IFNULL(JSON_EXTRACT(" ,field,",'$.EN'),JSON_EXTRACT(",field,",'$.ZH')))))"); return i18nField; }六、后端使用方式
先引入QueryWarpperBuilder
@Autowired private QueryWrapperBuilder<Example> queryWrapperBuilder;//获取请求参数的字段和元数据映射关系map Map<String, FieldMeta> fieldMetaMap = FieldMetaConverter.buildFieldMetaMap(exampleReq.getClass()); //构建查询条件 MPJLambdaWrapper<Example> wrapper = queryWrapperBuilder.buildWrapper(exampleReq.getSortConditions(),exampleReq.getFilterConditions(),fieldMetaMap); //构建查询sql中的查询字段 String[] strings = FieldMetaConverter.generateFieldSelectSql(ExampleVO.class); //如果需要进行连表查询 现在Example对应表的别名是t Example2对应表的别名是t1 也可以进行自定义 wrapper.leftJoin(Example.class, Example::getId, Example2::getUid); //如果是联表查询则用selectJoinPage Page<ExampleVO> page = exmpaleMapper.selectPage(page,wrapper );七、前端使用方式
通用查询条件分为两类
1、排序条件
field为字段key，这个需要前后端沟通统一
order为字段排序方式 true为升序,false为降序
"sortConditions": [ { "field":"", "order":true } ]2、过滤条件
field为字段key，这个需要前后端沟通统一
op为操作符，可以通过字典类型operator获取，目前分为 操作符 1大于 2等于 3小于等于 4小于 5不等于 6开头是 7包含 8结尾是 9为空 10非空 11大于等于
value为字段查询的值
rl为逻辑关系，可以通过字典类型relation获取，目前分为 1 且 0 或
目前将字段类型分为四大类：
1）字符类
字符类型支持的操作符为，等于，不等于，开头是，结尾是，包含，为空，非空
2）数字类
数字类后端使用的是BigDecimal，正常的数类型都可以支持
支持的操作符为大于，等于，小于等于，小于，小于等于，为空，非空，不等于
3）日期类
日期类型需要格式为 yyyy-MM-dd HH:mm:ss
支持的操作符为大于，等于，小于等于，小于，小于等于，为空，非空
4）字典类
字典类型比较特别一点，
支持的操作符为，为空，非空，等于，不等于，包含
需要前端读取字典接口，展示一个带搜索的多选框（选择操作符为包含时，可以多选），当选择的操作符为等于，不等于，包含时，需要将用户选择的字典项值传递到value字段中，如果有多个使用英文逗号","隔开
"filterConditions": [ { "field":"", "op":"", "value":"", "rl":"" } ]
