【SQL问题】SQLSyntaxErrorException

故障解决方式

在DaoImpl层加上@DataSource注释

@DataSource(DatasourceConstant.PAYMENT)

故障环境介绍

项目	描述
数据库类型	mysql
框架	spring boot
问题范围	多数据源问题

故障发生现象及报错信息

org.springframework.jdbc.BadSqlGrammarException:
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Table 'credittrans.tp_trade_order' doesn't exist
### The error may exist in file [D:\develophangying\hahu\evidence\target\classes\mybatis\mapper\payment\apper.xml]
### The error may involve com.hf.innet.cot.evice.maper.payment.TpTreOrderMapper.selectByExample-Inline
### The error occurred while setting parameters
### SQL: select                        bank_id, biz_type, biz_value, biz_ext_data, amount, actual_amount, source_type, channel,      uuid, start_time, current_action,   resp_code, resp_desc, cnaps_msgid         from tp_trade_order                         WHERE (  loan_order_id = ?                                                                and source_type = ? )
### Cause: java.sql.SQLSyntaxErrorException: Table 'credittrans.tp_trade_order' doesn't exist
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Table 'credittrans.tp_trade_order' doesn't exist
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:234)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
    at com.sun.proxy.$Proxy84.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.$Proxy93.selectByExample(Unknown Source)
    at com.hyxf.internet.court.evidence.dal.dao.payment.impl.TpTradeOrderDaoImpl.findLoanSerialByLoanInvoiceId(TpTradeOrderDaoImpl.java:43)
    at com.hyxf.internet.court.evidence.dal.dao.payment.impl.TpTradeOrderDaoImpl$$FastClassBySpringCGLIB$$9c693bea.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
    at com.hyxf.internet.court.evidence.dal.dao.payment.impl.TpTradeOrderDaoImpl$$EnhancerBySpringCGLIB$$be08bf81.findLoanSerialByLoanInvoiceId(<generated>)
    at com.hyxf.internet.court.evidence.service.product.EnjoyLoanService.createLoanSerial(EnjoyLoanService.java:292)
    at com.hyxf.internet.court.evidence.service.AbstractTemplateProduct$ParallelTask$6.compute(AbstractTemplateProduct.java:182)
    at com.hyxf.internet.court.evidence.service.AbstractTemplateProduct$ParallelTask$6.compute(AbstractTemplateProduct.java:178)
    at java.util.concurrent.RecursiveTask.exec(RecursiveTask.java:94)
    at java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:289)
    at java.util.concurrent.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1056)
    at java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1692)
    at java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:157)
Caused by: java.sql.SQLSyntaxErrorException: Table 'credittrans.tp_trade_order' doesn't exist
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:955)
    at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:372)
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3051)
    at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3049)
    at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
    at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

故障分析及解决过程

问题的关键是:"Table 'credittrans.tp_trade_order' doesn't exist" ,查找数据库会发现,[credittrans]库下面没有[tp_trade_order]这张表,从而排查到是数据源配置错误,使用的默认数据源进行的查询

关于多数据源可以查看:
寻找风口的猪:
mybatis用spring的动态数据源实现读写分离
以及
使用springboot + druid + mybatisplus完成多数据源配置
如何配置Springboot多数据源

总结

遇到这样的问题,还是需要理解清楚,多数据源的原理.大致实现过程,才更容易排查问题

life is beautiful，我是Alon，如果你有问题，欢迎给我留言。