文章目录
- 功能
- 环境依赖
- 多数据源配置
- 动态切换
- 事务重写
- mybatis配置类
- 示例
- 问题及解决方案
- 同一service中A方法内调用B、C方法导致数据源切换失败
- 使用@Transactional后数据源切换失败
功能
- 多个数据源的切换
- 事务内的数据源切换和事务的回滚
环境依赖
- springboot + mybatis + atomikos
<!--分布式事务 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jta-atomikos</artifactId>
</dependency>
<!-- 集成Mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.16</version>
</dependency>
<!-- 集成Oracle -->
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.3</version>
</dependency>
<!-- 集成MyBatis -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.2</version>
</dependency>
多数据源配置
- properties
## 多数据源配置 自己实现
# 数据源1 MySQL
spring.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.master.jdbc-url=jdbc:mysql://ip:port/test?serverTimezone=UTC&characterEncoding=utf-8&useSSL=false
spring.datasource.master.username=#####
spring.datasource.master.password=$$$$$
# 数据源2 oracle
spring.datasource.slave.driverClassName = oracle.jdbc.driver.OracleDriver
spring.datasource.slave.jdbc-url =jdbc:oracle:thin:@localhost:1521/orcl
spring.datasource.slave.username =####
spring.datasource.slave.password =$$$$
- 数据源配置类
@Configuration//定义为配置类
public class DataSourceConfiguration {
@Value("${spring.datasource.master.jdbc-url}")
private String master_url;
@Value("${spring.datasource.master.username}")
private String master_username;
@Value("${spring.datasource.master.password}")
private String master_password;
// 注入数据源2配置项
@Value("${spring.datasource.slave.jdbc-url}")
private String slave_url;
@Value("${spring.datasource.slave.username}")
private String slave_username;
@Value("${spring.datasource.slave.password}")
private String slave_password;
@Bean(name = "master")//将返回值注册为组件
// @ConfigurationProperties("spring.datasource.master")//指定配置前缀
public DataSource master(){
MysqlXADataSource dataSource = new MysqlXADataSource();
dataSource.setUrl(master_url);
dataSource.setUser(master_username);
dataSource.setPassword(master_password);
AtomikosDataSourceBean atomikosDataSourceBean = new AtomikosDataSourceBean();
atomikosDataSourceBean.setXaDataSource(dataSource);
atomikosDataSourceBean.setUniqueResourceName("master");
return atomikosDataSourceBean;
}
@Bean(name = "slave")
// @ConfigurationProperties("spring.datasource.slave")
public DataSource slave(){
try{
OracleXADataSource dataSource = new OracleXADataSource();
dataSource.setURL(slave_url);
dataSource.setUser(slave_username);
dataSource.setPassword(slave_password);
AtomikosDataSourceBean atomikosDataSourceBean = new AtomikosDataSourceBean();
atomikosDataSourceBean.setXaDataSource(dataSource);
atomikosDataSourceBean.setUniqueResourceName("slave");
return atomikosDataSourceBean;
}catch (Exception e){
e.printStackTrace();
return null;
}
}
@Bean(name = "dynamicDataSource")
public DynamicDataSource dataSource(DataSource master, DataSource slave) {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put("master", master);
targetDataSources.put("slave", slave);
return new DynamicDataSource(master, targetDataSources);
}
/**
* 分布式事务管理器
*/
@Bean(name = "transactionManager")
public JtaTransactionManager jtaTransactionManager() {
UserTransactionManager userTransactionManager = new UserTransactionManager();
UserTransaction userTransaction = new UserTransactionImp();
return new JtaTransactionManager(userTransaction, userTransactionManager);
}
}
- 定义动态数据源
/**
* @Description:
* 动态切换数据源主要依靠 AbstractRoutingDataSource。
* 创建一个 AbstractRoutingDataSource 的子类,重写 determineCurrentLookupKey 方法,
* 用于决定使用哪一个数据源。这里主要用到 AbstractRoutingDataSource 的两个属
* 性 defaultTargetDataSource和targetDataSources。defaultTargetDataSource 默认目标数据源,
* targetDataSources(map类型)存放用来切换的数据源。
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
super.setDefaultTargetDataSource(defaultTargetDataSource);
super.setTargetDataSources(targetDataSources);
// afterPropertiesSet()方法调用时用来将targetDataSources的属性写入resolvedDataSources中的
super.afterPropertiesSet();
}
@Override
protected Object determineCurrentLookupKey() {
return DynamicDataSourceContextHolder.getDataSource();
}
}
- 定义数据源注解,用于动态切换
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DS {
String value();
}
动态切换
- 定义线程绑定数据源
public class DynamicDataSourceContextHolder {
/***
* @description: 使用ThreadLocal维护变量,TThreadLocal为每个使用该变量的县城提供独立的变量副本,
* 所以每一个线程都可以独立改变自己的副本,而不会影响其他线程所对应的副本
*/
private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();
/***
* @description: 设置数据源变量
*/
public static void setDataSource(String dataSource) {
// System.out.println("切换到 "+ dataSource+"数据源");
CONTEXT_HOLDER.set(dataSource);
}
/***
* @description: 获取数据源变量
*/
public static String getDataSource() {
return CONTEXT_HOLDER.get();
}
/**
* 清空数据源变量
*/
public static void clearDataSource() {
CONTEXT_HOLDER.remove();
}
}
- 定义AOP切面,切换数据源
/**
* @Description: 通过拦截 @DS 注解,在其执行之前处理设置当前执行SQL的数据源的信息,
* CONTEXT_HOLDER.set(dataSourceType)这里的数据源信息从我们设置的注解上面获取信息,
* 如果没有设置就是用默认的数据源的信息。
*/
@Aspect
@Order(-1)
@Component
@EnableAspectJAutoProxy(exposeProxy = true,proxyTargetClass = true)
public class DataSourceAspect {
//定义切点
@Pointcut("@annotation(edson.MyTemplate.multiDataSource.DS)")
public void dsPointCut() {
}
@Around("dsPointCut()")
public Object around(ProceedingJoinPoint point) throws Throwable {
MethodSignature signature = (MethodSignature) point.getSignature();
Method method = signature.getMethod();
DS dataSource = method.getAnnotation(DS.class);
if (dataSource != null) {
//通过注解值设置数据源
DynamicDataSourceContextHolder.setDataSource(dataSource.value());
}
try {
return point.proceed();
} finally {
// 销毁数据源 在执行方法之后
DynamicDataSourceContextHolder.clearDataSource();
}
}
}
事务重写
- 重写transaction
public class MultiDataSourceTransaction implements Transaction {
private static final Log LOGGER = LogFactory.getLog(MultiDataSourceTransaction.class);
private final DataSource dataSource;
private Connection mainConnection;
private String mainDatabaseIdentification;
private ConcurrentMap<String, Connection> otherConnectionMap;
private boolean isConnectionTransactional;
private boolean autoCommit;
public MultiDataSourceTransaction(DataSource dataSource) {
notNull(dataSource, "No DataSource specified");
this.dataSource = dataSource;
otherConnectionMap = new ConcurrentHashMap<>();
mainDatabaseIdentification=DynamicDataSourceContextHolder.getDataSource();
}
/**
* {@inheritDoc}
*/
@Override
public Connection getConnection() throws SQLException {
String databaseIdentification = DynamicDataSourceContextHolder.getDataSource();
if (databaseIdentification.equals(mainDatabaseIdentification)) {
if (mainConnection != null) return mainConnection;
else {
openMainConnection();
mainDatabaseIdentification =databaseIdentification;
return mainConnection;
}
} else {
if (!otherConnectionMap.containsKey(databaseIdentification)) {
try {
Connection conn = dataSource.getConnection();
otherConnectionMap.put(databaseIdentification, conn);
} catch (SQLException ex) {
throw new CannotGetJdbcConnectionException("Could not get JDBC Connection", ex);
}
}
return otherConnectionMap.get(databaseIdentification);
}
}
private void openMainConnection() throws SQLException {
this.mainConnection = DataSourceUtils.getConnection(this.dataSource);
this.autoCommit = this.mainConnection.getAutoCommit();
this.isConnectionTransactional = DataSourceUtils.isConnectionTransactional(this.mainConnection, this.dataSource);
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(
"JDBC Connection ["
+ this.mainConnection
+ "] will"
+ (this.isConnectionTransactional ? " " : " not ")
+ "be managed by Spring");
}
}
/**
* {@inheritDoc}
*/
@Override
public void commit() throws SQLException {
if (this.mainConnection != null && !this.isConnectionTransactional && !this.autoCommit) {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug("Committing JDBC Connection [" + this.mainConnection + "]");
}
this.mainConnection.commit();
for (Connection connection : otherConnectionMap.values()) {
connection.commit();
}
}
}
/**
* {@inheritDoc}
*/
@Override
public void rollback() throws SQLException {
if (this.mainConnection != null && !this.isConnectionTransactional && !this.autoCommit) {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug("Rolling back JDBC Connection [" + this.mainConnection + "]");
}
this.mainConnection.rollback();
for (Connection connection : otherConnectionMap.values()) {
connection.rollback();
}
}
}
/**
* {@inheritDoc}
*/
@Override
public void close() throws SQLException {
DataSourceUtils.releaseConnection(this.mainConnection, this.dataSource);
for (Connection connection : otherConnectionMap.values()) {
DataSourceUtils.releaseConnection(connection, this.dataSource);
}
}
@Override
public Integer getTimeout() throws SQLException {
return null;
}
}
- 重写TransactionFactory
@Component
public class MultiDataSourceTransactionFactory extends SpringManagedTransactionFactory {
@Override
public Transaction newTransaction(DataSource dataSource, TransactionIsolationLevel level, boolean autoCommit) {
return new MultiDataSourceTransaction(dataSource);
}
}
mybatis配置类
- 设置为动态数据源
- 设置事务工厂为重写的TransactionFactory
@Configuration
@MapperScan(value = "edson.MyTemplate.dao", sqlSessionFactoryRef = "sqlSessionFactory")
public class MybatisConfiguration {
@Autowired //自动装配
@Qualifier("dynamicDataSource") // 指定装配的名称
private DataSource db;
@Autowired
private MultiDataSourceTransactionFactory multiDataSourceTransactionFactory;
@Bean
@Primary
public SqlSessionFactory sqlSessionFactory() throws Exception{
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(db);//设置动态数据源
//设置事务(重写)
sqlSessionFactoryBean.setTransactionFactory(multiDataSourceTransactionFactory);
sqlSessionFactoryBean//指定mapper文件路径
.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources("classpath:mappers/*.xml"));
return sqlSessionFactoryBean.getObject();
}
}
示例
@Service
public class UserService {
@Autowired
private UserDao userDao;
@Transactional(rollbackFor = Exception.class)
public void save(){
//动态代理 解决service内部方法调用aop失效问题
UserService aopUserService = ((UserService)AopContext.currentProxy());
User master = aopUserService.selectMasterUserById("1");
master.setName("cha012210000");
aopUserService.updateMaster(master);
User slave = master;
slave.setId(1);
slave.setName("change failfffffvvvds");
aopUserService.updateSlave(slave);
}
@DS("slave")
public User selectSlaveUserById(String id){
return userDao.selectById(id);
}
@DS("master")
public User selectMasterUserById(String id){
return userDao.selectById(id);
}
@DS("master")
public void updateMaster(User user){
userDao.update(user);
}
@DS("slave")
public void updateSlave(User user){
System.out.println("---开始updateSlave----");
userDao.update(user);
System.out.println("---结束updateSlave----");
}
// @DS("slave")
public void insertSlave(User user){
Integer res = userDao.insert(user);
}
}
问题及解决方案
同一service中A方法内调用B、C方法导致数据源切换失败
- 问题重现
public void save(){//方法A
User master = selectMasterUserById("1");//方法B
master.setName("cha012210000");
updateMaster(master);
User slave = master;
slave.setId(1);
slave.setName("change failfffffvvvds");
updateSlave(slave);//方法C
}
- 解决办法
- 在AOP切面类暴露代理类:
@EnableAspectJAutoProxy(exposeProxy = true,proxyTargetClass = true)
- 获取到当前的代理类
AopContext.currentProxy()
,调用代理类的方法;此时数据源AOP才会切入
//动态代理 解决service内部方法调用aop失效问题
UserService aopUserService = ((UserService)AopContext.currentProxy());
User master = aopUserService.selectMasterUserById("1");
master.setName("cha012210000");
aopUserService.updateMaster(master);
User slave = master;
slave.setId(1);
slave.setName("change failfffffvvvds");
aopUserService.updateSlave(slave);
使用@Transactional后数据源切换失败
- 解决办法
重写transaction
和transactionFactory
(参考事务重写和mybatis配置类)