mybatis动态表名（不使用xml，不手写sql）

这是为了动态分表做的一个功能可以实现表名的动态增加/查询我这边会将添加表的时候录入一个表信息中后续查询的时候会将这个表查询数据这样可以防止一张表数据量过多导致后续维护数据的时候可以根据指标查询对应的数据下面是实现代码pom可能还缺了点依赖我这边使用的是springboot3mybatisplus有需要的可以自己改一下就能用了dependencygroupIdcom.baomidou/groupIdartifactIdmybatis-plus-spring-boot3-starter/artifactId/dependencydependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-aop/artifactId/dependencydependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-validation/artifactId/dependencydependencygroupIdcom.mysql/groupIdartifactIdmysql-connector-j/artifactId/dependencydependencygroupIdcom.baomidou/groupIdartifactIdmybatis-plus-spring-boot3-starter/artifactId/dependency实体类packagecom.ljq.module.indexvalue.dal.dataobject.indexvalue;importcom.baomidou.mybatisplus.annotation.IdType;importcom.baomidou.mybatisplus.annotation.TableField;importcom.baomidou.mybatisplus.annotation.TableId;importcom.baomidou.mybatisplus.annotation.TableName;importcom.ljq.dynamic.indexvalue.table.interceptor.DynamicIndexValueInnerInterceptor;importlombok.*;importjava.io.Serializable;importjava.math.BigDecimal;importjava.time.LocalDate;importjava.time.LocalDateTime;/** * 指标值表 DO动态表名运行时根据 indexCode 解析 * * author jianqiang li */// 这个tableName我这边写成一个需要替换的动态表名可以是任何名称或uuid都可以TableName(DynamicIndexValueInnerInterceptor.PLACEHOLDER)DataToStringBuilderNoArgsConstructorAllArgsConstructorpublicclassTIndexValueDOimplementsSerializable{TableId(typeIdType.AUTO)privateLongid;privateLocalDatepubDate;privateLocalDateTimecollectTime;privateStringindexCode;privateBigDecimalindexValue;privateLocalDateTimecreateTime;privateLocalDateTimeupdateTime;}resolver只要实现这个接口返回真实的表名就能实现动态表名了为了方便我这边就不实现了可以按照自己想要的方式来实现即可返回如 index_base.index_0001这样格式的字符串就行了相当于我这边可以把指标值存放在不同的表中了这个方法中的resolve只是一个简单示例完全可以自己定义方法参数等等packagecom.ljq.dynamic.indexvalue.table.resolver;/** * 动态表名解析器接口根据 indexCode 解析出完整的 schema.tableName * * author jianqiang li */publicinterfaceDynamicTableResolver{/** * 根据 indexCode 解析完整表名 * * param indexCode 指标代码 * return 格式schema.tableName例如 db_info_f.t_index_0000003 */Stringresolve(StringindexCode);}context由于我这边是更具指标代码来区分表的所以就得在查询前手动调用一下这个setIndexCode方法否则这个动态表名就无法生效如果是按照年月区分的话就完全不需要这个了直接根据年月来设置即可packagecom.ljq.dynamic.indexvalue.table.context;/** * 动态指标值表上下文基于 ThreadLocal 存储当前请求的 indexCode * * author jianqiang li */publicclassDynamicIndexValueContextHolder{privatestaticfinalThreadLocalStringINDEX_CODE_HOLDERnewThreadLocal();/** * 设置当前请求的指标代码 */publicstaticvoidsetIndexCode(StringindexCode){INDEX_CODE_HOLDER.set(indexCode);}/** * 获取当前请求的指标代码 */publicstaticStringgetIndexCode(){returnINDEX_CODE_HOLDER.get();}/** * 清除 */publicstaticvoidclear(){INDEX_CODE_HOLDER.remove();}}拦截器 interceptorpackagecom.ljq.dynamic.indexvalue.table.interceptor;importcom.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;importcom.ljq.dynamic.indexvalue.table.context.DynamicIndexValueContextHolder;importcom.ljq.dynamic.indexvalue.table.resolver.DynamicTableResolver;importorg.apache.ibatis.executor.statement.StatementHandler;importorg.apache.ibatis.mapping.BoundSql;importorg.apache.ibatis.reflection.MetaObject;importorg.apache.ibatis.reflection.SystemMetaObject;importjava.sql.Connection;/** * 在mybatis层拦截只要是 PLACEHOLDER 表名的就需要替换成动态表名 * * author jianqiang li */publicclassDynamicIndexValueInnerInterceptorimplementsInnerInterceptor{// 这个就是它的表名只有是这个表名的时候才会动态分表publicstaticfinalStringPLACEHOLDERt_index_placeholder;privatefinalDynamicTableResolverresolver;publicDynamicIndexValueInnerInterceptor(DynamicTableResolverresolver){this.resolverresolver;}OverridepublicvoidbeforePrepare(StatementHandlersh,Connectionconnection,IntegertransactionTimeout){BoundSqlboundSqlsh.getBoundSql();StringsqlboundSql.getSql();if(sqlnull||!sql.contains(PLACEHOLDER)){return;}StringindexCodeDynamicIndexValueContextHolder.getIndexCode();// 获取动态表名Stringresolvedresolver.resolve(indexCode);if(resolved!null!resolved.isEmpty()){// 将mybatis生成的表名替换成真实表名查询StringnewSqlsql.replace(PLACEHOLDER,resolved);MetaObjectmetaObjectSystemMetaObject.forObject(boundSql);metaObject.setValue(sql,newSql);}}}config注册上面的拦截器使其生效packagecom.ljq.dynamic.indexvalue.table.config;importcom.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;importcom.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;importcom.ljq.dynamic.indexvalue.table.interceptor.DynamicIndexValueInnerInterceptor;importcom.ljq.dynamic.indexvalue.table.resolver.DynamicTableResolver;importorg.springframework.boot.autoconfigure.AutoConfiguration;importorg.springframework.boot.autoconfigure.condition.ConditionalOnClass;importorg.springframework.context.annotation.Bean;importjava.util.ArrayList;importjava.util.List;/** * 注册拦截器 * * author jianqiang li */AutoConfigurationConditionalOnClass(MybatisPlusInterceptor.class)publicclassDynamicTableAutoConfiguration{BeanpublicDynamicIndexValueInnerInterceptordynamicIndexValueInnerInterceptor(MybatisPlusInterceptorinterceptor,DynamicTableResolverresolver){DynamicIndexValueInnerInterceptorinnernewDynamicIndexValueInnerInterceptor(resolver);ListInnerInterceptorinnersnewArrayList(interceptor.getInterceptors());inners.add(1,inner);interceptor.setInterceptors(inners);returninner;}}使用到这里我们就已经把配置都编辑好了下面我们可以来使用起来了packagecom.ljq.module.indexvalue.service.indexvalue;importjava.time.LocalDate;importjava.util.*;importcom.ljq.module.indexvalue.controller.admin.indexvalue.vo.*;importcom.ljq.module.indexvalue.dal.dataobject.indexvalue.TIndexValueDO;importjakarta.validation.*;importcom.ljq.framework.common.pojo.PageResult;/** * 指标值表 Service 接口 * * author jianqiang li */publicinterfaceTIndexValueService{ListTIndexValueDOqueryDataListByParams(StringindexCode,LocalDate[]pubdate);}packagecom.ljq.module.indexvalue.service.indexvalue;importcn.hutool.core.util.StrUtil;importcom.ljq.dynamic.indexvalue.table.context.DynamicIndexValueContextHolder;importcom.ljq.framework.common.pojo.PageResult;importcom.ljq.framework.mybatis.core.query.LambdaQueryWrapperX;importcom.ljq.module.indexvalue.controller.admin.indexvalue.vo.*;importcom.ljq.module.indexvalue.dal.dataobject.indexvalue.TIndexValueDO;importcom.ljq.module.indexvalue.dal.mysql.indexvalue.TIndexValueMapper;importorg.springframework.stereotype.Service;importjakarta.annotation.Resource;importorg.springframework.validation.annotation.Validated;importjava.math.BigDecimal;importjava.time.LocalDate;importjava.util.*;importcom.ljq.framework.common.util.object.BeanUtils;importstaticcom.ljq.framework.common.exception.util.ServiceExceptionUtil.exception;/** * 指标值表 Service 接口 * * author jianqiang li */ServiceValidatedpublicclassTIndexValueServiceImplimplementsTIndexValueService{ResourceprivateTIndexValueMappertIndexValueMapper;OverridepublicListTIndexValueDOqueryDataListByParams(StringindexCode,LocalDate[]pubdate){if(StrUtil.isBlank(indexCode))returnCollections.emptyList();if(pubdatenull)pubdatenewLocalDate[]{null,null};DynamicIndexValueContextHolder.setIndexCode(indexCode);try{returntIndexValueMapper.selectIndexCodeList(indexCode,pubdate);}finally{DynamicIndexValueContextHolder.clear();}}}packagecom.ljq.module.indexvalue.dal.mysql.indexvalue;importcom.ljq.framework.common.pojo.PageResult;importcom.ljq.framework.mybatis.core.query.LambdaQueryWrapperX;importcom.ljq.framework.mybatis.core.mapper.BaseMapperX;importcom.ljq.module.indexvalue.controller.admin.indexvalue.vo.TIndexValuePageReqVO;importcom.ljq.module.indexvalue.dal.dataobject.indexvalue.TIndexValueDO;importorg.apache.ibatis.annotations.Mapper;importjava.time.LocalDate;importjava.util.List;/** * 指标值表 Mapper * * author jianqiang li */MapperpublicinterfaceTIndexValueMapperextendsBaseMapperXTIndexValueDO{defaultListTIndexValueDOselectIndexCodeList(StringindexCode,LocalDate[]pubdate){returnselectList(newLambdaQueryWrapperXTIndexValueDO().eq(TIndexValueDO::getIndexCode,indexCode).betweenIfPresent(TIndexValueDO::getPubDate,pubdate).orderByDesc(TIndexValueDO::getPubDate));}}实际调用AutowiredprivateTIndexValueServicetIndexValueService;ListTIndexValueDOtIndexValueDOStIndexValueService.queryDataListByParams(indexCode,null);System.out.println(tIndexValueDOS);按照上面的方法我们就实现了动态表的实现这里有个问题如果是动态数据源的话就不能这样了得用其他方式主要实现是 DynamicTableResolver 的 resolve 方法这样也处理之后也能节省点开发成本了不过我上面没有添加建表逻辑需要的可以自己实现一下大致流程都是差不多的就新建一个 TIndexValueDO 一摸一样的就行转载请注明原文链接点个关注谢谢大家支持