为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:dm8
【操作系统】:windows10-x64
【CPU】:i5
【问题描述】*:在做层次查询时阿里连接池报错:
not supported.pos 197, line 4, column 43, token IDENTIFIER START
sql语句是直接从咱们社区建议中拷贝的原文如下:
在idea里start显示的是白色的,而其他关键词select、from、with、connect by prior 都是黄色的字,这就意味着start并没有被识别出事SQL关键字,只有在start前面加了where才会变黄色,但网上很多标准写法也都没有加where ,我对此很是疑惑。网上查了很多都没有结果,搞不懂这个报错是阿里连接池不支持start with 语句还是达梦数据库不支持阿里连接池,请有知道的帮忙解答下,谢谢。
下面是异常日志信息:
2023-04-05 23:34:08.996 DEBUG 14776 --- [nio-8080-exec-8] c.d.s.m.S.selectChildSysRoleById : ==> Preparing: SELECT role_id,role_name,parent_id,role_code,summary,data_scope,status,sort_code,create_by,create_time,update_by,update_time,user_group_id FROM sys_role where status = '1' START WITH role_id = ? CONNECT BY PRIOR role_id = parent_id
2023-04-05 23:34:09.005 ERROR 14776 --- [nio-8080-exec-8] c.alibaba.druid.filter.stat.StatFilter : merge sql error, dbType dm, druid-1.2.16, sql : SELECT
role_id,role_name,parent_id,role_code,summary,data_scope,status,sort_code,create_by,create_time,update_by,update_time,user_group_id
FROM sys_role where status = '1' START WITH role_id = ?
CONNECT BY PRIOR role_id = parent_id
com.alibaba.druid.sql.parser.ParserException: not supported.pos 197, line 4, column 43, token IDENTIFIER START
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:615)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:101)
at com.alibaba.druid.sql.visitor.ParameterizedOutputVisitorUtils.parameterize(ParameterizedOutputVisitorUtils.java:163)
at com.alibaba.druid.sql.visitor.ParameterizedOutputVisitorUtils.parameterize(ParameterizedOutputVisitorUtils.java:134)
at com.alibaba.druid.filter.stat.StatFilter.mergeSql(StatFilter.java:148)
at com.alibaba.druid.filter.stat.StatFilter.createSqlStat(StatFilter.java:672)
at com.alibaba.druid.filter.stat.StatFilter.statementPrepareAfter(StatFilter.java:325)
at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:118)
at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:531)
at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:326)
at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:362)
at sun.reflect.GeneratedMethodAccessor125.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:55)
at com.sun.proxy.$Proxy137.prepareStatement(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:86)
at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59)
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:87)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:143)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
at com.sun.proxy.$Proxy136.query(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
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:433)
at com.sun.proxy.$Proxy75.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:147)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:80)
at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:145)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)
at com.sun.proxy.$Proxy79.selectChildSysRoleById(Unknown Source)
关键字显示这个应该不影响,可以把层次查询的语句直接拿到dm库中执行试试,应该问题不大,这个语法是支持的。
报错了unknown source,应该还是数据源有问题,可以从数据源配置,以及驱动版本等方面排查