SpringBoot2整合MyBatis实现读写分离

一、CentOS7搭建MariaDB主从环境

        CentOS7搭建MariaDB主从

二、新建SpringBoot项目

目录结构如下：

三、pom.xml文件如下

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.0.RELEASE</version>
        <relativePath/>
    </parent>
    <groupId>com.nifujia</groupId>
    <artifactId>data</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>data</name>
    <description>SpringBoot2+Druid+MyBatis+读写分离</description>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

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

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.11</version>
        </dependency>

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

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

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

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <optional>true</optional>
        </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>

    <build>
        <finalName>data</finalName>
        <!-- 打包时包含properties、xml -->
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <!-- 是否替换资源中的属性-->
                <filtering>true</filtering>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
            </resource>
        </resources>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-war-plugin</artifactId>
            </plugin>
            <!-- 解解决maven update project 后版本降低为1.5的bug -->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
            <!-- 单元测试 -->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-surefire-plugin</artifactId>
                <configuration>
                    <skip>true</skip>
                    <includes>
                        <include>**/*Test*.java</include>
                    </includes>
                    <testFailureIgnore>true</testFailureIgnore>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

四、application.yml配置

server:
  port: 8080
  tomcat:
    uri-encoding: UTF-8
  servlet:
    context-path: /data

spring:
  http:
    encoding:
      charset: UTF-8
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss
    time-zone: GMT+8
    default-property-inclusion: NON_NULL

druid:
  type: com.alibaba.druid.pool.DruidDataSource
  master:
    url: jdbc:mysql://192.168.174.129:3306/spring?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true&serverTimezone=UTC
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: mysql
    initialSize: 5
    minIdle: 1
    #maxIdle: 10
    maxActive: 100
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxPoolPreparedStatementPerConnectionSize: 20
    filters: stat,wall
    useGlobalDataSourceStat: true
  slave:
    url: jdbc:mysql://192.168.174.135:3306/spring?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true&serverTimezone=UTC
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: mysql
    initialSize: 5
    minIdle: 1
    #maxIdle: 10
    maxActive: 100
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxPoolPreparedStatementPerConnectionSize: 20
    filters: stat,wall
    useGlobalDataSourceStat: true

mybatis:
  type-aliases-package: com.nifujia.model
  mapper-locations: classpath:com/nifujia/mapper/*.xml

logging:
  level:
    tk.mybatis: TRACE

五、定义主从数据源，注入到Spring容器中，使用ThreadLocal来避免并发情况下切换数据源出错，自定义AbstractRoutingDataSource的子类实现数据源动态切换。

mybatis提供了一个类叫AbstractRoutingDataSource，重写determineCurrentLookupKey()方法，达到动态切换数据源的目的。

DataSourceConfiguration.java

@Configuration
@Slf4j
public class DataSourceConfiguration {


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


    @Bean(name = "masterDataSource")
    @Primary
    @ConfigurationProperties(prefix = "druid.master")
    public DataSource masterDataSource() {
        DataSource masterDataSource = DataSourceBuilder.create().type(dataSourceType).build();
        log.info("=====================MASTER======================" + masterDataSource);
        return masterDataSource;
    }

    @Bean(name = "slaveDataSource")
    @ConfigurationProperties(prefix = "druid.slave")
    public DataSource slaveDataSource() {
        DataSource slaveDataSource = DataSourceBuilder.create().type(dataSourceType).build();
        log.info("=====================SLAVE======================" + slaveDataSource);
        return slaveDataSource;
    }


    @Bean
    public DataSource routingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
                                        @Qualifier("slaveDataSource") DataSource slaveDataSource) {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DataBaseContextHolder.DataBaseType.MASTER, masterDataSource);
        targetDataSources.put(DataBaseContextHolder.DataBaseType.SLAVE, slaveDataSource);
        ReadWriteSplitRoutingDataSource routingDataSource = new ReadWriteSplitRoutingDataSource();
        routingDataSource.setDefaultTargetDataSource(masterDataSource);
        routingDataSource.setTargetDataSources(targetDataSources);
        return routingDataSource;
    }

    /**
     * druid监控台配置
     */
    @Bean
    public ServletRegistrationBean druidServlet() {

        ServletRegistrationBean reg = new ServletRegistrationBean();
        reg.setServlet(new StatViewServlet());
        reg.addUrlMappings("/druid/*");
        reg.addInitParameter("allow", "localhost");
        reg.addInitParameter("deny", "/deny");
        log.info(" druid console manager init : {} ", reg);
        return reg;
    }

    /**
     * filter配置
     */
    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico, /druid/*");
        log.info(" druid filter register : {} ", filterRegistrationBean);
        return filterRegistrationBean;
    }

}

DataBaseContextHolder.java

public enum DataBaseType {
        MASTER, SLAVE
    }

    private static final ThreadLocal<DataBaseType> context = new ThreadLocal<>();

    public static void setDataBaseType(DataBaseType dataBaseType) {
        if (dataBaseType == null) {
            throw new NullPointerException();
        }
        context.set(dataBaseType);
    }

    public static DataBaseType getDataBaseType() {
        return context.get() == null ? DataBaseType.MASTER : context.get();
    }

    public static void clearDataBaseType() {
        context.remove();
    }

 

public class ReadWriteSplitRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DataBaseContextHolder.getDataBaseType();
    }
}

六、自定义标识，标识什么时候切换数据源（这里使用自定义注解）

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

七、AOP拦截，当使用@ReadOnlyAnnotation 时，动态切换至从数据源

@Aspect
@Slf4j
public class ReadOnlyAnnotationInterceptor implements Ordered {

    @Around("@annotation(readOnlyAnnotation)")
    public Object proceed(ProceedingJoinPoint joinPoint, ReadOnlyAnnotation readOnlyAnnotation) throws Throwable {

        try {
            log.info("==================开始切换数据源为只读，从数据源==================");
            DataBaseContextHolder.setDataBaseType(DataBaseContextHolder.DataBaseType.SLAVE);
            Object proceed = joinPoint.proceed();
            return proceed;

        } finally {
            log.info("=================切换为主数据源=========================");
            DataBaseContextHolder.clearDataBaseType();
        }
    }

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

八、验证

主从数据库都开启日志，查看日志文件查询哪个库，之后，关闭日志。

#查看是否开启日志记录
SHOW VARIABLES WHERE Variable_name="general_log";
#显示为OFF为关闭
#临时开启日志记录，重启服务器之后失效
SET GLOBAL general_log=ON;
#查看日志的名称
SHOW VARIABLES WHERE Variable_name="general_log_file";

#登录MariaDB服务器，查看日志位置
find / --name "日志名"

tail -f /var/lib/mysql/localhost.log


#主从服务器，都关闭日志
SET GLOBAL general_log=OFF;