Spring Boot+MyBatis 动态数据源切换、多数据源，读写分离

作 者：呜呜呜啦啦啦

来 源：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

添加依赖

1. dependencies {

2. compile('org.mybatis.spring.boot:mybatis-spring-boot-starter:1.3.1')

3. compile('org.springframework.boot:spring-boot-starter-web')

4. compile('org.springframework.boot:spring-boot-starter-aop')

5. compile('com.alibaba:druid-spring-boot-starter:1.1.6')

6. runtime('mysql:mysql-connector-java')

7. testCompile('org.springframework.boot:spring-boot-starter-test')

8. }

创建数据库及表

product_master, product_slave_alpha, product_slave_beta, product_slave_gamma

在以上数据库中分别创建表 product，并插入不同数据

1. DROP DATABASE IF EXISTS product_master;

2. CREATE DATABASE product_master;

3. CREATE TABLE product_master.product(

4. id INT PRIMARY KEY AUTO_INCREMENT,

5. name VARCHAR(50) NOT NULL,

6. price DOUBLE(10,2) NOT NULL DEFAULT 0);

7. INSERT INTO product_master.product (name, price) VALUES('master', '1');

8. DROP DATABASE IF EXISTS product_slave_alpha;

9. CREATE DATABASE product_slave_alpha;

10. CREATE TABLE product_slave_alpha.product(

11. id INT PRIMARY KEY AUTO_INCREMENT,

12. name VARCHAR(50) NOT NULL,

13. price DOUBLE(10,2) NOT NULL DEFAULT 0);

14. INSERT INTO product_slave_alpha.product (name, price) VALUES('slaveAlpha', '1');

15. DROP DATABASE IF EXISTS product_slave_beta;

16. CREATE DATABASE product_slave_beta;

17. CREATE TABLE product_slave_beta.product(

18. id INT PRIMARY KEY AUTO_INCREMENT,

19. name VARCHAR(50) NOT NULL,

20. price DOUBLE(10,2) NOT NULL DEFAULT 0);

21. INSERT INTO product_slave_beta.product (name, price) VALUES('slaveBeta', '1');

22. DROP DATABASE IF EXISTS product_slave_gamma;

23. CREATE DATABASE product_slave_gamma;

24. CREATE TABLE product_slave_gamma.product(

25. id INT PRIMARY KEY AUTO_INCREMENT,

26. name VARCHAR(50) NOT NULL,

27. price DOUBLE(10,2) NOT NULL DEFAULT 0);

28. INSERT INTO product_slave_gamma.product (name, price) VALUES('slaveGamma', '1');

配置数据源

application.properties

1. ### Master datasource config

2. spring.datasource.druid.master.name=master

3. spring.datasource.druid.master.driver-class-name=com.mysql.jdbc.Driver

4. spring.datasource.druid.master.url=jdbc:mysql://localhost/product_master?useSSL=false

5. spring.datasource.druid.master.port=3306

6. spring.datasource.druid.master.username=root

7. spring.datasource.druid.master.password=123456

8. # SlaveAlpha datasource config

9. spring.datasource.druid.slave-alpha.name=SlaveAlpha

10. spring.datasource.druid.slave-alpha.driver-class-name=com.mysql.jdbc.Driver

11. spring.datasource.druid.slave-alpha.url=jdbc:mysql://localhost/product_slave_alpha?useSSL=false

12. spring.datasource.druid.slave-alpha.port=3306

13. spring.datasource.druid.slave-alpha.username=root

14. spring.datasource.druid.slave-alpha.password=123456

15. # SlaveBeta datasource config

16. spring.datasource.druid.slave-beta.name=SlaveBeta

17. spring.datasource.druid.slave-beta.driver-class-name=com.mysql.jdbc.Driver

18. spring.datasource.druid.slave-beta.url=jdbc:mysql://localhost/product_slave_beta?useSSL=false

19. spring.datasource.druid.slave-beta.port=3306

20. spring.datasource.druid.slave-beta.username=root

21. spring.datasource.druid.slave-beta.password=123456

22. # SlaveGamma datasource config

23. spring.datasource.druid.slave-gamma.name=SlaveGamma

24. spring.datasource.druid.slave-gamma.driver-class-name=com.mysql.jdbc.Driver

25. spring.datasource.druid.slave-gamma.url=jdbc:mysql://localhost/product_slave_gamma?useSSL=false

26. spring.datasource.druid.slave-gamma.port=3306

27. spring.datasource.druid.slave-gamma.username=root

28. spring.datasource.druid.slave-gamma.password=123456

29. # Druid dataSource config

30. spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

31. spring.datasource.druid.initial-size=5

32. spring.datasource.druid.max-active=20

33. spring.datasource.druid.min-idle=5

34. spring.datasource.druid.max-wait=60000

35. spring.datasource.druid.pool-prepared-statements=false

36. spring.datasource.druid.validation-query=SELECT 1

37. spring.datasource.druid.validation-query-timeout=30000

38. spring.datasource.druid.test-on-borrow=false

39. spring.datasource.druid.test-on-return=false

40. spring.datasource.druid.test-while-idle=true

41. #spring.datasource.druid.time-between-eviction-runs-millis=

42. #spring.datasource.druid.min-evictable-idle-time-millis=

43. #spring.datasource.druid.max-evictable-idle-time-millis=10000

44. # Druid stat filter config

45. spring.datasource.druid.filters=stat,wall,log4j

46. spring.datasource.druid.web-stat-filter.enabled=true

47. spring.datasource.druid.web-stat-filter.url-pattern=/*

48. spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*

49. spring.datasource.druid.web-stat-filter.session-stat-enable=true

50. spring.datasource.druid.web-stat-filter.session-stat-max-count=10

51. spring.datasource.druid.web-stat-filter.principal-session-name=user

52. #spring.datasource.druid.web-stat-filter.principal-cookie-name=

53. spring.datasource.druid.web-stat-filter.profile-enable=true

54. spring.datasource.druid.filter.stat.db-type=mysql

55. spring.datasource.druid.filter.stat.log-slow-sql=true

56. spring.datasource.druid.filter.stat.slow-sql-millis=1000

57. spring.datasource.druid.filter.stat.merge-sql=true

58. spring.datasource.druid.filter.wall.enabled=true

59. spring.datasource.druid.filter.wall.config.delete-allow=true

60. spring.datasource.druid.filter.wall.config.drop-table-allow=false

61. spring.datasource.druid.filter.slf4j.enabled=true

62. # Druid manage page config

63. spring.datasource.druid.stat-view-servlet.enabled=true

64. spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*

65. spring.datasource.druid.stat-view-servlet.reset-enable=true

66. spring.datasource.druid.stat-view-servlet.login-username=admin

67. spring.datasource.druid.stat-view-servlet.login-password=admin

68. #spring.datasource.druid.stat-view-servlet.allow=

69. #spring.datasource.druid.stat-view-servlet.deny=

70. spring.datasource.druid.use-global-data-source-stat=true

71. # Druid AOP config

72. spring.datasource.druid.aop-patterns=cn.com.hellowood.dynamicdatasource.service.*

73. spring.aop.proxy-target-class=true

74. # MyBatis config

75. mybatis.type-aliases-package=cn.com.hellowood.dynamicdatasource.mapper

76. mybatis.mapper-locations=mappers/**Mapper.xml

77. server.port=9999

配置数据源

DataSourceKey.java

1. package cn.com.hellowood.dynamicdatasource.common;

2. public enum DataSourceKey {

3. master,

4. slaveAlpha,

5. slaveBeta,

6. slaveGamma

7. }

DataSourceRoutingDataSource.java

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

1. package cn.com.hellowood.dynamicdatasource.configuration;

2. import org.slf4j.Logger;

3. import org.slf4j.LoggerFactory;

4. import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

5. public class DynamicRoutingDataSource extends AbstractRoutingDataSource {

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

7. @Override

8. protected Object determineCurrentLookupKey() {

9. logger.info("Current DataSource is [{}]", DynamicDataSourceContextHolder.getDataSourceKey());

10. return DynamicDataSourceContextHolder.getDataSourceKey();

11. }

12. }

DataSourceConfigurer.java

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

1. package cn.com.hellowood.dynamicdatasource.configuration;

2. import org.mybatis.spring.SqlSessionFactoryBean;

3. import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;

4. import org.springframework.boot.context.properties.ConfigurationProperties;

5. import org.springframework.context.annotation.Bean;

6. import org.springframework.context.annotation.Configuration;

7. import org.springframework.context.annotation.Primary;

8. import javax.sql.DataSource;

9. import java.util.HashMap;

10. import java.util.Map;

11. @Configuration

12. public class DataSourceConfigurer {

13. @Bean("master")

14. @Primary

15. @ConfigurationProperties(prefix = "spring.datasource.druid.master")

16. public DataSource master() {

17. return DruidDataSourceBuilder.create().build();

18. }

19. @Bean("slaveAlpha")

20. @ConfigurationProperties(prefix = "spring.datasource.druid.slave-alpha")

21. public DataSource slaveAlpha() {

22. return DruidDataSourceBuilder.create().build();

23. }

24. @Bean("slaveBeta")

25. @ConfigurationProperties(prefix = "spring.datasource.druid.slave-beta")

26. public DataSource slaveBeta() {

27. return DruidDataSourceBuilder.create().build();

28. }

29. @Bean("slaveGamma")

30. @ConfigurationProperties(prefix = "spring.datasource.druid.slave-gamma")

31. public DataSource slaveGamma() {

32. return DruidDataSourceBuilder.create().build();

33. }

34. @Bean("dynamicDataSource")

35. public DataSource dynamicDataSource() {

36. DynamicRoutingDataSource dynamicRoutingDataSource = new DynamicRoutingDataSource();

37. Map<Object, Object> dataSourceMap = new HashMap<>(4);

38. dataSourceMap.put(DataSourceKey.master.name(), master());

39. dataSourceMap.put(DataSourceKey.slaveAlpha.name(), slaveAlpha());

40. dataSourceMap.put(DataSourceKey.slaveBeta.name(), slaveBeta());

41. dataSourceMap.put(DataSourceKey.slaveGamma.name(), slaveGamma());

42. dynamicRoutingDataSource.setDefaultTargetDataSource(master());

43. dynamicRoutingDataSource.setTargetDataSources(dataSourceMap);

44. DynamicDataSourceContextHolder.dataSourceKeys.addAll(dataSourceMap.keySet());

45. DynamicDataSourceContextHolder.slaveDataSourceKeys.addAll(dataSourceMap.keySet());

46. DynamicDataSourceContextHolder.slaveDataSourceKeys.remove(DataSourceKey.master.name());

47. return dynamicRoutingDataSource;

48. }

49. @Bean

50. @ConfigurationProperties(prefix = "mybatis")

51. public SqlSessionFactoryBean sqlSessionFactoryBean() {

52. SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();

53. sqlSessionFactoryBean.setDataSource(dynamicDataSource());

54. return sqlSessionFactoryBean;

55. }

56. @Bean

57. public PlatformTransactionManager transactionManager() {

58. return new DataSourceTransactionManager(dynamicDataSource());

59. }

60. }

DynamicDataSourceContextHolder.java

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

1. package cn.com.hellowood.dynamicdatasource.configuration;

2. import cn.com.hellowood.dynamicdatasource.common.DataSourceKey;

3. import org.slf4j.Logger;

4. import org.slf4j.LoggerFactory;

5. import java.util.ArrayList;

6. import java.util.List;

7. import java.util.concurrent.locks.Lock;

8. import java.util.concurrent.locks.ReentrantLock;

9. public class DynamicDataSourceContextHolder {

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

11. private static Lock lock = new ReentrantLock();

12. private static int counter = 0;

13. private static final ThreadLocal<Object> CONTEXT_HOLDER = ThreadLocal.withInitial(DataSourceKey.master);

14. public static List<Object> dataSourceKeys = new ArrayList<>();

15. public static List<Object> slaveDataSourceKeys = new ArrayList<>();

16. public static void setDataSourceKey(String key) {

17. CONTEXT_HOLDER.set(key);

18. }

19. public static void useMasterDataSource() {

20. CONTEXT_HOLDER.set(DataSourceKey.master);

21. }

22. public static void useSlaveDataSource() {

23. lock.lock();

24. try {

25. int datasourceKeyIndex = counter % slaveDataSourceKeys.size();

26. CONTEXT_HOLDER.set(String.valueOf(slaveDataSourceKeys.get(datasourceKeyIndex)));

27. counter++;

28. } catch (Exception e) {

29. logger.error("Switch slave datasource failed, error message is {}", e.getMessage());

30. useMasterDataSource();

31. e.printStackTrace();

32. } finally {

33. lock.unlock();

34. }

35. }

36. public static String getDataSourceKey() {

37. return CONTEXT_HOLDER.get();

38. }

39. public static void clearDataSourceKey() {

40. CONTEXT_HOLDER.remove();

41. }

42. public static boolean containDataSourceKey(String key) {

43. return dataSourceKeys.contains(key);

44. }

45. }

DynamicDataSourceAspect.java

动态数据源切换的切面，切 DAO 层，通过 DAO 层方法名判断使用哪个数据源，实现数据源切换

1. package cn.com.hellowood.dynamicdatasource.configuration;

2. import org.aspectj.lang.JoinPoint;

3. import org.aspectj.lang.annotation.After;

4. import org.aspectj.lang.annotation.Aspect;

5. import org.aspectj.lang.annotation.Before;

6. import org.aspectj.lang.annotation.Pointcut;

7. import org.slf4j.Logger;

8. import org.slf4j.LoggerFactory;

9. import org.springframework.stereotype.Component;

10. @Aspect

11. @Component

12. public class DynamicDataSourceAspect {

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

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

15. @Pointcut("execution( * cn.com.hellowood.dynamicdatasource.mapper.*.*(..))")

16. public void daoAspect() {

17. }

18. @Before("daoAspect()")

19. public void switchDataSource(JoinPoint point) {

20. Boolean isQueryMethod = isQueryMethod(point.getSignature().getName());

21. if (isQueryMethod) {

22. DynamicDataSourceContextHolder.useSlaveDataSource();

23. logger.info("Switch DataSource to [{}] in Method [{}]",

24. DynamicDataSourceContextHolder.getDataSourceKey(), point.getSignature());

25. }

26. }

27. @After("daoAspect()")

28. public void restoreDataSource(JoinPoint point) {

29. DynamicDataSourceContextHolder.clearDataSourceKey();

30. logger.info("Restore DataSource to [{}] in Method [{}]",

31. DynamicDataSourceContextHolder.getDataSourceKey(), point.getSignature());

32. }

33. private Boolean isQueryMethod(String methodName) {

34. for (String prefix : QUERY_PREFIX) {

35. if (methodName.startsWith(prefix)) {

36. return true;

37. }

38. }

39. return false;

40. }

41. }

配置 Product REST API 接口

ProductController.java

1. package cn.com.hellowood.dynamicdatasource.controller;

2. import cn.com.hellowood.dynamicdatasource.common.CommonResponse;

3. import cn.com.hellowood.dynamicdatasource.common.ResponseUtil;

4. import cn.com.hellowood.dynamicdatasource.modal.Product;

5. import cn.com.hellowood.dynamicdatasource.service.ProductService;

6. import cn.com.hellowood.dynamicdatasource.utils.ServiceException;

7. import org.springframework.beans.factory.annotation.Autowired;

8. import org.springframework.web.bind.annotation.*;

9. @RestController

10. @RequestMapping("/product")

11. public class ProductController {

12. @Autowired

13. private ProductService productService;

14. @GetMapping("/{id}")

15. public CommonResponse getProduct(@PathVariable("id") Long productId) throws ServiceException {

16. return ResponseUtil.generateResponse(productService.select(productId));

17. }

18. @GetMapping

19. public CommonResponse getAllProduct() {

20. return ResponseUtil.generateResponse(productService.getAllProduct());

21. }

22. @PutMapping("/{id}")

23. public CommonResponse updateProduct(@PathVariable("id") Long productId, @RequestBody Product newProduct) throws ServiceException {

24. return ResponseUtil.generateResponse(productService.update(productId, newProduct));

25. }

26. @DeleteMapping("/{id}")

27. public CommonResponse deleteProduct(@PathVariable("id") long productId) throws ServiceException {

28. return ResponseUtil.generateResponse(productService.delete(productId));

29. }

30. @PostMapping

31. public CommonResponse addProduct(@RequestBody Product newProduct) throws ServiceException {

32. return ResponseUtil.generateResponse(productService.add(newProduct));

33. }

34. }

ProductService.java

1. package cn.com.hellowood.dynamicdatasource.service;

2. import cn.com.hellowood.dynamicdatasource.mapper.ProductDao;

3. import cn.com.hellowood.dynamicdatasource.modal.Product;

4. import cn.com.hellowood.dynamicdatasource.utils.ServiceException;

5. import org.springframework.beans.factory.annotation.Autowired;

6. import org.springframework.dao.DataAccessException;

7. import org.springframework.stereotype.Service;

8. import org.springframework.transaction.annotation.Transactional;

9. import java.util.List;

10. @Service

11. public class ProductService {

12. @Autowired

13. private ProductDao productDao;

14. public Product select(long productId) throws ServiceException {

15. Product product = productDao.select(productId);

16. if (product == null) {

17. throw new ServiceException("Product:" + productId + " not found");

18. }

19. return product;

20. }

21. @Transactional(rollbackFor = DataAccessException.class)

22. public Product update(long productId, Product newProduct) throws ServiceException {

23. if (productDao.update(newProduct) <= 0) {

24. throw new ServiceException("Update product:" + productId + "failed");

25. }

26. return newProduct;

27. }

28. @Transactional(rollbackFor = DataAccessException.class)

29. public boolean add(Product newProduct) throws ServiceException {

30. Integer num = productDao.insert(newProduct);

31. if (num <= 0) {

32. throw new ServiceException("Add product failed");

33. }

34. return true;

35. }

36. @Transactional(rollbackFor = DataAccessException.class)

37. public boolean delete(long productId) throws ServiceException {

38. Integer num = productDao.delete(productId);

39. if (num <= 0) {

40. throw new ServiceException("Delete product:" + productId + "failed");

41. }

42. return true;

43. }

44. public List<Product> getAllProduct() {

45. return productDao.getAllProduct();

46. }

47. }

ProductDao.java

1. package cn.com.hellowood.dynamicdatasource.mapper;

2. import cn.com.hellowood.dynamicdatasource.modal.Product;

3. import org.apache.ibatis.annotations.Mapper;

4. import org.apache.ibatis.annotations.Param;

5. import java.util.List;

6. @Mapper

7. public interface ProductDao {

8. Product select(@Param("id") long id);

9. Integer update(Product product);

10. Integer insert(Product product);

11. Integer delete(long productId);

12. List<Product> getAllProduct();

13. }

ProductMapper.xml

启动项目，此时访问 /product/1 会返回 productmaster 数据库中 product 表中的所有数据，多次访问 /product 会分别返回 productslavealpha、productslavebeta、productslave_gamma 数据库中 product 表中的数据，同时也可以在看到切换数据源的 log，说明动态切换数据源是有效的

注意

在该应用中因为使用了 DAO 层的切面切换数据源，所以 @Transactional 注解不能加在类上，只能用于方法；有 @Trasactional注解的方法无法切换数据源

推荐阅读