springboot+mybatis_plus实现读写分离

1、引言

读写分离主要是为了将读与写分离开，方便更加快速的查询与增删改操作。做的事情就是为一条SQL通过多个数据库（一主多从）去该选择哪个数据库去执行，至于由谁来做选择数据库这件事儿，无非两个，要么中间件帮我们做，要么程序自己做。

本文主要讲解的是基于springBoot+mybatisPlus从自己做的程序方面来实现读写分离，主要原理是多数据源集中配置，Spring提供的路由数据源，以及AOP配置等

2、详细代码配置转看项目地址

gitee:https://gitee.com/ilikelbb/read_write_separation.git

3、部分配置

3.1. maven依赖

pom.xml

<?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/>
    </parent>
    <groupId>com.demo</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 多数据源配置，本文以三个为例，一主二从

定义数据源枚举 DBTypeEnum

package com.demo.constant;

/**
 * @author wugang
 * @desc 数据库类型枚举
 */

public enum DBTypeEnum {
    /**
     * 主节点
     */
    MASTER,
    /**
     * 从1
     */
    SLAVE1,
    /**
     * 从2
     */
    SLAVE2;

}

application.yml

#配置数据源
server:
  port: 8001
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
      #从数据源1
      slave1:
        username: root
        password: root
        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
      #从数据源2
      slave2:
        username: root
        password: root
        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.demo.domain
  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'

 DataSourceConfig.java

package com.demo.config;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.demo.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;

/**
 *
 * @author wugang
 */

@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;
    }

}

 SqlSessionFactoryConfig.java

package com.demo.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 wugang
 */
@EnableTransactionManagement
@Configuration
public class SqlSessionFactoryConfig {

    @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容器中现在有多个数据源，所以我们需要为事务管理器和MyBatis手动指定一个明确的数据源。

3.3 使用AOP实现切换数据源，所有的查询都走从库，插入/修改/删除走主库

package com.demo.aop;

import com.demo.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 wugang
 */
@Aspect
@Component
public class DataSourceAop {

    /**
     * 需要读的方法,切面
     */
    @Pointcut("!@annotation(com.demo.annotation.Master)" +
            "&& (execution(* com.demo.service..*.select*(..)) " +
            "|| execution(* com.demo.service..*.get*(..)))" +
            "|| execution(* com.demo.service..*.query*(..)))")
    public void readPointcut() {

    }

    /**
     * 写切面
     */
    @Pointcut("@annotation(com.demo.annotation.Master) " +
            "|| execution(* com.demo.service..*.insert*(..))" +
            "|| execution(* com.demo.service..*.save*(..))" +
            "|| execution(* com.demo.service..*.add*(..))" +
            "|| execution(* com.demo.service..*.update*(..))" +
            "|| execution(* com.demo.service..*.edit*(..))" +
            "|| execution(* com.demo.service..*.delete*(..))" +
            "|| execution(* com.demo.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();
    }
}

备注：在一些特殊情况下我们需要强制读主库，针对这种情况，我们定义一个主键，用该注解标注的就读主库

package com.demo.annotation;

/**
 * @author wugang
 */
public @interface Master {
}

4、实际应用

在service层方法前增加注解@Master表示使用主库，进行增删改的操作使用主库。
在service层方法前增加注解@Slave表示使用从库，进行查的操作使用从库，默认使用从库，可不配置。
@ Transactional注解加到service层，增加了@Transactional注解后，启用事务后，一个事务内部的connection是复用的，所以就算AOP切了数据源字符串，但是数据源并不会被真正修改。所以@Transactional注解不要写在controller层，不然在service层也切换不了数据源。
@Transactional与@Master可同时使用，已经配置@Master注解的优先级较高，先切换数据源后执行事务。

5、测试

package com.demo.service;

import com.demo.domain.Goods;
import com.baomidou.mybatisplus.extension.service.IService;

import java.util.List;

/**
 * @author wugang
 */
public interface GoodsService extends IService<Goods> {
    /**
     * 保存
     * @param goods 商品
     * @return  是否成功
     */
    boolean saveGoods(Goods goods);

    /**
     * 删除
     * @param id id
     * @return  是否成功
     */
    boolean deleteGoods(Long id);
    /**
     * 查询全部
     * @return  全部
     */
    List<Goods> getGoodsAll();

    /**
     * 查询单个
     * @param id id
     * @return  商品
     */
    Goods getGoodsById(Long id);

}

package com.demo.service.impl;

import com.demo.domain.Goods;
import com.demo.mapper.GoodsMapper;
import com.demo.service.GoodsService;
import com.demo.annotation.Master;
import org.springframework.stereotype.Service;

import java.util.List;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.transaction.annotation.Transactional;

/**
 * @author wugang
 */
@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);
    }

}