Spring Boot2(IV) : Use Spring Boot multi-data source implementation process

This article is published at the same time in personal technology blog [Bird does not poop]. For details, you can also scan the qr code at the end of the article to pay attention to personal public number [Bird does not poop].

preface

In actual business scenarios, it is impossible to have only one library, so there is the emergence of sub-database sub-table and multi-data source. To achieve read and write separation, the master library is responsible for adding and deleting, from the library is responsible for the query. This article will implement Spring Boot how to implement multiple data sources, dynamic data source switching, read and write separation and other operations.

Code deployment

Quick new project Spring-boot project

1. Add maven dependencies

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

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

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter</artifactId>
</dependency>

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

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>
Copy the code

2, Application configure multiple data source read configuration

As in the previous tutorial, first configure application.yml

Set the configuration file to test
spring:
  profiles:
    active: test

# configuration Mybatis
mybatis:
  configuration:
    Table(create_time) -> Entity(createTime) Mybatis will automatically recognize 'uppercase letters and underscores'
    map-underscore-to-camel-case: true

Print SQL logs
logging:
  level:
    com.niaobulashi.mapper.*: DEBUG
Copy the code

The printing of SQL logs is represented by * because it is a multi-data source and distinguishes different database source XML files under mapper package.

Configure application-test.yml as follows

Spring: a datasource: # main library master: JDBC - url: JDBC: mysql: / / 127.0.0.1:3306 / test? serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true username: root password: root driver-class-name: Com. Mysql. Cj, JDBC Driver # from library slave: JDBC - url: JDBC: mysql: / / 127.0.0.1:3306 / test2? serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true username: root password: root driver-class-name: com.mysql.cj.jdbc.DriverCopy the code

From the spring. Datasource node, distinguish the master library from slave library. The primary database is connected to test, and the secondary database is connected to test2.

Note: It is important to note that since Spring Boot2, some configurations have changed when configuring multiple data sources, and many tutorials online use spring.datasource. Url. JdbcUrl is required with driverClassName. The problem.

Solution: Change the spring.datasource. Url configuration to spring.datasource. Jdbc-url when configuring multiple data sources

3. Add the master library configuration information

We are going to analyze a wave of posts written by a well-known blogger: Pure Smile

First look at the main library configuration code:

@Configuration
@MapperScan(basePackages = "com.niaobulashi.mapper.master", sqlSessionTemplateRef = "masterSqlSessionTemplate")
public class DataSourceMasterConfig {

    /** * The spring.datasource. Master configuration in application-test.yml is valid *@return* /
    @Bean(name = "masterDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.master")
    @Primary
    public DataSource masterDataSource(a) {
        return DataSourceBuilder.create().build();
    }

    /** * Inject configuration information into SqlSessionFactoryBean *@paramDataSource Database connection information *@return
     * @throws Exception
     */
    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/master/*.xml"));
        return bean.getObject();
    }

    /** * transaction manager, inject master * at instantiation time@param dataSource
     * @return* /
    @Bean(name = "masterTransactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager(@Qualifier("masterDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /** * SqlSessionTemplate is thread-safe *@param sqlSessionFactory
     * @return
     * @throws Exception
     */
    @Bean(name = "masterSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate masterSqlSessionTemplate(@Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return newSqlSessionTemplate(sqlSessionFactory); }}Copy the code

Question: MasterSqlSessionFactory: SqlSessionFactoryBean obtains only the spring.datasource. Master database connection information. Mybatis. Configuration does not get the configuration information for multiple databases, so we need to configure the camel name rule. The configuration information is not injected into the SqlSessionFactoryBean. User_id, dept_id, create_time: user_id, dept_id, create_time

Solution: Add Configuration to the Configuration

At the same time, inject the configuration information into the SqlSessionFactoryBean

/** * Inject configuration information into SqlSessionFactoryBean *@paramDataSource Database connection information *@return
 * @throws Exception
 */
@Bean(name = "slaveSqlSessionFactory")
public SqlSessionFactory slaveSqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource) throws Exception {
    SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
    // The configuration information is loaded into the class and then injected into the SqlSessionFactoryBean
    org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
    configuration.setMapUnderscoreToCamelCase(true);
    bean.setConfiguration(configuration);
    bean.setDataSource(dataSource);
    bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/slave/*.xml"));
    return bean.getObject();
}
Copy the code

4. Add the slave library configuration information

It is the same as adding the main library configuration, except that you do not need to add the @primary preferred annotation

The following code

@Configuration
@MapperScan(basePackages = "com.niaobulashi.mapper.slave", sqlSessionTemplateRef = "slaveSqlSessionTemplate")
public class DataSourceSlaveConfig {

    /** * The spring.datasource. Master configuration in application-test.yml is valid *@return* /
    @Bean(name = "slaveDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    public DataSource slaveDataSource(a) {
        return DataSourceBuilder.create().build();
    }

    /** * Inject configuration information into SqlSessionFactoryBean *@paramDataSource Database connection information *@return
     * @throws Exception
     */
    @Bean(name = "slaveSqlSessionFactory")
    public SqlSessionFactory slaveSqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        // The configuration information is loaded into the class and then injected into the SqlSessionFactoryBean
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setMapUnderscoreToCamelCase(true);
        bean.setConfiguration(configuration);
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/slave/*.xml"));
        return bean.getObject();
    }

    /** * transaction manager, inject master * at instantiation time@param dataSource
     * @return* /
    @Bean(name = "slaveTransactionManager")
    public DataSourceTransactionManager slaveTransactionManager(@Qualifier("slaveDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /** * SqlSessionTemplate is thread-safe *@param sqlSessionFactory
     * @return
     * @throws Exception
     */
    @Bean(name = "slaveSqlSessionTemplate")
    public SqlSessionTemplate slaveSqlSessionTemplate(@Qualifier("slaveSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return newSqlSessionTemplate(sqlSessionFactory); }}Copy the code

5. The extended configuration method will report an error

The @ConfigurationProperties annotation provides the following configuration information for Mybatis

/** * Try the mybatis. Configuration configuration in application.yml to take effect, if not actively configured, because@OrderIf the configuration order is different, the configuration will not take effect in a timely manner@return* /
@Bean
@ConfigurationProperties(prefix = "mybatis.configuration")
public org.apache.ibatis.session.Configuration configuration(a) {
    return new org.apache.ibatis.session.Configuration();
}
Copy the code

The id of prefix in the master and slave libraries is the same and must be different, otherwise IDEA will prompt an error of Duplicate prefix

As a result, only the master library can execute the Mybatis configuration, and the slave library is invalid.

@Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource, org.apache.ibatis.session.Configuration configuration) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        // The configuration information is loaded into the class and then injected into the SqlSessionFactoryBean
        bean.setConfiguration(configuration);
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/master/*.xml"));
        return bean.getObject();
    }
Copy the code

This validation is valid only for the master library, not for the hump method resolution of the slave library. Check back later…

6. Data layer code

The code structure is as follows

SysUserMasterDao code

public interface SysUserMasterDao {
	
	/** * Query user information based on userId *@paramUserId userId */
	List<SysUserEntity> queryUserInfo(Long userId);

	/** * Query all user information */
	List<SysUserEntity> queryUserAll(a);

	/** * Update user's email address and mobile phone number according to userId *@return* /
	int updateUserInfo(SysUserEntity user);

}
Copy the code

7. Execute statement under resource

SysCodeMasterDao.xml

<mapper namespace="com.niaobulashi.mapper.master.SysUserMasterDao">

    <! -- Query all user information -->
    <select id="queryUserAll" resultType="com.niaobulashi.entity.SysUserEntity">
        SELECT
            ur.*
        FROM
            sys_user ur
        WHERE
            1 = 1
    </select>

    <! Select * from userId where userId = userId;
    <select id="queryUserInfo" resultType="com.niaobulashi.entity.SysUserEntity">
        SELECT
            ur.*
        FROM
            sys_user ur
        WHERE
            1 = 1
          AND ur.user_id = #{userId}
    </select>

    <! Update email and phone number according to UserId -->
    <update id="updateUserInfo" parameterType="com.niaobulashi.entity.SysUserEntity">
        UPDATE sys_user u
        <set>
            <if test="email ! = null">
                u.email = #{email},
            </if>
            <if test="mobile ! = null">
                u.mobile = #{mobile},
            </if>
        </set>
        WHERE
        u.user_id = #{userId}
    </update>

</mapper>
Copy the code

8. Controller layer test

@RestController
public class SysUserController {

    @Autowired
    private SysUserMasterDao sysUserMasterDao;

    @Autowired
    private SysUserSlaveDao sysUserSlaveDao;

    /** * Query all user information Master *@return* /
    @RequestMapping("/getUserMasterAll")
    private List<SysUserEntity> getUserMaster(a) {
        System.out.println("Query primary library");
        List<SysUserEntity> userList = sysUserMasterDao.queryUserAll();
        return userList;
    }

    /** * Queries information about all users Slave *@return* /
    @RequestMapping("/getUserSlaveAll")
    private List<SysUserEntity> getUserSlave(a) {
        System.out.println("Query slave library");
        List<SysUserEntity> userList = sysUserSlaveDao.queryUserAll();
        return userList;
    }

    /** * Query user information based on userId Master *@return* /
    @RequestMapping("/getUserMasterById")
    private List<SysUserEntity> getUserMasterById(@RequestParam(value = "userId", required = false) Long userId) {
        List<SysUserEntity> userList = sysUserMasterDao.queryUserInfo(userId);
        return userList;
    }

    /** * Queries user information based on the userId Slave *@return* /
    @RequestMapping("/getUserSlaveById")
    private List<SysUserEntity> getUserSlaveById(@RequestParam(value = "userId", required = false) Long userId) {
        List<SysUserEntity> userList = sysUserSlaveDao.queryUserInfo(userId);
        returnuserList; }}Copy the code

Query all user interfaces

The main library: http://localhost:8080/getUserMasterAll

From the library: http://localhost:8080/getUserSlaveAll

conclusion

1. Achieve read and write separation at the database level through multiple data sources

2. Use spring.datasource. Jdbc-url to connect to multiple data sources

3, Multi-source mybatis. Configuration note: Manual injection of SqlSessionFactory is required

Example code – Github

No shit: A future architect working on Coding