springboot2+mybatis plus 自动化读写分离
原文来自简书作者 程序猿温温：https://www.jianshu.com/p/c9dc0334229c
引言
读写分离要做的事情就是对于一条SQL该选择哪个数据库去执行，至于谁来做选择数据库这件事儿，无非两个，要么中间件帮我们做，要么程序自己做。
因此，一般来讲，读写分离有两种实现方式。第一种是依靠中间件（比如：MyCat），也就是说应用程序连接到中间件，中间件帮我们做SQL分离；第二种是应用程序自己去做分离。这里我们选择程序自己来做，主要是利用Spring提供的路由数据源，以及AOP
然而，应用程序层面去做读写分离最大的弱点（不足之处）在于无法动态增加数据库节点，因为数据源配置都是写在配置中的，新增数据库意味着新加一个数据源，必然改配置，并重启应用。当然，好处就是相对简单。
1. 项目地址
git-hub:https://github.com/wenlinshan/wenlinshan/tree/main/master-slave-demo
2.关于AbstractRoutingDataSource
Spring boot提供了AbstractRoutingDataSource 根据用户定义的规则选择当前的数据源，这样我们可以在执行查询之前，设置使用的数据源。实现可动态路由的数据源，在每次数据库查询操作前执行。它的抽象方法 determineCurrentLookupKey() 决定使用哪个数据源。
3.实践
3.1 maven依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.0.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.wenlinshan</groupId>
<artifactId>master-slave-demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>master-slave-demo</name>
<description>master-slave-demo</description>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
<exclusions>
<exclusion>
<artifactId>spring-boot-starter</artifactId>
<groupId>org.springframework.boot</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--mybatis-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.3.4</version>
<exclusions>
<exclusion>
<artifactId>spring-boot-starter-jdbc</artifactId>
<groupId>org.springframework.boot</groupId>
</exclusion>
<exclusion>
<artifactId>spring-boot-autoconfigure</artifactId>
<groupId>org.springframework.boot</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.12.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<!--驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.8</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
3.2 数据源配置
application.yml
#配置数据源，根据不同库模拟主从库
server:
port: 8000
spring:
datasource:
druid:
master:
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/m1?characterEncoding=utf8&verifyServerCertificate=false&useSSL=true&serverTimezone=Asia/Shanghai
initialSize: 5
minIdle: 5
maxActive: 20
slave1:
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/s1?characterEncoding=utf8&verifyServerCertificate=false&useSSL=true&serverTimezone=Asia/Shanghai
initialSize: 5
minIdle: 5
maxActive: 20
slave2:
username: wen
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/s2?characterEncoding=utf8&verifyServerCertificate=false&useSSL=true&serverTimezone=Asia/Shanghai
initialSize: 5
minIdle: 5
maxActive: 20
mybatis-plus:
# 如果是放在src/main/java目录下 classpath:/com/yourpackage/*/mapper/*Mapper.xml
# 如果是放在resource目录 classpath:/mapper/*Mapper.xml
mapper-locations: classpath:/mapper/*Mapper.xml
#实体扫描，多个package用逗号或者分号分隔
typeAliasesPackage: com.seawatebt.ssm.entity
configuration:
#配置返回数据库(column下划线命名&&返回java实体是驼峰命名)，自动匹配无需as（没开启这个，SQL需要写as： select user_id as userId）
map-underscore-to-camel-case: true
cache-enabled: false
#配置JdbcTypeForNull, oracle数据库必须配置
jdbc-type-for-null: 'null'
3.3. 设置路由key / 查找数据源
目标数据源就是那前3个这个我们是知道的，但是使用的时候是如果查找数据源的呢？
首先，我们定义一个枚举来代表这三个数据源
/**
* @author wls
* @desc 数据库类型
*/
public enum DBTypeEnum {
/**
* 主节点
*/
MASTER,
/**
* 从1
*/
SLAVE1,
/**
* 从2
*/
SLAVE2;
}
新建DataSourceContextHolder
接下来，通过ThreadLocal将数据源设置到每个线程上下文中
package com.wenlinshan.masterslavedemo.config;
import com.wenlinshan.masterslavedemo.constant.DBTypeEnum;
import java.util.concurrent.atomic.AtomicInteger;
/**
* 通过ThreadLocal将数据源设置到每个线程上下文
*
* @author wls
*/
public class DataSourceContextHolder {
private static final ThreadLocal<DBTypeEnum> CONTEXT_HOLDER = new ThreadLocal<>();
private static final AtomicInteger COUNTER = new AtomicInteger(-1);
public static void set(DBTypeEnum dbType) {
CONTEXT_HOLDER.set(dbType);
}
public static DBTypeEnum get() {
return CONTEXT_HOLDER.get();
}
public static void clear(){
CONTEXT_HOLDER.remove();
}
public static void master() {
set(DBTypeEnum.MASTER);
System.out.println("切换到master");
}
public static void slave() {
// 轮询
int index = COUNTER.getAndIncrement() % 2;
if (COUNTER.get() > 9999) {
COUNTER.set(-1);
}
if (index == 0) {
set(DBTypeEnum.SLAVE1);
System.out.println("切换到slave1");
} else {
set(DBTypeEnum.SLAVE2);
System.out.println("切换到slave2");
}
}
}
利用AbstractRoutingDataSource 设置路由key
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.lang.Nullable;
/**
* 声明路由数据源key
* @author wls
*
*/
public class MyRoutingDataSource extends AbstractRoutingDataSource {
@Nullable
@Override
protected Object determineCurrentLookupKey() {
return DataSourceContextHolder.get();
}
}
多数据源配置
package com.wenlinshan.masterslavedemo.config;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.wenlinshan.masterslavedemo.constant.DBTypeEnum;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
/**
* 关于数据源配置，参考SpringBoot官方文档第79章《Data Access》
* 79. Data Access
* 79.1 Configure a Custom DataSource
* 79.2 Configure Two DataSources
*
* @author wls
*/
@Configuration
public class DataSourceConfig {
/**
* 配置主数据源
*
* @return 数据源
*/
@Bean(name = "master")
@ConfigurationProperties(prefix = "spring.datasource.druid.master" )
public DataSource masterDataSource() {
return DruidDataSourceBuilder.create().build();
}
/**
* 配置从数据源
*
* @return 数据源
*/
@Bean(name = "slave1")
@ConfigurationProperties(prefix = "spring.datasource.druid.slave1")
public DataSource slave1DataSource() {
return DruidDataSourceBuilder.create().build();
}
/**
* 配置从数据源
*
* @return 数据源
*/
@Bean(name = "slave2")
@ConfigurationProperties(prefix = "spring.datasource.druid.slave2")
public DataSource slave2DataSource() {
return DruidDataSourceBuilder.create().build();
}
/**
* 配置路由数据源
*
* @param masterDataSource 主节点
* @param slave1DataSource 从节点
* @param slave2DataSource 从节点
* @return 数据源
*/
@Bean
public DataSource myRoutingDataSource(@Qualifier("master") DataSource masterDataSource,
@Qualifier("slave1") DataSource slave1DataSource,
@Qualifier("slave2") DataSource slave2DataSource) {
Map<Object, Object> targetDataSources = new HashMap<>(3);
targetDataSources.put(DBTypeEnum.MASTER, masterDataSource);
targetDataSources.put(DBTypeEnum.SLAVE1, slave1DataSource);
targetDataSources.put(DBTypeEnum.SLAVE2, slave2DataSource);
MyRoutingDataSource myRoutingDataSource = new MyRoutingDataSource();
//设置默认数据源
myRoutingDataSource.setDefaultTargetDataSource(masterDataSource);
myRoutingDataSource.setTargetDataSources(targetDataSources);
return myRoutingDataSource;
}
}
这里，配置了4个数据源，1个master，2两个slave，1个路由数据源。前3个数据源都是为了生成第4个数据源，而且后续我们只用这最后一个路由数据源。
MyBatis配置
package com.wenlinshan.masterslavedemo.config;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.omg.PortableInterceptor.Interceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.annotation.Resource;
import javax.sql.DataSource;
/**
* mybatis 配置
* @author wls
*/
@EnableTransactionManagement
@Configuration
public class MyBatisConfig {
@Resource(name = "myRoutingDataSource")
private DataSource myRoutingDataSource;
@Bean(name = "sqlSessionFactory")
public SqlSessionFactory sqlSessionFactory() throws Exception {
MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
sqlSessionFactory.setDataSource(myRoutingDataSource);
MybatisConfiguration configuration = new MybatisConfiguration();
configuration.setJdbcTypeForNull(JdbcType.NULL);
configuration.setMapUnderscoreToCamelCase(true);
configuration.setCacheEnabled(false);
sqlSessionFactory.setConfiguration(configuration);
return sqlSessionFactory.getObject();
}
@Bean
public PlatformTransactionManager platformTransactionManager() {
return new DataSourceTransactionManager(myRoutingDataSource);
}
}
由于Spring容器中现在有4个数据源，所以我们需要为事务管理器和MyBatis手动指定一个明确的数据源。
3.4 使用aop实现数据源切换
默认情况下，所有的查询都走从库，插入/修改/删除走主库。我们通过方法名来区分操作类型（CRUD）
package com.wenlinshan.masterslavedemo.aop;
import com.wenlinshan.masterslavedemo.config.DataSourceContextHolder;
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.springframework.stereotype.Component;
/**
* 设置切面 执行具体方法选择的数据源
* @author wls
*/
@Aspect
@Component
public class DataSourceAop {
/**
* 需要读的方法,切面
*/
@Pointcut("!@annotation(com.wenlinshan.masterslavedemo.annotation.Master)" +
"&& (execution(* com.wenlinshan.masterslavedemo.service..*.select*(..)) " +
"|| execution(* com.wenlinshan.masterslavedemo.service..*.get*(..)))")
public void readPointcut() {
}
/**
* 写切面
*/
@Pointcut("@annotation(com.wenlinshan.masterslavedemo.annotation.Master) " +
"|| execution(* com.wenlinshan.masterslavedemo.service..*.insert*(..))" +
"|| execution(* com.wenlinshan.masterslavedemo.service..*.save*(..))" +
"|| execution(* com.wenlinshan.masterslavedemo.service..*.add*(..))" +
"|| execution(* com.wenlinshan.masterslavedemo.service..*.update*(..))" +
"|| execution(* com.wenlinshan.masterslavedemo.service..*.edit*(..))" +
"|| execution(* com.wenlinshan.masterslavedemo.service..*.delete*(..))" +
"|| execution(* com.wenlinshan.masterslavedemo.service..*.remove*(..))")
public void writePointcut() {
}
@Before("readPointcut()")
public void read() {
DataSourceContextHolder.slave();
}
@Before("writePointcut()")
public void write() {
DataSourceContextHolder.master();
}
@After("readPointcut()")
public void readAfter() {
DataSourceContextHolder.clear();
}
@After("writePointcut()")
public void writeAfter() {
DataSourceContextHolder.clear();
}
}
4 测试
service层
package com.wenlinshan.masterslavedemo.service.impl;
import com.wenlinshan.masterslavedemo.annotation.Master;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.wenlinshan.masterslavedemo.domain.Goods;
import com.wenlinshan.masterslavedemo.mapper.GoodsMapper;
import com.wenlinshan.masterslavedemo.service.GoodsService;
import org.springframework.transaction.annotation.Transactional;
/**
* @author wls
*/
@Service
public class GoodsServiceImpl extends ServiceImpl<GoodsMapper, Goods> implements GoodsService{
/**
* 保存
* @param goods 商品
* @return 是否成功
*/
@Override
@Transactional(rollbackFor = Exception.class)
public boolean saveGoods(Goods goods){
return this.save(goods);
}
/**
* 删除
*
* @param id id
* @return 是否成功
*/
@Transactional(rollbackFor = Exception.class)
@Override
public boolean deleteGoods(Long id) {
return this.removeById(id);
}
/**
* 查询全部
*
* @return 全部
*/
@Override
public List<Goods> getGoodsAll() {
return this.list();
}
/**
* 查询单个
*
* @param id id
* @return 商品
*/
@Master
@Override
public Goods getGoodsById(Long id) {
return this.getById(id);
}
}
controller层
package com.wenlinshan.masterslavedemo.controller;
import com.wenlinshan.masterslavedemo.annotation.Master;
import com.wenlinshan.masterslavedemo.domain.Goods;
import com.wenlinshan.masterslavedemo.service.GoodsService;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import java.util.List;
/**
* @author wls
*/
@RestController
public class GoodsController {
@Resource
private GoodsService goodsService;
/**
* 保存
* @param goods 商品
* @return 是否成功
*/
@PostMapping("/saveGoods")
public boolean saveGoods(Goods goods){
return goodsService.saveGoods(goods);
}
/**
* 删除
*
* @param id id
* @return 是否成功
*/
@DeleteMapping("/deleteGoods")
public boolean deleteGoods(Long id) {
return goodsService.deleteGoods(id);
}
/**
* 查询全部
*
* @return 全部
*/
@GetMapping("/getGoodsAll")
public List<Goods> getGoodsAll() {
return goodsService.getGoodsAll();
}
/**
* 查询单个
*
* @param id id
* @return 商品
*/
@GetMapping("getGoodsById")
public Goods getGoodsById(Long id) {
return goodsService.getGoodsById(id);
}
}