druid的1.2.9版本jar包parser不支持oracle的within GROUP语法
项目中采用1.2.16版本的druid jar包，后台打印出的日志里有一段sql报语法错误。但是在控制台中将sql完整粘贴到数据库客户端中执行运行正常。
sql中使用了oracle的LISTAGG...WITHIN GROUP...语法.
具体报错sql如下:
SELECT
soe.org_id,
soe.ext_type,
LISTAGG ( soe.ext_value, ',' ) WITHIN GROUP ( ORDER BY soe.ext_value ) ext_value,
LISTAGG ( to_char( ss.staff_name ), ',' ) WITHIN GROUP ( ORDER BY ss.staff_name ) ext_value_show
FROM
sys_org_ext soe,
sys_staff ss
WHERE
soe.ext_value = ss.staff_id
AND soe.org_id = ?
AND soe.ext_type IN ( 'DE1', 'DE2', 'DE3', 'DE4', 'DE5', 'DE6', 'DE7', 'DE9' )
GROUP BY
soe.org_id,
soe.ext_type UNION
SELECT
soe.org_id,
soe.ext_type,
soe.ext_value ext_value,
'' ext_value_show
FROM
sys_org_ext soe
WHERE
soe.org_id = ?
AND soe.ext_type IN ( 'DP_TYPE', 'DE8', 'DE10' ) UNION
SELECT
soe.org_id,
soe.ext_type,
soe.ext_value,
'' ext_value_show
FROM
sys_org_ext soe
WHERE
soe.org_id = ?
AND soe.ext_type NOT IN ( 'DE1', 'DE2', 'DE3', 'DE4', 'DE5', 'DE6', 'DE7', 'DE8', 'DE9', 'DE10', 'DP_TYPE' )
部分报错信息为:
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:200) ~[SQLStatementParser.class:?]
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:101) ~[SQLStatementParser.class:?]
at com.alibaba.druid.sql.visitor.ParameterizedOutputVisitorUtils.parameterize(ParameterizedOutputVisitorUtils.java:163) ~[ParameterizedOutputVisitorUtils.class:?]
at com.alibaba.druid.sql.visitor.ParameterizedOutputVisitorUtils.parameterize(ParameterizedOutputVisitorUtils.java:134) ~[ParameterizedOutputVisitorUtils.class:?]
at com.alibaba.druid.filter.stat.StatFilter.mergeSql(StatFilter.java:148) [StatFilter.class:?]
at com.alibaba.druid.filter.stat.StatFilter.createSqlStat(StatFilter.java:672) [StatFilter.class:?]
at com.alibaba.druid.filter.stat.StatFilter.statementPrepareAfter(StatFilter.java:325) [StatFilter.class:?]
at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:118) [FilterEventAdapter.class:?]
at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:531) [FilterChainImpl.class:?]
at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:326) [ConnectionProxyImpl.class:?]
at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:362) [DruidPooledConnection.class:?]
at sun.reflect.GeneratedMethodAccessor162.invoke(Unknown Source) ~[?:?]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_261]
at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_261]
at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:55) [ConnectionLogger.class:3.4.1]
at com.sun.proxy.$Proxy67.prepareStatement(Unknown Source) [?:?]
at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:87) [PreparedStatementHandler.class:3.4.1]
at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88) [BaseStatementHandler.class:3.4.1]
at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59) [RoutingStatementHandler.class:3.4.1]
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:85) [SimpleExecutor.class:3.4.1]
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62) [SimpleExecutor.class:3.4.1]
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324) [BaseExecutor.class:3.4.1]
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) [BaseExecutor.class:3.4.1]
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) [CachingExecutor.class:3.4.1]
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83) [CachingExecutor.class:3.4.1]
at sun.reflect.GeneratedMethodAccessor161.invoke(Unknown Source) ~[?:?]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_261]
at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_261]
at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49) [Invocation.class:3.4.1]
at com.github.pagehelper.SqlUtil._processPage(SqlUtil.java:401) [SqlUtil.class:?]
at com.github.pagehelper.SqlUtil.processPage(SqlUtil.java:374) [SqlUtil.class:?]
at com.github.pagehelper.PageHelper.intercept(PageHelper.java:255) [PageHelper.class:?]
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) [Plugin.class:3.4.1]
at com.sun.proxy.$Proxy435.query(Unknown Source) [?:?]
at sun.reflect.GeneratedMethodAccessor161.invoke(Unknown Source) ~[?:?]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_261]
at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_261]
at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49) [Invocation.class:3.4.1]
at cn.chinaunicom.sdsi.frm.mybatis.SqlMonitorPlugin.intercept(SqlMonitorPlugin.java:118) [SqlMonitorPlugin.class:?]
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) [Plugin.class:3.4.1]
at com.sun.proxy.$Proxy435.query(Unknown Source) [?:?]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148) [DefaultSqlSession.class:3.4.1]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141) [DefaultSqlSession.class:3.4.1]
at sun.reflect.GeneratedMethodAccessor218.invoke(Unknown Source) ~[?:?]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_261]
at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_261]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:434) [SqlSessionTemplate$SqlSessionInterceptor.class:1.3.0]
at com.sun.proxy.$Proxy52.selectList(Unknown Source) [?:?]
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:231) [SqlSessionTemplate.class:1.3.0]
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:137) [MapperMethod.class:3.4.1]
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75) [MapperMethod.class:3.4.1]
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53) [MapperProxy.class:3.4.1]
at com.sun.proxy.$Proxy65.selectOrgExtById(Unknown Source) [?:?]
at cn.chinaunicom.sdsi.frm.sys.service.PublicService.getOrgExtByType(PublicService.java:145) [PublicService.class:?]
at cn.chinaunicom.sdsi.frm.sys.service.PublicService.getOrgExtStrByType(PublicService.java:169) [PublicService.class:?]
at cn.chinaunicom.sdsi.frm.sys.service.PublicService$$FastClassBySpringCGLIB$$4cabbf27.invoke(<generated>) [PublicService.class:?]
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) [MethodProxy.class:4.2.7.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:720) [CglibAopProxy$CglibMethodInvocation.class:4.2.7.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) [ReflectiveMethodInvocation.class:4.2.7.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) [TransactionInterceptor$1.class:4.2.7.RELEASE]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281) [TransactionAspectSupport.class:4.2.7.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) [TransactionInterceptor.class:4.2.7.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [ReflectiveMethodInvocation.class:4.2.7.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655) [CglibAopProxy$DynamicAdvisedInterceptor.class:4.2.7.RELEASE]
at cn.chinaunicom.sdsi.frm.sys.service.PublicService$$EnhancerBySpringCGLIB$$25f00be3.getOrgExtStrByType(<generated>) [PublicService.class:?]
at unicom.controller.PaybackController.toProject(PaybackController.java:232) [PaybackController.class:?]
at unicom.controller.PaybackController$$FastClassBySpringCGLIB$$8eb5bed6.invoke(<generated>) [PaybackController.class:?]
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) [MethodProxy.class:4.2.7.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:720) [CglibAopProxy$CglibMethodInvocation.class:4.2.7.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) [ReflectiveMethodInvocation.class:4.2.7.RELEASE]
at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:85) [MethodInvocationProceedingJoinPoint.class:4.2.7.RELEASE]
at cn.chinaunicom.sdsi.frm.spring.OperationLog.doOperationLog(OperationLog.java:90) [OperationLog.class:?]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_261]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_261]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_261]
at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_261]
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:620) [AbstractAspectJAdvice.class:4.2.7.RELEASE]
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:609) [AbstractAspectJAdvice.class:4.2.7.RELEASE]
at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:68) [AspectJAroundAdvice.class:4.2.7.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [ReflectiveMethodInvocation.class:4.2.7.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655) [CglibAopProxy$DynamicAdvisedInterceptor.class:4.2.7.RELEASE]
at unicom.controller.PaybackController$$EnhancerBySpringCGLIB$$252d1d1b.toProject(<generated>) [PaybackController.class:?]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_261]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_261]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_261]
at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_261]
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221) [InvocableHandlerMethod.class:4.2.7.RELEASE]
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136) [InvocableHandlerMethod.class:4.2.7.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110) [ServletInvocableHandlerMethod.class:4.2.7.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:832) [RequestMappingHandlerAdapter.class:4.2.7.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:743) [RequestMappingHandlerAdapter.class:4.2.7.RELEASE]
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) [AbstractHandlerMethodAdapter.class:4.2.7.RELEASE]
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:961) [DispatcherServlet.class:4.2.7.RELEASE]
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:895) [DispatcherServlet.class:4.2.7.RELEASE]
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:967) [FrameworkServlet.class:4.2.7.RELEASE]
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:858) [FrameworkServlet.class:4.2.7.RELEASE]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:618) [servlet-api.jar:?]
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:843) [FrameworkServlet.class:4.2.7.RELEASE]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:725) [servlet-api.jar:?]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:301) [catalina.jar:8.0.3]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) [catalina.jar:8.0.3]
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) [tomcat-websocket.jar:8.0.3]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239) [catalina.jar:8.0.3]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) [catalina.jar:8.0.3]
at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:61) [ProxiedFilterChain.class:1.12.0]
at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108) [AdviceFilter.class:1.12.0]
at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137) [AdviceFilter.class:1.12.0]
at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:154) [OncePerRequestFilter.class:1.12.0]
at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66) [ProxiedFilterChain.class:1.12.0]
at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108) [AdviceFilter.class:1.12.0]
at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137) [AdviceFilter.class:1.12.0]
at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:154) [OncePerRequestFilter.class:1.12.0]
at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66) [ProxiedFilterChain.class:1.12.0]
at org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:458) [AbstractShiroFilter.class:1.12.0]
at org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:373) [AbstractShiroFilter$1.class:1.12.0]
at org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90) [SubjectCallable.class:1.12.0]
at org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83) [SubjectCallable.class:1.12.0]
at org.apache.shiro.subject.support.DelegatingSubject.execute(DelegatingSubject.java:387) [DelegatingSubject.class:1.12.0]
at org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:370) [AbstractShiroFilter.class:1.12.0]
展开
收起
4
条回答
写回答
写回答
-
Druid是一个流行的数据库连接池和SQL解析库，但某些版本可能存在兼容性问题。根据您提供的信息，Druid 1.2.9版本的SQL解析器不支持Oracle的within GROUP语法。
解决此问题的方法通常是升级Druid的版本，因为开发者在新版本中通常会修复旧版本中存在的问题和兼容性bug。您可以尝试升级到Druid的最新版本，以查看是否解决了此问题。
另外，您也可以考虑手动调整SQL语句，以使其与Druid 1.2.9版本兼容。但这种方法可能不是长期的解决方案，因为它可能会导致其他问题。
在任何情况下，了解Druid的官方文档和GitHub页面上的最新信息，以获取关于版本兼容性和问题修复的最新信息是很重要的。如果您的应用程序使用Oracle数据库，并且Druid的最新版本仍然不支持您的语法，您还可以考虑使用其他数据库连接池或SQL解析库，以满足您的需求。
2023-09-30 10:02:06赞同 展开评论 打赏 -
看起来这可能是一个Druid和Oracle之间的兼容性问题。Druid可能不完全支持Oracle的某些语法。您可以在Druid的官方文档中查找是否有相关的信息。如果没有，您可以尝试在Druid中禁用Oracle的某些功能，或者考虑使用其他支持Oracle语法的数据库连接池。如果问题仍然存在，建议您联系Druid的技术支持以获取帮助。
2023-09-29 11:04:36赞同 展开评论 打赏 -
北京阿里云ACE会长问题出在 Druid 的 SQL 解析上，具体来说是解析 Oracle 数据库的 WITHIN GROUP 语法时出现了问题。在 Druid 1.2.9 版本中，可能不支持这种语法。
要解决这个问题，您可以尝试升级到 Druid 的最新版本，例如 1.2.16，因为在新版本中可能已经修复了这个问题。如果升级到最新版本后问题仍然存在，您可以考虑在 SQL 语句中使用其他聚合函数（如 SUM、AVG 等）替代 LISTAGG，或者尝试将 LISTAGG 语句拆分成多个 SQL 语句执行。2023-09-29 09:09:15赞同 展开评论 打赏 -
关于你提到的问题，出现语法错误的可能原因可能有几个方面。首先，我们需要确保Oracle数据库的版本和你所使用的LISTAGG函数是兼容的。在一些较早的版本中，LISTAGG可能会出现一些问题。
其次，你的SQL查询中的WITHIN GROUP子句没有与GROUP BY语句配对。在SQL中，GROUP BY语句用于将结果集按照一个或多个列进行分组，而WITHIN GROUP子句则用于在每个组内进行聚合。因此，你需要确保在使用LISTAGG函数时，它是在GROUP BY子句之后使用的。
另外，你还要确保在使用LISTAGG函数时，它的参数和返回值都是正确的数据类型。如果其中的任何一个不匹配，都可能会导致语法错误。
根据你的描述，我无法直接找到错误的具体位置，因为未提供完整的错误信息。但是，以下是你提供的SQL查询的一个可能的修改版本：
sql
复制
SELECT
soe.org_id,
soe.ext_type,
LISTAGG ( soe.ext_value, ',' ) WITHIN GROUP ( ORDER BY soe.ext_value ) AS ext_value,
LISTAGG ( to_char( ss.staff_name ), ',' ) WITHIN GROUP ( ORDER BY ss.staff_name ) AS ext_value_show
FROM
sys_org_ext soe,
sys_staff ss
WHERE
soe.ext_value = ss.staff_id
AND soe.org_id = ?
AND soe.ext_type IN ( 'DE1', 'DE2', 'DE3', 'DE4', 'DE5', 'DE6', 'DE7', 'DE9' )
GROUP BY
soe.org_id,
soe.ext_type
UNION
SELECT
soe.org_id,
soe.ext_type,
soe.ext_value AS ext_value,
'' AS ext_value_show
FROM
sys_org_ext soe
WHERE
soe.org_id = ?
AND soe.ext_type IN ( 'DP_TYPE', 'DE8', 'DE10' )
UNION
SELECT
soe.org_id,
soe.ext_type,
soe.ext_value,
'' AS ext_value_show
FROM
sys_org_ext soe
WHERE
soe.org_id = ?
AND soe.ext_type NOT IN ( 'DE1', 'DE2', 'DE3', 'DE4', 'DE5', 'DE6', 'DE7', 'DE8', 'DE9', 'DE10', 'DP_TYPE' );上述查询中，我添加了AS关键字来为聚合函数的输出列命名，这是Oracle 11g中的要求。另外，我假设你在粘贴到数据库客户端中运行时，使用的数据库版本是与你的Druid jar包版本兼容的。如果你的数据库版本不同，可能需要对Druid jar包的版本进行调整。
2023-09-29 09:09:16赞同 展开评论 打赏
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
相关问答
