mybatis-plus集成分页插件,针对多数据源分页失效的问题

现在分页很方便了,不用写selectCount 方法查询出总条数,我们只需要写selectList方法,mybatis-plus就可以自动给我们分页,单数据源分页比较简单,需要集成如下插件:mybatis-plus的版本为3.5.1

如下:

			com.baomidou
			mybatis-plus-boot-starter
			3.5.1

插件bean如下:

	@Bean
	public MybatisPlusInterceptor mybatisPlusInterceptor() {
		MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
		interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
		return interceptor;
	}

在SpingBoot启动类或其他配置类配置mapper扫描路径,单数据源可以直接在启动类上配置,多数据源在每一个数据源配置项配置

@MapperScan(basePackages = { "com.cwh.springbootMybatis.mapper3.**" }

部分代码如下:我这里讲解的是多数据源,其中基类配置如下:

@Configuration
public class BaseDataSource {
	
	@Value("${spring.datasource.maxIdle}")
	protected int maxIdle;
	
	@Value("${spring.datasource.maxWait}")
	protected int maxWait;
	
	@Value("${spring.datasource.minIdle}")
	protected int minIdle;
	
	@Value("${spring.datasource.initialSize}")
	protected int initialSize;
	
	@Value("${spring.datasource.validationQuery}")
	protected String validationQuery;
	
	@Value("${spring.datasource.testOnBorrow}")
	protected boolean testOnBorrow;
 
	@Value("${spring.datasource.testWhileIdle}")
	protected boolean testWhileIdle;
	
	@Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
	protected int timeBetweenEvictionRunsMillis;
 
	@Bean
	public MybatisPlusInterceptor mybatisPlusInterceptor() {
		MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
		interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
		return interceptor;
	}
}

每个数据源配置都要配置,我这里有三个数据源MybatisConfig, MybatisConfig 2, MybatisConfig 3,代码如下:

@Configuration
@MapperScan(basePackages = {"com.cwh.springbootMybatis.mapper.**"}, sqlSessionTemplateRef = "sqlSessionTemplate")
public class MybatisConfig extends BaseDataSource{
   
   @Value("${first.datasource.driver-class-name}")
   private String driverClass;
   
   @Value("${first.datasource.url}")
   private String url;
   
   @Value("${first.datasource.username}")
   private String username;
   
   @Value("${first.datasource.password}")
   private String password;
   
   
   
    @Bean
   @Primary // 必须加此注解，不然报错，下一个类则不需要添加
    @ConfigurationProperties(prefix = "first.datasource") 
    public DataSource dataSource() {
       DruidDataSource datasource = new DruidDataSource();
       datasource.setDriverClassName(driverClass);
       datasource.setUrl(url);
       datasource.setName(username);
       datasource.setPassword(password);
       
       datasource.setMaxIdle(maxIdle);
       datasource.setMaxWait(maxWait);
       datasource.setMinIdle(minIdle);
       datasource.setInitialSize(initialSize);
       datasource.setValidationQuery(validationQuery);
       datasource.setTestOnBorrow(testOnBorrow);
       datasource.setTestWhileIdle(testWhileIdle);
       datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        return datasource;
    }
    @Bean
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
      MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
       bean.setDataSource(dataSource); 
       
       //添加XML目录
       ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
       try {//mapper/*Mapper.xml
          bean.setMapperLocations(resolver.getResources("classpath*:mapper/*Mapper.xml"));
         //分页插件配置,不配置分页插件无效,否则会出现total为0的情况
         bean.setPlugins(mybatisPlusInterceptor());
         return bean.getObject();
       } catch (Exception e) {
          e.printStackTrace();
          throw new RuntimeException(e);
       }
    }
    @Bean
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        SqlSessionTemplate template = new SqlSessionTemplate(sqlSessionFactory);
        return template;
    }

   @Bean
   public PlatformTransactionManager txManager(@Qualifier("dataSource") DataSource dataSource) {
      return new DataSourceTransactionManager(dataSource);
   }

@Configuration
@MapperScan(basePackages = { "com.cwh.springbootMybatis.mapper2.**" }, sqlSessionTemplateRef = "sqlSessionTemplate2")
public class MybatisConfig2 extends BaseDataSource {
   
   @Value("${second.datasource.driver-class-name}")
   private String driverClass;
   
   @Value("${second.datasource.url}")
   private String url;
   
   @Value("${second.datasource.username}")
   private String username;
   
   @Value("${second.datasource.password}")
   private String password;
   
   @Bean
   @ConfigurationProperties(prefix = "second.datasource") // prefix值必须是application.properteis中对应属性的前缀
   public DataSource dataSource2() {
      DruidDataSource datasource = new DruidDataSource();
      datasource.setDriverClassName(driverClass);
       datasource.setUrl(url);
       datasource.setName(username);
       datasource.setPassword(password);
       
       datasource.setMaxIdle(maxIdle);
       datasource.setMaxWait(maxWait);
       datasource.setMinIdle(minIdle);
       datasource.setInitialSize(initialSize);
       datasource.setValidationQuery(validationQuery);
       datasource.setTestOnBorrow(testOnBorrow);
       datasource.setTestWhileIdle(testWhileIdle);
       datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
      return datasource;
   }

   @Bean
   public SqlSessionFactory sessionFactory2(@Qualifier("dataSource2") DataSource dataSource)
         throws Exception {
      MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
      bean.setDataSource(dataSource);
//添加XML目录
      ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
      try {
         bean.setMapperLocations(resolver.getResources("classpath*:mapper2/*Mapper.xml"));
         //分页插件配置,不配置分页插件无效,否则会出现total为0的情况
         bean.setPlugins(mybatisPlusInterceptor());
         return bean.getObject();
      } catch (Exception e) {
         e.printStackTrace();
         throw new RuntimeException(e);
      }
   }

   @Bean
   public SqlSessionTemplate sqlSessionTemplate2(
         @Qualifier("sessionFactory2") SqlSessionFactory sqlSessionFactory) throws Exception {
      SqlSessionTemplate template = new SqlSessionTemplate(sqlSessionFactory);// 使用上面配置的Factory
      return template;
   }
   @Bean
   public PlatformTransactionManager txManager(@Qualifier("dataSource2") DataSource dataSource) {
      return new DataSourceTransactionManager(dataSource);
   }
}

@Configuration
@MapperScan(basePackages = { "com.cwh.springbootMybatis.mapper3.**" }, sqlSessionTemplateRef = "sqlSessionTemplate3")
public class MybatisConfig3 extends BaseDataSource {
   
   @Value("${third.datasource.driver-class-name}")
   private String driverClass;
   
   @Value("${third.datasource.url}")
   private String url;
   
   @Value("${third.datasource.username}")
   private String username;
   
   @Value("${third.datasource.password}")
   private String password;
   
   @Bean
   @ConfigurationProperties(prefix = "third.datasource") // prefix值必须是application.properteis中对应属性的前缀
   public DataSource dataSource3() {
      DruidDataSource datasource = new DruidDataSource();
      datasource.setDriverClassName(driverClass);
       datasource.setUrl(url);
       datasource.setName(username);
       datasource.setPassword(password);
       
       datasource.setMaxIdle(maxIdle);
       datasource.setMaxWait(maxWait);
       datasource.setMinIdle(minIdle);
       datasource.setInitialSize(initialSize);
       datasource.setValidationQuery(validationQuery);
       datasource.setTestOnBorrow(testOnBorrow);
       datasource.setTestWhileIdle(testWhileIdle);
       datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
      return datasource;
   }

   @Bean
   public SqlSessionFactory sessionFactory3(@Qualifier("dataSource3") DataSource dataSource)
         throws Exception {
      MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
      bean.setDataSource(dataSource);
//添加XML目录
      ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
      try {
         bean.setMapperLocations(resolver.getResources("classpath*:mapper3/*Mapper.xml"));
 //分页插件配置,不配置分页插件无效,否则会出现total为0的情况
         bean.setPlugins(mybatisPlusInterceptor());
         return bean.getObject();
      } catch (Exception e) {
         e.printStackTrace();
         throw new RuntimeException(e);
      }
   }

   @Bean
   public SqlSessionTemplate sqlSessionTemplate3(
         @Qualifier("sessionFactory3") SqlSessionFactory sqlSessionFactory) throws Exception {
      SqlSessionTemplate template = new SqlSessionTemplate(sqlSessionFactory);// 使用上面配置的Factory
      return template;
   }
   @Bean
   public PlatformTransactionManager txManager(@Qualifier("dataSource3") DataSource dataSource) {
      return new DataSourceTransactionManager(dataSource);
   }

}

分页代码接口实现类:

@Override
public IPage findPage(MyClass dto) {
    IPage page = new Page(dto.getPage(),dto.getPageSize());
    return appointmentMapper.findPage(page,dto);
} 

 mapper接口:

@Mapper
public interface MyClassMapper extends BaseMapper {

/**
 * 分页查询
 * @param page
 * @param dto
 * @return
 */
IPage findPage(IPage page, MyClass dto);

 }

mapper对应的xml SQL如下: 

 
 
select t0.* from table_1 t0 where t0.delflag=0
 
 

   and t0.source_from = #{dto.sourceFrom}