解决AbstractRoutingDataSource无法切换数据源始终是默认数据源的问题
豆豆 2021-05-20 10:32:15 233人已围观
一、配置多数据源
我这里使用了oracle、mysql、tidb三类数据库,涉及敏感信息处被删减或者已经替换,数据源配置如下:
<!-- oracle数据源配置--> <bean id="oracleDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close" primary="true"> <!-- 数据源驱动类可不写,Druid默认会自动根据URL识别DriverClass --> <property name="driverClassName" value="${oracle.jdbc.driver}" /> <!-- 基本属性 url、user、password --> <property name="url" value="${oracle.jdbc.url}" /> <property name="username" value="${oracle.jdbc.username}" /> <property name="password" value="${oracle.jdbc.password}" /> <!-- 配置初始化大小、最小、最大 --> <property name="initialSize" value="${oracle.jdbc.pool.minIdle}" /> <property name="minIdle" value="${oracle.jdbc.pool.minIdle}" /> <property name="maxActive" value="${oracle.jdbc.pool.maxActive}" /> </bean> <!-- mysql数据源配置, --> <bean id="mysqlDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"> <!-- 数据源驱动类可不写,Druid默认会自动根据URL识别DriverClass --> <property name="driverClassName" value="${mysql.jdbc.driver}" /> <!-- 基本属性 url、user、password --> <property name="url" value="${mysql.jdbc.url}" /> <property name="username" value="${mysql.jdbc.username}" /> <property name="password" value="${mysql.jdbc.password}" /> <!-- 配置初始化大小、最小、最大 --> <property name="initialSize" value="${mysql.jdbc.pool.minIdle}" /> <property name="minIdle" value="${mysql.jdbc.pool.minIdle}" /> <property name="maxActive" value="${mysql.jdbc.pool.maxActive}" /> </bean> <!-- TIDB数据源配置,--> <bean id="tidbDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"> <!-- 数据源驱动类可不写,Druid默认会自动根据URL识别DriverClass --> <property name="driverClassName" value="${tidb.jdbc.driver}" /> <!-- 基本属性 url、user、password --> <property name="url" value="${tidb.jdbc.url}" /> <property name="username" value="${tidb.jdbc.username}" /> <property name="password" value="${tidb.jdbc.password}" /> <!-- 配置初始化大小、最小、最大 --> <property name="initialSize" value="${tidb.jdbc.pool.minIdle}" /> <property name="minIdle" value="${tidb.jdbc.pool.minIdle}" /> <property name="maxActive" value="${tidb.jdbc.pool.maxActive}" /> </bean>
二、继承AbstractRoutingDataSource类
public class DynamicDataSource extends AbstractRoutingDataSource { private static final ThreadLocal<SourcesEnum> dataSourceTypes = new InheritableThreadLocal<SourcesEnum>(); public static void set(SourcesEnum dataSourceType){ dataSourceTypes.set(dataSourceType); } public static String get(){ SourcesEnum dataSources=dataSourceTypes.get(); if(dataSources==null){ dataSources=SourcesEnum.oracle; } System.out.println("DynamicDataSource----get============初始化========dataSource="+dataSources.toString()); return dataSources.toString(); } public static void clear() { dataSourceTypes.remove(); } /** * 获取动态数据源 * @return */ @Override protected Object determineCurrentLookupKey() { String dataSources =get(); System.out.println("DynamicDataSource----determineCurrentLookupKey====================dataSource="+dataSources); return dataSources; } }
三、实现org.aopalliance.aop.Advice接口
/** * 多数据库AOP切换拦截器 * * */ @Aspect @Order(1) @EnableAspectJAutoProxy(proxyTargetClass = true) @Slf4j @Component public class DataSourceInterceptor implements Advice { public DataSourceInterceptor() { // log.debug("DataSourceInterceptor加载成功……"); System.out.println("DataSourceInterceptor加载成功……***********************************"); } @Pointcut("execution(* com.xxxx.xxx..*.*.manager..*.select*(..))") public void pointCut() { // log.info("init......."); System.out.println("pointCut init……***********************************"); } @Before("pointCut()") public void before(JoinPoint joinpoint) { try { Class<?> target = joinpoint.getTarget().getClass(); MethodSignature signature = (MethodSignature) joinpoint.getSignature(); Method method = signature.getMethod(); DataSource dataSource = null; //从类初始化 dataSource = this.getDataSource(target, method); //从接口初始化 if (dataSource == null) { for (Class<?> clazz : target.getInterfaces()) { dataSource = getDataSource(clazz, method); if (dataSource != null) { break;//从某个接口中一旦发现注解,不再循环 } } } if (dataSource != null) { DynamicDataSource.set(dataSource.value()); System.out.println("DataSourceInterceptor切换数据源*****"+method.getName()+"******************************"+DynamicDataSource.get()); } } catch (Exception e) { // log.error("切换数据源异常", e); System.out.println("DataSourceInterceptor切换数据源异常***********************************"+e.getMessage()); } } /** * 清理掉当前设置的数据源,让默认的数据源不受影响 * * @param joinpoint */ @After("pointCut()") public void after(JoinPoint joinpoint) { DynamicDataSource.clear(); System.out.println("DataSourceInterceptor切换数据源清理***********************************"); } /** * 获取方法或类的注解对象DataSource * * @param target 类class * @param method 方法 * @return DataSource */ public DataSource getDataSource(Class<?> target, Method method) { try { //1.优先方法注解 Class<?>[] types = method.getParameterTypes(); Method m = target.getMethod(method.getName(), types); if (m.isAnnotationPresent(DataSource.class)) { return m.getAnnotation(DataSource.class); } //2.其次类注解 if (target.isAnnotationPresent(DataSource.class)) { return target.getAnnotation(DataSource.class); } } catch (Exception e) { e.printStackTrace(); // log.error(MessageFormat.format("通过注解切换数据源时发生异常[class={0},method={1}]:" // , target.getName(), method.getName()), e); } return null; } }
四、事物级别配置(重点)
<bean id="dynamicDataSource" class="com.xxxxxx.datasource.DynamicDataSource"> <property name="targetDataSources"> <map key-type="java.lang.String"> <entry key="oracle" value-ref="oracleDataSource"/> <entry key="mysql" value-ref="mysqlDataSource"/> <entry key="tidb" value-ref="tidbDataSource"/> </map> </property> <property name="defaultTargetDataSource" ref="mysqlDataSource"/> </bean> <!-- MyBatis配置 --> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="configLocation" value="classpath:/mybatis/mybatis-config.xml" /> <property name="dataSource" ref="dynamicDataSource" /> <property name="typeAliasesPackage" value="com.xxxxx.**.*.entity" /> <property name="mapperLocations" value="classpath:/mybatis/xxx/*Mapper.xml" /> </bean> <!-- 扫描basePackage下所有以接口 --> <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <property name="basePackage" value="com.xxxxx.**.*.dao" /> <property name="annotationClass" value="com.xxxxxx.mybatis.MyBatisxxxxx" /> </bean> <tx:advice id="txAdvice" transaction-manager="transactionManager"> <tx:attributes> <tx:method name="select*" propagation="REQUIRES_NEW" /> <!-- 重点!重点!重点!--> </tx:attributes> </tx:advice> <aop:config> <aop:pointcut id="serviceOperation" expression="execution(* com.xxxx..*.*.service.*.*(..))" /> <aop:advisor advice-ref="txAdvice" pointcut-ref="serviceOperation" order="2"/> </aop:config> <aop:aspectj-autoproxy proxy-target-class="true"/>
五、调用方法
// count = testService.resultCountMain(sysMonitor);//此种方式不切换数据源 if(sysMonitor.getDbType()!=null&&sysMonitor.getDbType().equals("oracle")){ count = testService.selectCountOracle(sysMonitor); }else if(sysMonitor.getDbType()!=null&&sysMonitor.getDbType().equals("mysql")){ count = testService.selectCountMySQL(sysMonitor); }else if(sysMonitor.getDbType()!=null&&sysMonitor.getDbType().equals("tidb")){ count = testService.selectCountTiDB(sysMonitor); }
六、测试
DynamicDataSource----get============初始化========dataSource=oracle DynamicDataSource----determineCurrentLookupKey====================dataSource=oracle ************************开始测试******************************** DataSourceInterceptor加载成功……*********************************** DynamicDataSource----get============初始化========dataSource=oracle DataSourceInterceptor切换数据源*****selectAll******************************oracle DynamicDataSource----get============初始化========dataSource=oracle DynamicDataSource----determineCurrentLookupKey====================dataSource=oracle into******************************** SysMonitorManagerImpl.selectAll#方法 out********************************into SysMonitorManagerImpl.selectAll#方法 DataSourceInterceptor切换数据源清理*********************************** 循环开始:********************1/select count(1) from sms.sms_fact_acct t where t.date_acct >= trunc(sysdate-1) and date_acct < trunc(sysdate) and t.org_id not in (1000000, 1000008) and t.is_summary='Y'/oracle/oracleDataSource DynamicDataSource----get============初始化========dataSource=oracle DataSourceInterceptor切换数据源*****selectCountOracle******************************oracle DynamicDataSource----get============初始化========dataSource=oracle DynamicDataSource----determineCurrentLookupKey====================dataSource=oracle into********************************into SysMonitorManagerImpl.resultCountOracle#方法 out********************************into SysMonitorManagerImpl.resultCountOracle#方法 DataSourceInterceptor切换数据源清理*********************************** ****************************当前执行id=1的监控SQL,其Count=0 循环开始:********************44/SELECT count(1) FROM sms.sms_mysql_test t where 1=1/mysql/mysqlDataSource DynamicDataSource----get============初始化========dataSource=mysql DataSourceInterceptor切换数据源*****selectCountMySQL******************************mysql DynamicDataSource----get============初始化========dataSource=mysql DynamicDataSource----determineCurrentLookupKey====================dataSource=mysql into******************************** SysMonitorManagerImpl.selectCountMySQL#方法 out******************************** SysMonitorManagerImpl.selectCountMySQL#方法 DataSourceInterceptor切换数据源清理*********************************** ****************************当前执行id=44的监控SQL,其Count=0 循环开始:********************45/SELECT count(1) FROM sms.sms_tidb_test t where 1=1/tidb/tidbDataSource DynamicDataSource----get============初始化========dataSource=tidb DataSourceInterceptor切换数据源*****selectCountTiDB******************************tidb DynamicDataSource----get============初始化========dataSource=tidb DynamicDataSource----determineCurrentLookupKey====================dataSource=tidb into********************************SysMonitorManagerImpl.selectCountTiDB#方法 out********************************SysMonitorManagerImpl.selectCountTiDB#方法 DataSourceInterceptor切换数据源清理*********************************** ****************************当前执行id=45的监控SQL,其Count=0 ************************结束测试********************************
七、注意事项
1、spring事物传播级别!spring事物传播级别!spring事物传播级别!
附录:
1、https://blog.csdn.net/chiseyang1975/article/details/100858799
2、https://blog.csdn.net/qq_37061442/article/details/82350258
3、https://blog.csdn.net/puhaiyang/article/details/88013712
4、https://blog.csdn.net/u013632755/article/details/51557956
5、https://blog.csdn.net/jackycjw/article/details/80506340
6、https://my.oschina.net/u/4034639/blog/3079435
7、https://blog.csdn.net/qq_35844400/article/details/109614648
8、https://www.pianshen.com/article/750640592/
9、https://blog.csdn.net/chengmeng_123/article/details/85048890
分享到:
编辑发布时间:2021-05-20 10:32:15