需求
业务场景: 日志、交易流水表或者其他数据量大的表,通过日期进行了水平分表,需要通过日期参数,动态的查询数据。
实现思路:利用MybatisPlus的动态表名插件DynamicTableNameInnerInterceptor ,实现Sql执行时,动态的修改表名。
实现步骤:在数据库预先创建好各年份或者月份的表之后,在配置类统一配置拦截器MybatisPlusInterceptor需要处理的动态表。
版本 3.5.2
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>
second
DBSecondConfig.java
打印单个数据源日志
配置拦截器
动态表名配置
配置分页,在下面配置,否则不是动态表名
@Configuration
@MapperScan(basePackages = SecondDataSourceConfig.PACKAGE, sqlSessionTemplateRef = "secondSqlSessionTemplate")
public class SecondDataSourceConfig {
static final String PACKAGE = "com.xazy.medical.mapper.second";
static final String MAPPER_LOCATION = "classpath*:mapper/second/*.xml";
@Primary
@ConfigurationProperties(prefix = "spring.datasource.second")
@Bean(name = "secondDataSource")
public DataSource secondDataSource() {
return DataSourceBuilder.create().build();
}
@Primary
@Bean(name = "secondSqlSessionFactory")
public SqlSessionFactory secondSqlSessionFactory(@Qualifier("secondDataSource") DataSource dataSource) throws Exception {
MybatisSqlSessionFactoryBean sessionFactory = new MybatisSqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
sessionFactory.setPlugins(mybatisPlusInterceptor());
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
MybatisConfiguration mybatisConfiguration = new MybatisConfiguration();
mybatisConfiguration.setLogImpl(org.apache.ibatis.logging.stdout.StdOutImpl.class);
sessionFactory.setConfiguration(mybatisConfiguration);
return sessionFactory.getObject();
}
@Primary
@Bean(name = "secondSqlSessionTemplate")
public SqlSessionTemplate secondSqlSessionTemplate(@Qualifier("secondSqlSessionFactory") SqlSessionFactory sessionFactory) throws Exception {
return new SqlSessionTemplate(sessionFactory);
}
// @Primary
// @Bean(name = "secondTransactionManager")
// public DataSourceTransactionManager secondTransactionManager() {
// return new DataSourceTransactionManager(secondDataSource());
// }
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInnerInterceptor();
//dynamicTableNameInnerInterceptor.setTableNameHandler(new MyTableNameHandler());//动态表名插件
dynamicTableNameInnerInterceptor.setTableNameHandler((sql, tableName) -> {
if("spzl".equals(tableName) || "spkc".equals(tableName) || "spjg".equals(tableName)){
tableName = tableName + "_001";
log.info("动态查询表" + tableName);
}
return tableName;
});
interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);
//放到下边位置
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
return interceptor;
}
}
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() {
log.info("=============init mybatis-plus");
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;
}
}