前提

2台mysql

①可以开两个docker ②也可以在一个MySQL服务器中用2个数据库

 docker run -d -p 3307:3306 --name mysql01 -e MYSQL_ROOT_PASSWORD=123456  docker.io/mysql
 docker run -d -p 3308:3306 --name mysql02 -e MYSQL_ROOT_PASSWORD=123456  docker.io/mysql

SpringBoot

代码

代码下载: Demooo/springboot-readwrite-separation-deno at master · cbeann/Demooo · GitHub

目录结构

maven依赖

    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.11</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.aspectj/aspectjweaver -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

配置文件application.yml

spring:
  datasource:
    master:
      jdbc-url: jdbc:mysql://202.204.124.110:3306/readwritedemo
      username: root
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver
    slave:
      jdbc-url: jdbc:mysql://202.204.124.110:3306/readwritedemo
      username: root   # 只读账户
      password: root
      driver-class-name: com.mysql.jdbc.Driver

数据源配置

DataSourceConfig

此处设计了两个数据源，一个是master,一个是slave

package com.example.config;
/**
 * @author chaird
 * @create 2020-12-30 21:27
 */
import com.example.enums.DBTypeEnum;
import com.example.bean.MyRoutingDataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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
 */
@Configuration
public class DataSourceConfig {
  @Bean
  @ConfigurationProperties("spring.datasource.master")
  public DataSource masterDataSource() {
    return DataSourceBuilder.create().build();
  }
  @Bean
  @ConfigurationProperties("spring.datasource.slave")
  public DataSource slaveDataSource() {
    return DataSourceBuilder.create().build();
  }
  @Bean
  public DataSource myRoutingDataSource(
      @Qualifier("masterDataSource") DataSource masterDataSource,
      @Qualifier("slaveDataSource") DataSource slave1DataSource) {
    Map<Object, Object> targetDataSources = new HashMap<>(2);
    targetDataSources.put(DBTypeEnum.MASTER, masterDataSource);
    targetDataSources.put(DBTypeEnum.SLAVE, slave1DataSource);
    MyRoutingDataSource myRoutingDataSource = new MyRoutingDataSource();
    myRoutingDataSource.setDefaultTargetDataSource(masterDataSource);
    myRoutingDataSource.setTargetDataSources(targetDataSources);
    return myRoutingDataSource;
  }
}

MyBatisConfig

MyBatis配置xml位置，事务管理器等

package com.example.config;
/**
 * @author chaird
 * @create 2020-12-30 21:27
 */
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
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;
@EnableTransactionManagement
@Configuration
public class MyBatisConfig {
  @Resource(name = "myRoutingDataSource")
  private DataSource myRoutingDataSource;
  @Bean
  public SqlSessionFactory sqlSessionFactory() throws Exception {
    SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
    sqlSessionFactoryBean.setDataSource(myRoutingDataSource);
    sqlSessionFactoryBean.setMapperLocations(
        new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
    return sqlSessionFactoryBean.getObject();
  }
  @Bean
  public PlatformTransactionManager platformTransactionManager() {
    return new DataSourceTransactionManager(myRoutingDataSource);
  }
}

DBTypeEnum 枚举类

package com.example.enums;
/**
 * @author chaird
 * @create 2020-12-30 21:28
 */
public enum DBTypeEnum {
  MASTER,
  SLAVE;
}

MyRoutingDataSource

AbstractRoutingDataSource 一个神奇的接口，自己在determineCurrentLookupKey方法debug就清楚了。

package com.example.bean;
/**
 * @author chaird
 * @create 2020-12-30 21:37
 */
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.lang.Nullable;
public class MyRoutingDataSource extends AbstractRoutingDataSource {
  @Nullable
  @Override
  protected Object determineCurrentLookupKey() {
    return DBContextHolder.get();
  }
}

AOP设置

DataSourceAop

设置切面和切面方法

package com.example.aop;
/**
 * @author chaird
 * @create 2020-12-30 21:30
 */
import com.example.bean.DBContextHolder;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;
/** 切面表达式和方法 */
@Aspect
@Component
public class DataSourceAop {
  /** 读切面（条件1&&条件2）
   * 条件1：没有Master注解 条件2：com.example.service包下 任意类 的select* 或者get*方法
   * */
  @Pointcut(
      "!@annotation(com.example.annotation.Master) "
          + "&& (execution(* com.example.service..*.select*(..)) "
          + "|| execution(* com.example.service..*.get*(..)))")
  public void readPointcut() {}
    /** 写切面
     * 类似上面，不做解释
     * */
  @Pointcut(
      "@annotation(com.example.annotation.Master) "
          + "|| execution(* com.example.service..*.insert*(..)) "
          + "|| execution(* com.example.service..*.add*(..)) "
          + "|| execution(* com.example.service..*.update*(..)) "
          + "|| execution(* com.example.service..*.edit*(..)) "
          + "|| execution(* com.example.service..*.delete*(..)) "
          + "|| execution(* com.example.service..*.remove*(..))")
  public void writePointcut() {}
    /**
     * Before方法，设置ThreadLocal里的一个变量为slave
     */
  @Before("readPointcut()")
  public void read() {
    DBContextHolder.slave();
  }
    /**
     * Before方法，设置ThreadLocal里的一个变量为master
     */
  @Before("writePointcut()")
  public void write() {
    DBContextHolder.master();
  }
  /** 另一种写法：if...else... 判断哪些需要读从数据库，其余的走主数据库 */
  //    @Before("execution(* com.cjs.example.service.impl.*.*(..))")
  //    public void before(JoinPoint jp) {
  //        String methodName = jp.getSignature().getName();
  //
  //        if (StringUtils.startsWithAny(methodName, "get", "select", "find")) {
  //            DBContextHolder.slave();
  //        }else {
  //            DBContextHolder.master();
  //        }
  //    }
}

Master

自定义注解,有该注解的的service方法出现主库

package com.example.annotation;
/**
 * @author chaird
 * @create 2020-12-30 21:35
 * 加此注解的的方法查询主库
 */
public @interface Master {}

DBContextHolder

给threadLocal设置参数

package com.example.bean;
/**
 * @author chaird
 * @create 2020-12-30 21:30
 */
import com.example.enums.DBTypeEnum;
import java.util.concurrent.atomic.AtomicInteger;
public class DBContextHolder {
  private static final ThreadLocal<DBTypeEnum> contextHolder = new ThreadLocal<>();
  private static final AtomicInteger counter = new AtomicInteger(-1);
  public static void set(DBTypeEnum dbType) {
    contextHolder.set(dbType);
  }
  public static DBTypeEnum get() {
    return contextHolder.get();
  }
  public static void master() {
    set(DBTypeEnum.MASTER);
    System.out.println("切换到master");
  }
  public static void slave() {
    //  轮询
    set(DBTypeEnum.SLAVE);
    System.out.println("切换到slave2");
  }
}

entity层

package com.example.entity;
import java.io.Serializable;
import lombok.Data;
/**
 * student
 * @author 
 */
@Data
public class Student implements Serializable {
    private Integer id;
    private String name;
    private Integer age;
    private static final long serialVersionUID = 1L;
}

mapper、xml（省略）

service层

package com.example.service;
import com.example.annotation.Master;
import com.example.dao.StudentDao;
import com.example.entity.Student;
import com.example.entity.StudentExample;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
 * @author chaird
 * @create 2020-12-30 21:38
 */
@Service
public class StudentService {
  @Autowired private StudentDao studentDao;
  public List<Student> getAllSlave() {
    return studentDao.selectByExample(new StudentExample());
  }
  @Master
  public List<Student> getAllMaster() {
    return studentDao.selectByExample(new StudentExample());
  }
  public void add(Student student) {
    studentDao.insert(student);
  }
}

接口层

package com.example.controller;
import com.example.entity.Student;
import com.example.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;
/**
 * @author chaird
 * @create 2020-12-30 21:40
 */
@RestController
public class StudentController {
  @Autowired private StudentService studentService;
  @GetMapping("/getAllMaster")
  public Object getAllMaster() {
    return studentService.getAllMaster();
  }
  @GetMapping("/getAllSlave")
  public Object getAllSlave() {
    return studentService.getAllSlave();
  }
  @PostMapping("/save")
  public Object save(@RequestBody Student student) {
    studentService.add(student);
    return "success";
  }
}

启动类

package com.example;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("com.example.dao")//扫描DAO包
public class ReadwriteSepApp {
  public static void main(String[] args) {
    SpringApplication.run(ReadwriteSepApp.class, args);
  }
}

测试

查询走写库：http://localhost:8080/getAllMaster

查询走读库：http://localhost:8080/getAllSlave

增加走写库：http://localhost:8080/save

原理

注意：该原理流程图是根据我上面的代码写的

1）创建两个数据源，并放在AbstractRoutingDataSource里的一个属性map中，其中（master->dataSource1,slave->dataSource2）

2) 先执行方法的AOP的Before方法，根据方法名称getAllSlave()方法在ThreadLocal中设置为 slave

3) 执行查询的时候需要获取dataSource，获取dataSource时AbstractRoutingDataSource.determineCurrentLookupKey（）方法，拿到了步骤2）中设置slave,

4）从步骤3）中拿到了slave属性，然后在步骤1）放入到的AbstractRoutingDataSource里的map里拿到dataSource

5）从而实现了变换dataSource

总结

1)ThreadLocal竟然可以用在此处，惊呆了，我的小伙伴

2）AOP也是秀

3）XXXHolder不知道出自哪，但是我看了几篇博客，都是这种编码风格，例如DBContextHolder

SpringBoot读写分离

前提

代码