Skip to content
BranchesTags

Repository files navigation

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

在使用的过程中基本踩遍了所有动态数据源切换的坑，将常见的一些坑和解决方法写在了 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.2')
compile('org.springframework.boot:spring-boot-starter-web')
compile('org.springframework.boot:spring-boot-starter-aop')
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
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
# Master datasource configspring.datasource.hikari.master.name=master
spring.datasource.hikari.master.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.hikari.master.jdbc-url=jdbc:mysql://localhost/product_master?useSSL=false
spring.datasource.hikari.master.port=3306
spring.datasource.hikari.master.username=root
spring.datasource.hikari.master.password=123456
# SlaveAlpha datasource configspring.datasource.hikari.slave-alpha.name=SlaveAlpha
spring.datasource.hikari.slave-alpha.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.hikari.slave-alpha.jdbc-url=jdbc:mysql://localhost/product_slave_alpha?useSSL=false
spring.datasource.hikari.slave-alpha.port=3306
spring.datasource.hikari.slave-alpha.username=root
spring.datasource.hikari.slave-alpha.password=123456
# SlaveBeta datasource configspring.datasource.hikari.slave-beta.name=SlaveBeta
spring.datasource.hikari.slave-beta.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.hikari.slave-beta.jdbc-url=jdbc:mysql://localhost/product_slave_beta?useSSL=false
spring.datasource.hikari.slave-beta.port=3306
spring.datasource.hikari.slave-beta.username=root
spring.datasource.hikari.slave-beta.password=123456
# SlaveGamma datasource configspring.datasource.hikari.slave-gamma.name=SlaveGamma
spring.datasource.hikari.slave-gamma.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.hikari.slave-gamma.jdbc-url=jdbc:mysql://localhost/product_slave_gamma?useSSL=false
spring.datasource.hikari.slave-gamma.port=3306
spring.datasource.hikari.slave-gamma.username=root
spring.datasource.hikari.slave-gamma.password=123456
spring.aop.proxy-target-class=true
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.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.hikari.master")
publicDataSourcemaster() {
returnDataSourceBuilder.create().build();
}
/** * Slave alpha data source. * * @return the data source */@Bean("slaveAlpha")
@ConfigurationProperties(prefix = "spring.datasource.hikari.slave-alpha")
publicDataSourceslaveAlpha() {
returnDataSourceBuilder.create().build();
}
/** * Slave beta data source. * * @return the data source */@Bean("slaveBeta")
@ConfigurationProperties(prefix = "spring.datasource.hikari.slave-beta")
publicDataSourceslaveBeta() {
returnDataSourceBuilder.create().build();
}
/** * Slave gamma data source. * * @return the data source */@Bean("slaveGamma")
@ConfigurationProperties(prefix = "spring.datasource.druid.slave-gamma")
publicDataSourceslaveGamma() {
returnDataSourceBuilder.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();
// 配置 MyBatissqlSessionFactoryBean.setTypeAliasesPackage("cn.com.hellowood.dynamicdatasource.mapper");
sqlSessionFactoryBean.setMapperLocations(newPathMatchingResourcePatternResolver().getResources("mappers/**Mapper.xml"));
// 配置数据源，此处配置为关键配置，如果没有将 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;
publicclassDynamicDataSourceContextHolder {
privatestaticfinalLoggerlogger = LoggerFactory.getLogger(DynamicDataSourceContextHolder.class);
/** * 用于轮循的计数器 */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() {
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();
} }
/** * 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 = {"get"};
@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.model.Product;
importcn.com.hellowood.dynamicdatasource.service.ProductService;
importcn.com.hellowood.dynamicdatasource.error.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.model.Product;
importcn.com.hellowood.dynamicdatasource.error.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.model.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注解的方法无法切换数据源

Stargazers over time

Stargazers over time

About

Dynamic datasource, multiple datasource of spring boot/ distribution transaction; spring boot 多数据源，动态数据源，分布式事务

Topics

dynamicspringbootspringboot-dynamic-datasourcespringboot-multiple-datasourcespringboot-mybatis

Resources

Readme
Apache-2.0 license
Activity

Stars

693 stars

Watchers

47 watching

Forks

310 forks
Report repository

Releases

Packages

Used by

Contributors

Languages