Spring Boot MyBatis 動態資料來源切換、多資料來源，讀寫分離

專案地址 https://github.com/helloworlde/SpringBoot-DynamicDataSource

本專案使用 Spring Boot 和 MyBatis 實現多資料來源，動態資料來源的切換；有多種不同的實現方式，在學習的過程中發現沒有文章將這些方式和常見的問題集中處理，所以將常用的方式和常見的問題都寫在了在本專案的不同分支上：

• master: 使用了多資料來源的 RESTful API 介面，使用 Druid 實現了 DAO 層資料來源動態切換和只讀資料來源負載均衡
• dev: 最簡單的切面和註解方式實現的動態資料來源切換
• druid: 通過切面和註解方式實現的使用 Druid 連線池的動態資料來源切換
• aspect_dao: 通過切面實現的 DAO 層的動態資料來源切換
• roundrobin: 通過切面使用輪詢方式實現的只讀資料來源負載均衡
• hikari: 升級到SpringBoot 2.0, 資料來源使用 Hikari

以上分支都是基於 dev 分支修改或擴充而來，基本涵蓋了常用的多資料來源動態切換的方式，基本的原理都一樣，都是通過切面根據不同的條件在執行資料庫操作前切換資料來源

在使用的過程中基本踩遍了所有動態資料來源切換的坑，將常見的一些坑和解決方法寫在了 Issues 裡面

該專案使用了一個可寫資料來源和多個只讀資料來源，為了減少資料庫壓力，使用輪循的方式選擇只讀資料來源；考慮到在一個 Service 中同時會有讀和寫的操作，所以本應用使用 AOP 切面通過 DAO 層的方法名切換隻讀資料來源；但這種方式要求資料來源主從一致，並且應當避免在同一個 Service 方法中寫入後立即查詢，如果必須在執行寫入操作後立即讀取，應當在 Service 方法上新增 @Transactional 註解以保證使用主資料來源

需要注意的是，使用 DAO 層切面後不應該在 Service 類層面上加 @Transactional 註解，而應該新增在方法上，這也是 Spring 推薦的做法

動態切換資料來源依賴 configuration 包下的4個類來實現，分別是：

• DataSourceRoutingDataSource.java
• DataSourceConfigurer.java
• DynamicDataSourceContextHolder.java
• DynamicDataSourceAspect.java

---

新增依賴

dependencies {
    compile('org.mybatis.spring.boot:mybatis-spring-boot-starter:1.3.1')
    compile('org.springframework.boot:spring-boot-starter-web')
    compile('org.springframework.boot:spring-boot-starter-aop')
    compile('com.alibaba:druid-spring-boot-starter:1.1.6')
    runtime('mysql:mysql-connector-java')
    testCompile('org.springframework.boot:spring-boot-starter-test')
}

建立資料庫及表

• 分別建立資料庫product_master, product_slave_alpha, product_slave_beta, product_slave_gamma
• 在以上資料庫中分別建立表 product，並插入不同資料

DROP DATABASE IF EXISTS product_master;
CREATE DATABASE product_master;
CREATE TABLE product_master.product(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  price DOUBLE(10,2) NOT NULL DEFAULT 0);
INSERT INTO product_master.product (name, price) VALUES('master', '1');


DROP DATABASE IF EXISTS product_slave_alpha;
CREATE DATABASE product_slave_alpha;
CREATE TABLE product_slave_alpha.product(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  price DOUBLE(10,2) NOT NULL DEFAULT 0);
INSERT INTO product_slave_alpha.product (name, price) VALUES('slaveAlpha', '1');

DROP DATABASE IF EXISTS product_slave_beta;
CREATE DATABASE product_slave_beta;
CREATE TABLE product_slave_beta.product(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  price DOUBLE(10,2) NOT NULL DEFAULT 0);
INSERT INTO product_slave_beta.product (name, price) VALUES('slaveBeta', '1');

DROP DATABASE IF EXISTS product_slave_gamma;
CREATE DATABASE product_slave_gamma;
CREATE TABLE product_slave_gamma.product(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  price DOUBLE(10,2) NOT NULL DEFAULT 0);
INSERT INTO product_slave_gamma.product (name, price) VALUES('slaveGamma', '1');

配置資料來源

• application.properties

# Master datasource config
spring.datasource.druid.master.name=master
spring.datasource.druid.master.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.druid.master.url=jdbc:mysql://localhost/product_master?useSSL=false
spring.datasource.druid.master.port=3306
spring.datasource.druid.master.username=root
spring.datasource.druid.master.password=123456

# SlaveAlpha datasource config
spring.datasource.druid.slave-alpha.name=SlaveAlpha
spring.datasource.druid.slave-alpha.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.druid.slave-alpha.url=jdbc:mysql://localhost/product_slave_alpha?useSSL=false
spring.datasource.druid.slave-alpha.port=3306
spring.datasource.druid.slave-alpha.username=root
spring.datasource.druid.slave-alpha.password=123456

# SlaveBeta datasource config
spring.datasource.druid.slave-beta.name=SlaveBeta
spring.datasource.druid.slave-beta.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.druid.slave-beta.url=jdbc:mysql://localhost/product_slave_beta?useSSL=false
spring.datasource.druid.slave-beta.port=3306
spring.datasource.druid.slave-beta.username=root
spring.datasource.druid.slave-beta.password=123456

# SlaveGamma datasource config
spring.datasource.druid.slave-gamma.name=SlaveGamma
spring.datasource.druid.slave-gamma.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.druid.slave-gamma.url=jdbc:mysql://localhost/product_slave_gamma?useSSL=false
spring.datasource.druid.slave-gamma.port=3306
spring.datasource.druid.slave-gamma.username=root
spring.datasource.druid.slave-gamma.password=123456

# Druid dataSource config
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.initial-size=5
spring.datasource.druid.max-active=20
spring.datasource.druid.min-idle=5
spring.datasource.druid.max-wait=60000
spring.datasource.druid.pool-prepared-statements=false
spring.datasource.druid.validation-query=SELECT 1
spring.datasource.druid.validation-query-timeout=30000
spring.datasource.druid.test-on-borrow=false
spring.datasource.druid.test-on-return=false
spring.datasource.druid.test-while-idle=true
#spring.datasource.druid.time-between-eviction-runs-millis=
#spring.datasource.druid.min-evictable-idle-time-millis=
#spring.datasource.druid.max-evictable-idle-time-millis=10000

# Druid stat filter config
spring.datasource.druid.filters=stat,wall,log4j
spring.datasource.druid.web-stat-filter.enabled=true
spring.datasource.druid.web-stat-filter.url-pattern=/*
spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*
spring.datasource.druid.web-stat-filter.session-stat-enable=true
spring.datasource.druid.web-stat-filter.session-stat-max-count=10
spring.datasource.druid.web-stat-filter.principal-session-name=user
#spring.datasource.druid.web-stat-filter.principal-cookie-name=
spring.datasource.druid.web-stat-filter.profile-enable=true
spring.datasource.druid.filter.stat.db-type=mysql
spring.datasource.druid.filter.stat.log-slow-sql=true
spring.datasource.druid.filter.stat.slow-sql-millis=1000
spring.datasource.druid.filter.stat.merge-sql=true
spring.datasource.druid.filter.wall.enabled=true
spring.datasource.druid.filter.wall.config.delete-allow=true
spring.datasource.druid.filter.wall.config.drop-table-allow=false
spring.datasource.druid.filter.slf4j.enabled=true
# Druid manage page config
spring.datasource.druid.stat-view-servlet.enabled=true
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.stat-view-servlet.reset-enable=true
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=admin
#spring.datasource.druid.stat-view-servlet.allow=
#spring.datasource.druid.stat-view-servlet.deny=
spring.datasource.druid.use-global-data-source-stat=true
# Druid AOP config
spring.datasource.druid.aop-patterns=cn.com.hellowood.dynamicdatasource.service.*
spring.aop.proxy-target-class=true

# MyBatis config
mybatis.type-aliases-package=cn.com.hellowood.dynamicdatasource.mapper
mybatis.mapper-locations=mappers/**Mapper.xml
server.port=9999

配置資料來源

• DataSourceKey.java

package cn.com.hellowood.dynamicdatasource.common;

public enum DataSourceKey {
    master,
    slaveAlpha,
    slaveBeta,
    slaveGamma
}

• DataSourceRoutingDataSource.java

該類繼承自 AbstractRoutingDataSource 類，在訪問資料庫時會呼叫該類的 determineCurrentLookupKey() 方法獲取資料庫例項的 key

package cn.com.hellowood.dynamicdatasource.configuration;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicRoutingDataSource extends AbstractRoutingDataSource {

    private final Logger logger = LoggerFactory.getLogger(getClass());

    @Override
    protected Object determineCurrentLookupKey() {
        logger.info("Current DataSource is [{}]", DynamicDataSourceContextHolder.getDataSourceKey());
        return DynamicDataSourceContextHolder.getDataSourceKey();
    }
}

• DataSourceConfigurer.java

資料來源配置類，在該類中生成多個資料來源例項並將其注入到 ApplicationContext 中

package cn.com.hellowood.dynamicdatasource.configuration;

import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
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 javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Configuration
public class DataSourceConfigurer {

    /**
     * master DataSource
     * @Primary 註解用於標識預設使用的 DataSource Bean，因為有5個 DataSource Bean，該註解可用於 master
     * 或 slave DataSource Bean, 但不能用於 dynamicDataSource Bean, 否則會產生迴圈呼叫 
     * 
     * @ConfigurationProperties 註解用於從 application.properties 檔案中讀取配置，為 Bean 設定屬性 
     * @return data source
     */
    @Bean("master")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.druid.master")
    public DataSource master() {
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * Slave alpha data source.
     *
     * @return the data source
     */
    @Bean("slaveAlpha")
    @ConfigurationProperties(prefix = "spring.datasource.druid.slave-alpha")
    public DataSource slaveAlpha() {
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * Slave beta data source.
     *
     * @return the data source
     */
    @Bean("slaveBeta")
    @ConfigurationProperties(prefix = "spring.datasource.druid.slave-beta")
    public DataSource slaveBeta() {
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * Slave gamma data source.
     *
     * @return the data source
     */
    @Bean("slaveGamma")
    @ConfigurationProperties(prefix = "spring.datasource.druid.slave-gamma")
    public DataSource slaveGamma() {
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * Dynamic data source.
     *
     * @return the data source
     */
    @Bean("dynamicDataSource")
    public DataSource dynamicDataSource() {
        DynamicRoutingDataSource dynamicRoutingDataSource = new DynamicRoutingDataSource();
        Map<Object, Object> dataSourceMap = new HashMap<>(4);
        dataSourceMap.put(DataSourceKey.master.name(), master());
        dataSourceMap.put(DataSourceKey.slaveAlpha.name(), slaveAlpha());
        dataSourceMap.put(DataSourceKey.slaveBeta.name(), slaveBeta());
        dataSourceMap.put(DataSourceKey.slaveGamma.name(), slaveGamma());

        // 將 master 資料來源作為預設指定的資料來源
        dynamicRoutingDataSource.setDefaultTargetDataSource(master());
        // 將 master 和 slave 資料來源作為指定的資料來源
        dynamicRoutingDataSource.setTargetDataSources(dataSourceMap);

        // 將資料來源的 key 放到資料來源上下文的 key 集合中，用於切換時判斷資料來源是否有效
        DynamicDataSourceContextHolder.dataSourceKeys.addAll(dataSourceMap.keySet());

        // 將 Slave 資料來源的 key 放在集合中，用於輪循
        DynamicDataSourceContextHolder.slaveDataSourceKeys.addAll(dataSourceMap.keySet());
        DynamicDataSourceContextHolder.slaveDataSourceKeys.remove(DataSourceKey.master.name());
        return dynamicRoutingDataSource;
    }   
    
    /**
     * 配置 SqlSessionFactoryBean
     * @ConfigurationProperties 在這裡是為了將 MyBatis 的 mapper 位置和持久層介面的別名設定到 
     * Bean 的屬性中，如果沒有使用 *.xml 則可以不用該配置，否則將會產生 invalid bond statement 異常
     * 
     * @return the sql session factory bean
     */
    @Bean
    @ConfigurationProperties(prefix = "mybatis")
    public SqlSessionFactoryBean sqlSessionFactoryBean() {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        // 配置資料來源，此處配置為關鍵配置，如果沒有將 dynamicDataSource 作為資料來源則不能實現切換
        sqlSessionFactoryBean.setDataSource(dynamicDataSource());
        return sqlSessionFactoryBean;
    }
    
    /**
     * 注入 DataSourceTransactionManager 用於事務管理
     */
    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dynamicDataSource());
    }
    
}

• DynamicDataSourceContextHolder.java

該類為資料來源上下文配置，用於切換資料來源

package cn.com.hellowood.dynamicdatasource.configuration;


import cn.com.hellowood.dynamicdatasource.common.DataSourceKey;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;

public class DynamicDataSourceContextHolder {

    private static final Logger logger = LoggerFactory.getLogger(DynamicDataSourceContextHolder.class);

    /**
     * 用於在切換資料來源時保證不會被其他執行緒修改
     */
    private static Lock lock = new ReentrantLock();

    /**
     * 用於輪循的計數器
     */
    private static int counter = 0;

    /**
     * Maintain variable for every thread, to avoid effect other thread
     */
    private static final ThreadLocal<Object> CONTEXT_HOLDER = ThreadLocal.withInitial(DataSourceKey.master);


    /**
     * All DataSource List
     */
    public static List<Object> dataSourceKeys = new ArrayList<>();

    /**
     * The constant slaveDataSourceKeys.
     */
    public static List<Object> slaveDataSourceKeys = new ArrayList<>();

    /**
     * To switch DataSource
     *
     * @param key the key
     */
    public static void setDataSourceKey(String key) {
        CONTEXT_HOLDER.set(key);
    }

    /**
     * Use master data source.
     */
    public static void useMasterDataSource() {
        CONTEXT_HOLDER.set(DataSourceKey.master);
    }

    /**
     * 當使用只讀資料來源時通過輪循方式選擇要使用的資料來源
     */
    public static void useSlaveDataSource() {
        lock.lock();

        try {
            int datasourceKeyIndex = counter % slaveDataSourceKeys.size();
            CONTEXT_HOLDER.set(String.valueOf(slaveDataSourceKeys.get(datasourceKeyIndex)));
            counter++;
        } catch (Exception e) {
            logger.error("Switch slave datasource failed, error message is {}", e.getMessage());
            useMasterDataSource();
            e.printStackTrace();
        } finally {
            lock.unlock();
        }
    }

    /**
     * Get current DataSource
     *
     * @return data source key
     */
    public static String getDataSourceKey() {
        return CONTEXT_HOLDER.get();
    }

    /**
     * To set DataSource as default
     */
    public static void clearDataSourceKey() {
        CONTEXT_HOLDER.remove();
    }

    /**
     * Check if give DataSource is in current DataSource list
     *
     * @param key the key
     * @return boolean boolean
     */
    public static boolean containDataSourceKey(String key) {
        return dataSourceKeys.contains(key);
    }
}

• DynamicDataSourceAspect.java

動態資料來源切換的切面，切 DAO 層，通過 DAO 層方法名判斷使用哪個資料來源，實現資料來源切換

package cn.com.hellowood.dynamicdatasource.configuration;

import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

@Aspect
@Component
public class DynamicDataSourceAspect {
    private static final Logger logger = LoggerFactory.getLogger(DynamicDataSourceAspect.class);

    private final String[] QUERY_PREFIX = {"select"};

    @Pointcut("execution( * cn.com.hellowood.dynamicdatasource.mapper.*.*(..))")
    public void daoAspect() {
    }

    @Before("daoAspect()")
    public void switchDataSource(JoinPoint point) {
        Boolean isQueryMethod = isQueryMethod(point.getSignature().getName());
        if (isQueryMethod) {
            DynamicDataSourceContextHolder.useSlaveDataSource();
            logger.info("Switch DataSource to [{}] in Method [{}]",
                    DynamicDataSourceContextHolder.getDataSourceKey(), point.getSignature());
        }
    }

    @After("daoAspect()")
    public void restoreDataSource(JoinPoint point) {
        DynamicDataSourceContextHolder.clearDataSourceKey();
        logger.info("Restore DataSource to [{}] in Method [{}]",
                DynamicDataSourceContextHolder.getDataSourceKey(), point.getSignature());
    }

    private Boolean isQueryMethod(String methodName) {
        for (String prefix : QUERY_PREFIX) {
            if (methodName.startsWith(prefix)) {
                return true;
            }
        }
        return false;
    }

}

配置 Product REST API 介面

• ProductController.java

package cn.com.hellowood.dynamicdatasource.controller;

import cn.com.hellowood.dynamicdatasource.common.CommonResponse;
import cn.com.hellowood.dynamicdatasource.common.ResponseUtil;
import cn.com.hellowood.dynamicdatasource.modal.Product;
import cn.com.hellowood.dynamicdatasource.service.ProductService;
import cn.com.hellowood.dynamicdatasource.utils.ServiceException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

@RestController
@RequestMapping("/product")
public class ProductController {

    @Autowired
    private ProductService productService;

    @GetMapping("/{id}")
    public CommonResponse getProduct(@PathVariable("id") Long productId) throws ServiceException {
        return ResponseUtil.generateResponse(productService.select(productId));
    }

    @GetMapping
    public CommonResponse getAllProduct() {
        return ResponseUtil.generateResponse(productService.getAllProduct());
    }

    @PutMapping("/{id}")
    public CommonResponse updateProduct(@PathVariable("id") Long productId, @RequestBody Product newProduct) throws ServiceException {
        return ResponseUtil.generateResponse(productService.update(productId, newProduct));
    }

    @DeleteMapping("/{id}")
    public CommonResponse deleteProduct(@PathVariable("id") long productId) throws ServiceException {
        return ResponseUtil.generateResponse(productService.delete(productId));
    }

    @PostMapping
    public CommonResponse addProduct(@RequestBody Product newProduct) throws ServiceException {
        return ResponseUtil.generateResponse(productService.add(newProduct));
    }
}

• ProductService.java

package cn.com.hellowood.dynamicdatasource.service;

import cn.com.hellowood.dynamicdatasource.mapper.ProductDao;
import cn.com.hellowood.dynamicdatasource.modal.Product;
import cn.com.hellowood.dynamicdatasource.utils.ServiceException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Service
public class ProductService {

    @Autowired
    private ProductDao productDao;

    public Product select(long productId) throws ServiceException {
        Product product = productDao.select(productId);
        if (product == null) {
            throw new ServiceException("Product:" + productId + " not found");
        }
        return product;
    }

    @Transactional(rollbackFor = DataAccessException.class)
    public Product update(long productId, Product newProduct) throws ServiceException {

        if (productDao.update(newProduct) <= 0) {
            throw new ServiceException("Update product:" + productId + "failed");
        }
        return newProduct;
    }

    @Transactional(rollbackFor = DataAccessException.class)
    public boolean add(Product newProduct) throws ServiceException {
        Integer num = productDao.insert(newProduct);
        if (num <= 0) {
            throw new ServiceException("Add product failed");
        }
        return true;
    }

    @Transactional(rollbackFor = DataAccessException.class)
    public boolean delete(long productId) throws ServiceException {
        Integer num = productDao.delete(productId);
        if (num <= 0) {
            throw new ServiceException("Delete product:" + productId + "failed");
        }
        return true;
    }

    public List<Product> getAllProduct() {
        return productDao.getAllProduct();
    }
}

• ProductDao.java

package cn.com.hellowood.dynamicdatasource.mapper;

import cn.com.hellowood.dynamicdatasource.modal.Product;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface ProductDao {
    Product select(@Param("id") long id);

    Integer update(Product product);

    Integer insert(Product product);

    Integer delete(long productId);

    List<Product> getAllProduct();
}

• ProductMapper.xml

啟動專案，此時訪問 /product/1 會返回 product_master 資料庫中 product 表中的所有資料，多次訪問 /product 會分別返回 product_slave_alpha、product_slave_beta、product_slave_gamma 資料庫中 product 表中的資料，同時也可以在看到切換資料來源的 log，說明動態切換資料來源是有效的

---

注意

在該應用中因為使用了 DAO 層的切面切換資料來源，所以 @Transactional 註解不能加在類上，只能用於方法；有 @Trasactional註解的方法無法切換資料來源