Build your own SpringBoot background framework from scratch (11)

Hello, in this chapter we configure the multi-data source function. If you have any questions, please contact me at [email protected]. Ask for directions of various gods, thank you

One: Configure the data source

Modify the application. The properties

spring.datasource.db1.driver-class-name=com.mysql.jdbc.Driver spring.datasource.db1.url=jdbc:mysql://localhost:3333/demo? useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&autoReconnect=true&failOverReadOnly=false
spring.datasource.db1.username=root
spring.datasource.db1.password=123456
spring.datasource.db1.initialSize=5
spring.datasource.db1.minIdle=5
spring.datasource.db1.maxActive=20
Connection wait timeout
spring.datasource.db1.maxWait=60000
Configure how often to check (detect idle connections that can be closed)
spring.datasource.db1.timeBetweenEvictionRunsMillis=60000
Set the minimum lifetime of the connection in the pool
spring.datasource.db1.minEvictableIdleTimeMillis=300000
spring.datasource.db1.validationQuery=SELECT 1 FROM DUAL
spring.datasource.db1.testWhileIdle=true
spring.datasource.db1.testOnBorrow=false
spring.datasource.db1.testOnReturn=false
Turn on PSCache and specify the size of PSCache on each connection
spring.datasource.db1.poolPreparedStatements=true
spring.datasource.db1.maxPoolPreparedStatementPerConnectionSize=20
# Configure the filters for monitoring statistics interception. After removing the filters, the MONITORING interface SQL cannot be counted. 'wall' is used for the firewall
spring.datasource.db1.filters=stat,wall,slf4j
Enable mergeSql via connectProperties; Slow SQL record
spring.datasource.db1.connectionProperties=druid.stat.mergeSql=true; druid.stat.slowSqlMillis=5000 spring.datasource.db2.driver-class-name=com.mysql.jdbc.Driver spring.datasource.db2.url=jdbc:mysql://localhost:3306/chuchen? useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&autoReconnect=true&failOverReadOnly=false
spring.datasource.db2.username=root
spring.datasource.db2.password=123456
spring.datasource.db2.initialSize=5
spring.datasource.db2.minIdle=5
spring.datasource.db2.maxActive=20
Connection wait timeout
spring.datasource.db2.maxWait=60000
Configure how often to check (detect idle connections that can be closed)
spring.datasource.db2.timeBetweenEvictionRunsMillis=60000
Set the minimum lifetime of the connection in the pool
spring.datasource.db2.minEvictableIdleTimeMillis=300000
spring.datasource.db2.validationQuery=SELECT 1 FROM DUAL
spring.datasource.db2.testWhileIdle=true
spring.datasource.db2.testOnBorrow=false
spring.datasource.db2.testOnReturn=false
Turn on PSCache and specify the size of PSCache on each connection
spring.datasource.db2.poolPreparedStatements=true
spring.datasource.db2.maxPoolPreparedStatementPerConnectionSize=20
# Configure the filters for monitoring statistics interception. After removing the filters, the MONITORING interface SQL cannot be counted. 'wall' is used for the firewall
spring.datasource.db2.filters=stat,wall,slf4j
Enable mergeSql via connectProperties; Slow SQL record
spring.datasource.db2.connectionProperties=druid.stat.mergeSql=true; druid.stat.slowSqlMillis=5000Copy the code

Change it to your own database connection

Two: Create data source configuration

MysqlDataSource1Config

package com.example.demo.core.configurer.dataSource;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Qualifier;
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.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import tk.mybatis.spring.annotation.MapperScan;

import javax.sql.DataSource;

@Configuration
@EnableTransactionManagement
@MapperScan(basePackages = {"com.example.demo.dao.db1"})
public class MysqlDataSource1Config {

    @Bean(name = "primaryDataSource"// Set the Primary data source @primary @configurationProperties (prefix=)"spring.datasource.db1")
    public DataSource dataSource(){// Not the same as beforereturn new DruidDataSource();
    }

    @Bean(name = "primaryTransactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager() {
        return new DataSourceTransactionManager(dataSource());

    }

    @Bean(name="primarySqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("primaryDataSource")DataSource primaryDataSource) throws Exception {
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(primaryDataSource);
        Resource[] mapperLocations = new PathMatchingResourcePatternResolver().getResources("classpath:mapper/db1/*.xml");
        sessionFactory.setMapperLocations(mapperLocations);
        returnsessionFactory.getObject(); }}Copy the code

MysqlDataSource2Config

package com.example.demo.core.configurer.dataSource;


import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import tk.mybatis.spring.annotation.MapperScan;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = {"com.example.demo.dao.db2"}, sqlSessionFactoryRef = "secondSqlSessionFactory")
public class MysqlDataSource2Config {

    @Bean(name = "secondDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db2")
    public DataSource dataSource() {
        return new DruidDataSource();
    }

    @Bean(name = "secondSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource());
        Resource[] mapperLocations = new PathMatchingResourcePatternResolver().getResources("classpath:mapper/db2/*.xml");
        sessionFactory.setMapperLocations(mapperLocations);
        returnsessionFactory.getObject(); }}Copy the code

3. Modify the directory structure

The directory structure is shown in the following figure, with DB1 as the primary data source and DB2 as the secondary data source, which will automatically switch data sources depending on the dao layer

Four: function test

Enter http://localhost:8080/userInfo/selectAll

Results:

{
    "code": 200,
    "data": {... omit"list": [{"id": "1"."userName": "Mr_ early morning"
            },
            {
                "id": "2"."userName": "Mr_ early morning"}],... Omit},"msg": "success"
}Copy the code

Enter http://localhost:8080/userLabel/list

Results:

{
    "code": 200,
    "data": {... omit"list": [{"createTime": 1523283547000,
                "id": "24084baed3c6409a8619a635bf4428a1"."labelId": "4"."userId": "1"}],... Omit},"msg": "success"
}Copy the code

Enter http://localhost:8080/druid/datasource.html

You can also see that there are two data sources