网站

MyBatis-Plus在线文档
https://baomidou.com/introduce/

需求

业务场景： 日志、交易流水表或者其他数据量大的表，通过日期进行了水平分表，需要通过日期参数，动态的查询数据。
实现思路：利用MybatisPlus的动态表名插件DynamicTableNameInnerInterceptor ,实现Sql执行时，动态的修改表名。

实现步骤：在数据库预先创建好各年份或者月份的表之后，在配置类统一配置拦截器MybatisPlusInterceptor需要处理的动态表。

【本文业务】
对客户1001的商品资料spzl_1001

版本 3.5.2

<dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.5.2</version> </dependency>

SecondDataSourceConfig

打印单个数据源日志
配置拦截器
动态表名配置
配置分页，在下面配置，否则不是动态表名

@Configuration@MapperScan(basePackages=SecondDataSourceConfig.PACKAGE,sqlSessionTemplateRef="secondSqlSessionTemplate")publicclassSecondDataSourceConfig{staticfinalStringPACKAGE="com.xazy.medical.mapper.second";staticfinalStringMAPPER_LOCATION="classpath*:mapper/second/*.xml";@Primary@ConfigurationProperties(prefix="spring.datasource.second")@Bean(name="secondDataSource")publicDataSourcesecondDataSource(){returnDataSourceBuilder.create().build();}@Primary@Bean(name="secondSqlSessionFactory")publicSqlSessionFactorysecondSqlSessionFactory(@Qualifier("secondDataSource")DataSourcedataSource)throwsException{MybatisSqlSessionFactoryBeansessionFactory=newMybatisSqlSessionFactoryBean();sessionFactory.setDataSource(dataSource);sessionFactory.setPlugins(mybatisPlusInterceptor());sessionFactory.setMapperLocations(newPathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));MybatisConfigurationmybatisConfiguration=newMybatisConfiguration();mybatisConfiguration.setLogImpl(org.apache.ibatis.logging.stdout.StdOutImpl.class);sessionFactory.setConfiguration(mybatisConfiguration);returnsessionFactory.getObject();}@Primary@Bean(name="secondSqlSessionTemplate")publicSqlSessionTemplatesecondSqlSessionTemplate(@Qualifier("secondSqlSessionFactory")SqlSessionFactorysessionFactory)throwsException{returnnewSqlSessionTemplate(sessionFactory);}// @Primary// @Bean(name = "secondTransactionManager")// public DataSourceTransactionManager secondTransactionManager() {// return new DataSourceTransactionManager(secondDataSource());// }@BeanpublicMybatisPlusInterceptormybatisPlusInterceptor(){MybatisPlusInterceptorinterceptor=newMybatisPlusInterceptor();DynamicTableNameInnerInterceptordynamicTableNameInnerInterceptor=newDynamicTableNameInnerInterceptor();//dynamicTableNameInnerInterceptor.setTableNameHandler(new MyTableNameHandler());//动态表名插件dynamicTableNameInnerInterceptor.setTableNameHandler((sql,tableName)->{if("spzl".equals(tableName)||"spkc".equals(tableName)||"spjg".equals(tableName)){tableName=tableName+"_001";log.info("动态查询表"+tableName);}returntableName;});interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);//放到下边位置interceptor.addInnerInterceptor(newPaginationInnerInterceptor(DbType.MYSQL));returninterceptor;}}

ServiceImpl 接口

/** * @author lyd * @Description: ErpSpkcService 接口实现类 * @date 2024/05/07 14:17 */ @Service public class ErpSpkcServiceImpl extends ServiceImpl<ErpSpkcMapper, ErpSpkc> implements ErpSpkcService { @Resource private ErpSpkcMapper erpSpkcMapper; @Override public List<ErpSpkc> getPage(){ Page<ErpSpkc> rowPage = new Page(1, 10); LambdaQueryWrapper<ErpSpkc> queryWrapper = new LambdaQueryWrapper<>(); queryWrapper.eq(ErpSpkc::getSpid, "0000000001709"); super.baseMapper.selectPage(rowPage, queryWrapper); //分页查询 return null; } @Override public List<ErpSpkc> getErpSpkcList(){ LambdaQueryWrapper<ErpSpkc> queryWrapper = new LambdaQueryWrapper<>(); queryWrapper.eq(ErpSpkc::getSpid, "0000000001709"); List<ErpSpkc> erpSpkcList = erpSpkcMapper.selectList(queryWrapper); //mapper查询 return null; } @Override public List<ErpSpkc> getOneErpSpkc(){ LambdaQueryWrapper<ErpSpkc> queryWrapper = new LambdaQueryWrapper<>(); queryWrapper.eq(ErpSpkc::getSpid, "0000000001709"); ErpSpkc erpSpkc = this.baseMapper.selectOne(queryWrapper); if(erpSpkc != null){ log.info("-----" + erpSpkc.getLasttime()); } return null; } }

问题

mybatis-plus添加多数据源插件和动态表名导致分页失效

日志

实体表 spkc 变为 spkc_001

2024-05-10 10:48:05.863 INFO 31392 --- [nio-8093-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring DispatcherServlet 'dispatcherServlet' 2024-05-10 10:48:05.863 INFO 31392 --- [nio-8093-exec-1] o.s.web.servlet.DispatcherServlet : Initializing Servlet 'dispatcherServlet' 2024-05-10 10:48:05.864 INFO 31392 --- [nio-8093-exec-1] o.s.web.servlet.DispatcherServlet : Completed initialization in 1 ms Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@615d3061] was not registered for synchronization because synchronization is not active 2024-05-10 10:48:05.937 INFO 31392 --- [nio-8093-exec-1] c.x.m.config.SecondDataSourceConfig : 动态查询表: spkc_001 2024-05-10 10:48:05.967 INFO 31392 --- [nio-8093-exec-1] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... 2024-05-10 10:48:06.698 INFO 31392 --- [nio-8093-exec-1] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed. JDBC Connection [HikariProxyConnection@654387138 wrapping com.mysql.cj.jdbc.ConnectionImpl@7e9f079b] will not be managed by Spring ==> Preparing: SELECT COUNT(*) AS total FROM spkc_001 WHERE (spid = ?) ==> Parameters: 0000000001709(String) <== Columns: total <== Row: 1 <== Total: 1 ==> Preparing: SELECT spid,spbh,shl,sxrq,cgy,ckid,lasttime,up_status,sjly FROM spkc_001 WHERE (spid = ?) LIMIT ? ==> Parameters: 0000000001709(String), 10(Long) <== Columns: spid, spbh, shl, sxrq, cgy, ckid, lasttime, up_status, sjly <== Row: 0000000001709, 00714, 0.0, , , , 2024/4/9 18:43:28, 0, TS039 <== Total: 1 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@615d3061] Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5847749b] was not registered for synchronization because synchronization is not active 2024-05-10 10:48:07.153 INFO 31392 --- [nio-8093-exec-1] c.x.m.config.SecondDataSourceConfig : 动态查询表: spkc_001 JDBC Connection [HikariProxyConnection@1955085493 wrapping com.mysql.cj.jdbc.ConnectionImpl@7e9f079b] will not be managed by Spring ==> Preparing: SELECT spid,spbh,shl,sxrq,cgy,ckid,lasttime,up_status,sjly FROM spkc_001 WHERE (spid = ?) ==> Parameters: 0000000001709(String) <== Columns: spid, spbh, shl, sxrq, cgy, ckid, lasttime, up_status, sjly <== Row: 0000000001709, 00714, 0.0, , , , 2024/4/9 18:43:28, 0, TS039 <== Total: 1 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5847749b] 2024-05-10 10:48:07.382 INFO 31392 --- [nio-8093-exec-1] c.x.m.service.impl.ErpSpkcServiceImpl : -----ErpSpkc(spid=0000000001709, spbh=00714, shl=0.0, sxrq=, cgy=, ckid=, lasttime=2024/4/9 18:43:28, upStatus=0, sjly=TS039) Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@454c8e60] was not registered for synchronization because synchronization is not active 2024-05-10 10:48:07.385 INFO 31392 --- [nio-8093-exec-1] c.x.m.config.SecondDataSourceConfig : 动态查询表: spkc_001 JDBC Connection [HikariProxyConnection@177690205 wrapping com.mysql.cj.jdbc.ConnectionImpl@7e9f079b] will not be managed by Spring ==> Preparing: SELECT spid,spbh,shl,sxrq,cgy,ckid,lasttime,up_status,sjly FROM spkc_001 WHERE (spid = ?) ==> Parameters: 0000000001709(String) <== Columns: spid, spbh, shl, sxrq, cgy, ckid, lasttime, up_status, sjly <== Row: 0000000001709, 00714, 0.0, , , , 2024/4/9 18:43:28, 0, TS039 <== Total: 1 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@454c8e60] 2024-05-10 10:48:07.614 INFO 31392 --- [nio-8093-exec-1] c.x.m.service.impl.ErpSpkcServiceImpl : -----2024/4/9 18:43:28

其他

@Configuration public class MybatisPlusConfig { @Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInnerInterceptor(); //dynamicTableNameInnerInterceptor.setTableNameHandler(new MyTableNameHandler());//动态表名插件 dynamicTableNameInnerInterceptor.setTableNameHandler((sql, tableName) -> { if("spzl".equals(tableName)){ tableName = tableName + "_001"; } if("spkc".equals(tableName)){ tableName = tableName + "_001"; } if("spjg".equals(tableName)){ tableName = tableName + "_001"; } return tableName; }); interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor); interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL)); return interceptor; } }