search

baomidou / mybatis-plus

An powerful enhanced toolkit of MyBatis for simplify development
https://baomidou.com
Apache License 2.0
16.32k stars 4.3k forks source link

自定义多字段in查询报错 #1673

Closed ITG001 closed 4 years ago

ITG001 commented 4 years ago

当前版本3.2.0

再mybatis mapper里面，自定义多字段in插叙，以user表为例

<select id="getUserList" resultMap="BaseResultMap">
    select id,username,password,name
    from  user
    where (username,name) in
    <foreach collection="list" item="item" open="(" separator="," close=")">
        (#{item.username} , #{item.name})
    </foreach>
</select>

报错信息如下：

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException:

Error querying database. Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, please exclude the tableName or statementId.

Error SQL: select id,username,password,name,salt,tel,email,status,last_login_time,create_time,modify_time,system_id,sex,remark from user where (username,name) in (
(? , ?) )

The error may exist in file [D:\work\project\bvc-service\bvc-face\bvc-face-provider\target\classes\mapper\system\UserMapper.xml]

The error may involve com.bvc.service.face.system.mapper.UserMapper.getUserList

The error occurred while executing a query

Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, please exclude the tableName or statementId.

Error SQL: select id,username,password,name,salt,tel,email,status,last_login_time,create_time,modify_time,system_id,sex,remark from user where (username,name) in (
(? , ?) )

at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:78)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
at com.sun.proxy.$Proxy124.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:158)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:76)
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:61)
at com.sun.proxy.$Proxy130.getUserList(Unknown Source)
at com.bvc.service.test.TestMyBatisPlus.test(TestMyBatisPlus.java:36)
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.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:74)
at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:84)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
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.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)

Caused by: org.apache.ibatis.exceptions.PersistenceException:

Error querying database. Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, please exclude the tableName or statementId.

Error SQL: select id,username,password,name,salt,tel,email,status,last_login_time,create_time,modify_time,system_id,sex,remark from user where (username,name) in (
(? , ?) )

The error may exist in file [D:\work\project\bvc-service\bvc-face\bvc-face-provider\target\classes\mapper\system\UserMapper.xml]

The error may involve com.bvc.service.face.system.mapper.UserMapper.getUserList

The error occurred while executing a query

Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, please exclude the tableName or statementId.

Error SQL: select id,username,password,name,salt,tel,email,status,last_login_time,create_time,modify_time,system_id,sex,remark from user where (username,name) in (
(? , ?) ) at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:149) 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:426) ... 37 more Caused by: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, please exclude the tableName or statementId. Error SQL: select id,username,password,name,salt,tel,email,status,last_login_time,create_time,modify_time,system_id,sex,remark from user where (username,name) in (
(? , ?) ) at com.baomidou.mybatisplus.core.toolkit.ExceptionUtils.mpe(ExceptionUtils.java:39) at com.baomidou.mybatisplus.core.parser.AbstractJsqlParser.parser(AbstractJsqlParser.java:74) at com.baomidou.mybatisplus.extension.handlers.AbstractSqlParserHandler.sqlParser(AbstractSqlParserHandler.java:76) at com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor.intercept(PaginationInterceptor.java:155) at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) at com.sun.proxy.$Proxy181.prepare(Unknown Source) at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.prepareStatement(MybatisSimpleExecutor.java:94) at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:66) 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.BaseExecutor.query(BaseExecutor.java:136) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147) ... 43 more Caused by: net.sf.jsqlparser.JSQLParserException at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatements(CCJSqlParserUtil.java:128) at com.baomidou.mybatisplus.core.parser.AbstractJsqlParser.parser(AbstractJsqlParser.java:60) ... 53 more Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "," "," at line 3, column 18.

Was expecting one of:

"&"
"&&"
")"
"::"
"<<"
">>"
"AND"
"COLLATE"
"^"
"|"

at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:20951)
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:20798)
at net.sf.jsqlparser.parser.CCJSqlParser.AndExpression(CCJSqlParser.java:6834)
at net.sf.jsqlparser.parser.CCJSqlParser.OrExpression(CCJSqlParser.java:6797)
at net.sf.jsqlparser.parser.CCJSqlParser.Expression(CCJSqlParser.java:6768)
at net.sf.jsqlparser.parser.CCJSqlParser.WhereClause(CCJSqlParser.java:5785)
at net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:3427)
at net.sf.jsqlparser.parser.CCJSqlParser.SetOperationList(CCJSqlParser.java:3600)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectBody(CCJSqlParser.java:3282)
at net.sf.jsqlparser.parser.CCJSqlParser.Select(CCJSqlParser.java:3275)
at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:119)
at net.sf.jsqlparser.parser.CCJSqlParser.Statements(CCJSqlParser.java:466)
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatements(CCJSqlParserUtil.java:126)
... 54 more

单个字段in查询正常，如果把版本降到2.3.3 多字段完全正常，不会报错

miemieYaho commented 4 years ago

jsqlparser 不支持解析这种sql

ITG001 commented 4 years ago

有办法解决这个问题么

miemieYaho commented 4 years ago

用SqlParser注解过滤掉该method,如果用了多租户就自己拼租户条件