记一次多数据源切换失效问题

事故现场

### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

### The error may exist in class path resource [mappers/scm/NCMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select name,code from bd_customer         where          name like '%%'  LIMIT ?
### Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:91)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
	at com.sun.proxy.$Proxy92.selectList(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)
	at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:139)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:76)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
	at com.sun.proxy.$Proxy187.findNCCustomerByKeyword(Unknown Source)
	at sun.reflect.GeneratedMethodAccessor1096.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
	at com.baomidou.dynamic.datasource.aop.DynamicDataSourceAnnotationInterceptor.invoke(DynamicDataSourceAnnotationInterceptor.java:50)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
	at com.sun.proxy.$Proxy188.findNCCustomerByKeyword(Unknown Source)
	at com.redsea.scm.controller.qc.QCController.ncCustomer(QCController.java:94)

自审

异常堆栈显示生成的sql不对，先说下背景，主数据源是mysql，副数据源是oracle，这个堆栈显示去查oracle数据库，但是用的分页是limit（mysql特有），说明生成分页语句有问题，带着这个疑问，想弄清楚分页mybaits插件是怎么实现的，为什么会选错数据源

调用代码

# controller
@GetMapping(value = "/aa")
public BaseResponse aa( String keyword, HttpServletRequest request){
    PageHelper.startPage(1, 20);
    List customers = ncMapper.aa(keyword);
    return BaseResponse.success(customers);
}

# Mapper.java
@DS("NCdata")
public interface NCMapper {
    List<NCCustomer> aa(@Param("k") String keyword);
}

# mapper.xml
<select id="aa" >
    select name,code from bd_customer
    where
     name like '%${k}%'
</select>

代码很简单 Bug很奇葩

怀疑出问题的点

1. @Ds DynamicDataSourceAnnotationInterceptor有问题
2. PageHelper.startPage(1, 20);

调试源码

PageHelper.startPage(1, 20);

    public static <E> Page<E> startPage(int pageNum, int pageSize, boolean count, Boolean reasonable, Boolean pageSizeZero) {
        Page<E> page = new Page<E>(pageNum, pageSize, count);
        page.setReasonable(reasonable);
        page.setPageSizeZero(pageSizeZero);
        //当已经执行过orderBy的时候
        Page<E> oldPage = getLocalPage();
        if (oldPage != null && oldPage.isOrderByOnly()) {
            page.setOrderBy(oldPage.getOrderBy());
        }
        setLocalPage(page);
        return page;
    }
    
    protected static final ThreadLocal<Page> LOCAL_PAGE = new ThreadLocal<Page>();

    protected static void setLocalPage(Page page) {
        LOCAL_PAGE.set(page);
    }

PageInterceptor 调用

初始化dialet

插件配置

单一数据源配置

多数据源正确配置

结论

1. @ds 是aop实现 没有问题
2. PageHelper 可以管理多个数据源而实现分页，默认只支持缓存单个数据源

# 增加配置
pagehelper.auto-runtime-dialect=true

参考 https://pagehelper.github.io/docs/howtouse/

感悟

1. 发现问题排查源码是解决问题根本指导
2. 官方配置多看几眼，有明确事例