1童本、引言

讀寫分離要做的事情就是對于一條SQL該選擇哪個數(shù)據(jù)庫去執(zhí)行辆童，至于誰來做選擇數(shù)據(jù)庫這件事兒亿遂，無非兩個傻昙，要么中間件幫我們做篓叶，要么程序自己做虎韵。

因此时鸵，一般來講言秸，讀寫分離有兩種實現(xiàn)方式什往。第一種是依靠中間件（比如：MyCat）扳缕，也就是說應(yīng)用程序連接到中間件，中間件幫我們做SQL分離别威；第二種是應(yīng)用程序自己去做分離躯舔。這里我們選擇程序自己來做，主要是利用Spring提供的路由數(shù)據(jù)源省古，以及AOP

然而粥庄，應(yīng)用程序?qū)用嫒プ鲎x寫分離最大的弱點（不足之處）在于無法動態(tài)增加數(shù)據(jù)庫節(jié)點，因為數(shù)據(jù)源配置都是寫在配置中的豺妓，新增數(shù)據(jù)庫意味著新加一個數(shù)據(jù)源惜互，必然改配置，并重啟應(yīng)用琳拭。當然训堆，好處就是相對簡單。

1.1項目地址

git-hub:https://github.com/wenlinshan/wenlinshan/tree/main/master-slave-demo

2白嘁、AbstractRoutingDataSource

基于特定的查找key路由到特定的數(shù)據(jù)源坑鱼。它內(nèi)部維護了一組目標數(shù)據(jù)源，并且做了路由key與目標數(shù)據(jù)源之間的映射絮缅，提供基于key查找數(shù)據(jù)源的方法鲁沥。

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>
        <!--驅(qū)動-->
        <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. 數(shù)據(jù)源配置

application.yml

#配置數(shù)據(jù)源耕魄，根據(jù)不同庫模擬主從庫
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:
    #配置返回數(shù)據(jù)庫(column下劃線命名&&返回java實體是駝峰命名)，自動匹配無需as（沒開啟這個吸奴，SQL需要寫as： select user_id as userId）
    map-underscore-to-camel-case: true
    cache-enabled: false
    #配置JdbcTypeForNull, oracle數(shù)據(jù)庫必須配置
    jdbc-type-for-null: 'null'

3.3. 設(shè)置路由key / 查找數(shù)據(jù)源

目標數(shù)據(jù)源就是那前3個這個我們是知道的允扇，但是使用的時候是如果查找數(shù)據(jù)源的呢马靠？

首先，我們定義一個枚舉來代表這三個數(shù)據(jù)源

/**
 * @author wls
 * @desc 數(shù)據(jù)庫類型
 */

public enum DBTypeEnum {
    /**
     * 主節(jié)點
     */
    MASTER,
    /**
     * 從1
     */
    SLAVE1,
    /**
     * 從2
     */
    SLAVE2;

}

新建DataSourceContextHolder

接下來蔼两，通過ThreadLocal將數(shù)據(jù)源設(shè)置到每個線程上下文中

package com.wenlinshan.masterslavedemo.config;

import com.wenlinshan.masterslavedemo.constant.DBTypeEnum;

import java.util.concurrent.atomic.AtomicInteger;

/**
 * 通過ThreadLocal將數(shù)據(jù)源設(shè)置到每個線程上下文
 *
 * @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");
        }
    }

}

設(shè)置路由key

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.lang.Nullable;

/**
 * 聲明路由數(shù)據(jù)源key
 * @author wls
 *
 */
public class MyRoutingDataSource extends AbstractRoutingDataSource {
    @Nullable
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.get();
    }

}

多數(shù)據(jù)源配置

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;

/**
 * 關(guān)于數(shù)據(jù)源配置，參考SpringBoot官方文檔第79章《Data Access》
 * 79. Data Access
 * 79.1 Configure a Custom DataSource
 * 79.2 Configure Two DataSources
 *
 * @author wls
 */

@Configuration
public class DataSourceConfig {

    /**
     * 配置主數(shù)據(jù)源
     *
     * @return 數(shù)據(jù)源
     */
    @Bean(name = "master")
    @ConfigurationProperties(prefix = "spring.datasource.druid.master" )
    public DataSource masterDataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * 配置從數(shù)據(jù)源
     *
     * @return 數(shù)據(jù)源
     */
    @Bean(name = "slave1")
    @ConfigurationProperties(prefix = "spring.datasource.druid.slave1")
    public DataSource slave1DataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * 配置從數(shù)據(jù)源
     *
     * @return 數(shù)據(jù)源
     */
    @Bean(name = "slave2")
    @ConfigurationProperties(prefix = "spring.datasource.druid.slave2")
    public DataSource slave2DataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * 配置路由數(shù)據(jù)源
     *
     * @param masterDataSource 主節(jié)點
     * @param slave1DataSource 從節(jié)點
     * @param slave2DataSource 從節(jié)點
     * @return 數(shù)據(jù)源
     */
    @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();
        //設(shè)置默認數(shù)據(jù)源
        myRoutingDataSource.setDefaultTargetDataSource(masterDataSource);
        myRoutingDataSource.setTargetDataSources(targetDataSources);
        return myRoutingDataSource;
    }

}

這里逞度，我們配置了4個數(shù)據(jù)源额划，1個master，2兩個slave档泽，1個路由數(shù)據(jù)源俊戳。前3個數(shù)據(jù)源都是為了生成第4個數(shù)據(jù)源，而且后續(xù)我們只用這最后一個路由數(shù)據(jù)源馆匿。

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容器中現(xiàn)在有4個數(shù)據(jù)源抑胎，所以我們需要為事務(wù)管理器和MyBatis手動指定一個明確的數(shù)據(jù)源。

3.4 使用aop實現(xiàn)數(shù)據(jù)源切換

默認情況下渐北，所有的查詢都走從庫阿逃，插入/修改/刪除走主庫。我們通過方法名來區(qū)分操作類型（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;

/**
 * 設(shè)置切面 執(zhí)行具體方法選擇的數(shù)據(jù)源
 * @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();
    }
}

有一般情況就有特殊情況赃蛛，特殊情況是某些情況下我們需要強制讀主庫恃锉，針對這種情況，我們定義一個主鍵呕臂，用該注解標注的就讀主庫

package com.wenlinshan.masterslavedemo.annotation;

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

Goods表

package com.wenlinshan.masterslavedemo.domain;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.math.BigDecimal;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@TableName(value = "goods")
public class Goods {
    @TableId(value = "id", type = IdType.ASSIGN_ID)
    private Long id;

    @TableField(value = "`name`")
    private String name;

    @TableField(value = "quantity")
    private Integer quantity;

    @TableField(value = "price")
    private BigDecimal price;

    public static final String COL_ID = "id";

    public static final String COL_NAME = "name";

    public static final String COL_QUANTITY = "quantity";

    public static final String COL_PRICE = "price";
}

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

    /**
     * 查詢?nèi)?     *
     * @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);
    }

    /**
     * 查詢?nèi)?     *
     * @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);
    }
}