druid+mybatis 多数据源及读写分离的处理

现如今复杂的业务系统，一般会将数据库按业务拆开，比如产品系统的数据库放在product db中，订单系统的数据库放在order db中...，然后，如果量大了，可能每个库还要考虑做读、写分离，以进一步提高系统性能，下面就来看看如何处理：

核心思路：配置多个数据源，然后利用RoutingDataSource结合AOP来动态切不同的库。

 存在一下几个问题：

  1、配置文件中，多数据源的配置节点如何设计？

#yml文件中进行如下配置
druid:
    type: com.alibaba.druid.pool.DruidDataSource
    study:
      master: #study库的主库名
        url: jdbc:mysql://localhost:3306/study?useSSL=false&characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: 1234 #加密很好
        initial-size: 5
        min-idle: 1
        max-active: 20
        test-on-borrow: true
      slave: #study库的从库名
        url: jdbc:mysql://localhost:3306/study_slave?useSSL=false&characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: A1b2c3@def.com
        initial-size: 5
        min-idle: 1
        max-active: 20
        test-on-borrow: true
    product:
      master: #product库的主库名
        url: jdbc:mysql://localhost:3306/product?useSSL=false&characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: 1234 #加密
        initial-size: 5
        min-idle: 1
        max-active: 20
        test-on-borrow: true
      slave: #product库的从库
        url: jdbc:mysql://localhost:3306/product_slave?useSSL=false&characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: 1234
        initial-size: 5
        min-idle: 1
        max-active: 20
        test-on-borrow: true

上面的配置写法供参数，如果slave节点数要扩展，按这个格式，改造成slave1,slave2... 自行扩展。

package com.aspire.sh.db.config;

import com.aspire.sh.db.datasource.DbContextHolder;
import com.aspire.sh.db.datasource.MasterSlaveRoutingDataSource;
import org.springframework.beans.factory.annotation.Value;
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 org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;


@Configuration
@EnableTransactionManagement
public class DataSourceConfiguration {

    @Value("${druid.type}")
    private Class<? extends DataSource> dataSourceType;

    @Bean(name = "studyMasterDataSource")
    @ConfigurationProperties(prefix = "druid.study.master")
    public DataSource studyMasterDataSource() {
        return DataSourceBuilder.create().type(dataSourceType).build();
    }

    @Bean(name = "studySlaveDataSource")
    @ConfigurationProperties(prefix = "druid.study.slave")
    public DataSource studySlaveDataSource1() {
        return DataSourceBuilder.create().type(dataSourceType).build();
    }

    @Bean(name = "productMasterDataSource")
    @ConfigurationProperties(prefix = "druid.product.master")
    public DataSource productMasterDataSource() {
        return DataSourceBuilder.create().type(dataSourceType).build();
    }

    @Bean(name = "productSlaveDataSource")
    @ConfigurationProperties(prefix = "druid.product.slave")
    public DataSource productSlaveDataSource1() {
        return DataSourceBuilder.create().type(dataSourceType).build();
    }

    @Bean(name = "dataSource")
    @Primary
    public AbstractRoutingDataSource dataSource() {
        MasterSlaveRoutingDataSource proxy = new MasterSlaveRoutingDataSource();
        Map<Object, Object> targetDataResources = new HashMap<>();
        targetDataResources.put(DbContextHolder.DbType.PRODUCT_MASTER, productMasterDataSource());
        targetDataResources.put(DbContextHolder.DbType.PRODUCT_SLAVE, productSlaveDataSource1());
        targetDataResources.put(DbContextHolder.DbType.STUDY_MASTER, studyMasterDataSource());
        targetDataResources.put(DbContextHolder.DbType.STUDY_SLAVE, studySlaveDataSource1());
        proxy.setDefaultTargetDataSource(productMasterDataSource());
        proxy.setTargetDataSources(targetDataResources);
        proxy.afterPropertiesSet();
        return proxy;
    }

}

注：@Primary一定要在动态数据源上，否则事务回滚无效！

3、根据什么来切换db?

a、用约定的方法前缀，比如：get/query/list开头的约定为读从库，其它为主库，但是这样还要考虑不同业务库的切换（即：何时切换到product库，何时切换到order库，可以再用不同的Scanner来处理，略复杂）

b、用自定义注解来处理，比如 @ProductMaster注解，表示切换到product的master库，这样同时把业务库，以及主还是从，一次性解决了，推荐这种。

这里，我定义了4个注解，代表product，study二个库的主及从。

4、aop在哪里拦截,如何拦截？

service层和mapper层都可以拦截，推荐在服务层拦截，否则如果一个业务方法里，即有读又有写，还得考虑如果遇到事务，要考虑的东西更多。

当然，如果拦截特定的注解，就不用过多考虑在哪个层，只认注解就行（当然，注解还是建议打在服务层上）。

dubbo-starter的一个小坑：spring boot中，只有managed bean才能用aop拦截，而dubbo-starter中的@service注解不是spring中的注解（是阿里package下的自定义注解），生成的service provider实例，aop拦截不到，解决办法，再加一个注解让spring认识它.

Aop拦截类的参考代码如下：

package com.aspire.sh.db.aspect;

import com.aspire.sh.db.annotation.ProductMaster;
import com.aspire.sh.db.annotation.ProductSlave;
import com.aspire.sh.db.annotation.StudyMaster;
import com.aspire.sh.db.annotation.StudySlave;
import com.aspire.sh.db.datasource.DbContextHolder;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.Ordered;
import org.springframework.stereotype.Component;


@Aspect
@Component 
public class MasterSlaveAspect implements Ordered {

    public static final Logger logger = LoggerFactory.getLogger(MasterSlaveAspect.class);


    /**
     * 切换到product主库
     *
     * @param proceedingJoinPoint
     * @param productMaster
     * @return
     * @throws Throwable
     */
    @Around("@annotation(productMaster)")
    public Object proceed(ProceedingJoinPoint proceedingJoinPoint, ProductMaster productMaster) throws Throwable {
        try {
            logger.info("set database connection to product-master only");
            DbContextHolder.setDbType(DbContextHolder.DbType.PRODUCT_MASTER);
            Object result = proceedingJoinPoint.proceed();
            return result;
        } finally {
            DbContextHolder.clearDbType();
            logger.info("restore database connection");
        }
    }


    /**
     * 切换到product从库
     *
     * @param proceedingJoinPoint
     * @param productSlave
     * @return
     * @throws Throwable
     */
    @Around("@annotation(productSlave)")
    public Object proceed(ProceedingJoinPoint proceedingJoinPoint, ProductSlave productSlave) throws Throwable {
        try {
            logger.info("set database connection to product-slave only");
            DbContextHolder.setDbType(DbContextHolder.DbType.PRODUCT_SLAVE);
            Object result = proceedingJoinPoint.proceed();
            return result;
        } finally {
            DbContextHolder.clearDbType();
            logger.info("restore database connection");
        }
    }

    /**
     * 切换到study主库
     *
     * @param proceedingJoinPoint
     * @param studyMaster
     * @return
     * @throws Throwable
     */
    @Around("@annotation(studyMaster)")
    public Object proceed(ProceedingJoinPoint proceedingJoinPoint, StudyMaster studyMaster) throws Throwable {
        try {
            logger.info("set database connection to study-master only");
            DbContextHolder.setDbType(DbContextHolder.DbType.STUDY_MASTER);
            Object result = proceedingJoinPoint.proceed();
            return result;
        } finally {
            DbContextHolder.clearDbType();
            logger.info("restore database connection");
        }
    }

    /**
     * 切换到study从库
     *
     * @param proceedingJoinPoint
     * @param studySlave
     * @return
     * @throws Throwable
     */
    @Around("@annotation(studySlave)")
    public Object proceed(ProceedingJoinPoint proceedingJoinPoint, StudySlave studySlave) throws Throwable {
        try {
            logger.info("set database connection to study-slave only");
            DbContextHolder.setDbType(DbContextHolder.DbType.STUDY_SLAVE);
            Object result = proceedingJoinPoint.proceed();
            return result;
        } finally {
            DbContextHolder.clearDbType();
            logger.info("restore database connection");
        }
    }

    @Override
    public int getOrder() {
        return 0;
    }
}

5、其它事项

启用类上，一定要排除spring-boot自带的datasource配置，即：

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
@EnableAspectJAutoProxy
@ComponentScan("com.aspire.sh")
@MapperScan(basePackages = "com.aspire.sh.dao.mapper")
public class ServiceProvider {
    public static void main(String[] args) {
        SpringApplication.run(ServiceProvider.class, args);
    }
}

6、日志中如何输出格式化且带参数值的sql?

是不是更友好！

方法：加一个mybtais的拦截器即可  (代码块在最前面)

这里面还用了hibernate的一个小工具，用于格式化sql(代码块在最前面)

接下来，把这个拦截器配置在mybatis-config.xml里

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="cacheEnabled" value="true"/>
    </settings>

    <plugins>
        <plugin interceptor="com.aspire.sh.db.interceptor.MybatisInterceptor">
        </plugin>
    </plugins>

</configuration>

最后在application.yml里指定mybatis-config.xml所在的路径：

注意：包路径可能会有些差异com.cnblogs.yjmyzz与com.aspire.sh二者取其一

实现方式二：

在pom.xml中增加相关依赖：

        <!-- aop -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context-support</artifactId>
        </dependency>
        <!-- mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
        </dependency>
        <!-- druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <!--如果不添加此依赖，自定义druid属性则会绑定失败-->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <!-- slf4j -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
        </dependency>

2、配置application.yml 

server:
  port: 8818
spring:
  application:
    name: read-write-separationp
  aop:
    proxy-target-class: true
    auto: true
  datasource:
    type: com.alibaba.druid.pool.DruidDataSourceC3P0Adapter
    druid:
      master:
        url: jdbc:mysql://207.148.33.32:3306/captain?useSSL=true&characterEncoding=UTF-8&serverTimezone=UTC
        username: captainLii
        password: Captain@1689
      slave:
        url: jdbc:mysql://45.32.120.84:3306/captain?useSSL=true&characterEncoding=UTF-8&serverTimezone=UTC
        username: captainLii
        password: Captain@1689
      # 配置初始化大小（默认0）、最小、最大（默认8）
      initial-size: 1
      min-idle: 1
      max-active: 20
      # 配置获取连接等待超时的时间
      max-wait: 60000
      # 是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大。 默认为false
      pool-prepared-statements: true
      # 要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。
      max-open-prepared-statements: 20
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      time-between-eviction-runs-millis: 60000
      # 配置一个连接在池中最小和最大生存的时间，单位是毫秒
      min-evictable-idle-time-millis: 300000
      max-evictable-idle-time-millis: 900000
      # 用来检测连接是否有效的sql，要求是一个查询语句，常用select 'x'。
      # 如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会起作用。
      validation-query: SELECT 'X'
      # 申请连接时执行validationQuery检测连接是否有效 默认为true
      test-on-borrow: true
      # 归还连接时执行validationQuery检测连接是否有效 默认为false
      test-on-return: false
      # 申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
      test-while-idle: true

# Mybatis
mybatis:
  mapper-locations: classpath:mapping/*.xml
  type-aliases-package: com.captain.readwriteseparation.entity

3、 定义数据源枚举类

package com.captain.readwriteseparation.dbconfig;

/**
 * @author 
 * @description 数据源枚举
 */
public enum DataSourceTypeEnum {
    master("master"), slave("slave");
    private String value;

    DataSourceTypeEnum(String value) {
        this.value = value;
    }

    public String getValue() {
        return value;
    }
}

4、设置获取数据源

package com.aspire.sh.dbconfig;

/**
 * @description 设置获取数据源
 */
public class DataSourceHolder {
    private static final ThreadLocal contextHolder = new ThreadLocal<>();

    /**
     * 设置数据源
     *
     * @param dbTypeEnum
     */
    public static void setDbType(DataSourceTypeEnum dbTypeEnum) {
        contextHolder.set(dbTypeEnum.getValue());
    }

    /**
     * 取得当前数据源
     *
     * @return
     */
    public static String getDbType() {
        return (String) contextHolder.get();
    }

    /**
     * 清除上下文数据
     */
    public static void clearDbType() {
        contextHolder.remove();
    }
}

 5、数据源切换(切入点和切面)

package com.captain.readwriteseparation.dbconfig;

import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

/**
 * @author splendor.s
 * @description 数据源切换(切入点和切面)
 */
@Aspect
@Component
public class DataSourceAop {
    static Logger logger = LoggerFactory.getLogger(DataSourceAop.class);

    @Before("execution(* com.captain.readwriteseparation.mapper.*.insert*(..)) || execution(* com.captain.readwriteseparation.mapper.*.update*(..)) || execution(* com.captain.readwriteseparation.mapper.*.delete*(..))")
    public void setWriteDataSourceType() {
        DataSourceHolder.setDbType(DataSourceTypeEnum.master);
        logger.info("change -------- write ------------");
    }

    @Before("execution(* com.captain.readwriteseparation.mapper.*.select*(..)) || execution(* com.captain.readwriteseparation.mapper.*.count*(..))")
    public void setReadDataSourceType() {
        DataSourceHolder.setDbType(DataSourceTypeEnum.slave);
        logger.info("change -------- read ------------");
    }

}

6、动态数据源决策

package com.captain.readwriteseparation.dbconfig;

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

/**
 * @authorsplendor.s
 * @description 动态数据源决策
 */
public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return  DataSourceHolder.getDbType();
    }
}

7、数据库（源）配置

package com.aspire.sh.dbconfig;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
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;

/**
 * @author splendor.s
 * @description 数据库（源）配置
 */
@Configuration
public class DruidDataSourceConfig {
    static Logger logger = LoggerFactory.getLogger(DruidDataSourceConfig.class);

    @Value("${spring.datasource.type}")
    private Class<? extends DataSource> dataSourceType;

    @Bean
    public ServletRegistrationBean staViewServlet() {
        ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
        Map<String, String> initParams = new HashMap<>();
        //设置servlet初始化参数
        initParams.put("loginUsername", "admin");//登陆名
        initParams.put("loginPassword", "123456");//密码
        initParams.put("allow", "");//默认就是允许所有访问
        initParams.put("deny", "192.168.10.17");//拒绝相对应的id访问
        //加载到容器中
        bean.setInitParameters(initParams);
        return bean;
    }

    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico," + "/druid/*");
        return filterRegistrationBean;
    }

    @Bean(name = "master")
    @ConfigurationProperties(prefix = "spring.datasource.druid.master")
    public DataSource master() {
        logger.info("-------------------- master init ---------------------");
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "slave")
    @ConfigurationProperties(prefix = "spring.datasource.druid.slave")
    public DataSource slaveOne() {
        logger.info("-------------------- slave init ---------------------");
        return DruidDataSourceBuilder.create().build();
    }

    // slave 多个时，可进行负载（另行处理）

    @Bean
    @Primary
    public DataSource multipleDataSource(@Qualifier("master") DataSource master,
                                         @Qualifier("slave") DataSource slave) {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DataSourceTypeEnum.master.getValue(), master);
        targetDataSources.put(DataSourceTypeEnum.slave.getValue(), slave);
        dynamicDataSource.setTargetDataSources(targetDataSources);
        dynamicDataSource.setDefaultTargetDataSource(slave);
        return dynamicDataSource;
    }

}

8、配置事务管理

package com.aspire.sh.dbconfig;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.sql.DataSource;
import javax.annotation.Resource;

/**
 * @author splendor.s
 * @description 事务控制
 */
@Configuration
@EnableTransactionManagement
public class DataSourceTransactionManager extends DataSourceTransactionManagerAutoConfiguration {

    static Logger logger = LoggerFactory.getLogger(DataSourceTransactionManager.class);

    @Resource(name = "master")
    private DataSource dataSource;

    /**
     * 自定义事务
     * MyBatis自动参与到spring事务管理中，无需额外配置，只要org.mybatis.spring.SqlSessionFactoryBean引用的数据源与DataSourceTransactionManager引用的数据源一致即可，否则事务管理会不起作用。
     *
     * @return
     */
    @Bean(name = "transactionManager")
    public org.springframework.jdbc.datasource.DataSourceTransactionManager transactionManagers() {
        logger.info("-------------------- transactionManager init ---------------------");
        return new org.springframework.jdbc.datasource.DataSourceTransactionManager(dataSource);
    }
}

实现方式三：

这里配置了4个数据源，1个master，2两个slave，1个路由数据源。前3个数据源都是为了生成第4个数据源，而且后续我们只用这最后一个路由数据源。

/**
 * 关于数据源配置，参考SpringBoot官方文档第79章《Data Access》
 * 79. Data Access
 * 79.1 Configure a Custom DataSource
 * 79.2 Configure Two DataSources
 * 这里配置了4个数据源，1个master，2两个slave，1个路由数据源。前3个数据源都是为了生成第4个数据源，而且后续我们只用这最后一个路由数据源。
 */

@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties("spring.datasource.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.slave1")
    public DataSource slave1DataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.slave2")
    public DataSource slave2DataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    public DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
                                          @Qualifier("slave1DataSource") DataSource slave1DataSource,
                                          @Qualifier("slave2DataSource") DataSource slave2DataSource) {
        Map<Object, Object> targetDataSources = new HashMap<>();
        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;
    }

}

配置Mybatis指定数据源

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

定义一个枚举类来代表这三个数据源

/**
 * 定义一个枚举来代表这三个数据源
 */
public enum DBTypeEnum {
    MASTER, SLAVE1, SLAVE2;
}

通过ThreadLocal将数据源绑定到每个线程上下文中

/**
 * 通过ThreadLocal将数据源设置到每个线程上下文中
 */
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() {
        //  轮询
        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");
        }
    }

}

通过Aop的前置通知来设置要使用的路由key（数据源）

/**
 * 默认情况下，所有的查询都走从库，插入/修改/删除走主库。我们通过方法名来区分操作类型（CRUD）
 *
 */
@Aspect
@Component
public class DataSourceAop {

    @Pointcut("!@annotation(huaxin.annotation.Master) " +
            "&& (execution(* huaxin.service.*.select*(..)) " +
            "|| execution(* huaxin.service..*.find*(..)))")
    public void readPointcut() {

    }

    @Pointcut("@annotation(huaxin.annotation.Master) " +
            "|| execution(* huaxin.service..*.save*(..)) " +
            "|| execution(* huaxin.service..*.add*(..)) " +
            "|| execution(* huaxin.service..*.update*(..)) " +
            "|| execution(* huaxin.service..*.edit*(..)) " +
            "|| execution(* huaxin..*.delete*(..)) " +
            "|| execution(* huaxin..*.remove*(..))")
    public void writePointcut() {

    }

    @Before("readPointcut()")
    public void read() {
        DBContextHolder.slave();
    }

    @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();
//        }
//    }
}

获取当前线程上绑定的路由key

/**
 * 获取路由key
 */
public class MyRoutingDataSource extends AbstractRoutingDataSource {
    @Nullable
    @Override
    protected Object determineCurrentLookupKey() {
        return DBContextHolder.get();
    }
}

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

public @interface Master {}

给查询所有添加@Master注解

启动入口加上@MapperScan("xx.xx")