Mybatis Generator Postgres return created id support

Question

I'm using postgres for a project that I just started to work on, and I realized that Mybatis provide support to retrieve the autogenerated id keys for many databases but unfortunately postgres is not one of them, I modified a little bit the generated sql mappers and: - changing select instead of insert in the generated xml - add "RETURNING id" as last line of each sentence

so it gonna look like:

<select id="insert" keyColumn="id" keyProperty="id" parameterType="com.myproject.Order" ...
    insert into ...
    ...
    RETURNING id
</select>

with that change it works like a charm but the problem is that as soon as the generator is executed again the changes should be applied again manually, I was looking for a plugin to help me to automate the changes but I did not found any that seems to help, did you do any time before something similar? what would be the recommendation?

Answer 1

For the explanation, I created a table users in a schema mbtest.

create table mbtest.users (
  id serial primary key,
  name varchar(20)
);

The below is the <table /> element in the generator config. (in the comment, I wrote sqlStatementType, but it should be sqlStatement)

<table tableName="users" domainObjectName="User"
    schema="mbtest">
  <generatedKey column="id" sqlStatement="JDBC" />
</table>

The generated 'UserMapper.xml' contains the following insert statement. Note that useGeneratedKeys, keyColumn and keyProperty populated by the <generatedKey /> above.

<insert id="insert" keyColumn="id"
    keyProperty="id" parameterType="test.model.User"
    useGeneratedKeys="true">
  <!--
    WARNING - @mbg.generated
    This element is automatically generated by MyBatis Generator, do not modify.
    This element was generated on Sun Dec 08 12:36:30 JST 2019.
  -->
  insert into mbtest.users (name)
  values (#{name,jdbcType=VARCHAR})
</insert>

(As the WARNING says, you should not modify the generated statements)

The insert statement will set the generated key to the id property of User.

try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
  UserMapper mapper = sqlSession.getMapper(UserMapper.class);
  User user = new User();
  user.setName("User1");
  mapper.insert(user);
  sqlSession.commit();
  assertNotNull(user.getId());
}

Here is a demo project so that you can verify yourself.