Spring Mybaits multi-data source dynamic switching

The scene that

In daily development, it is a common requirement to connect multiple databases. Our system is based on Spring Boot + Mybatis for database operation. The common idea on the Internet is to create different beans based on different databases.

package com.joylee.fd.crontab.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.joylee.fd.crontab.mapper.distribution", sqlSessionTemplateRef  = "distributionSqlSessionTemplate")
public class DistributionDBConfiguration {
        @Bean(name = "distributionDataSource")
        @ConfigurationProperties(prefix = "spring.datasource")
        @Primary
        public DataSource distributionDataSource(a) {
            return DataSourceBuilder.create().build();
        }

        @Bean(name = "distributionSqlSessionFactory")
        @Primary
        public SqlSessionFactory distributionSqlSessionFactory(@Qualifier("distributionDataSource") DataSource dataSource) throws Exception {
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:com/joylee/distribution/mapper/*.xml"));
            return bean.getObject();
        }

        @Bean(name = "distributionDataSourceTransactionManager")
        @Primary
        public DataSourceTransactionManager distributionTransactionManager(@Qualifier("distributionDataSource") DataSource dataSource) {
            return new DataSourceTransactionManager(dataSource);
        }

        @Bean(name = "distributionSqlSessionTemplate")
        @Primary
        public SqlSessionTemplate distributionSqlSessionTemplate(@Qualifier("distributionSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
            return newSqlSessionTemplate(sqlSessionFactory); }}Copy the code

Similarly, create multiple configurations and multiple beans. Since this is set to @Configuration, the beans will be created at startup. To use it, simply place the different database mapper in the set classpath (line 35), default to @primary, and add @qualifier(“bean name “) to the database DAO.

There are many implementations of this scheme on the Internet, which are detailed here. You can search for Spring Boot Mybatis multi-database solution.

This solution is suitable for the number of database fixed such requirements, if the database is dynamic real-time modification, then how to deal with it, or the number of database and has been expanding (such as the branch database scenario), such processing is obviously not, then how should we deal with it? Here is the solution.

Based on Mybatis implementation ideas

In order to understand MyBatis, here is a brief introduction to myBatis core classes related to database connection.

Mybatis implementation code structure is relatively easy to understand, we focus on the management of database connection switch, all we focus on the main is: SqlSessionFactory, SqlSession, Configuration, Environment, and of course a datasouce class, which is a JDBC class that manages database connections.

My initial approach was to bypass SqlSessionFactory Bean creation, since the Bean life cycle is executed at startup and seems to be immutable. So I considered the following ways:

Mybatis API new SqlSessionFactory().build() to create a new SqlSessionFactory for each database connection. SQL > create SqlSession from SqlSessionFactory Creating multiple SQLSessionFactories is obviously not a good idea. SqlSessionFactory is best used as a singleton and can create different SQLsessions for different databases.

Implementation based on Java Mybatis, the approximate steps are:

// Code from mybaits official website
DataSource dataSource = BaseDataTest.createBlogDataSource();
TransactionFactory transactionFactory = new JdbcTransactionFactory();

Environment environment = new Environment("development", transactionFactory, dataSource);

Configuration configuration = new Configuration(environment);
configuration.setLazyLoadingEnabled(true);
configuration.setEnhancementEnabled(true);
configuration.getTypeAliasRegistry().registerAlias(Blog.class);
configuration.getTypeAliasRegistry().registerAlias(Post.class);
configuration.getTypeAliasRegistry().registerAlias(Author.class);
configuration.addMapper(BoundBlogMapper.class);
configuration.addMapper(BoundAuthorMapper.class);

SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(configuration);
Copy the code

Based on Spring Mybatis implementation ideas

Now Java projects are almost all based on Spring development, for such a need, can certainly be solved in the way of Spring, I fell into a mistake, always under the influence of the idea that beans are difficult to modify after creation, abandoned the way of Spring bean management. But this is also the wrong direction.

SqlSessionFactorybean does create a bean at startup, but each bean itself provides many methods and attributes. Many of the attributes of the bean can be changed. All we have to do is get the bean we created when the program started, and then modify the value of the property.

 / * * *@paramCorpDatabaseBO Database entity *@return SqlSessionFactory
     * @throws PropertyVetoException
     */
    public  SqlSessionFactory changeSqlSessionFactory(CorpDatabaseBO corpDatabaseBO) throws Exception {
        // Get the current SqlSessionFactory bean
        
        SqlSessionFactory bean = SpringUtils.getBean(SqlSessionFactory.class);
        
        if(bean==null) {throw new NullPointerException("default SqlSessionFactory bean is not created");
            
        }
        
        logger.info(String.format("Current sqlsessionFactory :%s",bean.getConfiguration().getEnvironment().getDataSource().getConnection().getCatalog()));
        
		// because business needs, I use sqlserver
        SQLServerDataSource sqlServerDataSource = new SQLServerDataSource();
        sqlServerDataSource.setServerName(corpDatabaseBO.getUrl());
        sqlServerDataSource.setDatabaseName(corpDatabaseBO.getDatabasename());
        sqlServerDataSource.setUser(corpDatabaseBO.getUsername());
        sqlServerDataSource.setPassword(corpDatabaseBO.getPassword());
        sqlServerDataSource.setPortNumber(corpDatabaseBO.getPort());

        // The database connection pool uses Hikari
        HikariDataSource hikariDataSource = new HikariDataSource();
        hikariDataSource.setDataSource(sqlServerDataSource);

        TransactionFactory transactionFactory =  new JdbcTransactionFactory();
        Environment environment = new Environment(corpDatabaseBO.getDatabasename(), transactionFactory, hikariDataSource);

// Change the environment so that the database address can be changed.
        bean.getConfiguration().setEnvironment(environment);
        return bean;
    }

Copy the code

On line 30:

bean.getConfiguration().setEnvironment(environment);
Copy the code

To modify the database connection, obtain the Configuration through the bean and then set the Environment. Each time you need to perform a database switch, simply call the changeSqlSessionFactory method again.

conclusion

• Encountered in the development, in the case of have no good solution, can go online to find relevant information, but sometimes, because of the particularity of their business, in fact the Internet is no good solution, even some online solutions is not a good solution, we reference others solutions, Be sure to figure out the meaning of each layer of code, whether you really need, rather than simply copy.
• When there is no online solution, it is recommended to spend more time to read the source code, because this function, although not read all the source code of Mybatis, but the overall structure of Mybatis has a clear understanding, most of the source code also have to read, this is the biggest harvest.