java+++多数据源配置_SpringBoot + Mybatis 多数据源配置实现读写分离

本文来自网易云社区  作者：王超

应用场景：项目中有一些报表统计与查询功能，对数据实时性要求不高，因此考虑对报表的统计与查询去操作slave db，减少对master的压力。

根据网上多份资料测试发现总是使用master数据源，无法切换到slave，经过多次调试修改现已完美通过，现整理下详细步骤和完整代码如下：

实现方式：配置多个数据源，使用Spring AOP实现拦截注解实现数据源的动态切换。

1. application.yml数据库配置：

druid:

type: com.alibaba.druid.pool.DruidDataSource

master:

url: jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true

driver-class-name: com.mysql.jdbc.Driver

username: test

password: 123

initial-size: 5

max-active: 10

min-idle: 5

max-wait: 60000

time-between-eviction-runs-millis: 3000

min-evictable-idle-time-millis: 300000

validation-query: SELECT 'x' FROM DUAL

test-while-idle: true

test-on-borrow: true

test-on-return: false

filters: stat,wall,log4j2

slave:

url: jdbc:mysql://127.0.0.1:3307/test?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true

driver-class-name: com.mysql.jdbc.Driver

username: test

password: 123

initial-size: 5

max-active: 10

min-idle: 5

max-wait: 60000

time-between-eviction-runs-millis: 3000

min-evictable-idle-time-millis: 300000

validation-query: SELECT 'x' FROM DUAL

test-while-idle: true

test-on-borrow: true

test-on-return: false

filters: stat,wall,log4j2

2. 通过MybatisAutoConfiguration实现多数据源注入:

@Configuration

@EnableTransactionManagement

public class DataSourceConfiguration extends MybatisAutoConfiguration {

@Value("${druid.type}")

private Class extends DataSource> dataSourceType;

@Bean(name = "masterDataSource")

@Primary

@ConfigurationProperties(prefix = "druid.master")

public DataSource masterDataSource(){

return DataSourceBuilder.create().type(dataSourceType).build();

}

@Bean(name = "slaveDataSource")

@ConfigurationProperties(prefix = "druid.slave")

public DataSource slaveDataSource(){

return DataSourceBuilder.create().type(dataSourceType).build();

}

@Bean

@Override

public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {

return super.sqlSessionFactory(dataSource());

}

@Bean(name = "dataSource")

public AbstractRoutingDataSource dataSource() {

MasterSlaveRoutingDataSource proxy = new MasterSlaveRoutingDataSource();

Map 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：

public class MasterSlaveRoutingDataSource extends AbstractRoutingDataSource {

@Override

protected Object determineCurrentLookupKey() {

return DbContextHolder.getDbType();

}

}

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

public class DbContextHolder {

public enum DbType{

MASTER, SLAVE

}

private static final ThreadLocal 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. 注解实现

@Target({ElementType.METHOD, ElementType.TYPE})

@Retention(RetentionPolicy.RUNTIME)

public @interface ReadOnlyConnection {

}@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。