Spring boot connecting multiple data sources

1. Add two datasource s to application.yml

server:
  port: 8080
spring:
  application:
    name: king
  datasource:
    master:
      type: com.zaxxer.hikari.HikariDataSource
      jdbc-url: jdbc:mysql://localhost:3306/shengong?useUnicode=yes&characterEncoding=UTF-8&serverTimezone=UTC
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: root
      password: arongking
      idle-timeout: 10000
      minimum-idle: 1
      maximum-pool-size: 10
      pool-name: master
      auto-commit: false
      connection-test-query: SELECT 1
    slave:
      type: com.zaxxer.hikari.HikariDataSource
      jdbc-url: jdbc:mysql://localhost:3306/king?useUnicode=yes&characterEncoding=UTF-8&serverTimezone=UTC
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: root
      password: arongking
      idle-timeout: 10000
      minimum-idle: 1
      maximum-pool-size: 10
      pool-name: slave
      auto-commit: false
      connection-test-query: SELECT 1
2. Manually initialize the data sourcemasterdatasource
package cn.bj.king.config;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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 org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;

/**
* @author ARongking
* @date 2019-03-28
*/
@Configuration
@EnableTransactionManagement
@MapperScan(basePackages = {"cn.bj.king.mapper.master"},
        sqlSessionFactoryRef = "masterSqlSessionFactory")
public class WriteDataSourceConfig {

    @Bean(name = "masterDataSource")
    @Primary
    @Qualifier("masterDataSource")
    public DataSource masterDataSource(@Qualifier("masterHikariConfig")HikariConfig hikariConfig) {
        System.out.println("Instantiate main library");
        HikariDataSource hikariDataSource=new HikariDataSource(hikariConfig);
        return hikariDataSource;
    }

    /**
    * Configure connection pool information
    * @return
    */
    @ConfigurationProperties(prefix = "spring.datasource.master")
    @Bean("masterHikariConfig")
    public HikariConfig masterHikariConfig(){
        HikariConfig hikariConfig=new HikariConfig();
        return hikariConfig;
    }

    /**
     * SqlSessionFactory To configure
     *
     * @return
     * @throws Exception
     */
    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        // Configure mapper file location
        sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath:mapper/master/*.xml"));
        sqlSessionFactoryBean.setTypeAliasesPackage("cn.bj.king.entity");
        return sqlSessionFactoryBean.getObject();
    }

    /**
     * Configure things Manager
     *
     * @return
     */
    @Bean(name = "masterTransactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager(@Qualifier("masterDataSource") DataSource dataSource) {
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(dataSource);
        return dataSourceTransactionManager;
    }
}
3. Manually initialize the data sourcereaddatasource
package cn.bj.king.config;

import com.github.pagehelper.PageInterceptor;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;
import java.util.Properties;

@Configuration
@EnableTransactionManagement
@MapperScan(basePackages = {"cn.bj.king.mapper.slave"},
        sqlSessionFactoryRef = "slaveSqlSessionFactory")
public class ReadDataSourceConfig {

    @Autowired
    Environment environment;

    @Bean(name = "slaveDataSource")
    @Qualifier("slaveDataSource")
    public DataSource slaveDataSource(@Qualifier("slaveHikariConfig")HikariConfig hikariConfig) {
        System.out.println("Instantiate from library");
        HikariDataSource hikariDataSource=new HikariDataSource(hikariConfig);
        return hikariDataSource;
    }

    /**
     * Configure connection pool information
     * @return
     */
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    @Bean("slaveHikariConfig")
    public HikariConfig slaveHikariConfig(){
        HikariConfig hikariConfig=new HikariConfig();
        return hikariConfig;
    }

    /**
     * SqlSessionFactory To configure
     *
     * @return
     * @throws Exception
     */
    @Bean(name = "slaveSqlSessionFactory")
    public SqlSessionFactory slaveSqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        // Configure mapper file location
        sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath:mapper/slave/*.xml"));
        sqlSessionFactoryBean.setTypeAliasesPackage("cn.bj.king.entity");
        return sqlSessionFactoryBean.getObject();
    }

    /**
     * Configure things Manager
     *
     * @return
     */
    @Bean(name = "slaveTransactionManager")
    public DataSourceTransactionManager slaveTransactionManager(@Qualifier("slaveDataSource") DataSource dataSource) {
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(dataSource);
        return dataSourceTransactionManager;
    }
}
4. Test whether the initialization of master and slave data sources is normal
 5. There are several points to be noted here:
    *When configuring the database connection pool, if you do not manually set HikariConfig, that is, to obtain the configuration of the database connection pool in yml one by one through the Environment, the database connection pool (idle timeout, minimum idle, maximum pool size, pool name, auto commit, connection test query) must be level with the slave node, otherwise HikariConfig is Unable to read the information.
    *If you assign HikariConfig manually, there is no limit. You can write anywhere. Just use Environment to read the value of the configuration file and set it to HikariConfig manually.

Tags: Java JDBC Spring MySQL Mybatis

Posted on Mon, 04 Nov 2019 13:11:06 -0800 by calavera