使用注解解決ShardingJdbc不支持復(fù)雜SQL方法

第使用注解解決ShardingJdbc不支持復(fù)雜SQL方法目錄背景介紹問題復(fù)現(xiàn)解決思路具體實現(xiàn)編寫autoConfig類自定義數(shù)據(jù)源aop攔截器：aop切面定義ThreadLocal啟動類配置

背景介紹

公司最近做分庫分表業(yè)務(wù)，接入了ShardingJDBC，接入完成后，回歸測試時發(fā)現(xiàn)好幾個SQL執(zhí)行報錯，關(guān)鍵這幾個表都還不是分片表。

報錯如下：

這下糟了嘛。熟悉ShardingJDBC的同學(xué)應(yīng)該知道，有很多SQL它是不支持的。官方截圖如下：

如果要去修改這些復(fù)雜SQL的話，可能要花費很多時間。那怎么辦呢？只能從ShardingJDBC這里找突破口了，兩天的研究，出來了下面這個只需要加一個注解輕松解決ShardingJdbc不支持復(fù)雜SQL的方案。

問題復(fù)現(xiàn)

我本地寫了一個復(fù)雜SQL進行測試：

publicListMapString,ObjectqueryOrder(){

ListMapString,Objectorders=borderRepository.findOrders();

returnorders;

publicinterfaceBOrderRepositoryextendsJpaRepositoryBOrder,Long{

@Query(value="SELECT*FROM(SELECTid,CASEWHENcompany_id=1THEN'小'WHENcompany_id=4THEN'中'ELSE'大'ENDAScom,user_idasuserIdFROMb_order0)tWHERE='中'",nativeQuery=true)

ListMapString,ObjectfindOrders();

寫了個測試controller來調(diào)用，調(diào)用后果然報錯了。

解決思路

因為查詢的復(fù)雜SQL的表不是分片表，那能不能指定這幾個復(fù)雜查詢的時候不用ShardingJDBC的數(shù)據(jù)源呢？

在注入ShardingJDBC數(shù)據(jù)源的地方做處理，注入一個我們自定義的數(shù)據(jù)源這樣我們獲取連接的時候就能返回原生數(shù)據(jù)源了另外我們聲明一個注解，對標(biāo)識了注解的就返回原生數(shù)據(jù)源，否則還是返回Sharding數(shù)據(jù)源

具體實現(xiàn)

編寫autoConfig類

編寫一個autoConfig類，來替換ShardingSphereAutoConfiguration類

/**

*動態(tài)數(shù)據(jù)源核心自動配置類

@Configuration

@ComponentScan("org.apache.shardingsphere.spring.boot.converter")

@EnableConfigurationProperties(SpringBootPropertiesConfiguration.class)

@ConditionalOnProperty(prefix="spring.shardingsphere",name="enabled",havingValue="true",matchIfMissing=true)

@AutoConfigureBefore(DataSourceAutoConfiguration.class)

publicclassDynamicDataSourceAutoConfigurationimplementsEnvironmentAware{

privateStringdatabaseName;

privatefinalSpringBootPropertiesConfigurationprops;

privatefinalMapString,DataSourcedataSourceMap=newLinkedHashMap();

publicDynamicDataSourceAutoConfiguration(SpringBootPropertiesConfigurationprops){

ps=props;

*Getmodeconfiguration.

*@returnmodeconfiguration

@Bean

publicModeConfigurationmodeConfiguration(){

returnnull==props.getMode()null:newModeConfigurationYamlSwapper().swapToObject(props.getMode());

*GetShardingSpheredatasourcebean.

*@paramrulesrulesconfiguration

*@parammodeConfigmodeconfiguration

*@returndatasourcebean

*@throwsSQLExceptionSQLexception

@Bean

@Conditional(LocalRulesCondition.class)

@Autowired(required=false)

publicDataSourceshardingSphereDataSource(finalObjectProviderListRuleConfigurationrules,finalObjectProviderModeConfigurationmodeConfig)throwsSQLException{

CollectionRuleConfigurationruleConfigs=Optional.ofNullable(rules.getIfAvailable()).orElseGet(Collections::emptyList);

DataSourcedataSource=ShardingSphereDataSourceFactory.createDataSource(databaseName,modeConfig.getIfAvailable(),dataSourceMap,ruleConfigs,props.getProps());

returnnewWrapShardingDataSource((ShardingSphereDataSource)dataSource,dataSourceMap);

*Getdatasourcebeanfromregistrycenter.

*@parammodeConfigmodeconfiguration

*@returndatasourcebean

*@throwsSQLExceptionSQLexception

@Bean

@ConditionalOnMissingBean(DataSource.class)

publicDataSourcedataSource(finalModeConfigurationmodeConfig)throwsSQLException{

DataSourcedataSource=!dataSourceMap.isEmpty()ShardingSphereDataSourceFactory.createDataSource(databaseName,modeConfig,dataSourceMap,Collections.emptyList(),props.getProps())

:ShardingSphereDataSourceFactory.createDataSource(databaseName,modeConfig);

returnnewWrapShardingDataSource((ShardingSphereDataSource)dataSource,dataSourceMap);

*Createtransactiontypescanner.

*@returntransactiontypescanner

@Bean

publicTransactionTypeScannertransactionTypeScanner(){

returnnewTransactionTypeScanner();

@Override

publicfinalvoidsetEnvironment(finalEnvironmentenvironment){

dataSourceMap.putAll(DataSourceMapSetter.getDataSourceMap(environment));

databaseName=DatabaseNameSetter.getDatabaseName(environment);

@Role(BeanDefinition.ROLE_INFRASTRUCTURE)

@Bean

@ConditionalOnProperty(prefix="spring.datasource.dynamic.aop",name="enabled",havingValue="true",matchIfMissing=true)

publicAdvisordynamicDatasourceAnnotationAdvisor(){

DynamicDataSourceAnnotationInterceptorinterceptor=newDynamicDataSourceAnnotationInterceptor(true);

DynamicDataSourceAnnotationAdvisoradvisor=newDynamicDataSourceAnnotationAdvisor(interceptor,DS.class);

returnadvisor;

自定義數(shù)據(jù)源

publicclassWrapShardingDataSourceextendsAbstractDataSourceAdapterimplementsAutoCloseable{

privateShardingSphereDataSourcedataSource;

privateMapString,DataSourcedataSourceMap;

publicWrapShardingDataSource(ShardingSphereDataSourcedataSource,MapString,DataSourcedataSourceMap){

this.dataSource=dataSource;

this.dataSourceMap=dataSourceMap;

publicDataSourcegetTargetDataSource(){

Stringpeek=DynamicDataSourceContextHolder.peek();

if(StringUtils.isEmpty(peek)){

returndataSource;

returndataSourceMap.get(peek);

@Override

publicConnectiongetConnection()throwsSQLException{

returngetTargetDataSource().getConnection();

@Override

publicConnectiongetConnection(finalStringusername,finalStringpassword)throwsSQLException{

returngetConnection();

@Override

publicvoidclose()throwsException{

DataSourcetargetDataSource=getTargetDataSource();

if(targetDataSourceinstanceofAutoCloseable){

((AutoCloseable)targetDataSource).close();

@Override

publicintgetLoginTimeout()throwsSQLException{

DataSourcetargetDataSource=getTargetDataSource();

returntargetDataSource==null0:targetDataSource.getLoginTimeout();

@Override

publicvoidsetLoginTimeout(finalintseconds)throwsSQLException{

DataSourcetargetDataSource=getTargetDataSource();

targetDataSource.setLoginTimeout(seconds);

聲明指定數(shù)據(jù)源注解

@Target({ElementType.TYPE,ElementType.METHOD})

@Retention(RetentionPolicy.RUNTIME)

@Documented

public@interfaceDS{

*數(shù)據(jù)源名

Stringvalue();

另外使用AOP的方式攔截使用了注解的類或方法，并且要將這些用了注解的方法存起來，在獲取數(shù)據(jù)源連接的時候取出來進行判斷。這就還要用到ThreadLocal。

aop攔截器：

publicclassDynamicDataSourceAnnotationInterceptorimplementsMethodInterceptor{

privatefinalDataSourceClassResolverdataSourceClassResolver;

publicDynamicDataSourceAnnotationInterceptor(BooleanallowedPublicOnly){

dataSourceClassResolver=newDataSourceClassResolver(allowedPublicOnly);

@Override

publicObjectinvoke(MethodInvocationinvocation)throwsThrowable{

StringdsKey=determineDatasourceKey(invocation);

DynamicDataSourceContextHolder.push(dsKey);

try{

returnceed();

}finally{

DynamicDataSourceContextHolder.poll();

privateStringdetermineDatasourceKey(MethodInvocationinvocation){

Stringkey=dataSourceClassResolver.findKey(invocation.getMethod(),invocation.getThis());

returnkey;

aop切面定義

/**

*aopAdvisor

publicclassDynamicDataSourceAnnotationAdvisorextendsAbstractPointcutAdvisorimplementsBeanFactoryAware{

privatefinalAdviceadvice;

privatefinalPointcutpointcut;

privatefinalClassextendsAnnotationannotation;

publicDynamicDataSourceAnnotationAdvisor(MethodInterceptoradvice,

ClassextendsAnnotationannotation){

this.advice=advice;

this.annotation=annotation;

this.pointcut=buildPointcut();

@Override

publicPointcutgetPointcut(){

returnthis.pointcut;

@Override

publicAdvicegetAdvice(){

returnthis.advice;

@Override

publicvoidsetBeanFactory(BeanFactorybeanFactory)throwsBeansException{

if(this.adviceinstanceofBeanFactoryAware){

((BeanFactoryAware)this.advice).setBeanFactory(beanFactory);

privatePointcutbuildPointcut(){

Pointcutcpc=newAnnotationMatchingPointcut(annotation,true);

Pointcutmpc=newAnnotationMethodPoint(annotation);

returnnewComposablePointcut(cpc).union(mpc);

*Inordertobecompatiblewiththespringlowerthan5.0

privatestaticclassAnnotationMethodPointimplementsPointcut{

privatefinalClassextendsAnnotationannotationType;

publicAnnotationMethodPoint(ClassextendsAnnotationannotationType){

Assert.notNull(annotationType,"Annotationtypemustnotbenull");

this.annotationType=annotationType;

@Override

publicClassFiltergetClassFilter(){

returnClassFilter.TRUE;

@Override

publicMethodMatchergetMethodMatcher(){

returnnewAnnotationMethodMatcher(annotationType);

privatestaticclassAnnotationMethodMatcherextendsStaticMethodMatcher{

privatefinalClassextendsAnnotationannotationType;

publicAnnotationMethodMatcher(ClassextendsAnnotationannotationType){

this.annotationType=annotationType;

@Override

publicbooleanmatches(Methodmethod,ClasstargetClass){

if(matchesMethod(method)){

returntrue;

//Proxyclassesneverhaveannotationsontheirredeclaredmethods.

if(Proxy.isProxyClass(targetClass)){

returnfalse;

//Themethodmaybeonaninterface,solet'scheckonthetargetclassaswell.

MethodspecificMethod=AopUtils.getMostSpecificMethod(method,targetClass);

return(specificMethod!=methodmatchesMethod(specificMethod));

privatebooleanmatchesMethod(Methodmethod){

returnAnnotatedElementUtils.hasAnnotation(method,this.annotationType);

/**

*數(shù)據(jù)源解析器

publicclassDataSourceClassResolver{

privatestaticbooleanmpEnabled=false;

privatestaticFieldmapperInterfaceField;

static{

ClassproxyClass=null;

try{

proxyClass=Class.forName("com.baomidou.mybatisplus.core.override.MybatisMapperProxy");

}catch(ClassNotFoundExceptione1){

try{

proxyClass=Class.forName("com.baomidou.mybatisplus.core.override.PageMapperProxy");

}catch(ClassNotFoundExceptione2){

try{

proxyClass=Class.forName("org.apache.ibatis.binding.MapperProxy");

}catch(ClassNotFoundExceptionignored){

if(proxyClass!=null){

try{

mapperInterfaceField=proxyClass.getDeclaredField("mapperInterface");

mapperInterfaceField.setAccessible(true);

mpEnabled=true;

}catch(NoSuchFieldExceptione){

e.printStackTrace();

*緩存方法對應(yīng)的數(shù)據(jù)源

privatefinalMapObject,StringdsCache=newConcurrentHashMap();

privatefinalbooleanallowedPublicOnly;

*加入擴展,給外部一個修改aop條件的機會

*@paramallowedPublicOnly只允許公共的方法,默認(rèn)為true

publicDataSourceClassResolver(booleanallowedPublicOnly){

this.allowedPublicOnly=allowedPublicOnly;

*從緩存獲取數(shù)據(jù)

*@parammethod方法

*@paramtargetObject目標(biāo)對象

*@returnds

publicStringfindKey(Methodmethod,ObjecttargetObject){

if(method.getDeclaringClass()==Object.class){

return"";

ObjectcacheKey=newMethodClassKey(method,targetObject.getClass());

Stringds=this.dsCache.get(cacheKey);

if(ds==null){

ds=computeDatasource(method,targetObject);

if(ds==null){

ds="";

this.dsCache.put(cacheKey,ds);

returnds;

*查找注解的順序

*1.當(dāng)前方法

*2.橋接方法

*3.當(dāng)前類開始一直找到Object

*4.支持mybatis-plus,mybatis-spring

*@parammethod方法

*@paramtargetObject目標(biāo)對象

*@returnds

privateStringcomputeDatasource(Methodmethod,ObjecttargetObject){

if(allowedPublicOnly!Modifier.isPublic(method.getModifiers())){

returnnull;

//1.從當(dāng)前方法接口中獲取

StringdsAttr=findDataSourceAttribute(method);

if(dsAttr!=null){

returndsAttr;

ClasstargetClass=targetObject.getClass();

ClassuserClass=ClassUtils.getUserClass(targetClass);

//JDK代理時,獲取實現(xiàn)類的方法聲明.method:接口的方法,specificMethod:實現(xiàn)類方法

MethodspecificMethod=ClassUtils.getMostSpecificMethod(method,userClass);

specificMethod=BridgeMethodResolver.findBridgedMethod(specificMethod);

//2.從橋接方法查找

dsAttr=findDataSourceAttribute(specificMethod);

if(dsAttr!=null){

returndsAttr;

//從當(dāng)前方法聲明的類查找

dsAttr=findDataSourceAttribute(userClass);

if(dsAttr!=nullClassUtils.isUserLevelMethod(method)){

returndsAttr;

//since3.4.1從接口查找，只取第一個找到的

for(ClassinterfaceClazz:ClassUtils.getAllInterfacesForClassAsSet(userClass)){

dsAttr=findDataSourceAttribute(interfaceClazz);

if(dsAttr!=null){

returndsAttr;

//如果存在橋接方法

if(specificMethod!=method){

//從橋接方法查找

dsAttr=findDataSourceAttribute(method);

if(dsAttr!=null){

returndsAttr;

//從橋接方法聲明的類查找

dsAttr=findDataSourceAttribute(method.getDeclaringClass());

if(dsAttr!=nullClassUtils.isUserLevelMethod(method)){

returndsAttr;

returngetDefaultDataSourceAttr(targetObject);

*默認(rèn)的獲取數(shù)據(jù)源名稱方式

*@paramtargetObject目標(biāo)對象

*@returnds

privateStringgetDefaultDataSourceAttr(ObjecttargetObject){

ClasstargetClass=targetObject.getClass();

//如果不是代理類,從當(dāng)前類開始,不斷的找父類的聲明

if(!Proxy.isProxyClass(targetClass)){

ClasscurrentClass=targetClass;

while(currentClass!=Object.class){

StringdatasourceAttr=findDataSourceAttribute(currentClass);

if(datasourceAttr!=null){

returndatasourceAttr;

currentClass=currentClass.getSuperclass();

//mybatis-plus,mybatis-spring的獲取方式

if(mpEnabled){

finalClassclazz=getMapperInterfaceClass(targetObject);

if(clazz!=null){

StringdatasourceAttr=findDataSourceAttribute(clazz);

if(datasourceAttr!=null){

returndatasourceAttr;

//嘗試從其父接口獲取

returnfindDataSourceAttribute(clazz.getSuperclass());

returnnull;

*用于處理嵌套代理

*@paramtargetJDK代理類對象

*@returnInvocationHandler的Class

privateClassgetMapperInterfaceClass(Objecttarget){

Objectcurrent=target;

while(Proxy.isProxyClass(current.getClass())){

ObjectcurrentRefObject=AopProxyUtils.getSingletonTarget(current);

if(currentRefObject==null){

break;

current=currentRefObject;

try{

if(Proxy.isProxyClass(current.getClass())){

return(Class)mapperInterfaceField.get(Proxy.getInvocationHandler(current));

}catch(IllegalAccessExceptionignore){

returnnull;

*通過AnnotatedElement查找標(biāo)記的注解,映射為DatasourceHolder

*@paramaeAnnotatedElement

*@return數(shù)據(jù)源映射持有者

privateStringfindDataSourceAttribute(AnnotatedElementae){

AnnotationAttributesattributes=AnnotatedElementUtils.getMergedAnnotationAttributes(ae,DS.class);

if(attributes!=null){

returnattributes.getString("value");

returnnull;

ThreadLocal

publicfinalclassDynamicDataSourceContextHolder{

*為什么要用鏈表存儲(準(zhǔn)確的是棧)

*pre

*為了支持嵌套切換，如ABC三個service都是不同的數(shù)據(jù)源

*其中A的某個業(yè)務(wù)要調(diào)B的方法，B的方法需要調(diào)用C的方法。一級一級調(diào)用切換，形成了鏈。

*傳統(tǒng)的只設(shè)置當(dāng)前線程的方式不能滿足此業(yè)務(wù)需求，必須使用棧，后進先出。

*/pre

privatestaticfinalThreadLocalDequeStringLOOKUP_KEY_HOLDER=newNamedThreadLocalDequeSt