当遇到`expect RPAREN, actual LPAREN pos`这类Druid解析SQL错误时，可能是由于Druid数据库连接池过滤器对某些关键字处理不当。尽管SQL在MySQL8.0中能正常执行，但在程序中可能因解析问题导致异常。解决方案是检查SQL中的保留关键字，用反引号(`)将其包裹。尝试修改SQL查询语句，而不是仅修改关键字格式，以解决该异常。"
115971791,10542940,MATLAB实现：多尺度低秩稀疏分解及ADMM算法,"['低秩稀疏分解', 'MATLAB', '算法实现']
对于复杂sql执行时报错信息：
com.alibaba.druid.sql.parser.ParserException: syntax error, error in :' tmp as ((', expect RPAREN, actual LPAREN pos 31, line 1, column 14, token LPAREN
首先报错sql放到mysql8.0执行是ok的。但是在程序里报错。说明是解析sql时有问题导致生成的sql执行出现问题。问题就出现druid数据库连接池过滤器发现一些关键字，或者保留关键字没做引号标注，会报异常。
具体的sql及其报错信息如下。
merge sql error, dbType mysql, druid-1.1.21, sql : with tmp as (
(
select cvi.id as virtualClassId,
cvi.start_time as startTime,
cvi.student_user_id as id,
usi.real_name as name,
1 as classType,
ce.id as editionId,
ci.course_name as courseName,
cl.unit_no as unitNo,
cl.unit_lesson_no as unitLessonNo,
cvi.feedback_status as feedbackStatus,
cvi.teacher_attend_status as teacherAttendStatus
from classroom_virtualclass_info cvi
inner join finance_classroom_audit fca on fca.virtual_class_id = cvi.id
inner join user_student_info usi on cvi.student_user_id = usi.id
inner join course_lesson cl on cvi.lesson_id = cl.id
inner join course_info ci on cl.course_id = ci.id
inner join course_edition ce on ci.course_edition_id = ce.id
where cvi.tutor_user_id = ?
and cvi.start_time >= ?
and cvi.start_time <= ?
and cvi.remove = 0
and cvi.status in (3, 4)
and fca.remove = 0
and fca.class_type_id = 1
)
union all
(
select svi.id as virtualClassId,
svi.start_time as startTime,
svi.class_id as id,
sci.class_name_zh as name,
2 as classType,
ce.id as editionId,
ci.course_name as courseName,
cl.unit_no as unitNo,
cl.unit_lesson_no as unitLessonNo,
svi.feedback_status as feedbackStatus,
svi.teacher_attend_status as teacherAttendStatus
from smallclass_virtualclass_info svi
inner join finance_classroom_audit fca on fca.virtual_class_id = svi.id
inner join smallclass_class_info sci on svi.class_id = sci.id
inner join course_lesson cl on svi.lesson_id = cl.id
inner join course_info ci on cl.course_id = ci.id
inner join course_edition ce on ci.course_edition_id = ce.id
where svi.tutor_user_id = ?
and svi.start_time >= ?
and svi.start_time <= ?
and svi.remove = 0
and svi.status in (3, 4)
and fca.remove = 0
and fca.class_type_id = 1
)
)
select tmp.*
from tmp as tmp
order by tmp.startTime desc LIMIT ?
com.alibaba.druid.sql.parser.ParserException: syntax error, error in :' tmp as (
(
', expect RPAREN, actual LPAREN pos 31, line 1, column 14, token LPAREN
at com.alibaba.druid.sql.parser.SQLParser.printError(SQLParser.java:344)
at com.alibaba.druid.sql.parser.SQLParser.accept(SQLParser.java:352)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseWithQuery(SQLStatementParser.java:3311)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseWith(SQLStatementParser.java:3327)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:242)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:171)
at com.alibaba.druid.sql.visitor.ParameterizedOutputVisitorUtils.parameterize(ParameterizedOutputVisitorUtils.java:98)
at com.alibaba.druid.sql.visitor.ParameterizedOutputVisitorUtils.parameterize(ParameterizedOutputVisitorUtils.java:81)
at com.alibaba.druid.sql.visitor.ParameterizedOutputVisitorUtils.parameterize(ParameterizedOutputVisitorUtils.java:54)
at com.alibaba.druid.filter.stat.StatFilter.mergeSql(StatFilter.java:147)
at com.alibaba.druid.filter.stat.StatFilter.createSqlStat(StatFilter.java:648)
at com.alibaba.druid.filter.stat.StatFilter.statementPrepareAfter(StatFilter.java:311)
at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:124)
at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568)
at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:341)
at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:350)
at jdk.internal.reflect.GeneratedMethodAccessor379.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:567)
at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:55)
at com.sun.proxy.$Proxy385.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:86)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62)
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.CachingExecutor.query(CachingExecutor.java:109)
at com.github.pagehelper.util.ExecutorUtil.pageQuery(ExecutorUtil.java:177)
at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:104)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
at com.sun.proxy.$Proxy384.query(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
at jdk.internal.reflect.GeneratedMethodAccessor425.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:567)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
at com.sun.proxy.$Proxy166.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.invoke(MapperProxy.java:57)
at com.sun.proxy.$Proxy284.getTutorVirtualClassSalaryInfoList(Unknown Source)
at
Druid过滤器发现使用了sql默认保留的关键字 会报这个异常,在下面找到token后面的关键字对应的符号 然后在mapper中搜索 找到后使用``包裹即可
public enum Token {
FOR("FOR"),
IF("IF"),
INDEX("INDEX"),
PRIMARY("PRIMARY"),
KEY("KEY"),
DEFAULT("DEFAULT"),
CONSTRAINT("CONSTRAINT"),
CHECK("CHECK"),
VIEW("VIEW"),
CREATE("CREATE"),
ALTER("ALTER"),
DROP("DROP"),
TABLE("TABLE"),
UPDATE("UPDATE"),
SET("SET"),
SELECT("SELECT"),
FROM("FROM"),
WHERE("WHERE"),
ORDER("ORDER"),
BY("BY"),
GROUP("GROUP"),
HAVING("HAVING"),
INSERT("INSERT"),
INTO("INTO"),
NULL("NULL"),
NOT("NOT"),
AS("AS"),
DELETE("DELETE"),
DISTINCT("DISTINCT"),
UNIQUE("UNIQUE"),
FOREIGN("FOREIGN"),
REFERENCES("REFERENCES"),
ALL("ALL"),
UNION("UNION"),
INTERSECT("INTERSECT"),
MINUS("MINUS"),
INNER("INNER"),
LEFT("LEFT"),
RIGHT("RIGHT"),
FULL("FULL"),
OUTER("OUTER"),
JOIN("JOIN"),
ON("ON"),
SCHEMA("SCHEMA"),
CAST("CAST"),
COLUMN("COLUMN"),
USE("USE"),
DATABASE("DATABASE"),
AND("AND"),
OR("OR"),
XOR("XOR"),
CASE("CASE"),
WHEN("WHEN"),
THEN("THEN"),
ELSE("ELSE"),
END("END"),
EXISTS("EXISTS"),
IN("IN"),
NEW("NEW"),
ASC("ASC"),
DESC("DESC"),
IS("IS"),
LIKE("LIKE"),
ESCAPE("ESCAPE"),
BETWEEN("BETWEEN"),
VALUES("VALUES"),
INTERVAL("INTERVAL"),
LOCK("LOCK"),
SOME("SOME"),
ANY("ANY"),
TRUNCATE("TRUNCATE"),
// mysql
TRUE("TRUE"),
FALSE("FALSE"),
LIMIT("LIMIT"),
KILL("KILL"),
IDENTIFIED("IDENTIFIED"),
PASSWORD("PASSWORD"),
DUAL("DUAL"),
//postgresql
WINDOW("WINDOW"),
OFFSET("OFFSET"),
ROW("ROW"),
ROWS("ROWS"),
ONLY("ONLY"),
FIRST("FIRST"),
NEXT("NEXT"),
FETCH("FETCH"),
OF("OF"),
SHARE("SHARE"),
NOWAIT("NOWAIT"),
RECURSIVE("RECURSIVE"),
TEMPORARY("TEMPORARY"),
TEMP("TEMP"),
UNLOGGED("UNLOGGED"),
RESTART("RESTART"),
IDENTITY("IDENTITY"),
CONTINUE("CONTINUE"),
CASCADE("CASCADE"),
RESTRICT("RESTRICT"),
USING("USING"),
CURRENT("CURRENT"),
RETURNING("RETURNING"),
COMMENT("COMMENT"),
OVER("OVER"),
// oracle
START("START"),
PRIOR("PRIOR"),
CONNECT("CONNECT"),
WITH("WITH"),
EXTRACT("EXTRACT"),
CURSOR("CURSOR"),
MODEL("MODEL"),
MERGE("MERGE"),
MATCHED("MATCHED"),
ERRORS("ERRORS"),
REJECT("REJECT"),
UNLIMITED("UNLIMITED"),
BEGIN("BEGIN"),
EXCLUSIVE("EXCLUSIVE"),
MODE("MODE"),
WAIT("WAIT"),
ADVISE("ADVISE"),
SESSION("SESSION"),
PROCEDURE("PROCEDURE"),
LOCAL("LOCAL"),
SYSDATE("SYSDATE"),
DECLARE("DECLARE"),
EXCEPTION("EXCEPTION"),
GRANT("GRANT"),
LOOP("LOOP"),
GOTO("GOTO"),
COMMIT("COMMIT"),
SAVEPOINT("SAVEPOINT"),
CROSS("CROSS"),
// transact-sql
TOP("TOP"),
// hive
EOF,
ERROR,
IDENTIFIER,
HINT,
VARIANT,
LITERAL_INT,
LITERAL_FLOAT,
LITERAL_HEX,
LITERAL_CHARS,
LITERAL_NCHARS,
LITERAL_ALIAS,
LINE_COMMENT,
MULTI_LINE_COMMENT,
// Oracle
BINARY_FLOAT,
BINARY_DOUBLE,
LPAREN("("),
RPAREN(")"),
LBRACE("{"),
RBRACE("}"),
LBRACKET("["),
RBRACKET("]"),
SEMI(";"),
COMMA(","),
DOT("."),
DOTDOT(".."),
DOTDOTDOT("..,"),
EQ("="),
GT(">"),
LT("<"),
BANG("!"),
TILDE("~"),
QUES("?"),
COLON(":"),
COLONEQ(":="),
EQEQ("=="),
LTEQ("<="),
LTEQGT("<=>"),
LTGT("<>"),
GTEQ(">="),
BANGEQ("!="),
BANGGT("!>"),
BANGLT("!<"),
AMPAMP("&&"),
BARBAR("||"),
PLUS("+"),
SUB("-"),
STAR("*"),
SLASH("/"),
AMP("&"),
BAR("|"),
CARET("^"),
PERCENT("%"),
LTLT("<<"),
GTGT(">>"),
MONKEYS_AT("@");
public final String name;
Token(){
this(null);
}
Token(String name){
this.name = name;
}
}
通过已上可以将关键字加上单引号，测试是否通过。这里我实验了好几次。将name变成'name' ，所有关键字改成大写，依旧不好使。最后还是通过调整sql的查询语句才可以。
第一次 关键字大写，加双引号，不好使
WITH tmp AS (
(SELECT cvi.id AS virtualClassId,
cvi.start_time AS startTime,
cvi.student_user_id AS id,
usi.real_name AS 'name',
1 AS classType,
ce.id AS editionId,
ci.course_name AS courseName,
cl.unit_no AS unitNo,
cl.unit_lesson_no AS unitLessonNo,
cvi.feedback_status AS feedbackStatus,
cvi.teacher_attend_status AS teacherAttendStatus
FROM classroom_virtualclass_info cvi
INNER JOIN finance_classroom_audit fca ON fca.virtual_class_id = cvi.id
INNER JOIN user_student_info usi ON cvi.student_user_id = usi.id
INNER JOIN course_lesson cl ON cvi.lesson_id = cl.id
INNER JOIN course_info ci ON cl.course_id = ci.id
INNER JOIN course_edition ce ON ci.course_edition_id = ce.id
WHERE cvi.tutor_user_id = #{tutorId}
AND cvi.start_time >= #{startTime}
AND cvi.start_time <= #{endTime}
AND cvi.`remove` = 0
AND cvi.`status` in (3, 4)
AND fca.`remove` = 0
AND fca.class_type_id = 1)
UNION ALL
(SELECT svi.id AS virtualClassId,
svi.start_time AS startTime,
svi.class_id AS id,
sci.class_name_zh AS 'name',
2 AS classType,
ce.id AS editionId,
ci.course_name AS courseName,
cl.unit_no AS unitNo,
cl.unit_lesson_no AS unitLessonNo,
svi.feedback_status AS feedbackStatus,
svi.teacher_attend_status AS teacherAttendStatus
FROM smallclass_virtualclass_info svi
INNER JOIN finance_classroom_audit fca ON fca.virtual_class_id = svi.id
INNER JOIN smallclass_class_info sci ON svi.class_id = sci.id
INNER JOIN course_lesson cl ON svi.lesson_id = cl.id
INNER JOIN course_info ci ON cl.course_id = ci.id
INNER JOIN course_edition ce ON ci.course_edition_id = ce.id
WHERE svi.tutor_user_id = #{tutorId}
AND svi.start_time >= #{startTime}
AND svi.start_time <= #{endTime}
AND svi.`remove` = 0
AND svi.`status` IN (3, 4)
AND fca.`remove` = 0
AND fca.class_type_id = 1)
)
SELECT * FROM tmp ORDER BY startTime DESC
调整查询语句才好使。
SELECT * FROM (
(
(SELECT cvi.id AS virtualClassId,
cvi.start_time AS startTime,
cvi.student_user_id AS id,
usi.real_name AS 'name',
1 AS classType,
ce.id AS editionId,
ci.course_name AS courseName,
cl.unit_no AS unitNo,
cl.unit_lesson_no AS unitLessonNo,
cvi.feedback_status AS feedbackStatus,
cvi.teacher_attend_status AS teacherAttendStatus
FROM classroom_virtualclass_info cvi
INNER JOIN finance_classroom_audit fca ON fca.virtual_class_id = cvi.id
INNER JOIN user_student_info usi ON cvi.student_user_id = usi.id
INNER JOIN course_lesson cl ON cvi.lesson_id = cl.id
INNER JOIN course_info ci ON cl.course_id = ci.id
INNER JOIN course_edition ce ON ci.course_edition_id = ce.id
WHERE cvi.tutor_user_id = #{tutorId}
AND cvi.start_time >= #{startTime}
AND cvi.start_time <= #{endTime}
AND cvi.`remove` = 0
AND cvi.`status` in (3, 4)
AND fca.`remove` = 0
AND fca.class_type_id = 1)
UNION ALL
(SELECT svi.id AS virtualClassId,
svi.start_time AS startTime,
svi.class_id AS id,
sci.class_name_zh AS 'name',
2 AS classType,
ce.id AS editionId,
ci.course_name AS courseName,
cl.unit_no AS unitNo,
cl.unit_lesson_no AS unitLessonNo,
svi.feedback_status AS feedbackStatus,
svi.teacher_attend_status AS teacherAttendStatus
FROM smallclass_virtualclass_info svi
INNER JOIN finance_classroom_audit fca ON fca.virtual_class_id = svi.id
INNER JOIN smallclass_class_info sci ON svi.class_id = sci.id
INNER JOIN course_lesson cl ON svi.lesson_id = cl.id
INNER JOIN course_info ci ON cl.course_id = ci.id
INNER JOIN course_edition ce ON ci.course_edition_id = ce.id
WHERE svi.tutor_user_id = #{tutorId}
AND svi.start_time >= #{startTime}
AND svi.start_time <= #{endTime}
AND svi.`remove` = 0
AND svi.`status` IN (3, 4)
AND fca.`remove` = 0
AND fca.class_type_id = 1)
)
)tmp ORDER BY tmp.startTime DESC
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
