英文:
net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "@" "@"
问题
在我的SQL语句中添加变量语句时，类似于这样在MyBatis中：
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
...
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
...
我使用的包是：
api "com.baomidou:mybatis-plus-boot-starter:3.2.0"
我该如何解决这个问题？MyBatis-Plus 不支持用户变量吗？
英文:
When I add variable statement like this in my SQL in mybatis:
set @update_id := 0;
this is my full statement:
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;
but throw this error:
### 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
this is the package I am using:
api "com.baomidou:mybatis-plus-boot-starter:3.2.0"
what should I do to fix it? mybatis-plus not support user variable?
答案1
得分: 1
这对我来说似乎是一个依赖性问题。这是我在旧版本的jsqlparser库上运行的查询：
"SELECT COLUMN FROM OLDTABLE a GROUP BY stringcol = 'AAA'"
旧版本的jsqlParser会抛出JSQLParserException错误：
在执行SQL时出错"SELECT stringCol = 'AAA' from DATASET group by stringCol = 'AAA'": 远程驱动程序错误：JDBCException: 解析SQL错误'SELECT stringCol = 'AAA' from DATASET group by stringCol = 'AAA'' -> JSQLParserException：遇到意外的标记："=" "="。
将jsqlParser库更新到最新版本，从'com.github.jsqlparser:jsqlparser:4.0'更新到'com.github.jsqlparser:jsqlparser:4.4'后，错误得到解决。
你可以在这个仓库链接中找到他们的发布文档：https://github.com/JSQLParser/JSqlParser
英文:
This seemed to be a dependency issue for me. This is the query I ran with the older version of the jsqlparser library:
"SELECT COLUMN FROM OLDTABLE a GROUP BY stringcol = 'AAA'"
The older version of the jsqlParser, throws the JSQLParserException error:
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: "=" "="'.
After updating the jsqlParser library to the latest version from 'com.github.jsqlparser:jsqlparser:4.0' to 'com.github.jsqlparser:jsqlparser:4.4'. The error was resolved.
Here is a link to the repo where you can find their release docs - https://github.com/JSQLParser/JSqlParser
答案2
得分: 0
@SqlParser(filter = true)
RoomSeat selectMultiRoomSeatForUpdateEnhance(@Param("queryParam") Map<String, Object> queryParam,
@Param("user1") User user1,
@Param("user2") User user2);
fix it.
英文:
add sqlparse filter in your mapper:
@SqlParser(filter = true)
RoomSeat selectMultiRoomSeatForUpdateEnhance(@Param("queryParam") Map<String, Object> queryParam,
@Param("user1") User user1,
@Param("user2") User user2);
fix it.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论