32位数据源中没有mysql_Spring Boot+MyBatis 动态数据源切换、多数据源,读写分离

d2a3bfc220527ba8bcde774182eed75f.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. 29 个问答带你梳理 Spring !

2. Spring 中如何运用设计模式?

3. 我从 500 个技术号,选出这 10 个

4. 为什么要前后端分离?有什么优缺点

da2018a5b4601304af754ae08bd3748b.png

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值