mybatis select latest primary key after insert record in PostgreSQL 13

Question

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?

Answer 1

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.