net.sf.jsqlparser.parser.ParseException：遇到意外令牌："@" "@"

Question

当我在mybatis中的SQL中添加这样的变量语句时：

set @update_id := 0;

这是我的完整声明：

set @update_id := 0;
UPDATE r_room_seat s
SET s.status = 1,
s.user_id = (
case when s.seat_num = 1
then #{user1.id,jdbcType=BIGINT}
when s.seat_num = 2
then #{user2.id,jdbcType=BIGINT}
else -1 end
),
s.robot_flag = (
case when s.seat_num = 1
then #{user1.isRobot,jdbcType=INTEGER}
when s.seat_num = 2
then #{user2.isRobot,jdbcType=INTEGER}
else 0 end
)
WHERE s.status = 0
and s.online = 1
and s.tenant_id = #{queryParam.tenantId,jdbcType=BIGINT}
and s.room_play_id in (
select room_play_id
from (
select room_play_id as room_play_id
from r_room_seat
where status = 0
and app_id = 4
and tenant_id = #{queryParam.tenantId,jdbcType=BIGINT}
group by room_play_id
having count(*) = 2
limit 1
) a
)
and s.room_id = #{queryParam.roomTypeId,jdbcType=BIGINT}
LIMIT 2;
select
<include refid="Base_Column_List"/>
from r_room_seat
where room_play_id = 5
limit 1;

但是抛出这个错误：

### Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, please exclude the tableName or statementId.
Error SQL: set @update_id := 0;
UPDATE r_room_seat s
SET s.status = 1,
room_play_id = (SELECT @update_id := max(room_play_id)),
s.user_id = (
case when s.seat_num = 1
then ?
when s.seat_num = 2
then ?
else -1 end
),
s.robot_flag = (
case when s.seat_num = 1
then ?
when s.seat_num = 2
then ?
else 0 end
)
WHERE s.status = 0
and s.online = 1
and s.room_play_id in (
select room_play_id
from (
select room_play_id as room_play_id
from r_room_seat
where status = 0
and app_id = 4
group by room_play_id
having count(*) = 2
limit 1
) a
)
and s.room_id = ?
LIMIT 2;
select


id, room_play_id, room_id, user_id, creator, recent_active, deleted, sort, updated_time,
created_time, `status`, robot_flag, app_id, app_mark, `online`, version

from r_room_seat
where room_play_id = @update_id
limit 1;
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30) ~[mybatis-3.5.2.jar!/:3.5.2]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:149) ~[mybatis-3.5.2.jar!/:3.5.2]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) ~[mybatis-3.5.2.jar!/:3.5.2]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:76) ~[mybatis-3.5.2.jar!/:3.5.2]
at sun.reflect.GeneratedMethodAccessor358.invoke(Unknown Source) ~[na:na]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_252]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_252]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426) ~[mybatis-spring-2.0.2.jar!/:2.0.2]
... 114 common frames omitted
Caused by: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, please exclude the tableName or statementId.
Error SQL: set @update_id := 0;
UPDATE r_room_seat s
SET s.status = 1,
room_play_id = (SELECT @update_id := max(room_play_id)),
s.user_id = (
case when s.seat_num = 1
then ?
when s.seat_num = 2
then ?
else -1 end
),
s.robot_flag = (
case when s.seat_num = 1
then ?
when s.seat_num = 2
then ?
else 0 end
)
WHERE s.status = 0
and s.online = 1
and s.room_play_id in (
select room_play_id
from (
select room_play_id as room_play_id
from r_room_seat
where status = 0
and app_id = 4
group by room_play_id
having count(*) = 2
limit 1
) a
)
and s.room_id = ?
LIMIT 2;
select


id, room_play_id, room_id, user_id, creator, recent_active, deleted, sort, updated_time,
created_time, `status`, robot_flag, app_id, app_mark, `online`, version

from r_room_seat
where room_play_id = @update_id
limit 1;
at com.baomidou.mybatisplus.core.toolkit.ExceptionUtils.mpe(ExceptionUtils.java:39) ~[mybatis-plus-core-3.2.0.jar!/:3.2.0]
at com.baomidou.mybatisplus.core.parser.AbstractJsqlParser.parser(AbstractJsqlParser.java:74) ~[mybatis-plus-core-3.2.0.jar!/:3.2.0]
at com.baomidou.mybatisplus.extension.handlers.AbstractSqlParserHandler.sqlParser(AbstractSqlParserHandler.java:76) ~[mybatis-plus-extension-3.2.0.jar!/:3.2.0]
at com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor.intercept(PaginationInterceptor.java:155) ~[mybatis-plus-extension-3.2.0.jar!/:3.2.0]
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.5.2.jar!/:3.5.2]
at com.sun.proxy.$Proxy169.prepare(Unknown Source) ~[na:na]
at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.prepareStatement(MybatisSimpleExecutor.java:94) ~[mybatis-plus-core-3.2.0.jar!/:3.2.0]
at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:66) ~[mybatis-plus-core-3.2.0.jar!/:3.2.0]
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324) ~[mybatis-3.5.2.jar!/:3.5.2]
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[mybatis-3.5.2.jar!/:3.5.2]
at sun.reflect.GeneratedMethodAccessor201.invoke(Unknown Source) ~[na:na]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_252]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_252]
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) ~[mybatis-3.5.2.jar!/:3.5.2]
at com.sun.proxy.$Proxy168.query(Unknown Source) ~[na:na]
at sun.reflect.GeneratedMethodAccessor201.invoke(Unknown Source) ~[na:na]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_252]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_252]
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) ~[mybatis-3.5.2.jar!/:3.5.2]
at com.sun.proxy.$Proxy168.query(Unknown Source) ~[na:na]
at sun.reflect.GeneratedMethodAccessor201.invoke(Unknown Source) ~[na:na]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_252]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_252]
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) ~[mybatis-3.5.2.jar!/:3.5.2]
at com.sun.proxy.$Proxy168.query(Unknown Source) ~[na:na]
at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:108) ~[pagehelper-5.1.11.jar!/:na]
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.5.2.jar!/:3.5.2]
at com.sun.proxy.$Proxy168.query(Unknown Source) ~[na:na]
at sun.reflect.GeneratedMethodAccessor205.invoke(Unknown Source) ~[na:na]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_252]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_252]
at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49) ~[mybatis-3.5.2.jar!/:3.5.2]
at tk.mybatis.orderbyhelper.OrderByHelper.intercept(OrderByHelper.java:115) ~[orderby-helper-0.0.2.jar!/:na]
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.5.2.jar!/:3.5.2]
at com.sun.proxy.$Proxy168.query(Unknown Source) ~[na:na]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147) ~[mybatis-3.5.2.jar!/:3.5.2]
... 120 common frames omitted
Caused by: net.sf.jsqlparser.JSQLParserException: null
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatements(CCJSqlParserUtil.java:128) ~[jsqlparser-2.1.jar!/:na]
at com.baomidou.mybatisplus.core.parser.AbstractJsqlParser.parser(AbstractJsqlParser.java:60) ~[mybatis-plus-core-3.2.0.jar!/:3.2.0]
... 154 common frames omitted
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "@" "@"
at line 1, column 5.
Was expecting one of:
"ACTION"
"ANY"
"BYTE"
"CASCADE"
"CAST"
"CHAR"
"COLUMN"
"COLUMNS"
"COMMENT"
"COMMIT"
"DESCRIBE"
"DO"
"DOUBLE"
"ENABLE"
"END"
"EXTRACT"
"FIRST"
"FN"
"FOLLOWING"
"IF"
"INDEX"
"INSERT"
"INTERVAL"
"ISNULL"
"KEY"
"LAST"
"LEFT"
"MATERIALIZED"
"NEXTVAL"
"NO"
"NULLS"
"OPEN"
"OPTIMIZE"
"OVER"
"PARTITION"
"PATH"
"PERCENT"
"PRECISION"
"PRIMARY"
"PRIOR"
"RANGE"
"REPLACE"
"RIGHT"
"ROW"
"ROWS"
"SEPARATOR"
"SET"
"SIBLINGS"
"TABLE"
"TEMP"
"TEMPORARY"
"TOP"
"TRUNCATE"
"TYPE"
"UNSIGNED"
"VALUE"
"VALUES"
"XML"
"ZONE"
<K_DATETIMELITERAL>
<K_DATE_LITERAL>
<S_IDENTIFIER>
<S_QUOTED_IDENTIFIER>
at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:20951) ~[jsqlparser-2.1.jar!/:na]
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:20798) ~[jsqlparser-2.1.jar!/:na]
at net.sf.jsqlparser.parser.CCJSqlParser.RelObjectNameExt(CCJSqlParser.java:3033) ~[jsqlparser-2.1.jar!/:na]
at net.sf.jsqlparser.parser.CCJSqlParser.Set(CCJSqlParser.java:612) ~[jsqlparser-2.1.jar!/:na]
at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:185) ~[jsqlparser-2.1.jar!/:na]
at net.sf.jsqlparser.parser.CCJSqlParser.Statements(CCJSqlParser.java:466) ~[jsqlparser-2.1.jar!/:na]
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatements(CCJSqlParserUtil.java:126) ~[jsqlparser-2.1.jar!/:na]
... 155 common frames omitted

这是我正在使用的包裹：

api "com.baomidou:mybatis-plus-boot-starter:3.2.0"

我该怎么修？mybatis plus不支持用户变量？

Answer 1

这对我来说似乎是一个依赖性问题。这是我用旧版本的jsqlparser库运行的查询：

"SELECT COLUMN FROM OLDTABLE a GROUP BY stringcol = 'AAA'"

旧版本的jsqlParser抛出JSQLParserException错误：

Error while executing SQL "SELECT stringCol = 'AAA' from DATASET group by stringCol = 'AAA'": Remote driver error: JDBCException: Error parsing SQL 'SELECT stringCol = 'AAA' from DATASET group by stringCol = 'AAA'' -> JSQLParserException: Encountered unexpected token: "=" "="'. 

在将jsqlParser库从"com.github.jsqlParser:jsqlParser:4.0"更新到"com.github.jsqlparse:jsql解析器:4.4"的最新版本后。错误已得到解决。

这里有一个指向回购的链接，你可以在那里找到他们的发布文档-https://github.com/JSQLParser/JSqlParser

Answer 2

在映射程序中添加sqlparse筛选器：

@SqlParser(filter = true)
RoomSeat selectMultiRoomSeatForUpdateEnhance(@Param("queryParam") Map<String, Object> queryParam,
@Param("user1") User user1,
@Param("user2") User user2);

修复它。