作 者：嗚嗚嗚啦啦啦
來 源：blog.csdn.net/u013360850
廣而告之：由于此訂閱号換了個皮膚，系統自動取消了讀者的公衆号置頂。導緻使用者接受文章不及時。可以打開訂閱号，選擇置頂(标星)公衆号，重磅幹貨，第一時間送達！
本項目使用 Spring Boot 和 MyBatis 實作多資料源，動态資料源的切換；有多種不同的實作方式，在學習的過程中發現沒有文章将這些方式和常見的問題集中處理，是以将常用的方式和常見的問題都寫在了在本項目的不同分支上：
項目位址:
https://github.com/helloworlde/SpringBoot-DynamicDataSource
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
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 {
@Bean("master")
@Primary
@ConfigurationProperties(prefix = "spring.datasource.druid.master")
public DataSource master() {
return DruidDataSourceBuilder.create().build();
}
@Bean("slaveAlpha")
@ConfigurationProperties(prefix = "spring.datasource.druid.slave-alpha")
public DataSource slaveAlpha() {
return DruidDataSourceBuilder.create().build();
}
@Bean("slaveBeta")
@ConfigurationProperties(prefix = "spring.datasource.druid.slave-beta")
public DataSource slaveBeta() {
return DruidDataSourceBuilder.create().build();
}
@Bean("slaveGamma")
@ConfigurationProperties(prefix = "spring.datasource.druid.slave-gamma")
public DataSource slaveGamma() {
return DruidDataSourceBuilder.create().build();
}
@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());
dynamicRoutingDataSource.setDefaultTargetDataSource(master());
dynamicRoutingDataSource.setTargetDataSources(dataSourceMap);
DynamicDataSourceContextHolder.dataSourceKeys.addAll(dataSourceMap.keySet());
DynamicDataSourceContextHolder.slaveDataSourceKeys.addAll(dataSourceMap.keySet());
DynamicDataSourceContextHolder.slaveDataSourceKeys.remove(DataSourceKey.master.name());
return dynamicRoutingDataSource;
}
@Bean
@ConfigurationProperties(prefix = "mybatis")
public SqlSessionFactoryBean sqlSessionFactoryBean() {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dynamicDataSource());
return sqlSessionFactoryBean;
}
@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;
private static final ThreadLocal<Object> CONTEXT_HOLDER = ThreadLocal.withInitial(DataSourceKey.master);
public static List<Object> dataSourceKeys = new ArrayList<>();
public static List<Object> slaveDataSourceKeys = new ArrayList<>();
public static void setDataSourceKey(String key) {
CONTEXT_HOLDER.set(key);
}
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();
}
}
public static String getDataSourceKey() {
return CONTEXT_HOLDER.get();
}
public static void clearDataSourceKey() {
CONTEXT_HOLDER.remove();
}
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 會傳回 productmaster 資料庫中 product 表中的所有資料，多次通路 /product 會分别傳回 productslavealpha、productslavebeta、productslave_gamma 資料庫中 product 表中的資料，同時也可以在看到切換資料源的 log，說明動态切換資料源是有效的
注意
在該應用中因為使用了 DAO 層的切面切換資料源，是以 @Transactional 注解不能加在類上，隻能用于方法；有 @Trasactional注解的方法無法切換資料源
推薦閱讀
1. 29 個問答帶你梳理 Spring ！
2. Spring 中如何運用設計模式？
3. 我從 500 個技術号，選出這 10 個
4. 為什麼要前後端分離？有什麼優缺點
