利用mybatis-plus和mybatis-plus-join实现列表自定义查询条件

前言主要是通过给param和结果集的字段增加注解再使用工具类将类的字段与sql的字段做匹配映射。然后通过MPJLambdaQueryWrapper自动生成sql。使用mybatis-plus-join主要是考虑到会有联表查询的情况原生的mybatis-plus还不支持联表查询一、引入包dependency groupIdcom.github.yulichang/groupId artifactIdmybatis-plus-join-boot-starter/artifactId version1.5.4/version /dependency dependency groupIdcom.baomidou/groupId artifactIdmybatis-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 ListFilterCondition filterConditions; ApiModelProperty(value 排序字段) private ListSortCondition 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 GMT8) ApiModelProperty(value 创建时间) private Date createTime; FieldMetaRsp(fieldName t.update_time) JsonFormat(pattern yyyy-MM-dd HH:mm:ss, timezone GMT8) 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 MapString, FieldMeta buildFieldMetaMap(Class? clazz) { MapString, 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) { ListString 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 MapString, FieldMeta buildFieldMetaMapI18n(Class? clazz,String languageCode) { MapString, 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) { ListString 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 ListString generateFieldGroupBy(Class? voClass) { ListString 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 QueryWrapperBuilderT { public MPJLambdaWrapperT buildWrapper(ListSortCondition sorts, ListFilterCondition filters, MapString,FieldMeta fieldMetaMap) { MPJLambdaWrapperT wrapper new MPJLambdaWrapper(); // 处理排序条件 processSorts(wrapper, sorts,fieldMetaMap); // 处理过滤条件 processFilters(wrapper, filters,fieldMetaMap); return wrapper; } private void processSorts(MPJLambdaWrapperT wrapper, ListSortCondition sorts, MapString,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(MPJLambdaWrapperT wrapper, ListFilterCondition filters, MapString,FieldMeta fieldMetaMap) { if (CollUtil.isEmpty(filters)) return; // 按字段分组 MapString, ListFilterCondition 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(MPJLambdaWrapperT 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(MPJLambdaWrapperT 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(MPJLambdaWrapperT 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(MPJLambdaWrapperT 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(MPJLambdaWrapperT 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(MPJLambdaWrapperT 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(,); SetString 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; }六、后端使用方式先引入QueryWarpperBuilderAutowired private QueryWrapperBuilderExample queryWrapperBuilder;//获取请求参数的字段和元数据映射关系map MapString, FieldMeta fieldMetaMap FieldMetaConverter.buildFieldMetaMap(exampleReq.getClass()); //构建查询条件 MPJLambdaWrapperExample 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 PageExampleVO 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: } ]