說(shuō)明

• master 主庫(kù) ---- 寫
• slave 從庫(kù) --- 讀
• Centos 7 從0到1搭建Mysql8.0.16主從

初始化SpringBoot項(xiàng)目

（過(guò)程略）

實(shí)現(xiàn)過(guò)程

1、在pom.xml中增加相關(guān)依賴

        <!-- 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屬性則會(huì)綁定失敗-->
        <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
      # 配置初始化大?。J(rèn)0）、最小、最大（默認(rèn)8）
      initial-size: 1
      min-idle: 1
      max-active: 20
      # 配置獲取連接等待超時(shí)的時(shí)間
      max-wait: 60000
      # 是否緩存preparedStatement，也就是PSCache。PSCache對(duì)支持游標(biāo)的數(shù)據(jù)庫(kù)性能提升巨大。 默認(rèn)為false
      pool-prepared-statements: true
      # 要啟用PSCache，必須配置大于0，當(dāng)大于0時(shí)，poolPreparedStatements自動(dòng)觸發(fā)修改為true。
      max-open-prepared-statements: 20
      # 配置間隔多久才進(jìn)行一次檢測(cè)，檢測(cè)需要關(guān)閉的空閑連接，單位是毫秒
      time-between-eviction-runs-millis: 60000
      # 配置一個(gè)連接在池中最小和最大生存的時(shí)間，單位是毫秒
      min-evictable-idle-time-millis: 300000
      max-evictable-idle-time-millis: 900000
      # 用來(lái)檢測(cè)連接是否有效的sql，要求是一個(gè)查詢語(yǔ)句，常用select 'x'。
      # 如果validationQuery為null，testOnBorrow、testOnReturn、testWhileIdle都不會(huì)起作用。
      validation-query: SELECT 'X'
      # 申請(qǐng)連接時(shí)執(zhí)行validationQuery檢測(cè)連接是否有效 默認(rèn)為true
      test-on-borrow: true
      # 歸還連接時(shí)執(zhí)行validationQuery檢測(cè)連接是否有效 默認(rèn)為false
      test-on-return: false
      # 申請(qǐng)連接的時(shí)候檢測(cè)，如果空閑時(shí)間大于timeBetweenEvictionRunsMillis，執(zhí)行validationQuery檢測(cè)連接是否有效。
      test-while-idle: true

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

3、定義數(shù)據(jù)源枚舉類

package com.captain.readwriteseparation.dbconfig;

/**
 * @author captain
 * @description 數(shù)據(jù)源枚舉
 * @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、設(shè)置獲取數(shù)據(jù)源

package com.captain.readwriteseparation.dbconfig;

/**
 * @author captain
 * @description 設(shè)置獲取數(shù)據(jù)源
 * @date 2019-12-23 14:59
 */
public class DataSourceHolder {
    private static final ThreadLocal contextHolder = new ThreadLocal<>();

    /**
     * 設(shè)置數(shù)據(jù)源
     *
     * @param dbTypeEnum
     */
    public static void setDbType(DataSourceTypeEnum dbTypeEnum) {
        contextHolder.set(dbTypeEnum.getValue());
    }

    /**
     * 取得當(dāng)前數(shù)據(jù)源
     *
     * @return
     */
    public static String getDbType() {
        return (String) contextHolder.get();
    }

    /**
     * 清除上下文數(shù)據(jù)
     */
    public static void clearDbType() {
        contextHolder.remove();
    }
}

5、數(shù)據(jù)源切換(切入點(diǎn)和切面)

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 數(shù)據(jù)源切換(切入點(diǎn)和切面)
 * @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、動(dòng)態(tài)數(shù)據(jù)源決策

package com.captain.readwriteseparation.dbconfig;

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

/**
 * @author captain
 * @description 動(dòng)態(tài)數(shù)據(jù)源決策
 * @date 2019-12-23 16:58
 */
public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return  DataSourceHolder.getDbType();
    }
}

7、數(shù)據(jù)庫(kù)（源）配置

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 數(shù)據(jù)庫(kù)（源）配置
 * @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<>();
        //設(shè)置servlet初始化參數(shù)
        initParams.put("loginUsername", "admin");//登陸名
        initParams.put("loginPassword", "123456");//密碼
        initParams.put("allow", "");//默認(rèn)就是允許所有訪問
        initParams.put("deny", "192.168.10.17");//拒絕相對(duì)應(yīng)的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 多個(gè)時(shí)，可進(jìn)行負(fù)載（另行處理）

    @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、配置事務(wù)管理

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 事務(wù)控制
 * @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;

    /**
     * 自定義事務(wù)
     * MyBatis自動(dòng)參與到spring事務(wù)管理中，無(wú)需額外配置，只要org.mybatis.spring.SqlSessionFactoryBean引用的數(shù)據(jù)源與DataSourceTransactionManager引用的數(shù)據(jù)源一致即可，否則事務(wù)管理會(huì)不起作用。
     *
     * @return
     */
    @Bean(name = "transactionManager")
    public org.springframework.jdbc.datasource.DataSourceTransactionManager transactionManagers() {
        logger.info("-------------------- transactionManager init ---------------------");
        return new org.springframework.jdbc.datasource.DataSourceTransactionManager(dataSource);
    }
}

測(cè)試

• 啟動(dòng)項(xiàng)目

  
  
  項(xiàng)目啟動(dòng)

• maste 與slave數(shù)據(jù)

  
  
  請(qǐng)從數(shù)據(jù)庫(kù)

• 查（read）

  
  
  讀
• 寫（write）
  
  寫
  
  
  主從數(shù)據(jù)
  
  
  
  
  聲明：原創(chuàng)，歡迎轉(zhuǎn)載~！ 記得點(diǎn)個(gè)關(guān)注我哦~！