前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP最新优惠活动
返回腾讯云官网
社区首页 >专栏 >Spring Boot MyBatis 动态数据源切换、多数据源，读写分离

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

作者头像
JAVA葵花宝典
发布于 2019-06-11 15:25:53
1.7K0
发布于 2019-06-11 15:25:53
举报
文章被收录于专栏：JAVA葵花宝典JAVA葵花宝典

来源：https://blog.csdn.net/u013360850/article/details/78861442 作者：呜呜呜啦啦啦

项目地址: https://github.com/helloworlde/SpringBoot-DynamicDataSource

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

  • 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

添加依赖

代码语言：javascript
复制
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，并插入不同数据

代码语言：javascript
复制
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

代码语言：javascript
复制
### 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

代码语言：javascript
复制
package cn.com.hellowood.dynamicdatasource.common;
public enum DataSourceKey {
    master,
    slaveAlpha,
    slaveBeta,
    slaveGamma
}

DataSourceRoutingDataSource.java

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

代码语言：javascript
复制
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 中

代码语言：javascript
复制
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

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

代码语言：javascript
复制
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 层方法名判断使用哪个数据源，实现数据源切换

代码语言：javascript
复制
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

代码语言：javascript
复制
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

代码语言：javascript
复制
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

代码语言：javascript
复制
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注解的方法无法切换数据源

本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2019-06-04，如有侵权请联系 cloudcommunity@tencent.com 删除
java
spring

本文分享自 JAVA葵花宝典 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ，欢迎热爱写作的你一起参与！

java
spring
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
LV.
文章
0
获赞
0
目录
  • 添加依赖
  • 创建数据库及表
  • 配置数据源
    • application.properties
    • 配置数据源
    • 配置 Product REST API 接口
    • ProductMapper.xml
    • 注意
    相关产品与服务
    负载均衡
    负载均衡（Cloud Load Balancer，CLB）提供安全快捷的流量分发服务，访问流量经由 CLB 可以自动分配到云中的多台后端服务器上，扩展系统的服务能力并消除单点故障。负载均衡支持亿级连接和千万级并发，可轻松应对大流量访问，满足业务需求。
    免费体验产品介绍产品文档
    精选特惠 用云无忧
    领券

    腾讯云开发者

    扫码关注腾讯云开发者

    扫码关注腾讯云开发者

    领取腾讯云代金券

    热门产品

    热门推荐

    更多推荐

    Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

    深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

    腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

    问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

    Copyright © 2013 - 2024 Tencent Cloud.

    All Rights Reserved. 腾讯云 版权所有

    登录 后参与评论