Spring Boot + Mybatis 多数据源配置实现读写分离

Spring Boot + Mybatis 多数据源配置实现读写分离
应用场景：项目中有一些报表统计与查询功能，对数据实时性要求不高，因此考虑对报表的统计与查询去操作slave db，减少对master的压力。
根据网上多份资料测试发现总是使用master数据源，无法切换到slave，经过多次调试修改现已完美通过，现整理下详细步骤和完整代码如下：
实现方式：配置多个数据源，使用Spring AOP实现拦截注解实现数据源的动态切换。

1. application.yml数据库配置：
   spring:
   tomcat:
   max-threads: 1000
   accept-count: 10000
   datasource:
   druid:
   type: com.alibaba.druid.pool.DruidDataSource
   master:
   driver-class-name: com.mysql.cj.jdbc.Driver
   initialSize: 10
   minIdle: 1
   maxActive: 1000
   maxWait: 60000
   timeBetweenEvictionRunsMillis: 60000
   minEvictableIdleTimeMillis: 30000
   validationQuery: SELECT 1 FROM DUAL
   testWhileIdle: true
   testOnBorrow: false
   #exceptionSorter: true
   testOnReturn: false
   poolPreparedStatements: true
   filter: stat,wall,log4j
   maxPoolPreparedStatementPerConnectionSize: 20
   connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
   useGlobalDataSourceStat: true
   url: jdbc:mysql://你的数据库地址:3306/你的库名?useUnicode=true&characterEncoding=utf-8&useSSL=false
   username: root
   password: ********
   slave:
   driver-class-name: com.mysql.cj.jdbc.Driver
   initialSize: 10
   minIdle: 1
   maxActive: 1000
   maxWait: 60000
   timeBetweenEvictionRunsMillis: 60000
   minEvictableIdleTimeMillis: 30000
   validationQuery: SELECT 1 FROM DUAL
   testWhileIdle: true
   testOnBorrow: false
   #exceptionSorter: true
   testOnReturn: false
   poolPreparedStatements: true
   filter: stat,wall,log4j
   maxPoolPreparedStatementPerConnectionSize: 20
   connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
   useGlobalDataSourceStat: true
   url: jdbc:mysql://你的数据库地址:3306/库名?useUnicode=true&characterEncoding=utf-8&useSSL=false
   username: root
   password: *******

Mybatis

mybatis:
typeAliasesPackage: com.sxchain.notary.persistence
mapperLocations: classpath:mapper/*.xml
configuration:
map-underscore-to-camel-case: true

server:
port: 8083

打印sql

logging:
level:
root: info #日志配置DEBUG,INFO,WARN,ERROR
com.sxchain.notary.persistence : debug
path: /var/logs
2. 实现多数据源注入:
package com.sxchain.notary.config;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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{
private static Logger log = LoggerFactory.getLogger(DataSourceConfiguration.class);
@Value("$druid.type")privateClass<?extendsDataSource>dataSourceType;@Value("${slave.initialSize}")
private String master;
@Bean(name = “masterDataSource”)
@ConfigurationProperties(prefix = “master”)
public DataSource masterDataSource() {
// log.info(master);
return DataSourceBuilder.create().type(dataSourceType).build();
}

@Bean(name = "slaveDataSource")
@ConfigurationProperties(prefix = "slave")
public DataSource slaveDataSource() {
    return DataSourceBuilder.create().type(dataSourceType).build();
}
@Primary
@Bean(name = "dataSource")
public AbstractRoutingDataSource dataSource() {
    MasterSlaveRoutingDataSource proxy = new MasterSlaveRoutingDataSource();
    Map<Object, Object> targetDataResources = new HashMap<>();
    targetDataResources.put(DbContextHolder.DbType.MASTER, masterDataSource());
    targetDataResources.put(DbContextHolder.DbType.SLAVE, slaveDataSource());
    proxy.setDefaultTargetDataSource(masterDataSource());
    proxy.setTargetDataSources(targetDataResources);
  //  proxy.afterPropertiesSet();
    return proxy;
}

}

3. 基于 AbstractRoutingDataSource 和 AOP 的多数据源的配置
   我们自己定义一个DataSource类，来继承 AbstractRoutingDataSource：
   package com.sxchain.notary.config;
   import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class MasterSlaveRoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DbContextHolder.getDbType();
}
}

这里通过determineCurrentLookupKey（）返回的不同key到sqlSessionFactory中获取对应数据源然后使用ThreadLocal来存放线程的变量，将不同的数据源标识记录在ThreadLocal中

package com.sxchain.notary.config;

public class DbContextHolder {

public enum DbType {
    MASTER, SLAVE
}

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

public static void setDbType(DbType dbType) {
    if (dbType == null) {
        throw new NullPointerException();
    }
    contextHolder.set(dbType);
}

public static DbType getDbType() {
    return contextHolder.get() == null ? DbType.MASTER : contextHolder.get();
}

public static void clearDbType() {
    contextHolder.remove();
}

}

4. 注解实现
   package com.sxchain.notary.config;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

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

}

通过切面实现方法
package com.sxchain.notary.config;

import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.springframework.core.Ordered;
import org.springframework.stereotype.Component;
@Aspect
@Component
public class ReadOnlyConnectionInterceptor implements Ordered {
@Around("@annotation(readOnlyConnection)")
public Object proceed(ProceedingJoinPoint proceedingJoinPoint, ReadOnlyConnection readOnlyConnection) throws Throwable {
try {
DbContextHolder.setDbType(DbContextHolder.DbType.SLAVE);
Object result = proceedingJoinPoint.proceed();
return result;
} finally {
DbContextHolder.clearDbType();
}
}

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

}

5. 应用方式：
   service层接口增加ReadOnlyConnection注解即可：
   @ReadOnlyConnectionpublic CommonPagingVO
   pagingByCondition(GroupGoodsCondition condition, int pageNum, int pageSize)
   {
   Page
   page = PageHelper.startPage(pageNum, pageSize).doSelectPage(()
   -> groupGoodsMapper.listByCondition(condition));
   return CommonPagingVO.get(page,page.getResult());
   }
   对于未加ReadOnlyConnection注解的默认使用masterDataSource。