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

比眉伴天荒 2021-08-19 15:30 441阅读 0赞

![640?wx\_fmt=png][640_wx_fmt_png]

作 者：呜呜呜啦啦啦

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

### 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.***

***2.***

***3.***

***4.***

![640?][640]

[640_wx_fmt_png]: /images/20210731/24665f2622ea46ae8af3a9ee40f6d350.png?wx_fmt=png
[640]: /images/20210731/289f268a20124ec5bfc8de9a1464e46e.png?