大概思路
在appliaction.yml中配置数据源信息,在数据源配置中读取yml中的值,将配置好的数据源放入sqlSessionFactory中,接下来需要考虑多数据源切换问题,采用如下方式,自定义一个注解,将注解放到类上(放方法上太麻烦),当使用aop拦截时出现以下问题:1、@within当方法执行时将注解放到类上是拦截不到的 2:通过execution可以拦截到但总是在切换数据源之后执行。因此放弃aop拦截的方式,改用通过实现Mybatis拦截器进行数据源切换。
1. 首先在application.yml中加入配置:
spring:
jdbc:
# mysql default
default:
type: mysql
username: root
password: root
url: jdbc:mysql://localhost:3306/zw_eq_js?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
driver-class-name: com.mysql.jdbc.Driver
# mysql 2
mysql2:
type: mysql
username: root
password: Zw55350002@
url: jdbc:mysql://localhost:3307/zw_eq_js?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
driver-class-name: com.mysql.jdbc.Driver
2. 数据源初始化:读取application.yml中数据源的配置,将所有数据源放到routingDataSource(动态数据源)中:
package com.test.config.datasource;
import com.alibaba.druid.pool.DruidDataSource;
import com.test.common.io.PropertiesUtils;
import com.test.common.lang.StringUtils;
import org.springframework.boot.jta.atomikos.AtomikosDataSourceBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.annotation.Order;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.*;
/**
* 数据源配置(支持多数据源)
*/
@Order(-1)
@Configuration
public class DataSourceConfig {
public static Map<Object,Object> targetDataSource = new HashMap<>();
// @Primary // 如没有配置则默认使用
// @Bean(name = "dataSourceOne")
// public DataSource dataSourceOne() {
// String prefix = "spring.jdbc.one.";
// return getDataSource(prefix,"dataSourceOne");
// }
//
@Bean(name = "dataSource")
public RoutingDataSource dataSource() throws SQLException {
RoutingDataSource a = new RoutingDataSource();
getDataSource(null, a);
return a;
}
public static DataSource getDataSource(String dataSourceName, RoutingDataSource routingDataSource){
PropertiesUtils _env = PropertiesUtils.getInstance();
List<String> dataSourceNames = new ArrayList<>();
if(StringUtils.isEmpty(dataSourceName)){
Properties p = _env.getProperties();
Enumeration en = p.propertyNames();
while (en.hasMoreElements()){
Object el = en.nextElement();
String elStr = String.valueOf(el);
if(el != null && StringUtils.startsWith(elStr,"spring.jdbc") ){
String[] elArr = elStr.split("\\.");
if(!dataSourceNames.contains(elArr[2])){
dataSourceNames.add(elArr[2]);
}
}
}
}else{
dataSourceNames.add(dataSourceName);
}
String preFix = "spring.jdbc.";
for(String _dataSourceName : dataSourceNames){
dataSourceName = _dataSourceName;
_dataSourceName = preFix + _dataSourceName;
Properties prop = build(_dataSourceName);
Object dataSource = null;
if("true".equalsIgnoreCase(_env.getProperty("spring.jta.enabled"))){ // 开启分布式事务
AtomikosDataSourceBean ds = new AtomikosDataSourceBean();
ds.setXaDataSourceClassName("com.alibaba.druid.pool.xa.DruidXADataSource");
ds.setUniqueResourceName(dataSourceName);
ds.setXaProperties(prop);
dataSource = ds;
} else{
DruidDataSource ds = new DruidDataSource();
ds.setName(dataSourceName);
ds.setUrl(prop.getProperty("url"));
ds.setUsername(prop.getProperty("username"));
ds.setPassword(prop.getProperty("password"));
ds.setDriverClassName(prop.getProperty("driverClassName"));
ds.setInitialSize(Integer.valueOf(prop.getProperty("initialSize")));
ds.setMaxActive(Integer.valueOf(prop.getProperty("maxActive")));
ds.setMinIdle(Integer.valueOf(prop.getProperty("minIdle")));
ds.setMaxWait(Integer.valueOf(prop.getProperty("maxWait")));
ds.setTestOnBorrow(Boolean.valueOf(prop.getProperty("testOnBorrow")));
ds.setTestOnReturn(Boolean.valueOf(prop.getProperty("testOnReturn")));
ds.setTimeBetweenEvictionRunsMillis(Long.valueOf(prop.getProperty("timeBetweenEvictionRunsMillis")));
ds.setMinEvictableIdleTimeMillis(Long.valueOf(prop.getProperty("minEvictableIdleTimeMillis")));
ds.setRemoveAbandoned(Boolean.valueOf(prop.getProperty("removeAbandoned")));
ds.setRemoveAbandonedTimeout(Integer.valueOf(prop.getProperty("removeAbandonedTimeout")));
ds.setPoolPreparedStatements(true);
ds.setMaxPoolPreparedStatementPerConnectionSize(ds.getMaxActive());
dataSource = ds;
}
// 添加动态数据源上下文
// DynamicDataSourceContextHolder.dataSourceNames.add(dataSourceName);
// 设置默认数据源
if("default".equalsIgnoreCase(dataSourceName)){
routingDataSource.setDefaultTargetDataSource(dataSource);
// DynamicDataSourceContextHolder.setDataSourceName(_dataSourceName);
}else{
targetDataSource.put(dataSourceName,dataSource);
}
}
// 注册目标数据源
routingDataSource.setTargetDataSources(targetDataSource);
routingDataSource.afterPropertiesSet();
return routingDataSource;
}
protected static Properties build(String prefix) {
Properties prop = new Properties();
PropertiesUtils _env = PropertiesUtils.getInstance();
prefix += ".";
prop.put("url", _env.getProperty(prefix + "url"));
prop.put("username", _env.getProperty(prefix + "username"));
prop.put("password", _env.getProperty(prefix + "password"));
prop.put("driverClassName", _env.getProperty(prefix + "driver-class-name", ""));
prop.put("initialSize", _env.getProperty("spring.pool.init"));
prop.put("maxActive", _env.getProperty("spring.pool.maxActive"));
prop.put("minIdle", _env.getProperty("spring.pool.minIdle"));
prop.put("maxWait", _env.getProperty("spring.pool.maxWait"));
// prop.put("poolPreparedStatements", _env.getProperty(prefix + "poolPreparedStatements"));
// prop.put("maxPoolPreparedStatementPerConnectionSize",
// _env.getProperty(prefix + "maxPoolPreparedStatementPerConnectionSize"));
// prop.put("validationQuery", _env.getProperty(prefix + "validationQuery"));
// prop.put("validationQueryTimeout", _env.getProperty(prefix + "validationQueryTimeout"));
prop.put("testOnBorrow", _env.getProperty("spring.pool.testOnBorrow"));
prop.put("testOnReturn", _env.getProperty("spring.pool.testOnReturn"));
// prop.put("testWhileIdle", _env.getProperty(prefix + "testWhileIdle"));
prop.put("timeBetweenEvictionRunsMillis", _env.getProperty("spring.pool.timeBetweenEvictionRunsMillis"));
prop.put("minEvictableIdleTimeMillis", _env.getProperty("spring.pool.minEvictableIdleTimeMillis"));
prop.put("removeAbandoned", _env.getProperty("spring.pool.removeAbandoned"));
prop.put("removeAbandonedTimeout", _env.getProperty("spring.pool.removeAbandonedTimeout"));
// prop.put("useGlobalDataSourceStat",_env.getProperty(prefix + "useGlobalDataSourceStat"));
// prop.put("filters", _env.getProperty(prefix + "filters"));
return prop;
}
}
3. 其中动态数据源的定义如下:
package com.test.config.datasource;
import com.test.common.lang.StringUtils;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import javax.sql.DataSource;
import java.util.Map;
/**
* 多数据源
*/
public class RoutingDataSource extends AbstractRoutingDataSource {
private Map<Object, Object> targetDataSources;
private Object defaultTargetDataSource;
public RoutingDataSource() {
}
@Override
public void setDefaultTargetDataSource(Object defaultTargetDataSource) {
super.setDefaultTargetDataSource(this.defaultTargetDataSource = defaultTargetDataSource);
}
@Override
protected Object determineCurrentLookupKey() {
/*
* DynamicDataSourceContextHolder代码中使用setDataSourceName
* 设置当前的数据源,在路由类中使用getDataSourceName进行获取,
* 交给AbstractRoutingDataSource进行注入使用。
*/
return DynamicDataSourceContextHolder.getDataSourceName();
}
public DataSource getTargetDataSource(String dataSourceName) {
if (!StringUtils.isBlank(dataSourceName) && !"default".equals(dataSourceName)) {
Object a;
return (a = this.targetDataSources.get(dataSourceName)) == null ? (DataSource)this.defaultTargetDataSource : (DataSource)a;
} else {
return (DataSource)this.defaultTargetDataSource;
}
}
public void removeTargetDataSource(String dataSourceName) {
this.targetDataSources.remove(dataSourceName);
super.afterPropertiesSet();
}
public void setTargetDataSources(Map<Object, Object> targetDataSources) {
super.setTargetDataSources(this.targetDataSources = targetDataSources);
}
public void addTargetDataSource(String dataSourceName, DataSource dataSource) {
this.targetDataSources.put(dataSourceName, dataSource);
super.afterPropertiesSet();
}
}
package com.test.config.datasource;
import org.apache.commons.lang3.StringUtils;
/**
* 动态数据源上下文,在Mybatis拦截器(DataSourceInterceptor)中修改当前数据源
* @author lyh
* @version v.0.1
*/
public class DynamicDataSourceContextHolder {
public static final String DEFAULT = "default";
private static final ThreadLocal<String> dataSourceNames = new ThreadLocal<>();
public static String getDataSourceName() {
return (String)dataSourceNames.get();
}
public DynamicDataSourceContextHolder() {
}
public static void setDataSourceName(String dataSourceName) {
if (!StringUtils.isBlank(dataSourceName) && !"default".equals(dataSourceName)) {
DynamicDataSourceContextHolder.dataSourceNames.set(dataSourceName);
} else {
clearDataSourceName();
}
}
public static void clearDataSourceName() {
dataSourceNames.remove();
}
}
4. 将数据源配置到sqlSessionFactory中:
package com.test.config.mybatis;
import com.test.annotation.MyBatisDao;
import com.test.base.dao.BaseEntity;
import com.test.common.io.PropertiesUtils;
import com.test.config.datasource.DataSourceInterceptor;
import org.apache.ibatis.plugin.Interceptor;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.boot.autoconfigure.SpringBootVFS;
import org.mybatis.spring.mapper.MapperScannerConfigurer;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import javax.sql.DataSource;
@Configuration
public class MyBatisConfig{
@Bean(name="sqlSessionFactory")
public SqlSessionFactoryBean sqlSessionFactory(DataSource dataSource) throws Exception {
SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
factory.setPlugins(new Interceptor[]{new DataSourceInterceptor()});
// 因springboot和mybatis整合配置文件不支持TypeAliasesSuperType(只会扫描其类下的子类)及TypeAliasesPackage的正则因此使用此配置文件进行配置
factory.setTypeAliasesSuperType(BaseEntity.class);
// 指定entity包路径
factory.setTypeAliasesPackage(PropertiesUtils.getInstance().getProperty("mybatis.typeAliasesPackage"));
// 指定xml文件位置
factory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(PropertiesUtils.getInstance().getProperty("mybatis.mapper-locations")));
factory.setDataSource(dataSource);
// 配置自定义的事务工厂
factory.setTransactionFactory(new TransactionFactory());
factory.setVfs(SpringBootVFS.class);
return factory;
}
@Bean
public static MapperScannerConfigurer mapperScannerConfigurer() {
MapperScannerConfigurer a = new MapperScannerConfigurer();
a.setSqlSessionFactoryBeanName("sqlSessionFactory");
a.setBasePackage("com.test");
a.setAnnotationClass(MyBatisDao.class);
return a;
}
}
5. 动态数据源的事务配置:
package com.test.config.mybatis;
import org.apache.ibatis.session.TransactionIsolationLevel;
import org.apache.ibatis.transaction.Transaction;
import org.mybatis.spring.transaction.SpringManagedTransactionFactory;
import javax.sql.DataSource;
/**
* 事务工厂,在创建sqlSessionFactory时注入此工厂使用自定义的事务CustomizeTransaction
*/
public class TransactionFactory extends SpringManagedTransactionFactory {
public Transaction newTransaction(DataSource dataSource, TransactionIsolationLevel level, boolean autoCommit) {
return (Transaction)(new CustomizeTransaction(dataSource));
}
public TransactionFactory() {
}
}
package com.test.config.mybatis;
import com.test.common.collect.MapUtils;
import com.test.common.lang.StringUtils;
import com.test.config.datasource.DynamicDataSourceContextHolder;
import com.test.config.datasource.RoutingDataSource;
import org.apache.ibatis.transaction.Transaction;
import org.mybatis.spring.transaction.SpringManagedTransaction;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.Map;
/**
* 自定义事务,getConnection时根据数据源上下文(当前数据源名称,没有则为默认)生成相应数据源
*
*/
public class CustomizeTransaction implements Transaction {
private Map<String, SpringManagedTransaction> A;
private RoutingDataSource K;
public void commit() throws SQLException {
Iterator ite;
for(Iterator ite1 = ite = this.A.entrySet().iterator(); ite1.hasNext(); ite1 = ite) {
((SpringManagedTransaction)((Map.Entry)ite.next()).getValue()).commit();
}
}
public void rollback() throws SQLException {
Iterator ite;
for(Iterator ite1 = ite = this.A.entrySet().iterator(); ite1.hasNext(); ite1 = ite) {
// Object obj = ite.next();
SpringManagedTransaction springManagedTransaction = (SpringManagedTransaction)((Map.Entry)ite.next()).getValue();
springManagedTransaction.rollback();
// System.out.println(obj);
// ((SpringManagedTransaction)((Map.Entry)ite.next()).getValue()).rollback();
}
}
public Connection getConnection() throws SQLException {
String dataSourceName = DynamicDataSourceContextHolder.getDataSourceName();
SpringManagedTransaction springManagedTransaction;
if (StringUtils.isEmpty(dataSourceName) || (springManagedTransaction = (SpringManagedTransaction)this.A.get(dataSourceName)) == null) {
DataSource dataSource = this.K;
if (!"default".equals(dataSourceName) && !StringUtils.isEmpty(dataSourceName)) {
dataSource = this.K.getTargetDataSource(dataSourceName);
}else{
dataSourceName = "default";
}
springManagedTransaction = new SpringManagedTransaction((DataSource)dataSource);
this.A.put(dataSourceName, springManagedTransaction);
}
return springManagedTransaction.getConnection();
}
public CustomizeTransaction(DataSource dataSource) {
this.K = (RoutingDataSource)dataSource;
this.A = MapUtils.newConcurrentMap();
}
public Integer getTimeout() throws SQLException {
String dataSourceName = DynamicDataSourceContextHolder.getDataSourceName();
if(StringUtils.isEmpty(dataSourceName)){
dataSourceName = "default";
}
SpringManagedTransaction springManagedTransaction;
return (springManagedTransaction = (SpringManagedTransaction)this.A.get(dataSourceName)) != null ? springManagedTransaction.getTimeout() : null;
}
public void close() throws SQLException {
Iterator ite;
for(Iterator ite1 = ite = this.A.entrySet().iterator(); ite1.hasNext(); ite1 = ite) {
((SpringManagedTransaction)((Map.Entry)ite.next()).getValue()).close();
}
this.A.clear();
}
}
6. 配置数据源管理器:
package com.test.config.mybatis;
import com.atomikos.icatch.jta.UserTransactionImp;
import com.atomikos.icatch.jta.UserTransactionManager;
import com.test.config.datasource.RoutingDataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.DependsOn;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.transaction.jta.JtaTransactionManager;
import javax.sql.DataSource;
import javax.transaction.TransactionManager;
import javax.transaction.UserTransaction;
/**
* 配置事务管理器(一版事务管理器(DataSourceTransactionManager)及分布式的事务管理器(atomikosTransactionManager))
* 配置多数据源后,使用非默认数据源时事务失效
* 原因:jeesite只配置了一个事务管理器,动态切换数据源后使用的还是之前默认的事务管理器,因此失效。
* 解决方案:针对每个数据源配置其事务管理器,在@Transactional注解后标注自己要使用的事务管理器,但只针对每个数据源内部事务,跨数据源还是要使用jta分布式事务管理
*/
@Configuration
@EnableTransactionManagement
public class TransactionConfig {
@Bean(
initMethod = "init",
destroyMethod = "close"
)
@ConditionalOnProperty(
name = {"spring.jta.enabled"},
havingValue = "true",
matchIfMissing = false
)
public TransactionManager atomikosTransactionManager() throws Throwable {
UserTransactionManager userTransactionManager = new UserTransactionManager();
userTransactionManager.setForceShutdown(false);
return userTransactionManager;
}
public TransactionConfig() {
}
@Bean({"transactionManager"})
@DependsOn({"userTransaction", "atomikosTransactionManager"})
@ConditionalOnProperty(
name = {"spring.jta.enabled"},
havingValue = "true",
matchIfMissing = false
)
@Primary
public PlatformTransactionManager jtaTransactionManager(UserTransaction userTransaction, TransactionManager atomikosTransactionManager) throws Throwable {
return new JtaTransactionManager(userTransaction, atomikosTransactionManager);
}
@ConditionalOnProperty(
name = {"spring.jta.enabled"},
havingValue = "false",
matchIfMissing = true
)
@Bean("transactionManager")
@Qualifier("transactionManager")
@Primary
public PlatformTransactionManager transactionManager(RoutingDataSource dataSource) {
DataSourceTransactionManager dataSourceTransactionManagernew = new DataSourceTransactionManager(dataSource);
return dataSourceTransactionManagernew;
}
@ConditionalOnProperty(
name = {"spring.jta.enabled"},
havingValue = "false",
matchIfMissing = true
)
@Bean("mysql2TransactionManager")
@Qualifier("mysql2TransactionManager")
public PlatformTransactionManager mysql2TransactionManager(RoutingDataSource dataSource) {
DataSource _dataSource = dataSource.getTargetDataSource("mysql2");
DataSourceTransactionManager dataSourceTransactionManagernew = new DataSourceTransactionManager(_dataSource);
return dataSourceTransactionManagernew;
}
@Bean
@ConditionalOnProperty(
name = {"spring.jta.enabled"},
havingValue = "true",
matchIfMissing = false
)
public UserTransaction userTransaction() throws Throwable {
UserTransactionImp userTransactionImp = new UserTransactionImp();
userTransactionImp.setTransactionTimeout(180);
return userTransactionImp;
}
}
7. 实现Mybatis拦截器,读取注解配置的数据源,进行切换,次切换通过DynamicDataSourceContextHolder完成,DynamicDataSourceContextHolder在配置事务的时候(CustomizeTransaction)通过DynamicDataSourceContextHolder.getDataSourceName();获取当前数据源,因此只需要在此拦截器中DynamicDataSourceContextHolder.setDataSourceName();,最后把此拦截器注入到sqlSessionFatory中才会起作用
package com.test.config.datasource;
import com.test.annotation.MyBatisDao;
import com.test.common.lang.StringUtils;
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.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import java.util.Properties;
/**
* Mybatis拦截器,因使用了自定义的sqlSessionFatory,因此需要在sqlSessionFatory中注入拦截器,否则将不生效
* 使用拦截器根据MyBatisDao注解的dataSourceName设置数据源上下文(当前数据源名称)
*/
@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, CacheKey.class, BoundSql.class}
), @Signature(
type = Executor.class,
method = "query",
args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}
)})
public class DataSourceInterceptor implements Interceptor{
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
public Object intercept(Invocation invocation) throws Throwable {
MappedStatement mappedStatement = (MappedStatement)invocation.getArgs()[0];
String className = StringUtils.substringBeforeLast(mappedStatement.getId(), ".");
//获取当前的指定的数据源;
String dsId = ((MyBatisDao) Class.forName(className).getAnnotation(MyBatisDao.class)).dataSourceName();
//如果不在我们注入的所有的数据源范围之内,那么输出警告信息,系统自动使用默认的数据源。
if(StringUtils.isEmpty(dsId)){
dsId = "default";
}
boolean flag = false;
if (!dsId.equalsIgnoreCase(DynamicDataSourceContextHolder.getDataSourceName())) {
DynamicDataSourceContextHolder.setDataSourceName(dsId);
flag = true;
} else {
//找到的话,那么设置到动态数据源上下文中。
DynamicDataSourceContextHolder.setDataSourceName(dsId);
}
Object obj = invocation.proceed();
if(flag){
DynamicDataSourceContextHolder.setDataSourceName(dsId);
}
return obj;
}
public void setProperties(Properties properties) {
}
}
【注】因多数据源中每个数据源都有自己的管理器,所以在配置管理时不要忘了给每个数据源配置管理器,这样事务才会起效(参考TransactionConfig),并且通过@Transactional(“mysql2TransactionManager”)选用管理器,这种处理方式只适合在同一事务下只有一个数据源的情况,如果在同一个事务下使用多个数据源的话,得使用分布式事务进行处理,如在DataSourceConfig中的AtomikosDataSourceBean使用以及TransactionConfig中的JtaTransactionManager配置,但是目前代码有部分报错还没调好,如后续如解决会放上来。