Skip to content
/SpringBoot-DynamicDataSourcePublic
forked from helloworlde/SpringBoot-DynamicDataSource

Spring Boot Dynamic and Multiple DataSource project / Spring Boot 动态数据源、多数据源切换

0 stars 312 forks BranchesTagsActivity
Star
Notifications You must be signed in to change notification settings

Jason110141/SpringBoot-DynamicDataSource

BranchesTags

Repository files navigation

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

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

  • master: 使用了多数据源的 RESTful API 接口，使用 Druid 实现了 DAO 层数据源动态切换和只读数据源负载均衡
  • dev: 最简单的切面和注解方式实现的动态数据源切换
  • druid: 通过切面和注解方式实现的使用 Druid 连接池的动态数据源切换
  • aspect_dao: 通过切面实现的 DAO 层的动态数据源切换
  • roundrobin: 通过切面使用轮询方式实现的只读数据源负载均衡
  • hikari: 升级到SpringBoot 2.0版本 数据源使用 Hikar
  • 多数据源分布式事务: 使用 Seata 实现的多数据源事务

以上分支都是基于 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.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.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注解的方法无法切换数据源

About

Spring Boot Dynamic and Multiple DataSource project / Spring Boot 动态数据源、多数据源切换

Resources

Readme
Activity

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

No packages published

Languages

  • Java96.4%
  • TSQL3.6%