【Druid】异常syntax error, expect RPAREN, actual FOR FOR

Question

环境jdk1.6 + tomcat6.0+sqlserver2005

执行的插入语句INSERT INTO MMS_SETTLEMENT_COM(handler,handleTime,MID,MERCHANTNAME,TOTALAMT,ACTUALAMT,paymentMoney,STATUS,SERIAL_NUM,REMARKS)SELECT 'admin',getdate(),MID,MERCHANTNAME,SUM(CONVERT(DECIMAL(18,2),isnull(TOTALAMT,0))) TOTALAMT,SUM(CONVERT(DECIMAL(18,2),isnull(ACTUALAMT,0))) ACTUALAMT,SUM(CONVERT(DECIMAL(18,2),isnull(paymentMoney,0))) paymentMoney,2,126,(SELECT REMARKS+'' FROM MMS_SETTLEMENT_COM WHERE MID=A.MID FOR XML PATH('')) AS REMARKS FROM MMS_SETTLEMENT_COM A WHERE ID IN (304,305,306,297,108,184) GROUP BY MID ,MERCHANTNAME

能够插入成功，但是后台druid会报异常

com.alibaba.druid.sql.parser.ParserException: syntax error, expect RPAREN, actual FOR FOR
at com.alibaba.druid.sql.parser.SQLExprParser.accept(SQLExprParser.java:1382)
at com.alibaba.druid.sql.parser.SQLExprParser.primary(SQLExprParser.java:194)
at com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerExprParser.primary(SQLServerExprParser.java:54)
at com.alibaba.druid.sql.parser.SQLExprParser.expr(SQLExprParser.java:109)
at com.alibaba.druid.sql.parser.SQLSelectParser.expr(SQLSelectParser.java:434)
at com.alibaba.druid.sql.parser.SQLSelectParser.parseSelectItem(SQLSelectParser.java:295)
at com.alibaba.druid.sql.parser.SQLSelectParser.parseSelectList(SQLSelectParser.java:273)
at com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerSelectParser.query(SQLServerSelectParser.java:89)
at com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerSelectParser.select(SQLServerSelectParser.java:47)
at com.alibaba.druid.sql.parser.SQLExprParser.primary(SQLExprParser.java:373)
at com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerExprParser.primary(SQLServerExprParser.java:54)
at com.alibaba.druid.sql.parser.SQLExprParser.expr(SQLExprParser.java:109)
at com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser.parseInsert0(SQLServerStatementParser.java:134)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseInsert0(SQLStatementParser.java:1035)
at com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser.parseInsert(SQLServerStatementParser.java:85)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:144)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:108)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:103)
at com.alibaba.druid.sql.visitor.ParameterizedOutputVisitorUtils.parameterize(ParameterizedOutputVisitorUtils.java:53)
at com.alibaba.druid.filter.stat.StatFilter.mergeSql(StatFilter.java:145)
at com.alibaba.druid.filter.stat.StatFilter.createSqlStat(StatFilter.java:629)
at com.alibaba.druid.filter.stat.StatFilter.statementPrepareAfter(StatFilter.java:305)
at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:124)
at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:448)
at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:342)
at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:311)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:505)
at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:94)
at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:87)
at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:157)
at org.hibernate.impl.SessionImpl.executeNativeUpdate(SessionImpl.java:1163)
at org.hibernate.impl.SQLQueryImpl.executeUpdate(SQLQueryImpl.java:334)
at hsir.settlement.service.MmsSettlementSummaryService.doMerge(MmsSettlementSummaryService.java:939)
at hsir.settlement.service.MmsSettlementSummaryService$$FastClassByCGLIB$$d80e3b47.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:696)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:631)
at hsir.settlement.service.MmsSettlementSummaryService$$EnhancerByCGLIB$$5d5afacc.doMerge(<generated>)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:304)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
at org.springframework.aop.framework.adapter.AfterReturningAdviceInterceptor.invoke(AfterReturningAdviceInterceptor.java:50)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy48.doMerge(Unknown Source)
at hsir.settlement.litebean.Settlement1Bean.btn_merge(Settlement1Bean.java:342)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.operamasks.faces.binding.ModelBean.invokeMethod(ModelBean.java:319)
at org.operamasks.faces.binding.ModelBean.invokeAction(ModelBean.java:394)
at org.operamasks.faces.binding.impl.ActionMethodAdapter.invoke(ActionMethodAdapter.java:66)
at org.operamasks.faces.binding.impl.CompositeMethodAdapter.invoke(CompositeMethodAdapter.java:62)
at org.operamasks.faces.application.impl.ActionListenerImpl.processAction(ActionListenerImpl.java:109)
at javax.faces.component.UICommand.broadcast(UICommand.java:386)
at javax.faces.component.UIViewRoot.broadcastEvent(UIViewRoot.java:491)
at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:451)
at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:794)
at org.operamasks.faces.lifecycle.InvokeApplication.execute(InvokeApplication.java:42)
at org.operamasks.faces.lifecycle.LifecycleImpl.doPhase(LifecycleImpl.java:135)
at org.operamasks.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:83)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:244)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at filter.UserLoginFilter.doFilter(UserLoginFilter.java:35)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:124)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.operamasks.faces.webapp.filter.DefaultSuffixRedirector.doFilter(DefaultSuffixRedirector.java:73)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Thread.java:619)
31 三月 14 12:45:06,72102 [http-8080-1] INFO  hsir.settlement.service.MmsSettlementSummaryService  - 1条记录被更新

Answer 1

已经提交，↖(^ω^)↗

Answer 2

能否到github上填一个issue，好让我有空了记得修掉https://github.com/alibaba/druid/issues

Answer 3

今天sqlserver 使用合并行 stuff for xml path  数据库连接池druid（1.0.12） 也遇到这个问题～～～～～也是醉了。更新druid到最新版本也没有解决这个问题。。@wenshao

[ERROR] [2016-04-21 14:12:54] com.alibaba.druid.filter.stat.StatFilter.mergeSql(147) | merge sql error, dbType jtds, sql : 
 com.alibaba.druid.sql.parser.ParserException: syntax error, expect RPAREN, actual IDENTIFIER PATH

Answer 4

自定义字符串拼接函数

Answer 5

好像是不支持，我最后是用自定义函数实现了想要的功能，用自定义函数将查出来的值进行了拼接。

Answer 6

回复
不喜欢用函数

Answer 7

com.alibaba.druid.sql.parser.ParserException: syntax error, FOR for, pos 74 我这里也报错了，难道真不支持？

我用最新版1.0.5 还是不行