Preface:

Application as the increase of the number of users, the number of concurrent requests corresponding will also follow the increasing, gradually, a single database has no way to meet our frequent database operation request, in some scenarios, we might need to configure the multiple data sources, using multiple data sources (such as separate database, speaking, reading and writing) to alleviate the pressure of system, etc., in the same way, Springboot officially provides an implementation to help developers configure multiple data sources, usually in two ways (as far as I know), subcontracting and AOP. The use of AOP for dynamic switching between multiple data sources will be covered in a separate article. Considering that MyBatis is a database framework frequently used by Java developers, this article uses Springboot+ MyBatis to realize the configuration of multiple data sources.

Without further ado, let’s go.

1. Database preparation:

Since we are configuring multiple data sources, we naturally need to prepare the corresponding data sources first. Here, I create two databases locally, as shown in the following table:

The database testdatasource1 testdatasource2
The data table sys_user sys_user2
field User_id (int), user_name(varchar) user_age (int) with

For comparison purposes, testdatasource1 is Zhang SAN, 25, and Testdatasource2 is Li Si, 30.

2. Environment preparation

Start by creating a new Springboot project (2.1.7.release) and introducing dependencies in the POM file: The key dependencies are as follows:

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

Copy the code

At this point our environment is basically configured.

3. Code parts

1. Configure multiple data sources

First of all, you have datasourse in our Springboot configuration file. Unlike before, we have two data sources, so we specify the name of the database, where the primary data source is primary, and the secondary data source is secondary.

# configuration primary database spring. The datasource. Primary. JDBC - url = JDBC: mysql: / / localhost: 3306 / testdatasource1? useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false spring.datasource.primary.username=root Spring. The datasource. Primary. Password = root spring. The datasource. Primary. The driver - class - name = com. Mysql. Cj). The JDBC driver # # configuration database spring.datasource.secondary.jdbc-url=jdbc:mysql://localhost:3306/testdatasource2? useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false spring.datasource.secondary.username=root spring.datasource.secondary.password=root spring.datasource.secondary.driver-class-name=com.mysql.cj.jdbc.Driver spring.http.encoding.charset=UTF-8 spring.http.encoding.enabled=true spring.http.encoding.force=trueCopy the code

Note that Springboot2.0 requires the use of JDBC-URL when configuring database connections, and will be reported if only the URL is used

jdbcUrl is required with driverClassName. Error.

Create a new configuration class, PrimaryDataSourceConfig, to configure the beans associated with our primary database as follows:

@Configuration
@MapperScan(basePackages = "com.jdkcb.mybatisstuday.mapper.one", sqlSessionFactoryRef = "PrimarySqlSessionFactory")//basePackages: indicates the package path of the interface file
public class PrimaryDataSourceConfig {

    @Bean(name = "PrimaryDataSource")
    // Indicates that the data source is the default
    @Primary// This must be added. If there is no @primary for either source, an error will be reported
    @ConfigurationProperties(prefix = "spring.datasource.primary")// The prefix in our configuration file
    public DataSource getPrimaryDateSource(a) {
        return DataSourceBuilder.create().build();
    }


    @Bean(name = "PrimarySqlSessionFactory")
    @Primary
    public SqlSessionFactory primarySqlSessionFactory(@Qualifier("PrimaryDataSource") DataSource datasource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(datasource);
        bean.setMapperLocations( 
                new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/one/*.xml"));
        return bean.getObject();// Set the location of myBatis XML
    }
    
    
    @Bean("PrimarySqlSessionTemplate")
    // Indicates that the data source is the default
    @Primary
    public SqlSessionTemplate primarySqlSessionTemplate(
            @Qualifier("PrimarySqlSessionFactory") SqlSessionFactory sessionfactory) {
        return newSqlSessionTemplate(sessionfactory); }}Copy the code

Remarks:

MapperScan: Configures where the myBatis interface class is placed

@primary: indicates that the default database is used, otherwise it will cause an error because you do not know which database is the default database

ConfigurationProperties: Reads the configuration parameters in Application. properties mapped into an object, where prefix indicates the prefix of the parameter

Are we done? No, then configure our second data source configuration class as follows:

@Configuration
@MapperScan(basePackages = "com.jdkcb.mybatisstuday.mapper.two", sqlSessionFactoryRef = "SecondarySqlSessionFactory")
public class SecondaryDataSourceConfig {

    @Bean(name = "SecondaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource getSecondaryDataSource(a) {
        return DataSourceBuilder.create().build();
    }
    @Bean(name = "SecondarySqlSessionFactory")
    public SqlSessionFactory secondarySqlSessionFactory(@Qualifier("SecondaryDataSource") DataSource datasource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(datasource);
        bean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/two/*.xml"));
        return bean.getObject();// Set the location of myBatis XML
    }
    @Bean("SecondarySqlSessionTemplate")
    public SqlSessionTemplate secondarySqlSessionTemplate(
            @Qualifier("SecondarySqlSessionFactory") SqlSessionFactory sessionfactory) {
        return new SqlSessionTemplate(sessionfactory);
    }

Copy the code

All that remains is to write our corresponding XML file and interface class as follows:

@Component
@Mapper
public interface PrimaryUserMapper {
    List<User> findAll(a);
}


@Component
@Mapper
public interface SecondaryUserMapper {
    List<User> findAll(a);
}
Copy the code

The relevant XML file is as follows:

<? The XML version = "1.0" encoding = "utf-8"? > <! DOCTYPE mapper PUBLIC "- / / mybatis.org//DTD mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > < mapper namespace="com.jdkcb.mybatisstuday.mapper.one.PrimaryUserMapper"> <select id="findAll" resultType="com.jdkcb.mybatisstuday.pojo.User"> select * from sys_user; </select> </mapper> <? The XML version = "1.0" encoding = "utf-8"? > <! DOCTYPE mapper PUBLIC "- / / mybatis.org//DTD mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > < mapper namespace="com.jdkcb.mybatisstuday.mapper.two.SecondaryUserMapper"> <select id="findAll" resultType="com.jdkcb.mybatisstuday.pojo.User"> select * from sys_user2; </select> </mapper>Copy the code

Note: The XML file in this example is resources/mapping

2. The test

Write a Controller to test, because it’s a test instance and the code is relatively simple, so I won’t write the Service layer here.

The code is as follows:

@RestController
public class UserController {

    @Autowired
    private PrimaryUserMapper primaryUserMapper;
    @Autowired
    private SecondaryUserMapper secondaryUserMapper;
    @RequestMapping("primary")
    public Object primary(a){
        List<User> list = primaryUserMapper.findAll();
        return list;
    }
    @RequestMapping("secondary")
    public Object secondary  (a){
        List<User> list = secondaryUserMapper.findAll();
        returnlist; }}Copy the code

Input, respectively, in a browser: http://127.0.0.1:8080/primary and http://127.0.0.1:8080/secondary

Here are the results:

[{" user_id ": 1," user_name ":" zhang ", "user_age" : 25}] / / primary [{" user_id ": 1," user_name ":" bill ", "user_age" : 30}] / / secondaryCopy the code

At this point, Springboot and MyBatis are ready to configure multiple data sources.

Finally, hello everyone, I am Han shu, hum, pay attention to me, you have good fruit to eat (akimbo).

Remember to click “like” before you go

Wait a minute:

Welcome to my Github download (welcome star) :

Github.com/hanshuaikan…