mysql读写分离（Springboot2.1.7 + Mybatis2.1.2 + druid1.11）

采坑2天，把网上的博客和github上的代码都看了一遍终于解决了

遇到一个bug，在有些方法中增删改使用主库，查询使用从库这是正常的

但是有些方法增删改使用从库，具体原因是这些方法加了@transition注解动态优先于动态切换数据源执行，导致动态切换数据源无效

目录

1.pom.xml(给出了核心的依赖druid，Mybatis)

2.application.yml(这里面配置主从数据库和druid的配置)

3.定义类DateSourceContextHolder来存储线程动态切换的数据源

4.定义注解（可以不定义，这里只写了MasterDataSource的注解，SlaveDataSource的一样）

5.动态数据源切换类（AOP切面类）

6.动态数据源决策

7.数据源配置

1.pom.xml(给出了核心的依赖druid，Mybatis)

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-actuator</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
    
        <dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-commons</artifactId>
            <version>2.1.5.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>net.logstash.logback</groupId>
            <artifactId>logstash-logback-encoder</artifactId>
            <version>5.3</version>
        </dependency>

        <!-- 阿里云OSS -->
        <dependency>
            <groupId>com.aliyun.oss</groupId>
            <artifactId>aliyun-sdk-oss</artifactId>
            <version>2.5.0</version>
        </dependency>
        <!--集成druid连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.11</version>
        </dependency>
        <!-- MyBatis 生成器 -->
        <dependency>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-core</artifactId>
            <version>1.3.7</version>
        </dependency>
        <!-- MyBatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.2</version>
        </dependency>
        <!--Mysql数据库驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.15</version>
        </dependency>
        <!--MyBatis分页插件starter-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.10</version>
        </dependency>
        <!--Swagger-UI API文档生产工具-->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.7.0</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.7.0</version>
        </dependency>
        <!--Hutool Java工具包-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>4.5.7</version>
        </dependency>
        <!-- excel工具 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <!--消息队列依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-amqp</artifactId>
        </dependency>

        <!--SpringSecurity依赖配置-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>
        </dependency>

        <!--JWT(Json Web Token)登录支持-->
        <dependency>
            <groupId>io.jsonwebtoken</groupId>
            <artifactId>jjwt</artifactId>
            <version>0.9.0</version>
        </dependency>

        <!--netty-->
        <dependency>
            <groupId>io.netty</groupId>
            <artifactId>netty-all</artifactId>
            <version>4.1.50.Final</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!--redis依赖配置-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-redis</artifactId>
        </dependency>

        <dependency>
            <groupId>org.hibernate.validator</groupId>
            <artifactId>hibernate-validator</artifactId>
            <version>6.0.0.Final</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.72</version>
        </dependency>

        <dependency>
            <groupId>commons-logging</groupId>
            <artifactId>commons-logging</artifactId>
            <version>1.2</version>
        </dependency>

    </dependencies>

2.application.yml(这里面配置主从数据库和druid的配置)

这里只是把这些配置放在配置文件中，此时druid并不能加载数据源，需要我们手动写配置类配置，这里配置的摆放格式无所谓，到时候反正是读到属性里面。

spring:
  datasource:
    master:
      url: jdbc:mysql://192.168.0.1:3306/DB1?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
      username: root
      password: root
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.cj.jdbc.Driver
    slave:
      url: jdbc:mysql://192.168.0.2/DB2?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
      username: root
      password: root
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.cj.jdbc.Driver
    druid:
      initial-size: 10     #初始化连接池大小
      min-idle: 10         #最小大小
      max-active: 50       #最大大小
      max-wait: 60000      #获取连接时最大等待时间，单位毫秒
      time-between-eviction-runs-millis: 60000   #配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      min-evictable-idle-time-millis: 300000     #配置一个连接在池中最小生存的时间，单位是毫秒
      validation-query: SELECT 1 FROM DUAL       #用来检测连接是否有效的sql
      test-while-idle: true                      #申请连接的时候检测，建议配置为true，不影响性能，并且保证安全性
      testOnBorrow: false                        #获取连接时执行检测，建议关闭，影响性能
      testOnReturn: false                        #归还连接时执行检测，建议关闭，影响性能
      pool-prepared-statements: false            #是否开启PSCache，PSCache对支持游标的数据库性能提升巨大，oracle建议开启，mysql下建议关闭
      filters: stat,wall                   #配置扩展插件，常用的插件有=>stat:监控统计  log4j:日志  wall:防御sql注入
      connection-properties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000   #打开慢sql记录和延迟时间
      web-stat-filter:
        exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"
      stat-view-servlet:
        login-username: druid
        login-password: druid

3.定义类DateSourceContextHolder来存储线程动态切换的数据源

使用ThreadLocal来存储线程需要使用的数据源，具有线程安全性。

public class DataSourceContextHolder {

    private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();

    /**
     * @param dataSourceType 数据库类型
     * @Description: 设置数据源类型
     */
    public static void setDataSourceType(String dataSourceType) {
        if(!StrUtil.isEmpty(dataSourceType)) {
            contextHolder.set(dataSourceType);
        }else{
            contextHolder.set("master");
        }
    }

    /**
     * @Description: 获取数据源类型
     */
    public static String getDataSourceType() {

        String dataSource = contextHolder.get();
        if(StrUtil.isEmpty(dataSource)) {
            return  "master";
        }else{
            return dataSource;
        }
    }

    /**
     * @Description: 清除数据源类型
     */
    public static void clearDataSourceType() {
        contextHolder.remove();
    }
}

4.定义注解（可以不定义，这里只写了MasterDataSource的注解，SlaveDataSource的一样）

只要在方法上标注了该注解，就表示使用主数据库，我们会在AOP切面类中扫描该注解

@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
@Documented
public @interface MasterDataSource {
}

5.动态数据源切换类（AOP切面类）

在这里遇到bug，修改居然使用的是从库，原因是方法加了@Transactional注解，动态切换无效

这里需要注意

（1）为什么要使用@order(-1)，因为在service里面用@Transactional(rollbackFor = Exception.class)的时候就已经确定了数据源，@Transactional优先于自定义的注解执行，所以再切换数据源无效，使用-1表示优先级高，先执行。

（2）这里的切点表达式不能写到Mapper层，对于不标注@Transactional的方法，写到Mapper层没影响，但是对于标注了@Transactional的方法，切点写到Mapper层，此时数据源已近确定，动态切换无效，使用应该写到Service层，并且需要在@Transactional前执行动态切换

@Aspect
@Order(-1)
@Component
public class DataSourceAspect {
    private final Logger log = LoggerFactory.getLogger(this.getClass());


    @Before("execution(* com.demo.service.*.list*(..)) "
            + " || execution(* com.demo.service.*.get*(..)) "
            + " ||  @annotation(com.demo.common.annotation.SlaveDataSource)")
    public void setReadDataSourceType() {
        DataSourceContextHolder.setDataSourceType("slave");
    }


    @Before("execution(* com.demo.service.*.update*(..)) "
            + " || execution(* com.demo.service.*.create*(..)) "
            + " || execution(* com.demo.service.*.delete*(..)) "
            + " ||  @annotation(com.demo.common.annotation.MasterDataSource)")
    public void setWriteDataSourceType() {
        DataSourceContextHolder.setDataSourceType("master");
    }
}

6.动态数据源决策

每当使用数据源时，获取当前线程的数据源类型

public class DaterminDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDataSourceType();
    }
}

7.数据源配置

这里面把application.yml的配置文件读取到属性中，配置druid和多数据源

注意这里配置数据源的类型为DruidDataSource，而不是DataSourceBuilder，因为我使用DataSourceBuilder创建的数据源是springboot默认的HikariCP连接池。

多数据源中创建了master数据源，slave数据源和两者组合的数据源，若要事务生效，DataSourceTransactionManager使用的数据源要和SqlSessionFactoryBean使用的数据源相同。

@Configuration
@EnableTransactionManagement
@MapperScan(value = {"com.histor.demo.mbg.mapper","com.histor.demo.Dao"})
public class DateSourceConfig {
    private static Logger log = LoggerFactory.getLogger(DateSourceConfig.class);

    @Value("${spring.datasource.master.url}")
    private String masterUrl;

    @Value("${spring.datasource.master.username}")
    private String masterUserName;

    @Value("${spring.datasource.master.password}")
    private String masterPassWord;

    @Value("${spring.datasource.master.driver-class-name}")
    private String masterDrive;

    @Value("${spring.datasource.slave.url}")
    private String slaveUrl;

    @Value("${spring.datasource.slave.username}")
    private String slaveUserName;

    @Value("${spring.datasource.slave.password}")
    private String slavePassWord;

    @Value("${spring.datasource.slave.driver-class-name}")
    private String slaveDrive;

    @Value("${spring.datasource.druid.initial-size}")
    private int initislSize;

    @Value("${spring.datasource.druid.min-idle}")
    private int minIdle;

    @Value("${spring.datasource.druid.max-active}")
    private int maxActive;

    @Value("${spring.datasource.druid.max-wait}")
    private Long maxWait;

    @Value("${spring.datasource.druid.time-between-eviction-runs-millis}")
    private Long timeRunMillis;

    @Value("${spring.datasource.druid.min-evictable-idle-time-millis}")
    private Long minIdleMillis;

    @Value("${spring.datasource.druid.validation-query}")
    private String validationQuery;

    @Value("${spring.datasource.druid.test-while-idle}")
    private Boolean testIdle;

    @Value("${spring.datasource.druid.testOnBorrow}")
    private Boolean testOnBorrow;

    @Value("${spring.datasource.druid.testOnReturn}")
    private Boolean testOnReturn;

    @Value("${spring.datasource.druid.pool-prepared-statements}")
    private Boolean poolState;

    @Value("${spring.datasource.druid.filters}")
    private String filters;

    @Value("${spring.datasource.druid.connection-properties}")
    private String connectionProperties;

    @Value("${spring.datasource.druid.web-stat-filter.exclusions}")
    private String exclusion;

    @Value("${spring.datasource.druid.stat-view-servlet.login-username}")
    private String loginName;

    @Value("${spring.datasource.druid.stat-view-servlet.login-password}")
    private String loginPass;

    /**
     * 配置druid的servlet
     * @return
     */
    @Bean
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean();
        servletRegistrationBean.setServlet(new StatViewServlet());
        servletRegistrationBean.addUrlMappings("/druid/*");
        // IP白名单
        //servletRegistrationBean.addInitParameter("allow", "192.168.2.25,127.0.0.1");
        // IP黑名单(共同存在时，deny优先于allow)
        //servletRegistrationBean.addInitParameter("deny", "192.168.1.100");
        //控制台管理用户
        servletRegistrationBean.addInitParameter("loginUsername", loginName);
        servletRegistrationBean.addInitParameter("loginPassword", loginPass);
        //是否能够重置数据 禁用HTML页面上的“Reset All”功能
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;
    }

    /**
     * 配置druid的过滤器
     * @return
     */
    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", exclusion);
        return filterRegistrationBean;
    }


    /**
     * 这里使用druid连接池，没有使用springboot默认的HikariCP连接池
     * @return
     */
    @Bean(name = "master")
    @Primary
    public DataSource masterDataSource(){
        DruidDataSource master = new DruidDataSource();
        master.setUrl(masterUrl);
        master.setUsername(masterUserName);
        master.setPassword(masterPassWord);
        master.setDriverClassName(masterDrive);
        setParam(master);
        return master;
    }

    @Bean(name = "slave")
    public DataSource slaveDataSource(){
        DruidDataSource slave = new DruidDataSource();
        slave.setUrl(slaveUrl);
        slave.setUsername(slaveUserName);
        slave.setPassword(slavePassWord);
        slave.setDriverClassName(slaveDrive);
        setParam(slave);
        return slave;
    }


    public void setParam(DruidDataSource param) {
        param.setInitialSize(initislSize);
        param.setMinIdle(minIdle);
        param.setMaxActive(maxActive);
        param.setMaxWait(maxWait);
        param.setTimeBetweenEvictionRunsMillis(timeRunMillis);
        param.setMinEvictableIdleTimeMillis(minIdleMillis);
        param.setValidationQuery(validationQuery);
        param.setTestWhileIdle(testIdle);
        param.setTestOnBorrow(testOnBorrow);
        param.setTestOnReturn(testOnReturn);
        param.setPoolPreparedStatements(poolState);
        try{
            param.setFilters(filters);
        }catch (SQLException e){
            e.printStackTrace();
        }
        param.setConnectionProperties(connectionProperties);

    }


    @Bean(name = "dataSource")
    public DaterminDataSource mutiDataSource(@Qualifier("master") DataSource master,
                                             @Qualifier("slave") DataSource slave) {
        DaterminDataSource dynamicDataSource = new DaterminDataSource();
        HashMap<Object, Object> hashMap = new HashMap<>();
        hashMap.put("master", master);
        hashMap.put("slave", slave);
        dynamicDataSource.setTargetDataSources(hashMap);
        dynamicDataSource.setDefaultTargetDataSource(master);
        dynamicDataSource.afterPropertiesSet();
        return dynamicDataSource;
    }


    //spring容器中有3个数据源，需要指定明确的数据源
    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(resolveMapperLocations());
        return bean.getObject();
    }

    @Bean
    public DataSourceTransactionManager platformTransactionManager(@Qualifier("dataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

   /* @Bean
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }*/



    public Resource[] resolveMapperLocations() {
        ResourcePatternResolver resourceResolver = new PathMatchingResourcePatternResolver();
        List<String> mapperLocations = new ArrayList<>();
        mapperLocations.add("classpath*:Dao/*.xml");
        mapperLocations.add("classpath*:mapper/*.xml");
        List<Resource> resources = new ArrayList();
        if (mapperLocations != null) {
            for (String mapperLocation : mapperLocations) {
                try {
                    Resource[] mappers = resourceResolver.getResources(mapperLocation);
                    resources.addAll(Arrays.asList(mappers));
                } catch (IOException e) {
                    // ignore
                }
            }
        }
        return resources.toArray(new Resource[resources.size()]);
    }


}