英文:
mybatis select latest primary key after insert record in PostgreSQL 13
问题
我正在使用MyBatis将记录插入到PostgreSQL 13中，这是插入定义：
<insert id="insertSelective" parameterType="com.dolphin.soa.post.model.entity.Users">
<!--
WARNING - @mbggenerated
This element is automatically generated by MyBatis Generator, do not modify.
-->
<selectKey keyProperty="id" order="AFTER" resultType="java.lang.Long">
PostgreSQL
</selectKey>
insert into users
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="nickname != null">
nickname,
</if>
<!-- 其他属性类似 -->
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="nickname != null">
#{nickname,jdbcType=VARCHAR},
</if>
<!-- 其他属性类似 -->
</trim>
</insert>
但是在执行插入操作时，显示以下错误：
Caused by: org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.executor.ExecutorException: Error selecting key or setting result to parameter object. Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, please exclude the tableName or statementId.
Error SQL: PostgreSQL
...
Caused by: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, please exclude the tableName or statementId.
Error SQL: PostgreSQL
...
Caused by: net.sf.jsqlparser.JSQLParserException: null
...
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "PostgreSQL" <S_IDENTIFIER>
...
我猜测可能是因为PostgreSQL不支持select key操作。那么我该如何修复这个问题呢？
英文:
I am insert a record into PostgreSQL 13 using MyBatis, this is the insert define:
<insert id="insertSelective" parameterType="com.dolphin.soa.post.model.entity.Users">
<!--
WARNING - @mbggenerated
This element is automatically generated by MyBatis Generator, do not modify.
-->
<selectKey keyProperty="id" order="AFTER" resultType="java.lang.Long">
PostgreSQL
</selectKey>
insert into users
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="nickname != null">
nickname,
</if>
<if test="avatarUrl != null">
avatar_url,
</if>
<if test="phone != null">
phone,
</if>
<if test="updatedTime != null">
updated_time,
</if>
<if test="createdTime != null">
created_time,
</if>
<if test="salt != null">
salt,
</if>
<if test="pwd != null">
pwd,
</if>
<if test="sex != null">
sex,
</if>
<if test="level != null">
`level`,
</if>
<if test="phoneRegion != null">
phone_region,
</if>
<if test="countryCode != null">
country_code,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="nickname != null">
#{nickname,jdbcType=VARCHAR},
</if>
<if test="avatarUrl != null">
#{avatarUrl,jdbcType=VARCHAR},
</if>
<if test="phone != null">
#{phone,jdbcType=VARCHAR},
</if>
<if test="updatedTime != null">
#{updatedTime,jdbcType=BIGINT},
</if>
<if test="createdTime != null">
#{createdTime,jdbcType=BIGINT},
</if>
<if test="salt != null">
#{salt,jdbcType=VARCHAR},
</if>
<if test="pwd != null">
#{pwd,jdbcType=VARCHAR},
</if>
<if test="sex != null">
#{sex,jdbcType=INTEGER},
</if>
<if test="level != null">
#{level,jdbcType=VARCHAR},
</if>
<if test="phoneRegion != null">
#{phoneRegion,jdbcType=VARCHAR},
</if>
<if test="countryCode != null">
#{countryCode,jdbcType=INTEGER},
</if>
</trim>
</insert>
but when execute the insert action, shows this error:
Caused by: org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.executor.ExecutorException: Error selecting key or setting result to parameter object. Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, please exclude the tableName or statementId.
Error SQL: PostgreSQL
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92) ~[mybatis-spring-2.0.5.jar!/:2.0.5]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440) ~[mybatis-spring-2.0.5.jar!/:2.0.5]
at com.sun.proxy.$Proxy118.insert(Unknown Source) ~[na:na]
at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:271) ~[mybatis-spring-2.0.5.jar!/:2.0.5]
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62) ~[mybatis-3.5.5.jar!/:3.5.5]
at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:152) ~[mybatis-3.5.5.jar!/:3.5.5]
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85) ~[mybatis-3.5.5.jar!/:3.5.5]
at com.sun.proxy.$Proxy122.insertSelective(Unknown Source) ~[na:na]
at com.dolphin.soa.post.service.impl.user.UserService.add(UserService.java:44) ~[classes!/:na]
at com.dolphin.soa.post.controller.impl.user.UserController.setPwd(UserController.java:96) ~[classes!/:na]
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.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190) ~[spring-web-5.1.13.RELEASE.jar!/:5.1.13.RELEASE]
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138) ~[spring-web-5.1.13.RELEASE.jar!/:5.1.13.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105) ~[spring-webmvc-5.1.13.RELEASE.jar!/:5.1.13.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:893) ~[spring-webmvc-5.1.13.RELEASE.jar!/:5.1.13.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:798) ~[spring-webmvc-5.1.13.RELEASE.jar!/:5.1.13.RELEASE]
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.1.13.RELEASE.jar!/:5.1.13.RELEASE]
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040) ~[spring-webmvc-5.1.13.RELEASE.jar!/:5.1.13.RELEASE]
... 44 common frames omitted
Caused by: org.apache.ibatis.executor.ExecutorException: Error selecting key or setting result to parameter object. Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, please exclude the tableName or statementId.
Error SQL: PostgreSQL
at org.apache.ibatis.executor.keygen.SelectKeyGenerator.processGeneratedKeys(SelectKeyGenerator.java:90) ~[mybatis-3.5.5.jar!/:3.5.5]
at org.apache.ibatis.executor.keygen.SelectKeyGenerator.processAfter(SelectKeyGenerator.java:54) ~[mybatis-3.5.5.jar!/:3.5.5]
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:51) ~[mybatis-3.5.5.jar!/:3.5.5]
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) ~[mybatis-3.5.5.jar!/:3.5.5]
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.5.jar!/:3.5.5]
at com.sun.proxy.$Proxy157.update(Unknown Source) ~[na:na]
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) ~[mybatis-3.5.5.jar!/:3.5.5]
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[mybatis-3.5.5.jar!/:3.5.5]
at misc.interceptor.db.DefaultTimeInterceptor.invokeUpdate(DefaultTimeInterceptor.java:93) ~[dolphin-common-1.0.0-SNAPSHOT.jar!/:na]
at misc.interceptor.db.DefaultTimeInterceptor.intercept(DefaultTimeInterceptor.java:55) ~[dolphin-common-1.0.0-SNAPSHOT.jar!/:na]
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.5.5.jar!/:3.5.5]
at com.sun.proxy.$Proxy155.update(Unknown Source) ~[na:na]
at misc.interceptor.db.DefaultTimeInterceptor.invokeUpdate(DefaultTimeInterceptor.java:93) ~[dolphin-common-1.0.0-SNAPSHOT.jar!/:na]
at misc.interceptor.db.DefaultTimeInterceptor.intercept(DefaultTimeInterceptor.java:55) ~[dolphin-common-1.0.0-SNAPSHOT.jar!/:na]
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.5.5.jar!/:3.5.5]
at com.sun.proxy.$Proxy155.update(Unknown Source) ~[na:na]
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.Invocation.proceed(Invocation.java:49) ~[mybatis-3.5.5.jar!/:3.5.5]
at misc.interceptor.db.DynamicDataSourceInterceptor.intercept(DynamicDataSourceInterceptor.java:86) ~[dolphin-common-1.0.0-SNAPSHOT.jar!/:na]
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.5.5.jar!/:3.5.5]
at com.sun.proxy.$Proxy155.update(Unknown Source) ~[na:na]
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.5.jar!/:3.5.5]
at com.sun.proxy.$Proxy155.update(Unknown Source) ~[na:na]
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.5.jar!/:3.5.5]
at com.sun.proxy.$Proxy155.update(Unknown Source) ~[na:na]
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) ~[mybatis-3.5.5.jar!/:3.5.5]
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184) ~[mybatis-3.5.5.jar!/:3.5.5]
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.5.jar!/:2.0.5]
... 63 common frames omitted
Caused by: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, please exclude the tableName or statementId.
Error SQL: PostgreSQL
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.5.jar!/:3.5.5]
at com.sun.proxy.$Proxy157.prepare(Unknown Source) ~[na:na]
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:87) ~[mybatis-3.5.5.jar!/:3.5.5]
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62) ~[mybatis-3.5.5.jar!/:3.5.5]
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325) ~[mybatis-3.5.5.jar!/:3.5.5]
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[mybatis-3.5.5.jar!/:3.5.5]
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.5.jar!/:3.5.5]
at com.sun.proxy.$Proxy155.query(Unknown Source) ~[na:na]
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.5.jar!/:3.5.5]
at com.sun.proxy.$Proxy155.query(Unknown Source) ~[na:na]
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.5.jar!/:3.5.5]
at com.sun.proxy.$Proxy155.query(Unknown Source) ~[na:na]
at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:111) ~[pagehelper-5.2.0.jar!/:na]
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.5.5.jar!/:3.5.5]
at com.sun.proxy.$Proxy155.query(Unknown Source) ~[na:na]
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.Invocation.proceed(Invocation.java:49) ~[mybatis-3.5.5.jar!/:3.5.5]
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.5.jar!/:3.5.5]
at com.sun.proxy.$Proxy155.query(Unknown Source) ~[na:na]
at org.apache.ibatis.executor.keygen.SelectKeyGenerator.processGeneratedKeys(SelectKeyGenerator.java:67) ~[mybatis-3.5.5.jar!/:3.5.5]
... 109 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]
... 147 common frames omitted
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "PostgreSQL" <S_IDENTIFIER>
at line 1, column 1.
Was expecting one of:
"("
";"
"ALTER"
"BEGIN"
"CALL"
"COMMENT"
"COMMIT"
"CREATE"
"DELETE"
"DESCRIBE"
"DROP"
"EXEC"
"EXECUTE"
"EXPLAIN"
"INSERT"
"MERGE"
"REPLACE"
"SET"
"SHOW"
"TRUNCATE"
"UPDATE"
"UPSERT"
"USE"
"VALUES"
"WITH"
<K_SELECT>
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.Statements(CCJSqlParser.java:475) ~[jsqlparser-2.1.jar!/:na]
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatements(CCJSqlParserUtil.java:126) ~[jsqlparser-2.1.jar!/:na]
... 148 common frames omitted
I am guessing maybe the PostgreSQL not support select key action. so what should I do to fix the problem?
答案1
得分: 1
在 mybatis 中使用了 SQL 解析器 JSqlParser。你异常信息中的最后一个原因 遇到意外的标记: "PostgreSQL" <S_IDENTIFIER> 表明 mybatis 希望解析一个使用此版本 JSqlParser 无法解析的生成SQL。
首先，看到应该被解析的语句会很有帮助。
其次，mybatis 应该以某种方式强制生成不同的 SQL。
话虽如此，由于 mybatis 的无效配置，特别是负责这部分的配置：
<selectKey keyProperty="id" order="AFTER" resultType="java.lang.Long">
PostgreSQL
</selectKey>
可能会生成语法不正确的 SQL。
英文:
Within mybatis the sql parser JSqlParser is used. The last cause Encountered unexpected token: "PostgreSQL" <S_IDENTIFIER> of your exception shows that mybatis wants to parse a generated SQL that cannot be parsed using this version of JSqlParser.
First, it would be helpful to see the statement that should be parsed.
Second, mybatis should somehow forced to generate a different SQL.
Saying that, it could be possible that due to a invalid configuration of mybatis especially the one responsible for this part:
<selectKey keyProperty="id" order="AFTER" resultType="java.lang.Long">
PostgreSQL
</selectKey>
a syntactically incorrect SQL is generated.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论