今天解决springboot多数据源加事务,在网上找了很多案例,都没有描述清楚,踩了很多坑,这里记录下来,让广大朋友少踩坑。
一:配置文件:
# 数据源配置
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
druid:
# 主库数据源
master:
url: jdbc:mysql://192.168.0.121:3306/xi_mall?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone= GMT%2B8 &rewriteBatchedStatements=true&allowMultiQueries=true
username:
password:
uniqueResourceName: MASTER
# 从库数据源
slave:
# 从数据源开关/默认关闭
enabled: true
url: jdbc:mysql://192.168.0.121:3306/lixin_mall?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone= GMT%2B8 &rewriteBatchedStatements=true&allowMultiQueries=true
username:
password:
uniqueResourceName: SLAVE
# 初始连接数
initialSize: 5
# 最小连接池数量
minIdle: 10
# 最大连接池数量
maxActive: 20
# 配置获取连接等待超时的时间
maxWait: 600000
# 配置连接超时时间
connectTimeout: 30000
# 配置网络超时时间
socketTimeout: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 300000
# 配置一个连接在池中最大生存的时间,单位是毫秒
maxEvictableIdleTimeMillis: 900000
# 配置检测连接是否有效
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
webStatFilter:
enabled: true
statViewServlet:
enabled: true
# 设置白名单,不填则允许所有访问
allow:
url-pattern: /druid/*
# 控制台管理用户名和密码
login-username: lixinAdmin
login-password: vN0jc1JkQjNm2jNZ
filter:
stat:
enabled: true
# 慢SQL记录
log-slow-sql: true
slow-sql-millis: 1000
merge-sql: true
wall:
config:
multi-statement-allow: true
validation-query-timeout: 10
二:以下是修改和增加配置文件:
1,在framework模块增加maven依赖:
<!--atomikos 分布式事务-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jta-atomikos</artifactId>
</dependency>
2,在com.ruoyi.framework.config.properties包下增加druid 配置属性
package com.ligou.framework.config.properties;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Configuration;
import com.alibaba.druid.pool.DruidDataSource;
/**
* druid 配置属性
*
* @author ruoyi
*/
@Configuration
public class DruidProperties
{
@Value("${spring.datasource.druid.initialSize}")
private int initialSize;
@Value("${spring.datasource.druid.minIdle}")
private int minIdle;
@Value("${spring.datasource.druid.maxActive}")
private int maxActive;
@Value("${spring.datasource.druid.maxWait}")
private int maxWait;
@Value("${spring.datasource.druid.connectTimeout}")
private int connectTimeout;
@Value("${spring.datasource.druid.socketTimeout}")
private int socketTimeout;
@Value("${spring.datasource.druid.timeBetweenEvictionRunsMillis}")
private int timeBetweenEvictionRunsMillis;
@Value("${spring.datasource.druid.minEvictableIdleTimeMillis}")
private int minEvictableIdleTimeMillis;
@Value("${spring.datasource.druid.maxEvictableIdleTimeMillis}")
private int maxEvictableIdleTimeMillis;
@Value("${spring.datasource.druid.validationQuery}")
private String validationQuery;
@Value("${spring.datasource.druid.testWhileIdle}")
private boolean testWhileIdle;
@Value("${spring.datasource.druid.testOnBorrow}")
private boolean testOnBorrow;
@Value("${spring.datasource.druid.testOnReturn}")
private boolean testOnReturn;
public DruidDataSource dataSource(DruidDataSource datasource)
{
/** 配置初始化大小、最小、最大 */
datasource.setInitialSize(initialSize);
datasource.setMaxActive(maxActive);
datasource.setMinIdle(minIdle);
/** 配置获取连接等待超时的时间 */
datasource.setMaxWait(maxWait);
/** 配置驱动连接超时时间,检测数据库建立连接的超时时间,单位是毫秒 */
datasource.setConnectTimeout(connectTimeout);
/** 配置网络超时时间,等待数据库操作完成的网络超时时间,单位是毫秒 */
datasource.setSocketTimeout(socketTimeout);
/** 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 */
datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
/** 配置一个连接在池中最小、最大生存的时间,单位是毫秒 */
datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
datasource.setMaxEvictableIdleTimeMillis(maxEvictableIdleTimeMillis);
/**
* 用来检测连接是否有效的sql,要求是一个查询语句,常用select 'x'。如果validationQuery为null,testOnBorrow、testOnReturn、testWhileIdle都不会起作用。
*/
datasource.setValidationQuery(validationQuery);
/** 建议配置为true,不影响性能,并且保证安全性。申请连接的时候检测,如果空闲时间大于timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。 */
datasource.setTestWhileIdle(testWhileIdle);
/** 申请连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能。 */
datasource.setTestOnBorrow(testOnBorrow);
/** 归还连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能。 */
datasource.setTestOnReturn(testOnReturn);
return datasource;
}
public int getInitialSize()
{
return initialSize;
}
public void setInitialSize(int initialSize)
{
this.initialSize = initialSize;
}
public int getMinIdle()
{
return minIdle;
}
public void setMinIdle(int minIdle)
{
this.minIdle = minIdle;
}
public int getMaxActive()
{
return maxActive;
}
public void setMaxActive(int maxActive)
{
this.maxActive = maxActive;
}
public int getMaxWait()
{
return maxWait;
}
public void setMaxWait(int maxWait)
{
this.maxWait = maxWait;
}
public int getConnectTimeout()
{
return connectTimeout;
}
public void setConnectTimeout(int connectTimeout)
{
this.connectTimeout = connectTimeout;
}
public int getSocketTimeout()
{
return socketTimeout;
}
public void setSocketTimeout(int socketTimeout)
{
this.socketTimeout = socketTimeout;
}
public int getTimeBetweenEvictionRunsMillis()
{
return timeBetweenEvictionRunsMillis;
}
public void setTimeBetweenEvictionRunsMillis(int timeBetweenEvictionRunsMillis)
{
this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
}
public int getMinEvictableIdleTimeMillis()
{
return minEvictableIdleTimeMillis;
}
public void setMinEvictableIdleTimeMillis(int minEvictableIdleTimeMillis)
{
this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
}
public int getMaxEvictableIdleTimeMillis()
{
return maxEvictableIdleTimeMillis;
}
public void setMaxEvictableIdleTimeMillis(int maxEvictableIdleTimeMillis)
{
this.maxEvictableIdleTimeMillis = maxEvictableIdleTimeMillis;
}
public String getValidationQuery()
{
return validationQuery;
}
public void setValidationQuery(String validationQuery)
{
this.validationQuery = validationQuery;
}
public boolean isTestWhileIdle()
{
return testWhileIdle;
}
public void setTestWhileIdle(boolean testWhileIdle)
{
this.testWhileIdle = testWhileIdle;
}
public boolean isTestOnBorrow()
{
return testOnBorrow;
}
public void setTestOnBorrow(boolean testOnBorrow)
{
this.testOnBorrow = testOnBorrow;
}
public boolean isTestOnReturn()
{
return testOnReturn;
}
public void setTestOnReturn(boolean testOnReturn)
{
this.testOnReturn = testOnReturn;
}
}
3,修改com.ligou.framework.config.ApplicationConfig配置文件
package com.ligou.framework.config;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.autoconfigure.jackson.Jackson2ObjectMapperBuilderCustomizer;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
import java.util.TimeZone;
/**
* 程序注解配置
*
* @author ruoyi
*/
@Configuration
// 表示通过aop框架暴露该代理对象,AopContext能够访问
@EnableAspectJAutoProxy(exposeProxy = true)
// 指定要扫描的Mapper类的包的路径。(注意com.ruoyi.**.mapper这里需要改成自己的包名)
@MapperScan(basePackages = "com.ruoyi.**.mapper", sqlSessionTemplateRef = "sqlSessionTemplate")
public class ApplicationConfig
{
/**
* 时区配置
*/
@Bean
public Jackson2ObjectMapperBuilderCustomizer jacksonObjectMapperCustomization()
{
return jacksonObjectMapperBuilder -> jacksonObjectMapperBuilder.timeZone(TimeZone.getDefault());
}
}
4,在com.ruoyi.framework.config包下面增加JTA事务配置
package com.ruoyi.framework.config;
import javax.transaction.TransactionManager;
import javax.transaction.UserTransaction;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.DependsOn;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.jta.JtaTransactionManager;
import com.atomikos.icatch.jta.UserTransactionImp;
import com.atomikos.icatch.jta.UserTransactionManager;
/**
* JTA 事务配置
*
* @author ruoyi
*/
@Configuration
public class AtomikosConfig
{
@Bean(name = "userTransaction")
public UserTransaction userTransaction() throws Throwable
{
UserTransactionImp userTransactionImp = new UserTransactionImp();
userTransactionImp.setTransactionTimeout(10000);
return userTransactionImp;
}
@Bean(name = "atomikosTransactionManager", initMethod = "init", destroyMethod = "close")
public TransactionManager atomikosTransactionManager() throws Throwable
{
UserTransactionManager userTransactionManager = new UserTransactionManager();
userTransactionManager.setForceShutdown(false);
return userTransactionManager;
}
@Bean(name = "transactionManager")
@DependsOn({ "userTransaction", "atomikosTransactionManager" })
public PlatformTransactionManager transactionManager() throws Throwable
{
UserTransaction userTransaction = userTransaction();
TransactionManager atomikosTransactionManager = atomikosTransactionManager();
return new JtaTransactionManager(userTransaction, atomikosTransactionManager);
}
}
5,修改com.ligou.framework.config包下的druid 配置多数据源
package com.ruoyi.framework.config;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.sql.DataSource;
import com.ligou.common.enums.DataSourceType;
import com.ligou.common.utils.spring.SpringUtils;
import com.ligou.framework.config.properties.DruidProperties;
import com.ligou.framework.datasource.DynamicDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.DependsOn;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties;
import com.alibaba.druid.util.Utils;
import com.atomikos.jdbc.AtomikosDataSourceBean;
/**
* druid 配置多数据源
*
* @author ruoyi
*/
@Configuration
public class DruidConfig
{
public static final String MASTER = DataSourceType.MASTER.name();
public static final String SLAVE = DataSourceType.SLAVE.name();
@Autowired
private DruidProperties druidProperties;
@Primary
@Bean
@DependsOn({"transactionManager"})
@ConfigurationProperties("spring.datasource.druid.master")
public DataSource masterDataSource(Environment env)
{
String prefix = "spring.datasource.druid.master.";
return getDataSource(env, prefix, MASTER);
}
@Bean
@ConfigurationProperties("spring.datasource.druid.slave")
@DependsOn({"transactionManager"})
@ConditionalOnProperty(prefix = "spring.datasource.druid.slave", name = "enabled", havingValue = "true")
public DataSource slaveDataSource(Environment env)
{
String prefix = "spring.datasource.druid.slave.";
return getDataSource(env, prefix, SLAVE);
}
protected DataSource getDataSource(Environment env, String prefix, String dataSourceName)
{
Properties prop = build(env, prefix);
AtomikosDataSourceBean ds = new AtomikosDataSourceBean();
ds.setXaDataSourceClassName("com.alibaba.druid.pool.xa.DruidXADataSource");
// 添加连接池限制
ds.setMaxPoolSize(50);
ds.setMinPoolSize(5);
ds.setBorrowConnectionTimeout(60);
ds.setUniqueResourceName(dataSourceName);
ds.setXaProperties(prop);
return ds;
}
protected Properties build(Environment env, String prefix)
{
Properties prop = new Properties();
prop.put("url", env.getProperty(prefix + "url"));
prop.put("username", env.getProperty(prefix + "username"));
prop.put("password", env.getProperty(prefix + "password"));
prop.put("initialSize", druidProperties.getInitialSize());
prop.put("minIdle", druidProperties.getMinIdle());
prop.put("maxActive", druidProperties.getMaxActive());
prop.put("maxWait", druidProperties.getMaxWait());
prop.put("timeBetweenEvictionRunsMillis", druidProperties.getTimeBetweenEvictionRunsMillis());
prop.put("minEvictableIdleTimeMillis", druidProperties.getMinEvictableIdleTimeMillis());
prop.put("maxEvictableIdleTimeMillis", druidProperties.getMaxEvictableIdleTimeMillis());
prop.put("validationQuery", druidProperties.getValidationQuery());
prop.put("testWhileIdle", druidProperties.isTestWhileIdle());
prop.put("testOnBorrow", druidProperties.isTestOnBorrow());
prop.put("testOnReturn", druidProperties.isTestOnReturn());
return prop;
}
@Bean(name = "dynamicDataSource")
@Primary
public DynamicDataSource dataSource(DataSource masterDataSource)
{
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(MASTER, masterDataSource);
setDataSource(targetDataSources, SLAVE, "slaveDataSource");
return new DynamicDataSource(masterDataSource, targetDataSources);
}
/**
* 设置数据源
*
* @param targetDataSources 备选数据源集合
* @param sourceName 数据源名称
* @param beanName bean名称
*/
public void setDataSource(Map<Object, Object> targetDataSources, String sourceName, String beanName)
{
try
{
DataSource dataSource = SpringUtils.getBean(beanName);
targetDataSources.put(sourceName, dataSource);
}
catch (Exception e)
{
}
}
/**
* 去除监控页面底部的广告
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
@Bean
@ConditionalOnProperty(name = "spring.datasource.druid.statViewServlet.enabled", havingValue = "true")
public FilterRegistrationBean removeDruidFilterRegistrationBean(DruidStatProperties properties)
{
// 获取web监控页面的参数
DruidStatProperties.StatViewServlet config = properties.getStatViewServlet();
// 提取common.js的配置路径
String pattern = config.getUrlPattern() != null ? config.getUrlPattern() : "/druid/*";
String commonJsPattern = pattern.replaceAll("\\*", "js/common.js");
final String filePath = "support/http/resources/js/common.js";
// 创建filter进行过滤
Filter filter = new Filter()
{
@Override
public void init(javax.servlet.FilterConfig filterConfig) throws ServletException
{
}
@Override
public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)
throws IOException, ServletException
{
chain.doFilter(request, response);
// 重置缓冲区,响应头不会被重置
response.resetBuffer();
// 获取common.js
String text = Utils.readFromResource(filePath);
// 正则替换banner, 除去底部的广告信息
text = text.replaceAll("<a.*?banner\"></a><br/>", "");
text = text.replaceAll("powered.*?shrek.wang</a>", "");
response.getWriter().write(text);
}
@Override
public void destroy()
{
}
};
FilterRegistrationBean registrationBean = new FilterRegistrationBean();
registrationBean.setFilter(filter);
registrationBean.addUrlPatterns(commonJsPattern);
return registrationBean;
}
}
6,修改com.ligou.framework.config包下的Mybaits配置
package com.ruoyi.framework.config;
import java.io.IOException;
import java.util.*;
import javax.sql.DataSource;
import com.ligou.framework.datasource.DynamicSqlSessionTemplate;
import org.apache.ibatis.io.VFS;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.boot.autoconfigure.SpringBootVFS;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.core.io.DefaultResourceLoader;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.core.type.classreading.CachingMetadataReaderFactory;
import org.springframework.core.type.classreading.MetadataReader;
import org.springframework.core.type.classreading.MetadataReaderFactory;
import org.springframework.util.ClassUtils;
import com.ligou.common.utils.StringUtils;
/**
* Mybatis支持*匹配扫描包
*
* @author ligou
*/
@Configuration
public class MyBatisConfigBackups
{
// @Autowired
// private Environment env;
static final String DEFAULT_RESOURCE_PATTERN = "**/*.class";
public static String setTypeAliasesPackage(String typeAliasesPackage)
{
ResourcePatternResolver resolver = (ResourcePatternResolver) new PathMatchingResourcePatternResolver();
MetadataReaderFactory metadataReaderFactory = new CachingMetadataReaderFactory(resolver);
List<String> allResult = new ArrayList<String>();
try
{
for (String aliasesPackage : typeAliasesPackage.split(","))
{
List<String> result = new ArrayList<String>();
aliasesPackage = ResourcePatternResolver.CLASSPATH_ALL_URL_PREFIX
+ ClassUtils.convertClassNameToResourcePath(aliasesPackage.trim()) + "/" + DEFAULT_RESOURCE_PATTERN;
Resource[] resources = resolver.getResources(aliasesPackage);
if (resources != null && resources.length > 0)
{
MetadataReader metadataReader = null;
for (Resource resource : resources)
{
if (resource.isReadable())
{
metadataReader = metadataReaderFactory.getMetadataReader(resource);
try
{
result.add(Class.forName(metadataReader.getClassMetadata().getClassName()).getPackage().getName());
}
catch (ClassNotFoundException e)
{
e.printStackTrace();
}
}
}
}
if (result.size() > 0)
{
HashSet<String> hashResult = new HashSet<String>(result);
allResult.addAll(hashResult);
}
}
if (allResult.size() > 0)
{
typeAliasesPackage = String.join(",", (String[]) allResult.toArray(new String[0]));
}
else
{
throw new RuntimeException("mybatis typeAliasesPackage 路径扫描错误,参数typeAliasesPackage:" + typeAliasesPackage + "未找到任何包");
}
}
catch (IOException e)
{
e.printStackTrace();
}
return typeAliasesPackage;
}
public Resource[] resolveMapperLocations(String[] mapperLocations)
{
ResourcePatternResolver resourceResolver = new PathMatchingResourcePatternResolver();
List<Resource> resources = new ArrayList<Resource>();
if (mapperLocations != null)
{
for (String mapperLocation : mapperLocations)
{
try
{
Resource[] mappers = resourceResolver.getResources(mapperLocation);
resources.addAll(Arrays.asList(mappers));
}
catch (IOException e)
{
// ignore
}
}
}
return resources.toArray(new Resource[resources.size()]);
}
// @Bean
public SqlSessionFactory sqlSessionFactory(Environment env,DataSource dataSource) throws Exception
{
String typeAliasesPackage = env.getProperty("mybatis.typeAliasesPackage");
String mapperLocations = env.getProperty("mybatis.mapperLocations");
String configLocation = env.getProperty("mybatis.configLocation");
typeAliasesPackage = setTypeAliasesPackage(typeAliasesPackage);
VFS.addImplClass(SpringBootVFS.class);
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
sessionFactory.setTypeAliasesPackage(typeAliasesPackage);
sessionFactory.setMapperLocations(resolveMapperLocations(StringUtils.split(mapperLocations, ",")));
sessionFactory.setConfigLocation(new DefaultResourceLoader().getResource(configLocation));
// sessionFactory.setPlugins(new SqlAreaInterceptor());
// sessionFactory.setPlugins(new SqlAreaScopeInterceptor());
return sessionFactory.getObject();
}
//这里一定要加@Primary,要不然会有多个bean,需要选择注入
@Primary
@Bean(name = "sqlSessionFactoryMaster")
public SqlSessionFactory sqlSessionFactoryMaster(Environment env, @Qualifier("masterDataSource") DataSource dataSource) throws Exception
{
return sqlSessionFactory(env, dataSource);
}
@Bean(name = "sqlSessionFactorySlave")
public SqlSessionFactory sqlSessionFactorySlave(Environment env, @Qualifier("slaveDataSource") DataSource dataSource) throws Exception
{
return sqlSessionFactory(env, dataSource);
}
@Bean(name = "sqlSessionTemplate")
public DynamicSqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactoryMaster") SqlSessionFactory factoryMaster,
@Qualifier("sqlSessionFactorySlave") SqlSessionFactory factorySlave) throws Exception
{
Map<Object, SqlSessionFactory> sqlSessionFactoryMap = new HashMap<>();
sqlSessionFactoryMap.put(DruidConfig.MASTER, factoryMaster);
sqlSessionFactoryMap.put(DruidConfig.SLAVE, factorySlave);
DynamicSqlSessionTemplate customSqlSessionTemplate = new DynamicSqlSessionTemplate(factoryMaster);
customSqlSessionTemplate.setTargetSqlSessionFactorys(sqlSessionFactoryMap);
return customSqlSessionTemplate;
}
}
7,在com.ligou.framework.datasource包下增加自定义的SqlSessionTemplate,动态切换数据源
package com.ruoyi.framework.datasource;
import static java.lang.reflect.Proxy.newProxyInstance;
import static org.apache.ibatis.reflection.ExceptionUtil.unwrapThrowable;
import static org.mybatis.spring.SqlSessionUtils.closeSqlSession;
import static org.mybatis.spring.SqlSessionUtils.getSqlSession;
import static org.mybatis.spring.SqlSessionUtils.isSqlSessionTransactional;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.exceptions.PersistenceException;
import org.apache.ibatis.executor.BatchResult;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.MyBatisExceptionTranslator;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.dao.support.PersistenceExceptionTranslator;
/**
* 自定义SqlSessionTemplate,动态切换数据源
*
* @author ruoyi
*/
public class DynamicSqlSessionTemplate extends SqlSessionTemplate
{
private final SqlSessionFactory sqlSessionFactory;
private final ExecutorType executorType;
private final SqlSession sqlSessionProxy;
private final PersistenceExceptionTranslator exceptionTranslator;
private Map<Object, SqlSessionFactory> targetSqlSessionFactorys;
private SqlSessionFactory defaultTargetSqlSessionFactory;
public DynamicSqlSessionTemplate(SqlSessionFactory sqlSessionFactory)
{
this(sqlSessionFactory, sqlSessionFactory.getConfiguration().getDefaultExecutorType());
}
public DynamicSqlSessionTemplate(SqlSessionFactory sqlSessionFactory, ExecutorType executorType)
{
this(sqlSessionFactory, executorType, new MyBatisExceptionTranslator(
sqlSessionFactory.getConfiguration().getEnvironment().getDataSource(), true));
}
public DynamicSqlSessionTemplate(SqlSessionFactory sqlSessionFactory, ExecutorType executorType,
PersistenceExceptionTranslator exceptionTranslator)
{
super(sqlSessionFactory, executorType, exceptionTranslator);
this.sqlSessionFactory = sqlSessionFactory;
this.executorType = executorType;
this.exceptionTranslator = exceptionTranslator;
this.sqlSessionProxy = (SqlSession) newProxyInstance(SqlSessionFactory.class.getClassLoader(),
new Class[] { SqlSession.class }, new SqlSessionInterceptor());
this.defaultTargetSqlSessionFactory = sqlSessionFactory;
}
public void setTargetSqlSessionFactorys(Map<Object, SqlSessionFactory> targetSqlSessionFactorys)
{
this.targetSqlSessionFactorys = targetSqlSessionFactorys;
}
public void setDefaultTargetSqlSessionFactory(SqlSessionFactory defaultTargetSqlSessionFactory)
{
this.defaultTargetSqlSessionFactory = defaultTargetSqlSessionFactory;
}
@Override
public SqlSessionFactory getSqlSessionFactory()
{
SqlSessionFactory targetSqlSessionFactory = targetSqlSessionFactorys
.get(DynamicDataSourceContextHolder.getDataSourceType());
if (targetSqlSessionFactory != null)
{
return targetSqlSessionFactory;
}
else if (defaultTargetSqlSessionFactory != null)
{
return defaultTargetSqlSessionFactory;
}
return this.sqlSessionFactory;
}
@Override
public Configuration getConfiguration()
{
return this.getSqlSessionFactory().getConfiguration();
}
public ExecutorType getExecutorType()
{
return this.executorType;
}
public PersistenceExceptionTranslator getPersistenceExceptionTranslator()
{
return this.exceptionTranslator;
}
/**
* {@inheritDoc}
*/
public <T> T selectOne(String statement)
{
return this.sqlSessionProxy.<T> selectOne(statement);
}
/**
* {@inheritDoc}
*/
public <T> T selectOne(String statement, Object parameter)
{
return this.sqlSessionProxy.<T> selectOne(statement, parameter);
}
/**
* {@inheritDoc}
*/
public <K, V> Map<K, V> selectMap(String statement, String mapKey)
{
return this.sqlSessionProxy.<K, V> selectMap(statement, mapKey);
}
/**
* {@inheritDoc}
*/
public <K, V> Map<K, V> selectMap(String statement, Object parameter, String mapKey)
{
return this.sqlSessionProxy.<K, V> selectMap(statement, parameter, mapKey);
}
/**
* {@inheritDoc}
*/
public <K, V> Map<K, V> selectMap(String statement, Object parameter, String mapKey, RowBounds rowBounds)
{
return this.sqlSessionProxy.<K, V> selectMap(statement, parameter, mapKey, rowBounds);
}
/**
* {@inheritDoc}
*/
public <E> List<E> selectList(String statement)
{
return this.sqlSessionProxy.<E> selectList(statement);
}
/**
* {@inheritDoc}
*/
public <E> List<E> selectList(String statement, Object parameter)
{
return this.sqlSessionProxy.<E> selectList(statement, parameter);
}
/**
* {@inheritDoc}
*/
public <E> List<E> selectList(String statement, Object parameter, RowBounds rowBounds)
{
return this.sqlSessionProxy.<E> selectList(statement, parameter, rowBounds);
}
/**
* {@inheritDoc}
*/
@SuppressWarnings("rawtypes")
public void select(String statement, ResultHandler handler)
{
this.sqlSessionProxy.select(statement, handler);
}
/**
* {@inheritDoc}
*/
@SuppressWarnings("rawtypes")
public void select(String statement, Object parameter, ResultHandler handler)
{
this.sqlSessionProxy.select(statement, parameter, handler);
}
/**
* {@inheritDoc}
*/
@SuppressWarnings("rawtypes")
public void select(String statement, Object parameter, RowBounds rowBounds, ResultHandler handler)
{
this.sqlSessionProxy.select(statement, parameter, rowBounds, handler);
}
/**
* {@inheritDoc}
*/
public int insert(String statement)
{
return this.sqlSessionProxy.insert(statement);
}
/**
* {@inheritDoc}
*/
public int insert(String statement, Object parameter)
{
return this.sqlSessionProxy.insert(statement, parameter);
}
/**
* {@inheritDoc}
*/
public int update(String statement)
{
return this.sqlSessionProxy.update(statement);
}
/**
* {@inheritDoc}
*/
public int update(String statement, Object parameter)
{
return this.sqlSessionProxy.update(statement, parameter);
}
/**
* {@inheritDoc}
*/
public int delete(String statement)
{
return this.sqlSessionProxy.delete(statement);
}
/**
* {@inheritDoc}
*/
public int delete(String statement, Object parameter)
{
return this.sqlSessionProxy.delete(statement, parameter);
}
/**
* {@inheritDoc}
*/
public <T> T getMapper(Class<T> type)
{
return getConfiguration().getMapper(type, this);
}
/**
* {@inheritDoc}
*/
public void commit()
{
throw new UnsupportedOperationException("Manual commit is not allowed over a Spring managed SqlSession");
}
/**
* {@inheritDoc}
*/
public void commit(boolean force)
{
throw new UnsupportedOperationException("Manual commit is not allowed over a Spring managed SqlSession");
}
/**
* {@inheritDoc}
*/
public void rollback()
{
throw new UnsupportedOperationException("Manual rollback is not allowed over a Spring managed SqlSession");
}
/**
* {@inheritDoc}
*/
public void rollback(boolean force)
{
throw new UnsupportedOperationException("Manual rollback is not allowed over a Spring managed SqlSession");
}
/**
* {@inheritDoc}
*/
public void close()
{
throw new UnsupportedOperationException("Manual close is not allowed over a Spring managed SqlSession");
}
/**
* {@inheritDoc}
*/
public void clearCache()
{
this.sqlSessionProxy.clearCache();
}
/**
* {@inheritDoc}
*/
public Connection getConnection()
{
return this.sqlSessionProxy.getConnection();
}
/**
* {@inheritDoc}
*
* @since 1.0.2
*/
public List<BatchResult> flushStatements()
{
return this.sqlSessionProxy.flushStatements();
}
/**
* Proxy needed to route MyBatis method calls to the proper SqlSession got from Spring's Transaction Manager It also
* unwraps exceptions thrown by {@code Method#invoke(Object, Object...)} to pass a {@code PersistenceException} to
* the {@code PersistenceExceptionTranslator}.
*/
private class SqlSessionInterceptor implements InvocationHandler
{
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable
{
final SqlSession sqlSession = getSqlSession(DynamicSqlSessionTemplate.this.getSqlSessionFactory(),
DynamicSqlSessionTemplate.this.executorType, DynamicSqlSessionTemplate.this.exceptionTranslator);
try
{
Object result = method.invoke(sqlSession, args);
if (!isSqlSessionTransactional(sqlSession, DynamicSqlSessionTemplate.this.getSqlSessionFactory()))
{
sqlSession.commit(true);
}
return result;
}
catch (Throwable t)
{
Throwable unwrapped = unwrapThrowable(t);
if (DynamicSqlSessionTemplate.this.exceptionTranslator != null
&& unwrapped instanceof PersistenceException)
{
Throwable translated = DynamicSqlSessionTemplate.this.exceptionTranslator
.translateExceptionIfPossible((PersistenceException) unwrapped);
if (translated != null)
{
unwrapped = translated;
}
}
throw unwrapped;
}
finally
{
closeSqlSession(sqlSession, DynamicSqlSessionTemplate.this.getSqlSessionFactory());
}
}
}
}
8,测试代码
//DataSource可以加在类上也可以加在方法上。如果方法跟类上都不加的话,默认就使用master数据源
@DataSource(DataSourceType.SLAVE)
public interface LXUserMapper {
/**
* 新增用户
*
* @param user 用户
* @return 结果
*/
int insertUser(LXUser user);
}
//这里没有加DateSource注解,默认使用master
public interface UserMapper{
public int updateBalance(String userId,Double money);
}
@Service
public class UserService{
@Resouce
private LXUserMapper lxUserMapper;
@Resouce
private UserMapper userMapper;
@Transactional
@Override
public int updateUserInfo(LXUser user){
lxUserMapper.updateUser(user);
userMapper.updateBalance(user.getId(),user.getMoney);
// int t = 1/0; 这里模拟报错。两个物理库的数据都会回滚自己亲自测没问题。
return 1;
}
}