SpringBoot integrated with Mybatis dynamic multi-data source, MybatisPlus IPage failure solution

background

When I was doing data extraction, I built a shelf for dynamic data source switching of MyBatis. It makes it easier for them to write code for extraction. Today, my colleague asked me why the IPage of Mybatisplus in the shelf failed. Mybatis configuration overwrites the existing configuration when I write the dynamic data source. So I asked him to delete the configuration I wrote first to see if it was normal. Get a reply, delete normal. The next solution to this problem is simply to add a pager to the configuration.

The solution

Set up a pager bean configuration

@Bean
public PaginationInterceptor paginationInterceptor() {
    return new PaginationInterceptor();
}
Copy the code

Let’s just create a custom class, but let’s hand it over to the Spring container as a bean.

Injection into sqlSessionFactory

    @Bean(name="sessionFactory")
    public SqlSessionFactory sessionFactory(
            @Qualifier("bigDataDataSource") DataSource bigDataDataSource,
            @Qualifier("branchDataSource") DataSource branchDataSource,
            @Qualifier("basicDataSource") DataSource basicDataSource, org.apache.ibatis.session.Configuration config) throws Exception{ SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean(); // construct method to solve the problem of dynamic data source loop dependency. MybatisConfiguration configuration = new MybatisConfiguration(); configuration.addInterceptor(new PaginationInterceptor()); sessionFactoryBean.setConfiguration(configuration); sessionFactoryBean.setConfiguration(config); sessionFactoryBean.setDataSource(this.DataSource(bigDataDataSource,branchDataSource, basicDataSource));return sessionFactoryBean.getObject();
    }
Copy the code

In this paper, we summarize the

At this point, our Mybatisplus paging plug-in is ready to go. The following to provide you with MyBatis multi-data source solution.

Dynamic data source solution

Database configuration file

Our project uses the configuration file in THE form of YML and the database connection pool of HIkari. The first step is naturally to configure multiple database sources. We find spring’s datasource and configure the three data sources below.

spring:
  application:
    name: dynamicDatasource
  datasource:
    test1: the driver - class - name: com. Mysql.. JDBC driver url: JDBC: mysql: / / 127.0.0.1:3306 /test1? serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false
      username: root
      password: 123456
    test2: the driver - class - name: com. Mysql.. JDBC driver url: JDBC: mysql: / / 127.0.0.1:3306 /test2? serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false
      username: root
      password: 123456
    test3: driver - class - name: com. Mysql.. JDBC driver url: JDBC: mysql: / / 127.0.0.1:3306 /test3? serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false
      username: root
      password: 123456
    hikari:
      leak-detection-threshold: 2000
Copy the code

Define the data source entity class

We can create a datasourceBean folder to manage the entity classes of the datasource. We’re going to create three entity classes here. They correspond to test1, test2, and test3

@Configuration
public class Test1DataSourceBean {

    @Value("${spring.datasource.test1.driver-class-name}")
    private String test1Driver;

    @Value("${spring.datasource.test1.url}")
    private String test1Url;

    @Value("${spring.datasource.test1.username}")
    private String test1Username;

    @Value("${spring.datasource.test1.password}")
    private String test1Password;

    @Bean(name="test1DataSource")
    public DataSource test1DataSource() throws Exception{
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setDriverClassName(test1Driver);
        dataSource.setJdbcUrl(test1Url);
        dataSource.setUsername(test1Username);
        dataSource.setPassword(test1Password);
        returndataSource; }}Copy the code

@Configuration
public class Test2DataSourceBean {

    @Value("${spring.datasource.test2.driver-class-name}")
    private String test2Driver;

    @Value("${spring.datasource.test2.url}")
    private String test2Url;

    @Value("${spring.datasource.test2.username}")
    private String test2Username;

    @Value("${spring.datasource.test2.password}")
    private String test2Password;

    @Bean(name="test2DataSource")
    public DataSource test2DataSource() throws Exception{
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setDriverClassName(test2Driver);
        dataSource.setJdbcUrl(test2Url);
        dataSource.setUsername(test2Username);
        dataSource.setPassword(test2Password);
        returndataSource; }}Copy the code

@Configuration
public class Test3DataSourceBean {

    @Value("${spring.datasource.test3.driver-class-name}")
    private String test3Driver;

    @Value("${spring.datasource.test3.url}")
    private String test3Url;

    @Value("${spring.datasource.test3.username}")
    private String test3Username;

    @Value("${spring.datasource.test3.password}")
    private String test3Password;

    @Bean(name="test3DataSource")
    public DataSource test3DataSource() throws Exception{
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setDriverClassName(test3Driver);
        dataSource.setJdbcUrl(test3Url);
        dataSource.setUsername(test3Username);
        dataSource.setPassword(test3Password);
        returndataSource; }}Copy the code

Define an enumerated class to manage the data source

public enum DatabaseType {

    test1 ("test1"."test1"),
    test2 ("test2"."test2"),
    test3 ("test3"."test3");

    private String name;
    private String value;

    DatabaseType(String name, String value){
        this.name = name;
        this.value = value;
    }

    public String getName() {return name;
    }

    public String getValue() {returnvalue; }}Copy the code

Define a thread-safe data source container

public class DatabaseContextHolder { private static final ThreadLocal<DatabaseType> contextHolder = new ThreadLocal<>();  public static voidsetDatabaseType(DatabaseType type){
        contextHolder.set(type);
    }

    public static DatabaseType getDatabaseType() {returncontextHolder.get(); }}Copy the code

Defining dynamic data sources

public class DynamicDataSource extends AbstractRoutingDataSource{

    protected Object determineCurrentLookupKey() {
        returnDatabaseContextHolder.getDatabaseType(); }}Copy the code

Mybatis configuration class

Many articles on the web are configured to create a data source dependency problem, which is solved here.

@Configuration
@MapperScan(basePackages="cn.test.jichi", sqlSessionFactoryRef="sessionFactory"Public class MybatisConfig {/** * @description: set the dynamic data source */ @bean (name="dynamicDataSource")
    @Primary
    public DynamicDataSource DataSource(
            @Qualifier("test1DataSource") DataSource test1DataSource,
            @Qualifier("test2DataSource") DataSource test2DataSource,
            @Qualifier("test3DataSource") DataSource test3DataSource){
        Map<Object, Object> targetDataSource = new HashMap<>();
        targetDataSource.put(DatabaseType.test1, test1DataSource);
        targetDataSource.put(DatabaseType.test2, test2DataSource);
        targetDataSource.put(DatabaseType.test3, test3DataSource);
        DynamicDataSource dataSource = new DynamicDataSource();
        dataSource.setTargetDataSources(targetDataSource);
        dataSource.setDefaultTargetDataSource(test1DataSource);
        return dataSource;
    }

    /**
     *  @Description:根据动态数据源创建sessionFactory
     */
    @Bean(name="sessionFactory")
    public SqlSessionFactory sessionFactory(
            @Qualifier("test1DataSource") DataSource test1DataSource,
            @Qualifier("test2DataSource") DataSource test2DataSource,
            @Qualifier("test3DataSource") DataSource test3DataSource) throws Exception{ SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean(); // construct method to solve the problem of dynamic data source loop dependency. sessionFactoryBean.setDataSource(this.DataSource(test1DataSource,test2DataSource, test3DataSource));
        returnsessionFactoryBean.getObject(); }}Copy the code

Provide an example

    public void testDymnaicDatasource(){// Do not switch the data source default is its own. System.out.println("----- Default data source");
        DemoEntity totalCount = demoMapper.getTotalCount();
        String nameCount1 = totalCount.getNameCount();
        String ageCount2 = totalCount.getAgeCount();
        System.out.println("nameCount:"+nameCount1);
        System.out.println("ageCount:"+ageCount2); // Switch to branch system.out.println ("----- data source is test2");
        DynamicDataSourceUtils.chooseBranchDataSource();
        Integer nameCount = demoMapper.getNameCount();
        Integer ageCount = demoMapper.getAgeCount();
        System.out.println("nameCount:"+nameCount);
        System.out.println("ageCount:"+ageCount); // The data source is basic system.out.println ("----- data source is test3");
        DynamicDataSourceUtils.chooseBasicDataSource();
        Integer ageCount1 = demoMapper.getAgeCount();
        System.out.println("ageCount:"+ageCount1);

    }
Copy the code

conclusion

At this point, the problem we discussed in the title has been solved and a solution for dynamic data sources has been provided.