Spring boot + mybatis + multiple data sources

Keywords: Spring Database Mybatis JDBC

Spring boot + mybatis + multiple data sources

Necessary dependencies

  <!--Connect mysql data base-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!--Link pool configuration, connection driver -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.13</version>
        </dependency>

        <!--Introduce mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.0</version>
        </dependency>

application.properties configures multiple data source connections and connection pools

#Configure multiple data sources
spring.datasource.primary.jdbc-url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8 &serverTimezone=Asia/Shanghai
spring.datasource.primary.username=root
spring.datasource.primary.password=root
spring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driver


###########################  cbh data base   ##########################
#Configure multiple data sources
spring.datasource.secondary.jdbc-url=jdbc:mysql://127.0.0.1:3306/cbh?useUnicode=true&characterEncoding=utf8 &serverTimezone=Asia/Shanghai
spring.datasource.secondary.username=root
spring.datasource.secondary.password=root
spring.datasource.secondary.driver-class-name=com.mysql.cj.jdbc.Driver

#Do not use the default configuration source
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.initialSize=2
spring.datasource.minIdle=1
spring.datasource.maxActive=20
spring.datasource.maxWait=60000

Configuring the Core Configuration of Multiple Data Sources

Configuration of Connecting test Master Database

@ Primary // is used to mark the master data source, which is not added to any injected file except the master data source.
@ MapperScan(basePackages = com.example.demo.dao.primary), sqlSession Template Ref = Primary Session Template) where basePackages represent the scope of the data source when connecting mybatis to a database

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.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.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.example.demo.dao.primary", sqlSessionTemplateRef  = "PrimarySessionTemplate")
public class PrimaryDataSourceConfig {

    @Bean(name = "PrimaryDataSource") //As a bean object and named
    @ConfigurationProperties(prefix = "spring.datasource.primary") //In the configuration file, the prefix of the data source
    @Primary   //For marking up master data sources, this annotation is not added to any injected files other than master data sources
    public DataSource PrimaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "PrimarySessionFactory")
    @Primary
    public SqlSessionFactory PrimarySessionFactory(@Qualifier("PrimaryDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }

    @Bean(name = "PrimaryTransactionManager")
    @Primary
    public DataSourceTransactionManager PrimaryTransactionManager(@Qualifier("PrimaryDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "PrimarySessionTemplate")
    @Primary
    public SqlSessionTemplate PrimarySessionTemplate(@Qualifier("PrimarySessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

Configuration of connecting cbh database (from database)

@Configuration
@MapperScan(basePackages = "com.example.demo.dao.secondary", sqlSessionTemplateRef  = "SecondarySessionTemplate")
public class SecondaryDataSourceConfig {
    @Bean(name = "SecondaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    //  @Primary
    public DataSource SecondaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "SecondarySessionFactory")
    //  @Primary
    public SqlSessionFactory SecondarySessionFactory(@Qualifier("SecondaryDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }

    @Bean(name = "SecondaryTransactionManager")
    //   @Primary
    public DataSourceTransactionManager SecondaryTransactionManager(@Qualifier("SecondaryDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "SecondarySessionTemplate")
    //   @Primary
    public SqlSessionTemplate SecondarySessionTemplate(@Qualifier("SecondarySessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

Project structure and project code

Operation of mybatis connecting database

Operation of Connecting Main Database

I put it in the com.example.demo.dao.primary directory and connected to the test database.

@Repository
public interface TuserDao {
    //View all Tuser information
    @Select("select * from t_user")
    List<Tuser> getListOfTuser();

}

Operation of Connection Number Database

I put it in the com.example.demo.dao.secondary directory and connected to the cbh database.

@Repository
public interface CBHDao {
    @Select("select id,name,phone,uuid,login_pwd from t_user")
    @Results({
            @Result(column = "login_pwd", property = "pwd")
    })
    List<Tuser> getListOfTuserByCBH();
}

Control layer

Operating test database

@Autowired
    private TuserService tuserServiceimpl;

    @GetMapping("/test/getListOfTuser")
    public Object getListOfTuser(){
        List<Tuser> list=tuserServiceimpl.getListOfTuser();
        return list;
    }

Operating cbh database

@Autowired
    private CBHService cbhServiceimpl;


    @GetMapping("/cbh/getListOfTuserByCBH")
    public Object getListOfTuserByCBH(){
        List<Tuser> list=cbhServiceimpl.getListOfTuserByCBH();
        return "list Number of records"+list.size();
    }

Results from multiple data sources

Connecting to test database: http://localhost:8080/test/getListOfTuser

Operation of connecting CBH database: http://localhost:8080/cbh/getListOfTuserByCBH

Posted by raidon on Tue, 01 Oct 2019 23:35:28 -0700