springboot+mybatisplus+mysql+druid雙資料源動态切換（附代碼）

1、首先引入各種jar，springboot、mybatisplus等，配置pom檔案如下：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.hualife</groupId>
    <artifactId>springboot</artifactId>
    <version>1.0-SNAPSHOT</version>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.5</version>
    </parent>
    <properties>
        <java.version>1.8</java.version>
        <mybatis.spring.boot.version>2.2.2</mybatis.spring.boot.version>
        <pagehelper.spring.boot.version>1.4.5</pagehelper.spring.boot.version>
        <druid.version>1.2.22</druid.version>
        <commons.io.version>2.11.0</commons.io.version>
        <commons.configuration.version>1.10</commons.configuration.version>
        <mysql.version>8.0.30</mysql.version>
        <druid.version>1.2.13</druid.version>
        <mybatisplus.version>3.5.2</mybatisplus.version>
    </properties>
    <dependencies>
        <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-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>${pagehelper.spring.boot.version}</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>${druid.version}</version>
        </dependency>
        <!-- mysql驅動 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.4</version>
            <exclusions>
                <exclusion>
                    <artifactId>druid</artifactId>
                    <groupId>com.alibaba</groupId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatisplus.version}</version>
            <exclusions>
                <exclusion>
                    <groupId>com.baomidou</groupId>
                    <artifactId>mybatis-plus-generator</artifactId>
                </exclusion>
                <exclusion>
                    <artifactId>jsqlparser</artifactId>
                    <groupId>com.github.jsqlparser</groupId>
                </exclusion>
                <exclusion>
                    <artifactId>mybatis</artifactId>
                    <groupId>org.mybatis</groupId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.22</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>2.7.5</version>
            </plugin>
        </plugins>
    </build>
</project>

2、在yml中配置資料源資訊如下（可多配置一些資料庫連接配接池資訊，自行百度吧有很多可以借鑒）：

server:
  port: 8080
spring:
  datasource:
    dynamic:
      datasource:
        master:
          #MySQL配置
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://localhost:3306/DB1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true
          username: root
          password:
          initial-size: 2
          max-active: 20
          min-idle: 1
        slave:
          #MySQL配置
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://localhost:3306/DB2?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true
          username: root
          password:
          initial-size: 2
          max-active: 20
          min-idle: 1
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  mapper-locations: classpath:mapper/**/*.xml

3、建資料庫，建表，并且生成對應的Java實體及mapper及xml等（可使用開源版人人代碼生成器）。

4、搭建springboot項目，項目結構如下圖（除了config以外，其他的包應該都知道是啥）：

springboot+mybatisplus+mysql+druid雙資料源動态切換（附代碼）

項目結構圖

5、在config目錄下建立Java類，CurrDataSource.java（注解類，可用于項目中區分資料源），DataSourceAspect.java(切面類，根據切面選擇不同的資料源)，DataSourceFactory.java（資料源工廠，包含資料庫連接配接池資訊），DataSourceProperties.java（資料源資訊封裝的實體類），DBTypeName.java（資料源名稱常量），DynamicContextHolder.java（資料源上下文切換用的，實際是隊列），

DynamicDataSourceProperties.java(雙資料源資訊對應的map，具體看下文中的代碼吧)，MyDynamicDataSourceConfig.java（動态資料源配置類），MyRoutingDataSource.java(資料源路由類)

6、資料源切換的注解：

import java.lang.annotation.*;
/**
 * 多資料源注解
 * 自己項目中添加
 */
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Inherited
public @interface CurrDataSource {
    String value() default "master";
}

7、aop切面類：

import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.core.Ordered;
import org.springframework.core.annotation.AnnotationUtils;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
import java.lang.reflect.Method;
/**
 * 多資料源，切面處理類
 */
@Aspect
@Component
@Order(Ordered.HIGHEST_PRECEDENCE)
@Slf4j
public class DataSourceAspect {

    @Pointcut("@annotation(com.hualife.modules.config.CurrDataSource) "  //方法包含注解
            +"|| @within(com.hualife.modules.config.CurrDataSource)"    //類包含注解
            +"|| within(com.baomidou.mybatisplus.core.mapper.BaseMapper+)")
  // 切BaseMapper及其子類，因為在mapper加注解但調用父類的方法切不到，故加了within，這樣就可以切到父類了
  //其實隻要within表達式就行，因為不管是service的impl還是mapper最終調的都是mapper的方法
  public void dSPointCut() {
    }

    @Around("dSPointCut()")
    public Object around(ProceedingJoinPoint point) throws Throwable {
        MethodSignature signature = (MethodSignature) point.getSignature();
        Class targetClass = point.getTarget().getClass();
        Method method = signature.getMethod();
        log.info("執行資料庫操作的類是：{},函數是：{}", targetClass.getName(), method.getName());
//        CurrDataSource targetDataSource = (CurrDataSource) targetClass.getAnnotation(CurrDataSource.class);
        CurrDataSource targetDataSource = AnnotationUtils.findAnnotation(targetClass, CurrDataSource.class);//擷取目标類上注解
        CurrDataSource methodDataSource = method.getAnnotation(CurrDataSource.class);//擷取目标函數上注解
        if (targetDataSource != null || methodDataSource != null) {
            log.info(targetDataSource + "===========" + methodDataSource);
            String value;
            if (methodDataSource != null) {
                value = methodDataSource.value();
            } else {
                value = targetDataSource.value();
            }
            DynamicContextHolder.push(value);
        } else {
            log.info("執行資料庫操作的類及其函數上沒有@CurrDataSource，而這個執行資料庫操作的函數屬于BaseMapper及其子類下的函數，故調用master");
            DynamicContextHolder.push(DBTypeName.MASTER);
        }
        try {
            return point.proceed();
        } finally {
            DynamicContextHolder.poll();
            log.info("clean datasource");
        }
    }
}

8、資料源工廠：

import com.alibaba.druid.pool.DruidDataSource;
import java.sql.SQLException;
/**
 * DruidDataSource
 */
public class DataSourceFactory {
    public static DruidDataSource buildDruidDataSource(DataSourceProperties properties) {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName(properties.getDriverClassName());
        druidDataSource.setUrl(properties.getUrl());
        druidDataSource.setUsername(properties.getUsername());
        druidDataSource.setPassword(properties.getPassword());

        druidDataSource.setInitialSize(properties.getInitialSize());
        druidDataSource.setMaxActive(properties.getMaxActive());
        druidDataSource.setMinIdle(properties.getMinIdle());
        druidDataSource.setMaxWait(properties.getMaxWait());
        druidDataSource.setTimeBetweenEvictionRunsMillis(properties.getTimeBetweenEvictionRunsMillis());
        druidDataSource.setMinEvictableIdleTimeMillis(properties.getMinEvictableIdleTimeMillis());
        druidDataSource.setMaxEvictableIdleTimeMillis(properties.getMaxEvictableIdleTimeMillis());
        druidDataSource.setValidationQuery(properties.getValidationQuery());
        druidDataSource.setValidationQueryTimeout(properties.getValidationQueryTimeout());
        druidDataSource.setTestOnBorrow(properties.isTestOnBorrow());
        druidDataSource.setTestOnReturn(properties.isTestOnReturn());
        druidDataSource.setPoolPreparedStatements(properties.isPoolPreparedStatements());
        druidDataSource.setMaxOpenPreparedStatements(properties.getMaxOpenPreparedStatements());
        druidDataSource.setSharePreparedStatements(properties.isSharePreparedStatements());
        try {
            // druidDataSource.setFilters(properties.getFilters());
            druidDataSource.init();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return druidDataSource;
    }
}

9、多資料源屬性類：

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
 * 多資料源屬性，yml
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class DataSourceProperties {
    private String driverClassName;
    private String url;
    private String username;
    private String password;
    /**
     * Druid預設參數
     */
    private int initialSize = 2;
    private int maxActive = 10;
    private int minIdle = -1;
    private long maxWait = 60 * 1000L;
    private long timeBetweenEvictionRunsMillis = 60 * 1000L;
    private long minEvictableIdleTimeMillis = 1000L * 60L * 30L;
    private long maxEvictableIdleTimeMillis = 1000L * 60L * 60L * 7;
    private String validationQuery = "select 1";
    private int validationQueryTimeout = -1;
    private boolean testOnBorrow = false;
    private boolean testOnReturn = false;
    private boolean testWhileIdle = true;
    private boolean poolPreparedStatements = false;
    private int maxOpenPreparedStatements = -1;
    private boolean sharePreparedStatements = false;
    private String filters = "stat,wall";
}

10、資料庫名字常量：

/**
 * 資料庫名字常量
 */
public class DBTypeName {
    public static final String MASTER = "master";
    public static final String SLAVE = "slave";
}

11、多資料源上下文，用來放置資料源名字：

import java.util.ArrayDeque;
import java.util.Deque;
/**
 * 多資料源上下文
 * 用來放置資料源
 */
public class DynamicContextHolder {
    private static final ThreadLocal<Deque<String>> HOLDER = new ThreadLocal<Deque<String>>() {
        @Override
        protected Deque<String> initialValue() {
            return new ArrayDeque<String>();
        }
    };
    /**
     * 獲得目前線程資料源
     * @return 資料源名稱
     */
    public static String peek() {
        return HOLDER.get().peek();
    }

    /**
     * 設定目前線程資料源
     * @param dataSource 資料源名稱
     */
    public static void push(String dataSource) {
        HOLDER.get().push(dataSource);
    }
    /**
     * 清空目前線程資料源
     */
    public static void poll() {
        Deque<String> deque = HOLDER.get();
        deque.poll();
        if (deque.isEmpty()) {
            HOLDER.remove();
        }
    }
}

12、多資料源屬性類（把yml中多資料源資訊對應成map）：

import org.springframework.boot.context.properties.ConfigurationProperties;
import java.util.LinkedHashMap;
import java.util.Map;
/**
 * 多資料源屬性
 */
@ConfigurationProperties(prefix = "spring.datasource.dynamic")
public class DynamicDataSourceProperties {
    private Map<String, DataSourceProperties> datasource = new LinkedHashMap<>();
    public Map<String, DataSourceProperties> getDatasource() {
        return datasource;
    }
    public void setDatasource(Map<String, DataSourceProperties> datasource) {
        this.datasource = datasource;
    }
}

13、多資料源動态切換配置類（整合了druid的資料監控）：

import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
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.context.properties.EnableConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.HashMap;
import java.util.Map;

/**
 * 配置多資料源
 */
@Slf4j
@Configuration
@EnableConfigurationProperties(DynamicDataSourceProperties.class)
public class MyDynamicDataSourceConfig {
    @Autowired
    private DynamicDataSourceProperties dynamicDataSourceProperties;

    @Bean
    public MyRoutingDataSource dynamicDataSource() {
        MyRoutingDataSource dynamicDataSource = new MyRoutingDataSource();
        Map<String, DataSourceProperties> dataSourcePropertiesMap = myDruidDataSourceProperties.getDatasource();
        Map<Object, Object> targetDataSources = new HashMap<>(dataSourcePropertiesMap.size());
        dataSourcePropertiesMap.forEach((k, v) -> {
            DruidDataSource druidDataSource = DataSourceFactory.createDataSource(v);
            targetDataSources.put(k, druidDataSource);
            if("master".equals(k)){
                dynamicDataSource.setDefaultTargetDataSource(druidDataSource);
            }
        });
        dynamicDataSource.setTargetDataSources(targetDataSources);
        return dynamicDataSource;
    }
    
   @Bean
    public GlobalConfig globalConfig(@Qualifier("sqlInjector") ISqlInjector sqlInjector) {
        GlobalConfig globalConfig = new GlobalConfig();
        globalConfig.setSqlInjector(sqlInjector);
        globalConfig.setBanner(false);
        globalConfig.setMetaObjectHandler(new MyMetaObjectHandler());
        return globalConfig;
    }

    @Bean("sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("globalConfig") GlobalConfig globalConfig) throws Exception {
        MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(multipleDataSource());
        sqlSessionFactory.setMapperLocations(resolveMapperLocations());
//        sqlSessionFactory.setTransactionFactory();
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setDefaultScriptingLanguage(MybatisXMLLanguageDriver.class);
        configuration.setJdbcTypeForNull(JdbcType.NULL);
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setCacheEnabled(false);
        //configuration.setLogImpl(StdOutImpl.class);
        sqlSessionFactory.setConfiguration(configuration);
        //添加自定義sql注入接口
        sqlSessionFactory.setGlobalConfig(globalConfig);//添加自定義sql注入接口
        return sqlSessionFactory.getObject();
    }

    /**
     * druid通路配置 資料監控
     */
    @Bean
    public ServletRegistrationBean<StatViewServlet> druidServlet() {
        ServletRegistrationBean<StatViewServlet> servletRegistrationBean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
        //控制台管理使用者，加入下面2行 進入druid背景就需要登入
        servletRegistrationBean.addInitParameter("loginUsername", "root");
        servletRegistrationBean.addInitParameter("loginPassword", "123456");
        return servletRegistrationBean;
    }

    @Bean
    public FilterRegistrationBean<WebStatFilter> filterRegistrationBean() {
        FilterRegistrationBean<WebStatFilter> filterRegistrationBean = new FilterRegistrationBean<>();
        filterRegistrationBean.setFilter(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        filterRegistrationBean.addInitParameter("profileEnable", "true");
        return filterRegistrationBean;
    }

    @Bean
    public StatFilter statFilter() {
        StatFilter statFilter = new StatFilter();
        //slowSqlMillis用來配置SQL慢的标準，執行時間超過slowSqlMillis的就是慢。
        statFilter.setLogSlowSql(true);
        //SQL合并配置
        statFilter.setMergeSql(true);
        //slowSqlMillis的預設值為3000，也就是3秒。
        statFilter.setSlowSqlMillis(1000);
        return statFilter;
    }

    @Bean
    public WallFilter wallFilter() {
        WallFilter wallFilter = new WallFilter();
        //允許執行多條SQL
        WallConfig config = new WallConfig();
        config.setMultiStatementAllow(true);
        wallFilter.setConfig(config);
        return wallFilter;
    }
}

14、多資料源路由：

import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
 * 多資料源
 */
@Slf4j
public class MyRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        String dataSource = DynamicContextHolder.peek();
        log.info("使用資料源：{}", dataSource);
        return dataSource;
    }
}

15、多資料源路由了解：參考下圖（借用别人的圖）

https://www.cnblogs.com/zzsuje/p/15465431.html

16、備注

項目搭建及部分代碼參考人人代碼生成器

springboot+mybatisplus+mysql+druid雙資料源動态切換（附代碼）

繼續閱讀

SpringMVC的啟動過程源碼分析1 SpringMVC 配置XML的方式啟動2 SpringMVC純注解模式啟動

Java Spring 架構

java 8 Lamda 求 List 中 Bigdecimal類型的各種值求最大值求最小值求和求平均值

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

(SpringBoot)日志種類：log、monitor、access、out、gc、backup

SpringCloud微服務（四）之ribbon筆記ribbon是什麼？入門案例自定義均衡負載算法

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

用mybatis的generator插件在項目中自動生成dao及entity

mysql使用source指令導入.sql檔案

從mybatis到mybatis plus(mp)