当前使用版本(必填,否则不予处理)

mybatis-plus-core-3.3.2

该问题是如何引起的？(确定最新版也有问题再提!!!)

pgsql 使用 insert on conflict 在sql解析的时候报错，原sql在 sql cli中执行没有任何问题

重现步骤(如果有就写完整)

pgsql 使用 insert on conflict

报错信息

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

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

Error SQL: insert into customers (id,uid,mobile,email,account_id) values(?,?,?,?,?) ON CONFLICT(account_id,uid) DO UPDATE SET mobile = ?,

        email = ?

The error may exist in file [/xxxMapper.xml]

The error may involve xxMapper.upsert

The error occurred while executing an update

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

Error SQL: insert into customers (id,uid,mobile,email,acid) values(?,?,?,?,?) ON CONFLICT(acid,uid) DO UPDATE SET mobile = ?,

        email = ?
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30) ~[mybatis-3.5.4.jar:3.5.4]
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:199) ~[mybatis-3.5.4.jar:3.5.4]
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184) ~[mybatis-3.5.4.jar:3.5.4]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426) ~[mybatis-spring-2.0.4.jar:2.0.4]
... 93 common frames omitted

Caused by: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, please exclude the tableName or statementId. Error SQL: insert into customers (id,uid,mobile,email,acid) values(?,?,?,?,?) ON CONFLICT(acid,uid) DO UPDATE SET mobile = ?,

        email = ?
at com.baomidou.mybatisplus.core.toolkit.ExceptionUtils.mpe(ExceptionUtils.java:39) ~[mybatis-plus-core-3.3.2.jar:3.3.2]
at com.baomidou.mybatisplus.core.parser.AbstractJsqlParser.parser(AbstractJsqlParser.java:76) ~[mybatis-plus-core-3.3.2.jar:3.3.2]
at com.baomidou.mybatisplus.extension.handlers.AbstractSqlParserHandler.sqlParser(AbstractSqlParserHandler.java:76) ~[mybatis-plus-extension-3.3.2.jar:3.3.2]
at com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor.intercept(PaginationInterceptor.java:168) ~[mybatis-plus-extension-3.3.2.jar:3.3.2]
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.5.4.jar:3.5.4]
at com.sun.proxy.$Proxy210.prepare(Unknown Source) ~[na:na]
at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.prepareStatement(MybatisSimpleExecutor.java:92) ~[mybatis-plus-core-3.3.2.jar:3.3.2]
at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doUpdate(MybatisSimpleExecutor.java:53) ~[mybatis-plus-core-3.3.2.jar:3.3.2]
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[mybatis-3.5.4.jar:3.5.4]
at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.update(MybatisCachingExecutor.java:83) ~[mybatis-plus-core-3.3.2.jar:3.3.2]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) ~[mybatis-3.5.4.jar:3.5.4]
at com.sun.proxy.$Proxy209.update(Unknown Source) ~[na:na]
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) ~[mybatis-3.5.4.jar:3.5.4]
... 99 common frames omitted

Caused by: net.sf.jsqlparser.JSQLParserException: null at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatements(CCJSqlParserUtil.java:137) ~[jsqlparser-3.1.jar:na] at com.baomidou.mybatisplus.core.parser.AbstractJsqlParser.parser(AbstractJsqlParser.java:62) ~[mybatis-plus-core-3.3.2.jar:3.3.2] ... 114 common frames omitted Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "CONFLICT" at line 2, column 8.

Was expecting:

"DUPLICATE"

at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:22439) ~[jsqlparser-3.1.jar:na]
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:22286) ~[jsqlparser-3.1.jar:na]
at net.sf.jsqlparser.parser.CCJSqlParser.Insert(CCJSqlParser.java:1792) ~[jsqlparser-3.1.jar:na]
at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:137) ~[jsqlparser-3.1.jar:na]
at net.sf.jsqlparser.parser.CCJSqlParser.Statements(CCJSqlParser.java:479) ~[jsqlparser-3.1.jar:na]
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatements(CCJSqlParserUtil.java:135) ~[jsqlparser-3.1.jar:na]
... 115 common frames omitted

Comment From: miemieYaho

jsqlparser 解析不了你的sql

Comment From: WenSteven

jsqlparser 解析不了你的sql

是jsqlparser 解析不了，但是既然jsqlparser 解析不了，为什么还要去用这个呢，（或者交给mybatis本身去解析）以及 AbstractSqlParserHandler 中

                    for (ISqlParser sqlParser : this.sqlParserList) {
                        if (sqlParser.doFilter(metaObject, originalSql)) {
                           //这里parse失败，是不是应该catch了，不catch导致其他sqlParser parse不了
                            SqlInfo sqlInfo = sqlParser.parser(metaObject, originalSql);
                            if (null != sqlInfo) {
                                originalSql = sqlInfo.getSql();
                                sqlChangedFlag = true;
                            }
                        }
                    }

Comment From: miemieYaho

你用到了需要解析sql的插件了

Comment From: jiangxiaoqiang

没有解决的方案么

Comment From: liangjh123

Caused by: net.sf.jsqlparser.JSQLParserException: Encountered unexpected token: "conflict"

针对upsert这样的语句 我们会支持解决吗？

Comment From: WatermelonXIGUAGUA

jsqlparser 4.5版本已经解决了这个问题

Comment From: CrazyZfp

jsqlparser 4.5版本已经解决了这个问题

4.5版本只解决了 ON CONFLICT (col1) 这种括号中只有一个列的情况，多列的时候仍然报错。除非使用联合索引代替列名。 ON CONFLICT ON CONSTRAINT uni_index_name

CONFLICT中多列的问题在jsqlparser 4.7 版本中解决了， https://github.com/JSQLParser/JSqlParser/issues/1820

目前 mybatis-plus 最新版(3.5.3.2) 只支持到 jsqlparser 4.6. 请问mybatis-plus后续是否有接入 jsqlparser 4.7的计划呢？ @miemieYaho

Comment From: huayanYu

@CrazyZfp 有,有点复杂.