需求

业务场景： 日志、交易流水表或者其他数据量大的表，通过日期进行了水平分表，需要通过日期参数，动态的查询数据。
实现思路：利用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")
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() {
        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;
    }
}