2.5.1 boot版本如何兼容sqlserver？集成多数据源不生效

Blade 未结

关注举报

 1  1158

社会你方哥剑圣 2020-11-12 15:59

针对2.5.1的boot版本，兼容sqlserver需要做哪些升级？我配置了两种数据源，一个是mysql 一个是sqlserver。

经测试单数据源sqlserver,分页查询有问题，列表查询则没问题。

1、分页查询报SQL语法错如下：

打印的执行SQL如下：

-- 20 2020-11-12 16:49:45.678 DEBUG 27432 --- [p-nio-80-exec-6] c.d.a.w.mapper.TkdMapper.selectTkdPage : ==>

SELECT

COUNT(1)

FROM

tkd

WHERE

mark = 0;

-- ------------------------------------------------------------------------------------------------

-- 21 2020-11-12 16:49:45.707 DEBUG 27432 --- [p-nio-80-exec-6] c.d.a.w.mapper.TkdMapper.selectTkdPage : ==>

WITH selectTemp AS (SELECT

TOP 100 PERCENT ROW_NUMBER() OVER (

ORDER BY

tkrq DESC) as __row_number__,

sn,

tkbh

FROM

tkd

WHERE

mark = 0

ORDER BY

tkrq DESC) SELECT

FROM

selectTemp

WHERE

__row_number__ BETWEEN 1 AND 10

ORDER BY

__row_number__;

-- ------------------------------------------------------------------------------------------------

2、列表查询结果正确返回：

集成了多数据源的时候去测试sqlserver发生了以下两种问题：

1、分页查询接口报错：

2、列表查询接口报错：报ems.tkd 表不存在，其实ems是我mysql的库，感觉没有真正连上我的sqlserver数据库。

多数据源配置文件如下：

启动如上图所示，两个数据源以及加载成功，我测试了一下简单的单表分页查询和列表查询，源代码如下：

调用分页查询接口控制台报错如下：

2020-11-12 15:52:36.534 INFO 3724 --- [p-nio-80-exec-7] c.dmf.core.log.aspect.RequestLogAspect :

================ Request Start ================

===> GET: /wuzi/tkd/page Parameters: {"query":{"current":1,"size":10,"ascs":null,"descs":null},"tkd":{"sn":null,"tkbh":null}}

===Headers=== host: localhost

===Headers=== connection: keep-alive

===Headers=== cache-control: max-age=0

===Headers=== upgrade-insecure-requests: 1

===Headers=== user-agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.183 Safari/537.36

===Headers=== accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9

===Headers=== sec-fetch-site: none

===Headers=== sec-fetch-mode: navigate

===Headers=== sec-fetch-user: ?1

===Headers=== sec-fetch-dest: document

===Headers=== accept-encoding: gzip, deflate, br

===Headers=== accept-language: zh-CN,zh;q=0.9,en;q=0.8

================ Request End ================

2020-11-12 15:52:36.536 DEBUG 3724 --- [p-nio-80-exec-7] c.d.a.w.mapper.TkdMapper.selectTkdPage : ==> Preparing: select sn, tkbh from tkd where mark = 0 order by tkrq desc

2020-11-12 15:52:36.538 DEBUG 3724 --- [p-nio-80-exec-7] com.dmf.core.tenant.EmsTenantSqlParser : Original SQL: select sn,

tkbh

from tkd

where mark = 0

order by tkrq desc

2020-11-12 15:52:36.538 DEBUG 3724 --- [p-nio-80-exec-7] com.dmf.core.tenant.EmsTenantSqlParser : parser sql: SELECT sn, tkbh FROM tkd WHERE mark = 0 ORDER BY tkrq DESC

2020-11-12 15:52:36.539 DEBUG 3724 --- [p-nio-80-exec-7] c.d.a.w.mapper.TkdMapper.selectTkdPage : ==> Preparing: SELECT COUNT(1) FROM tkd WHERE mark = 0

2020-11-12 15:52:36.539 DEBUG 3724 --- [p-nio-80-exec-7] c.d.a.w.mapper.TkdMapper.selectTkdPage : ==> Parameters:

2020-11-12 15:52:36.551 INFO 3724 --- [p-nio-80-exec-7] c.dmf.core.log.aspect.RequestLogAspect :

================ Response Start ================

<=== GET: /wuzi/tkd/page (17 ms)

================ Response End ================

2020-11-12 15:52:36.553 ERROR 3724 --- [p-nio-80-exec-7] c.d.c.l.e.EmsRestExceptionTranslator : 服务器异常

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException:

### Error querying database. Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Error: Method queryTotal execution error of sql :

SELECT COUNT(1) FROM tkd WHERE mark = 0

### The error may exist in file [F:\api-workspace\api-server\target\classes\com\dmf\api\wuzi\mapper\TkdMapper.xml]

### The error may involve defaultParameterMap

### The error occurred while setting parameters

### SQL: select sn, tkbh from tkd where mark = 0 order by tkrq desc

### Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Error: Method queryTotal execution error of sql :

SELECT COUNT(1) FROM tkd WHERE mark = 0

at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)

at com.sun.proxy.$Proxy130.selectList(Unknown Source)

at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223)

at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:177)

at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:78)

at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:96)

at com.sun.proxy.$Proxy131.selectTkdPage(Unknown Source)

at com.dmf.api.wuzi.service.impl.TkdServiceImpl.selectTkdPage1(TkdServiceImpl.java:34)

at com.dmf.api.wuzi.service.impl.TkdServiceImpl.selectTkdPage(TkdServiceImpl.java:23)

at com.dmf.api.wuzi.service.impl.TkdServiceImpl$$FastClassBySpringCGLIB$$3163d5ee.invoke(<generated>)

at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)

at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:687)

at com.dmf.api.wuzi.service.impl.TkdServiceImpl$$EnhancerBySpringCGLIB$$b9c88573.selectTkdPage(<generated>)

at com.dmf.api.wuzi.controller.TkdController.page(TkdController.java:40)

at com.dmf.api.wuzi.controller.TkdController$$FastClassBySpringCGLIB$$adbad966.invoke(<generated>)

at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:752)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)

at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:88)

at com.dmf.core.log.aspect.RequestLogAspect.aroundApi(RequestLogAspect.java:95)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:644)

at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:633)

at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)

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:691)

at com.dmf.api.wuzi.controller.TkdController$$EnhancerBySpringCGLIB$$7548b798.page(<generated>)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)

at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)

at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105)

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:892)

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:797)

at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)

at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)

at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)

at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:645)

at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:750)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

at com.github.xiaoymin.knife4j.spring.filter.ProductionSecurityFilter.doFilter(ProductionSecurityFilter.java:53)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

at com.github.xiaoymin.knife4j.spring.filter.SecurityBasicAuthFilter.doFilter(SecurityBasicAuthFilter.java:90)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

at com.dmf.core.tool.request.EmsRequestFilter.doFilter(EmsRequestFilter.java:33)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

at com.dmf.core.log.filter.LogTraceFilter.doFilter(LogTraceFilter.java:23)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:124)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:94)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)

at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)

at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374)

at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)

at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)

at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1590)

at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

at java.lang.Thread.run(Thread.java:748)

Caused by: org.apache.ibatis.exceptions.PersistenceException:

### Error querying database. Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Error: Method queryTotal execution error of sql :

SELECT COUNT(1) FROM tkd WHERE mark = 0

### The error may exist in file [F:\api-workspace\api-server\target\classes\com\dmf\api\wuzi\mapper\TkdMapper.xml]

### The error may involve defaultParameterMap

### The error occurred while setting parameters

### SQL: select sn, tkbh from tkd where mark = 0 order by tkrq desc

### Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Error: Method queryTotal execution error of sql :

SELECT COUNT(1) FROM tkd WHERE mark = 0

at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:149)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)

... 97 common frames omitted

Caused by: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Error: Method queryTotal execution error of sql :

SELECT COUNT(1) FROM tkd WHERE mark = 0

at com.baomidou.mybatisplus.core.toolkit.ExceptionUtils.mpe(ExceptionUtils.java:39)

at com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor.queryTotal(PaginationInterceptor.java:253)

at com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor.intercept(PaginationInterceptor.java:201)

at com.dmf.core.mp.plugins.EmsPaginationInterceptor.intercept(EmsPaginationInterceptor.java:33)

at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)

at com.sun.proxy.$Proxy426.prepare(Unknown Source)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)

at com.sun.proxy.$Proxy426.prepare(Unknown Source)

at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.prepareStatement(MybatisSimpleExecutor.java:92)

at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:66)

at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)

at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)

at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:136)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)

... 103 common frames omitted

Caused by: java.sql.SQLSyntaxErrorException: Table 'ems.tkd' 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:953)

at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1003)

at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:3240)

at com.alibaba.druid.wall.WallFilter.preparedStatement_executeQuery(WallFilter.java:647)

at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:3237)

at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeQuery(FilterEventAdapter.java:465)

at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:3237)

at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.executeQuery(PreparedStatementProxyImpl.java:181)

at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeQuery(DruidPooledPreparedStatement.java:227)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:56)

at com.sun.proxy.$Proxy428.executeQuery(Unknown Source)

at com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor.queryTotal(PaginationInterceptor.java:242)

... 119 common frames omitted

2020-11-12 15:52:36.556 DEBUG 3724 --- [sync-executor-1] c.d.m.s.mapper.LogErrorMapper.insert : ==> Preparing: INSERT INTO t_st_log_error ( id, stack_trace, exception_name, message, file_name, line_number, service_id, server_ip, server_host, env, remote_ip, user_agent, request_uri, method, method_class, method_name, params, create_by, create_time ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )

2020-11-12 15:52:36.557 DEBUG 3724 --- [sync-executor-1] com.dmf.core.tenant.EmsTenantSqlParser : Original SQL: INSERT INTO t_st_log_error ( id,

stack_trace,

exception_name,

message,

file_name,

line_number,

service_id,

server_ip,

server_host,

env,

remote_ip,

user_agent,

request_uri,

method,

method_class,

method_name,

params,

create_by,

create_time ) VALUES ( ?,

? )

2020-11-12 15:52:36.558 DEBUG 3724 --- [sync-executor-1] com.dmf.core.tenant.EmsTenantSqlParser : parser sql: INSERT INTO t_st_log_error (id, stack_trace, exception_name, message, file_name, line_number, service_id, server_ip, server_host, env, remote_ip, user_agent, request_uri, method, method_class, method_name, params, create_by, create_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

2020-11-12 15:52:36.558 DEBUG 3724 --- [sync-executor-1] c.d.m.s.mapper.LogErrorMapper.insert : ==> Preparing: INSERT INTO t_st_log_error (id, stack_trace, exception_name, message, file_name, line_number, service_id, server_ip, server_host, env, remote_ip, user_agent, request_uri, method, method_class, method_name, params, create_by, create_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

2020-11-12 15:52:36.567 DEBUG 3724 --- [sync-executor-1] c.d.m.s.mapper.LogErrorMapper.insert : ==> Parameters: 1326795013585199106(Long), org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: