Spring Boot 和 MyBatis 实现多数据源、动态数据源切换

本项目使用 Spring Boot 和 MyBatis 实现多数据源，动态数据源的切换；有多种不同的实现方式，在学习的过程中发现没有文章将这些方式和常见的问题集中处理，所以将常用的方式和常见的问题都写在了在本项目的不同分支上：

master: 使用了多数据源的 RESTful API 接口，使用 Druid 实现了 DAO 层数据源动态切换和只读数据源负载均衡
dev: 最简单的切面和注解方式实现的动态数据源切换
druid: 通过切面和注解方式实现的使用 Druid 连接池的动态数据源切换
aspect_dao: 通过切面实现的 DAO 层的动态数据源切换
roundrobin: 通过切面使用轮询方式实现的只读数据源负载均衡

以上分支都是基于 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，并插入不同数据

DROPDATABASE IF EXISTS product_master;
CREATEDATABASEproduct_master;
CREATETABLEproduct_master.product(
id INTPRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
price DOUBLE(10,2) NOT NULL DEFAULT 0);
INSERT INTOproduct_master.product (name, price) VALUES('master', '1');
DROPDATABASE IF EXISTS product_slave_alpha;
CREATEDATABASEproduct_slave_alpha;
CREATETABLEproduct_slave_alpha.product(
id INTPRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
price DOUBLE(10,2) NOT NULL DEFAULT 0);
INSERT INTOproduct_slave_alpha.product (name, price) VALUES('slaveAlpha', '1');
DROPDATABASE IF EXISTS product_slave_beta;
CREATEDATABASEproduct_slave_beta;
CREATETABLEproduct_slave_beta.product(
id INTPRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
price DOUBLE(10,2) NOT NULL DEFAULT 0);
INSERT INTOproduct_slave_beta.product (name, price) VALUES('slaveBeta', '1');
DROPDATABASE IF EXISTS product_slave_gamma;
CREATEDATABASEproduct_slave_gamma;
CREATETABLEproduct_slave_gamma.product(
id INTPRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
price DOUBLE(10,2) NOT NULL DEFAULT 0);
INSERT INTOproduct_slave_gamma.product (name, price) VALUES('slaveGamma', '1');

配置数据源

application.properties

# Master datasource configspring.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 configspring.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 configspring.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 configspring.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 configspring.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 configspring.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 configspring.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 configspring.datasource.druid.aop-patterns=cn.com.hellowood.dynamicdatasource.service.*
spring.aop.proxy-target-class=true
# MyBatis configmybatis.type-aliases-package=cn.com.hellowood.dynamicdatasource.mapper
mybatis.mapper-locations=mappers/**Mapper.xml
server.port=9999

配置数据源

DataSourceKey.java

packagecn.com.hellowood.dynamicdatasource.common;
publicenumDataSourceKey {
master,
slaveAlpha,
slaveBeta,
slaveGamma
}

DataSourceRoutingDataSource.java

该类继承自 AbstractRoutingDataSource 类，在访问数据库时会调用该类的 determineCurrentLookupKey() 方法获取数据库实例的 key

packagecn.com.hellowood.dynamicdatasource.configuration;
importorg.slf4j.Logger;
importorg.slf4j.LoggerFactory;
importorg.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
publicclassDynamicRoutingDataSourceextendsAbstractRoutingDataSource {
privatefinalLoggerlogger = LoggerFactory.getLogger(getClass());
@OverrideprotectedObjectdetermineCurrentLookupKey() {
logger.info("Current DataSource is [{}]", DynamicDataSourceContextHolder.getDataSourceKey());
returnDynamicDataSourceContextHolder.getDataSourceKey();
}
}

DataSourceConfigurer.java

数据源配置类，在该类中生成多个数据源实例并将其注入到 ApplicationContext 中

packagecn.com.hellowood.dynamicdatasource.configuration;
importorg.mybatis.spring.SqlSessionFactoryBean;
importorg.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
importorg.springframework.boot.context.properties.ConfigurationProperties;
importorg.springframework.context.annotation.Bean;
importorg.springframework.context.annotation.Configuration;
importorg.springframework.context.annotation.Primary;
importjavax.sql.DataSource;
importjava.util.HashMap;
importjava.util.Map;
@ConfigurationpublicclassDataSourceConfigurer {
/** * 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")
publicDataSourcemaster() {
returnDruidDataSourceBuilder.create().build();
}
/** * Slave alpha data source. * * @return the data source */@Bean("slaveAlpha")
@ConfigurationProperties(prefix = "spring.datasource.druid.slave-alpha")
publicDataSourceslaveAlpha() {
returnDruidDataSourceBuilder.create().build();
}
/** * Slave beta data source. * * @return the data source */@Bean("slaveBeta")
@ConfigurationProperties(prefix = "spring.datasource.druid.slave-beta")
publicDataSourceslaveBeta() {
returnDruidDataSourceBuilder.create().build();
}
/** * Slave gamma data source. * * @return the data source */@Bean("slaveGamma")
@ConfigurationProperties(prefix = "spring.datasource.druid.slave-gamma")
publicDataSourceslaveGamma() {
returnDruidDataSourceBuilder.create().build();
}
/** * Dynamic data source. * * @return the data source */@Bean("dynamicDataSource")
publicDataSourcedynamicDataSource() {
DynamicRoutingDataSourcedynamicRoutingDataSource = newDynamicRoutingDataSource();
Map<Object, Object> dataSourceMap = newHashMap<>(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());
returndynamicRoutingDataSource;
} /** * 配置 SqlSessionFactoryBean * @ConfigurationProperties 在这里是为了将 MyBatis 的 mapper 位置和持久层接口的别名设置到  * Bean 的属性中，如果没有使用 *.xml 则可以不用该配置，否则将会产生 invalid bond statement 异常 *  * @return the sql session factory bean */@Bean@ConfigurationProperties(prefix = "mybatis")
publicSqlSessionFactoryBeansqlSessionFactoryBean() {
SqlSessionFactoryBeansqlSessionFactoryBean = newSqlSessionFactoryBean();
// 配置数据源，此处配置为关键配置，如果没有将 dynamicDataSource 作为数据源则不能实现切换sqlSessionFactoryBean.setDataSource(dynamicDataSource());
returnsqlSessionFactoryBean;
}
/** * 注入 DataSourceTransactionManager 用于事务管理 */@BeanpublicPlatformTransactionManagertransactionManager() {
returnnewDataSourceTransactionManager(dynamicDataSource());
}
}

DynamicDataSourceContextHolder.java

该类为数据源上下文配置，用于切换数据源

packagecn.com.hellowood.dynamicdatasource.configuration;
importcn.com.hellowood.dynamicdatasource.common.DataSourceKey;
importorg.slf4j.Logger;
importorg.slf4j.LoggerFactory;
importjava.util.ArrayList;
importjava.util.List;
importjava.util.concurrent.locks.Lock;
importjava.util.concurrent.locks.ReentrantLock;
publicclassDynamicDataSourceContextHolder {
privatestaticfinalLoggerlogger = LoggerFactory.getLogger(DynamicDataSourceContextHolder.class);
/** * 用于在切换数据源时保证不会被其他线程修改 */privatestaticLocklock = newReentrantLock();
/** * 用于轮循的计数器 */privatestaticintcounter = 0;
/** * Maintain variable for every thread, to avoid effect other thread */privatestaticfinalThreadLocal<Object> CONTEXT_HOLDER = ThreadLocal.withInitial(DataSourceKey.master);
/** * All DataSource List */publicstaticList<Object> dataSourceKeys = newArrayList<>();
/** * The constant slaveDataSourceKeys. */publicstaticList<Object> slaveDataSourceKeys = newArrayList<>();
/** * To switch DataSource * * @param key the key */publicstaticvoidsetDataSourceKey(Stringkey) {
CONTEXT_HOLDER.set(key);
}
/** * Use master data source. */publicstaticvoiduseMasterDataSource() {
CONTEXT_HOLDER.set(DataSourceKey.master);
}
/** * 当使用只读数据源时通过轮循方式选择要使用的数据源 */publicstaticvoiduseSlaveDataSource() {
lock.lock();
try {
intdatasourceKeyIndex = counter % slaveDataSourceKeys.size();
CONTEXT_HOLDER.set(String.valueOf(slaveDataSourceKeys.get(datasourceKeyIndex)));
counter++;
} catch (Exceptione) {
logger.error("Switch slave datasource failed, error message is {}", e.getMessage());
useMasterDataSource();
e.printStackTrace();
} finally {
lock.unlock();
}
}
/** * Get current DataSource * * @return data source key */publicstaticStringgetDataSourceKey() {
returnCONTEXT_HOLDER.get();
}
/** * To set DataSource as default */publicstaticvoidclearDataSourceKey() {
CONTEXT_HOLDER.remove();
}
/** * Check if give DataSource is in current DataSource list * * @param key the key * @return boolean boolean */publicstaticbooleancontainDataSourceKey(Stringkey) {
returndataSourceKeys.contains(key);
}
}

DynamicDataSourceAspect.java

动态数据源切换的切面，切 DAO 层，通过 DAO 层方法名判断使用哪个数据源，实现数据源切换关于切面的 Order 可以可以不设，因为 @Transactional 是最低的，取决于其他切面的设置，并且在 org.springframework.core.annotation.AnnotationAwareOrderComparator 会重新排序

packagecn.com.hellowood.dynamicdatasource.configuration;
importorg.aspectj.lang.JoinPoint;
importorg.aspectj.lang.annotation.After;
importorg.aspectj.lang.annotation.Aspect;
importorg.aspectj.lang.annotation.Before;
importorg.aspectj.lang.annotation.Pointcut;
importorg.slf4j.Logger;
importorg.slf4j.LoggerFactory;
importorg.springframework.stereotype.Component;
@Aspect@ComponentpublicclassDynamicDataSourceAspect {
privatestaticfinalLoggerlogger = LoggerFactory.getLogger(DynamicDataSourceAspect.class);
privatefinalString[] QUERY_PREFIX = {"select"};
@Pointcut("execution( * cn.com.hellowood.dynamicdatasource.mapper.*.*(..))")
publicvoiddaoAspect() {
}
@Before("daoAspect()")
publicvoidswitchDataSource(JoinPointpoint) {
BooleanisQueryMethod = isQueryMethod(point.getSignature().getName());
if (isQueryMethod) {
DynamicDataSourceContextHolder.useSlaveDataSource();
logger.info("Switch DataSource to [{}] in Method [{}]",
DynamicDataSourceContextHolder.getDataSourceKey(), point.getSignature());
}
}
@After("daoAspect())")
publicvoidrestoreDataSource(JoinPointpoint) {
DynamicDataSourceContextHolder.clearDataSourceKey();
logger.info("Restore DataSource to [{}] in Method [{}]",
DynamicDataSourceContextHolder.getDataSourceKey(), point.getSignature());
}
privateBooleanisQueryMethod(StringmethodName) {
for (Stringprefix : QUERY_PREFIX) {
if (methodName.startsWith(prefix)) {
returntrue;
}
}
returnfalse;
}
}

配置 Product REST API 接口

ProductController.java

packagecn.com.hellowood.dynamicdatasource.controller;
importcn.com.hellowood.dynamicdatasource.common.CommonResponse;
importcn.com.hellowood.dynamicdatasource.common.ResponseUtil;
importcn.com.hellowood.dynamicdatasource.modal.Product;
importcn.com.hellowood.dynamicdatasource.service.ProductService;
importcn.com.hellowood.dynamicdatasource.utils.ServiceException;
importorg.springframework.beans.factory.annotation.Autowired;
importorg.springframework.web.bind.annotation.*;
@RestController@RequestMapping("/product")
publicclassProductController {
@AutowiredprivateProductServiceproductService;
@GetMapping("/{id}")
publicCommonResponsegetProduct(@PathVariable("id") LongproductId) throwsServiceException {
returnResponseUtil.generateResponse(productService.select(productId));
}
@GetMappingpublicCommonResponsegetAllProduct() {
returnResponseUtil.generateResponse(productService.getAllProduct());
}
@PutMapping("/{id}")
publicCommonResponseupdateProduct(@PathVariable("id") LongproductId, @RequestBodyProductnewProduct) throwsServiceException {
returnResponseUtil.generateResponse(productService.update(productId, newProduct));
}
@DeleteMapping("/{id}")
publicCommonResponsedeleteProduct(@PathVariable("id") longproductId) throwsServiceException {
returnResponseUtil.generateResponse(productService.delete(productId));
}
@PostMappingpublicCommonResponseaddProduct(@RequestBodyProductnewProduct) throwsServiceException {
returnResponseUtil.generateResponse(productService.add(newProduct));
}
}

ProductService.java

packagecn.com.hellowood.dynamicdatasource.service;
importcn.com.hellowood.dynamicdatasource.mapper.ProductDao;
importcn.com.hellowood.dynamicdatasource.modal.Product;
importcn.com.hellowood.dynamicdatasource.utils.ServiceException;
importorg.springframework.beans.factory.annotation.Autowired;
importorg.springframework.dao.DataAccessException;
importorg.springframework.stereotype.Service;
importorg.springframework.transaction.annotation.Transactional;
importjava.util.List;
@ServicepublicclassProductService {
@AutowiredprivateProductDaoproductDao;
publicProductselect(longproductId) throwsServiceException {
Productproduct = productDao.select(productId);
if (product == null) {
thrownewServiceException("Product:" + productId + " not found");
}
returnproduct;
}
@Transactional(rollbackFor = DataAccessException.class)
publicProductupdate(longproductId, ProductnewProduct) throwsServiceException {
if (productDao.update(newProduct) <= 0) {
thrownewServiceException("Update product:" + productId + "failed");
}
returnnewProduct;
}
@Transactional(rollbackFor = DataAccessException.class)
publicbooleanadd(ProductnewProduct) throwsServiceException {
Integernum = productDao.insert(newProduct);
if (num <= 0) {
thrownewServiceException("Add product failed");
}
returntrue;
}
@Transactional(rollbackFor = DataAccessException.class)
publicbooleandelete(longproductId) throwsServiceException {
Integernum = productDao.delete(productId);
if (num <= 0) {
thrownewServiceException("Delete product:" + productId + "failed");
}
returntrue;
}
publicList<Product> getAllProduct() {
returnproductDao.getAllProduct();
}
}

ProductDao.java

packagecn.com.hellowood.dynamicdatasource.mapper;
importcn.com.hellowood.dynamicdatasource.modal.Product;
importorg.apache.ibatis.annotations.Mapper;
importorg.apache.ibatis.annotations.Param;
importjava.util.List;
@MapperpublicinterfaceProductDao {
Productselect(@Param("id") longid);
Integerupdate(Productproduct);
Integerinsert(Productproduct);
Integerdelete(longproductId);
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注解的方法无法切换数据源

Name		Name	Last commit message	Last commit date
gradle/wrapper		gradle/wrapper
src		src
.gitignore		.gitignore
Issues.md		Issues.md
README.md		README.md
build.gradle		build.gradle
gradlew		gradlew
gradlew.bat		gradlew.bat

Navigation Menu

Repository files navigation

Spring Boot 和 MyBatis 实现多数据源、动态数据源切换

在使用的过程中基本踩遍了所有动态数据源切换的坑，将常见的一些坑和解决方法写在了 Issues 里面

添加依赖

创建数据库及表

配置数据源

配置数据源

配置 Product REST API 接口

注意

About

Releases

Packages

Contributors

Languages

Latest commit

History

Folders and files

Repository files navigation

Spring Boot 和 MyBatis 实现多数据源、动态数据源切换

在使用的过程中基本踩遍了所有动态数据源切换的坑，将常见的一些坑和解决方法写在了 Issues 里面

添加依赖

创建数据库及表

配置数据源

配置数据源

配置 Product REST API 接口

注意

About

Resources

Stars

Watchers

Forks

Releases

Packages

Contributors

Languages