2021年12月16日复盘 JSQLParser 命中Oracle关键词报错
今天遇到了一个JsqlParser解析sql报错的问题
原来以为是语法写的有什么问题，或者是太复杂，不支持解析
报错信息是这样的
net.sf.jsqlparser.JSQLParserException: Encountered unexpected token: "WAIT" "WAIT" at line 2, column 62. Was expecting one of: "ACTION" "ANY" "BYTE" "CASCADE" "CAST" "CHANGE" "CHAR" "CHARACTER" "COLUMN" "COLUMNS" "COMMENT" "COMMIT" "CYCLE" "DESC" "DESCRIBE" "DISABLE" "DIV" "DO" "DUPLICATE" "ENABLE" "END" "EXCLUDE" "EXTRACT" "FALSE" "FIRST" "FN" "FOLLOWING" "FORMAT" "INDEX" "INSERT" "INTERVAL" "ISNULL" "KEY" "LAST" "MATERIALIZED" "NEXTVAL" "NO" "NOLOCK" "NULLS" "OF" "OPEN" "OVER" "PARTITION" "PATH" "PERCENT" "PRECISION" "PRIMARY" "PRIOR" "RANGE" "READ" "REPLACE" "ROW" "ROWS" "SCHEMA" "SEPARATOR" "SEQUENCE" "SESSION" "SIBLINGS" "SIZE" "TABLE" "TEMP" "TEMPORARY" "TO" "TOP" "TRUE" "TRUNCATE" "TYPE" "UNSIGNED" "VALIDATE" "VALUE" "VALUES" "VIEW" "XML" "ZONE" <K_DATETIMELITERAL> <K_DATE_LITERAL> <S_CHAR_LITERAL> <S_IDENTIFIER> <S_QUOTED_IDENTIFIER> at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:165) at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:59) at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:37) at com.anta.test.rfc.Z_HCM_001_test.test1(Z_HCM_001_test.java:24) 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.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325) at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78) at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57) at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290) at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71) at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288) at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58) at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268) at org.junit.runners.ParentRunner.run(ParentRunner.java:363) at org.junit.runner.JUnitCore.run(JUnitCore.java:137) at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69) at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33) at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235) at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54) Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "WAIT" "WAIT" at line 2, column 62. Was expecting one of: "ACTION" "ANY" "BYTE" "CASCADE" "CAST" "CHANGE" "CHAR" "CHARACTER" "COLUMN" "COLUMNS" "COMMENT" "COMMIT" "CYCLE" "DESC" "DESCRIBE" "DISABLE" "DIV" "DO" "DUPLICATE" "ENABLE" "END" "EXCLUDE" "EXTRACT" "FALSE" "FIRST" "FN" "FOLLOWING" "FORMAT" "INDEX" "INSERT" "INTERVAL" "ISNULL" "KEY" "LAST" "MATERIALIZED" "NEXTVAL" "NO" "NOLOCK" "NULLS" "OF" "OPEN" "OVER" "PARTITION" "PATH" "PERCENT" "PRECISION" "PRIMARY" "PRIOR" "RANGE" "READ" "REPLACE" "ROW" "ROWS" "SCHEMA" "SEPARATOR" "SEQUENCE" "SESSION" "SIBLINGS" "SIZE" "TABLE" "TEMP" "TEMPORARY" "TO" "TOP" "TRUE" "TRUNCATE" "TYPE" "UNSIGNED" "VALIDATE" "VALUE" "VALUES" "VIEW" "XML" "ZONE" <K_DATETIMELITERAL> <K_DATE_LITERAL> <S_CHAR_LITERAL> <S_IDENTIFIER> <S_QUOTED_IDENTIFIER> at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:26538) at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:26377) at net.sf.jsqlparser.parser.CCJSqlParser.Alias(CCJSqlParser.java:5183) at net.sf.jsqlparser.parser.CCJSqlParser.SelectExpressionItem(CCJSqlParser.java:4881) at net.sf.jsqlparser.parser.CCJSqlParser.SelectItem(CCJSqlParser.java:5033) at net.sf.jsqlparser.parser.CCJSqlParser.SelectItemsList(CCJSqlParser.java:4789) at net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:4273) at net.sf.jsqlparser.parser.CCJSqlParser.SetOperationList(CCJSqlParser.java:4496) at net.sf.jsqlparser.parser.CCJSqlParser.SelectBody(CCJSqlParser.java:4163) at net.sf.jsqlparser.parser.CCJSqlParser.Select(CCJSqlParser.java:4158) at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:130) at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:81) at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:163) ... 25 more
sql是这样的
select NVL(ZQEYLB, '其他') ZQEYLB, sum(case when FZJH.ZWCZT='10' THEN 1 else 0 END) as WAIT, sum(case when FZJH.ZWCZT='20' THEN 1 else 0 END) as RUNNING, sum(case when FZJH.ZWCZT='30' THEN 1 else 0 END) as FINISHED from PRESCRIPTION_FZJH FZJH,PRESCRIPTION_MESSAGE MSG WHERE ZCFJNF = '2021' and FZJH.ZPREID=MSG.ZPREID GROUP BY ZQEYLB order by NVL(FZJH.ZQEYLB, 0) DESC
百思不得其解，于是开始分段删除语句。发现删掉wait语句的这行就不会报错
加上刚才关键报错里面有WAIT，所以就猜测可能是个关键词。
然后搜了一下，发现大概是这么个语句
select * from table for update wait 1000
然而这样也不能完成证明猜想是对的，所以我用了另一个关键词 nowait，也一样报错。。。
最后在WAIT外面包了一层双引号，别问我为什么不改别名。。。因为这是个老项目。。。。
