SpringBoot 2.X + Druid + mybatis实现MySQL读写分离（史上最强）

说明

master 主库 ---- 写
slave 从库 --- 读
Centos 7 从0到1搭建Mysql8.0.16主从

初始化SpringBoot项目

（过程略）

实现过程

1、在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 captain
 * @description 数据源枚举
 * @date 2019-12-23 14:55
 */
public enum DataSourceTypeEnum {
    master("master"), slave("slave");
    private String value;

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

    public String getValue() {
        return value;
    }
}

4、设置获取数据源

package com.captain.readwriteseparation.dbconfig;

/**
 * @author captain
 * @description 设置获取数据源
 * @date 2019-12-23 14:59
 */
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 captain
 * @description 数据源切换(切入点和切面)
 * @date 2019-12-23 15:04
 */
@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;

/**
 * @author captain
 * @description 动态数据源决策
 * @date 2019-12-23 16:58
 */
public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return  DataSourceHolder.getDbType();
    }
}

7、数据库（源）配置

package com.captain.readwriteseparation.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 captain
 * @description 数据库（源）配置
 * @date 2019-12-23 15:17
 */
@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.captain.readwriteseparation.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 captain
 * @description 事务控制
 * @date 2019-12-23 15:31
 */
@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);
    }
}

测试

启动项目

项目启动
maste 与slave数据

请从数据库
查（read）

读
写（write）

写

主从数据

$==== END ====$
声明：原创，欢迎转载~！记得点个关注我哦~！