springboot+mybatis/mybatis-plus拦截器实现多数据源切换
凤修筠
1、数据源配置
spring.datasource.master.driver-class-name = com.mysql.jdbc.Driver
spring.datasource.master.filters = wall,stat
spring.datasource.master.initial-size = 50
spring.datasource.master.max-active = 100
spring.datasource.master.max-open-prepared-statements = 100
spring.datasource.master.max-wait = 60000
spring.datasource.master.min-evictable-idle-time-millis = 300000
spring.datasource.master.min-idle = 50
spring.datasource.master.password = 8ZDs8U26rcU1v7zI
spring.datasource.master.pool-prepared-statements = true
spring.datasource.master.test-on-borrow = false
spring.datasource.master.test-on-return = false
spring.datasource.master.test-while-idle = true
spring.datasource.master.time-between-eviction-runs-millis = 60000
spring.datasource.master.jdbc-url = jdbc:mysql://rm-bp119f05ug9z24073782.mysql.rds.aliyuncs.com:3306/operation_porsche?allowMultiQueries=true&useUnicode=true&autoReconnect=true&rewriteBatchedStatements=TRUE&serverTimezone=Asia/Shanghai
spring.datasource.master.username = porsche_rw
spring.datasource.master.type = com.zaxxer.hikari.HikariDataSource
spring.datasource.master.hikari.register-mbeans = true
spring.datasource.master.hikari.pool-name = HikariConnectionPool
spring.datasource.master.hikari.minimum-idle = 50
spring.datasource.master.hikari.maximum-pool-size = 100
spring.datasource.master.hikari.connection-timeout = 60000
spring.datasource.master.hikari.idle-timeout = 30000
spring.datasource.master.hikari.connection-test-query = select 'x'
spring.datasource.master.hikari.max-lifetime = 1800000
spring.datasource.master.hikari.connection-init-sql = SET NAMES utf8mb4
spring.datasource.slave.driver-class-name = com.mysql.jdbc.Driver
spring.datasource.slave.filters = wall,stat
spring.datasource.slave.initial-size = 50
spring.datasource.slave.max-active = 100
spring.datasource.slave.max-open-prepared-statements = 100
spring.datasource.slave.max-wait = 60000
spring.datasource.slave.min-evictable-idle-time-millis = 300000
spring.datasource.slave.min-idle = 50
spring.datasource.slave.password = root
spring.datasource.slave.pool-prepared-statements = true
spring.datasource.slave.test-on-borrow = false
spring.datasource.slave.test-on-return = false
spring.datasource.slave.test-while-idle = true
spring.datasource.slave.time-between-eviction-runs-millis = 60000
spring.datasource.slave.jdbc-url = jdbc:mysql://localhost:3306/operation_porsche?allowMultiQueries=true&useUnicode=true&autoReconnect=true&rewriteBatchedStatements=TRUE&serverTimezone=Asia/Shanghai
spring.datasource.slave.username = root
spring.datasource.slave.type = com.zaxxer.hikari.HikariDataSource
spring.datasource.slave.hikari.register-mbeans = true
spring.datasource.slave.hikari.pool-name = HikariConnectionPool
spring.datasource.slave.hikari.minimum-idle = 50
spring.datasource.slave.hikari.maximum-pool-size = 100
spring.datasource.slave.hikari.connection-timeout = 60000
spring.datasource.slave.hikari.idle-timeout = 30000
spring.datasource.slave.hikari.connection-test-query = select 'x'
spring.datasource.slave.hikari.max-lifetime = 1800000
spring.datasource.slave.hikari.connection-init-sql = SET NAMES utf8mb42、DataSourceConfig：
mybatis：
package com.zhangmen.operation.porsche.relation.config;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Qualifier;
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 com.zhangmen.operation.porsche.relation.common.enums.DBTypeEnum;
import com.zhangmen.operation.porsche.relation.mybatis.MyRoutingDataSource;
/**
* @Author zhiyong.zhou
* @Date 2020/8/1
* @Description
*/
@Configuration
public class DataSourceConfig {
/**
* 写库
* @return
*/
@Bean
@Primary
@ConfigurationProperties("spring.datasource.master")
public DataSource masterDataSource() {
return DataSourceBuilder.create().build();
}
/**
* 读库
* @return
*/
@Bean
@ConfigurationProperties("spring.datasource.slave")
public DataSource slaveDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name="routeDataSource")
public DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
@Qualifier("slaveDataSource") DataSource slaveDataSource) {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DBTypeEnum.MASTER, masterDataSource);
targetDataSources.put(DBTypeEnum.SLAVE, slaveDataSource);
MyRoutingDataSource myRoutingDataSource = new MyRoutingDataSource();
myRoutingDataSource.setTargetDataSources(targetDataSources);
return myRoutingDataSource;
}
}
mybatis-plus:
package com.zhangmen.operation.porsche.relation.config;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Qualifier;
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.DataSourceTransactionManager;
import com.zhangmen.operation.porsche.relation.common.enums.DBTypeEnum;
import com.zhangmen.operation.porsche.relation.mybatis.MyRoutingDataSource;
/**
* @Author zhiyong.zhou
* @Date 2020/8/1
* @Description
*/
@Configuration
public class DataSourceConfig {
/**
* 写库
* @return
*/
@Bean
@Primary
@ConfigurationProperties("spring.datasource.master")
public DataSource masterDataSource() {
return DataSourceBuilder.create().build();
}
/**
* 读库
* @return
*/
@Bean
@ConfigurationProperties("spring.datasource.slave")
public DataSource slaveDataSource() {
return DataSourceBuilder.create().build();
}
/**
*
* @Title: multipleTransactionManager
* @Description: 配置事务管理器
* @param dataSource
* @return
* @throws
*/
@Bean(name = "multipleTransactionManager")
@Primary
public DataSourceTransactionManager multipleTransactionManager(
@Qualifier("routeDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name="routeDataSource")
public DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
@Qualifier("slaveDataSource") DataSource slaveDataSource) {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DBTypeEnum.MASTER, masterDataSource);
targetDataSources.put(DBTypeEnum.SLAVE, slaveDataSource);
MyRoutingDataSource myRoutingDataSource = new MyRoutingDataSource();
myRoutingDataSource.setTargetDataSources(targetDataSources);
myRoutingDataSource.setDefaultTargetDataSource(masterDataSource);
return myRoutingDataSource;
}
}3、MybatisConfig：
mybatis:
package com.zhangmen.operation.porsche.relation.config;
import javax.sql.DataSource;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.zhangmen.operation.porsche.relation.mybatis.MybatisPluginInterceptor;
/**
* @Author zhiyong.zhou
* @Date 2020/8/1
* @Description
*/
@EnableTransactionManagement
@Configuration
public class MybatisConfig {
@Bean
public PaginationInterceptor paginationInterceptor() {
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
return paginationInterceptor;
}
@Bean(name="sqlSessionFactory")
@ConditionalOnMissingBean
public SqlSessionFactory sqlSessionFactory(@Qualifier("routeDataSource") DataSource routeDataSource) throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean=new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(routeDataSource);
sqlSessionFactoryBean.setPlugins(new Interceptor[] {new MybatisPluginInterceptor()});
sqlSessionFactoryBean
.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/*.xml"));
//设置驼峰式映射
org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
configuration.setMapUnderscoreToCamelCase(true);
sqlSessionFactoryBean.setConfiguration(configuration);
return sqlSessionFactoryBean.getObject();
}
}
mybatis-plus:
package com.zhangmen.operation.porsche.relation.config;
import javax.sql.DataSource;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.zhangmen.operation.porsche.relation.mybatis.MybatisPluginInterceptor;
/**
* @Author zhiyong.zhou
* @Date 2020/8/1
* @Description
*/
@EnableTransactionManagement
@Configuration
public class MybatisConfig {
@Bean
public PaginationInterceptor paginationInterceptor() {
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
// 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求 默认false
paginationInterceptor.setOverflow(true);
return paginationInterceptor;
}
@Bean(name="sqlSessionFactory")
@ConditionalOnMissingBean
public SqlSessionFactory sqlSessionFactory(@Qualifier("routeDataSource") DataSource routeDataSource) throws Exception {
MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
sqlSessionFactory.setDataSource(routeDataSource);
sqlSessionFactory.setTypeAliasesPackage("com.zhangmen.operation.porsche.relation.entity");
/**
* 设置扫描路径
*/
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
Resource[] resource = resolver.getResources("classpath:mapper/*.xml");
sqlSessionFactory.setMapperLocations(resource);
/**
* mybatis-plus yml 配置不生效，要在这里代码里配置
*/
MybatisConfiguration configuration = new MybatisConfiguration();
configuration.setJdbcTypeForNull(JdbcType.NULL);
//是否使用转驼峰
configuration.setMapUnderscoreToCamelCase(true);
configuration.setCacheEnabled(false);
sqlSessionFactory.setConfiguration(configuration);
//添加分页功能
Interceptor[] plugins = {paginationInterceptor(),new MybatisPluginInterceptor()};
sqlSessionFactory.setPlugins(plugins);
return sqlSessionFactory.getObject();
}
}4、DBContextHolder：
package com.zhangmen.operation.porsche.relation.mybatis;
import com.zhangmen.operation.porsche.relation.common.enums.DBTypeEnum;
import lombok.extern.slf4j.Slf4j;
/**
*
* @ClassName: DBContextHolder
* @Description: 设置线程
* @author dingjy
* @date 2020年12月12日 上午11:06:21
*/
@Slf4j
public class DBContextHolder {
private static final ThreadLocal<DBTypeEnum> contextHolder = new ThreadLocal<>();
public static void set(DBTypeEnum dbType) {
contextHolder.set(dbType);
//log.warn("set thread:{},dbType:{}",Thread.currentThread().getId(),dbType);
}
public static DBTypeEnum get() {
DBTypeEnum dbType=contextHolder.get();
//log.warn("get thread:{},dbType:{}",Thread.currentThread().getId(),dbType);
return dbType;
}
public static void master() {
remove();
set(DBTypeEnum.MASTER);
}
public static void slave() {
remove();
set(DBTypeEnum.SLAVE);
}
public static void remove() {
//log.warn("remove thread:{},dbType:{}",Thread.currentThread().getId(),contextHolder.get());
contextHolder.remove();
}
}5、MybatisPluginInterceptor：
package com.zhangmen.operation.porsche.relation.mybatis;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.stereotype.Component;
import org.springframework.transaction.support.TransactionSynchronizationManager;
/**
*
* @ClassName: MyatisPluginInterceptor
* @Description: mybatis拦截器
* @author dingjy
* @date 2020年12月12日 下午6:58:17
*/
@Component
@Intercepts({ @Signature(type = Executor.class, method = "update", args = { MappedStatement.class, Object.class }),
@Signature(type = Executor.class, method = "query", args = { MappedStatement.class, Object.class,
RowBounds.class, ResultHandler.class }),
@Signature(type = Executor.class, method = "query", args = { MappedStatement.class, Object.class,
RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class }), })
public class MybatisPluginInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
boolean synchronizationActive = TransactionSynchronizationManager.isSynchronizationActive();
Object[] objects = invocation.getArgs();
MappedStatement ms = (MappedStatement) objects[0];
if (!synchronizationActive) {
if (ms.getSqlCommandType().equals(SqlCommandType.SELECT)) {
DBContextHolder.slave();
}else {
DBContextHolder.master();
}
} else {
DBContextHolder.master();
}
return invocation.proceed();
}
}6、MyRoutingDataSource：
package com.zhangmen.operation.porsche.relation.mybatis;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
/**
*
* @ClassName: MyRoutingDataSource
* @Description: 路由key
* @author dingjy
* @date 2020年12月12日 上午11:05:08
*/
@Slf4j
public class MyRoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
Object obj=DBContextHolder.get();
log.info("MyRoutingDataSource obj:{}",JSONObject.toJSONString(obj));
return obj;
}
}注意：
当有先查询，后变更的操作时：
a、当变更操作上不存在事务时，用原生的mybatis xml变更操作没问题
b、当变更操作上存在事务时，去掉事务即可
原因：http://t.zoukankan.com/yjmyzz-p-7390331.html
大致是因为开启事务时，会从缓存中获取上一次的数据源，就算当前切换了数据源也没用
类似资料：