最近项目中使用springboot2.1.4 + jpa + hibernate 使用aop注解形式实现多数据源管理,用的就是网上大多数人使用的一套代码,但是我在项目中使用后发现如果配置 spring配置文件 application.yml 中open-in-view: false 跨数据源查询正常,但是保存或者修改失败原因是事务没有提交,一旦把open-in-view改成true则不能跨数据源查询,修改保存正常事务正常提交求解,具体代码如下实现
1. application.yml
spring:
## 数据库配置
#datasource:
#driver-class-name: com.mysql.jdbc.Driver
#url: jdbc:mysql://192.168.101.125:3306/cdv?useSSL=false&characterEncoding=utf-8
#username: root
#password: 123456
#url: jdbc:mysql://127.0.0.1:3306/boot-jpa-back-sys2?useSSL=false&characterEncoding=utf-8
#username: root
#password:
datasource:
test1:
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.101.125:3306/cdv?useSSL=false&characterEncoding=utf-8
username: root
password: 123456
type: com.alibaba.druid.pool.DruidDataSource
test2:
driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc-url: jdbc:sqlserver://192.168.101.125:1433;database=Test;
username: sa
password: Zqvideo123
type: com.alibaba.druid.pool.DruidDataSource
## jpa配置
jpa:
show-sql: true
hibernate:
#ddl-auto: update
properties:
hibernate.dialect: com.common.mysql.MySQLDialectUTF8
hibernate.format_sql: false
open-in-view: false
DataSourceAsepct
package com.common.db;
import java.lang.reflect.Method;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.AfterThrowing;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
@Aspect
@Component
@Order(0)
public class DataSourceAsepct {
//加载数据源
@Pointcut("@annotation(com.common.db.DataSourceSet)")
// @AfterThrowing(pointcut="@annotation(com.common.db.DataSource)", throwing= "error")
public void pointCut(){ }
@Before("pointCut()")
public void before(JoinPoint joinPoint) {
Object target = joinPoint.getTarget();
MethodSignature signature = (MethodSignature) joinPoint.getSignature();
DataSourceEnum dataSource = DataSourceEnum.DEFAULT;
try {
Method method = target.getClass().getMethod(signature.getName(), signature.getParameterTypes());
if (method.isAnnotationPresent(DataSourceSet.class)) {
DataSourceSet annotation = method.getAnnotation(DataSourceSet.class);
dataSource = annotation.value();
}
} catch (NoSuchMethodException e) {
e.printStackTrace();
}
//实现aop切换数据源
System.out.println("aop切换数据源:" + dataSource.getName());
DataSourceHolder.setDataSource(dataSource);
}
// @After("pointCut()")
@AfterThrowing(pointcut="@annotation(com.common.db.DataSourceSet)", throwing= "error")
public void after() {
DataSourceHolder.clearDataSource();
String v = DataSourceHolder.getDataSource();
System.out.println(v);
}
}
DataSourceConfig
package com.common.db;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.jta.atomikos.AtomikosDataSourceBean;
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 org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.jta.JtaTransactionManager;
@Configuration
public class DataSourceConfig {
//连接数据库db1
@Bean("test1")
@ConfigurationProperties("spring.datasource.test1")
public DataSource test1() {
return DataSourceBuilder.create().build();
}
//连接数据库db2
@Bean("test2")
@ConfigurationProperties("spring.datasource.test2")
public DataSource test2() {
return DataSourceBuilder.create().build();
}
@Bean("dynamicDataSource")
@Primary
public DataSource dynamicDataSource() {
DynamicDataSource dataSource = new DynamicDataSource();
dataSource.setDefaultTargetDataSource(test1());
Map<Object, Object> dataSourceMap = new HashMap<>(2);
dataSourceMap.put(DataSourceEnum.TEST1.getName(), test1());
dataSourceMap.put(DataSourceEnum.TEST2.getName(), test2());
dataSource.setTargetDataSources(dataSourceMap);
return dataSource ;
}
@Bean
public PlatformTransactionManager transactionManager() {
return new DataSourceTransactionManager(dynamicDataSource());
}
}
DataSourceEnum
package com.common.db;
import lombok.AllArgsConstructor;
import lombok.Getter;
@AllArgsConstructor
@Getter
public enum DataSourceEnum {
DEFAULT("test1"),
TEST1("test1"),
TEST2("test2");
private String name;
}
DataSourceHolder
package com.common.db;
public class DataSourceHolder {
private static final ThreadLocal<String> DS_HOLDER = new ThreadLocal<>();
public static void setDataSource(DataSourceEnum dataSource) {
DS_HOLDER.set(dataSource.getName());
}
public static String getDataSource() {
return DS_HOLDER.get();
}
public static void clearDataSource() {
DS_HOLDER.remove();
}
}
DataSourceSet
package com.common.db;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
@Documented
public @interface DataSourceSet {
//枚举
DataSourceEnum value();
}
DynamicDataSource
package com.common.db;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
//基于aop切换数据源
System.out.println("dynamic datasource 切换数据源" + DataSourceHolder.getDataSource());
return DataSourceHolder.getDataSource();
}
}
测试调用
server层
@Override
@DataSourceSet(DataSourceEnum.TEST1)
public String test1(Long id) {
String s = programRepository.findids(id);
System.out.println(s);
return s;
}
@Override
@DataSourceSet(DataSourceEnum.TEST2)
public String test2(Long id) {
String s = testRepository.findids1(id);
System.out.println(s);
return s;
}
Controller层
@PostMapping("/test")
@ResponseBody
public ResultVo test(Long id) {
id = 1l;
String beProgram1 = programService.test1(id);
System.out.println(222);
id=2l;
String beProgram = testService.test2(id);
System.out.println(333);
return ResultVoUtil.success(beProgram+beProgram1);
}