MyBatis Upsert操作返回ID问询：自定义ID生成场景下的实现

Hey there! Let's figure out how to get the corresponding ID back from your MyBatis upsert operation. Since you're generating the ID on the application side and using PostgreSQL's ON CONFLICT clause, here's what you need to do:

Step 1: Modify Your MyBatis Insert Statement with RETURNING #

PostgreSQL supports the RETURNING clause which lets you retrieve specific fields after an insert or update. You can add this to the end of your upsert query to get the ID back—whether the operation resulted in an insert or an update.

Here's the adjusted statement:

<insert id="createRecord" parameterType="map" useGeneratedKeys="false" keyProperty="id">
  INSERT INTO MYTABLE (id, name, creator, description)
  VALUES (#{id}, #{name}, #{creator}, #{description})
  ON CONFLICT ON CONSTRAINT (name, creator)
  DO UPDATE SET 
    description = #{description}
  RETURNING id;
</insert>

Note: I removed the redundant name and creator updates in the DO UPDATE block since those are the columns triggering the conflict—updating them doesn't change anything. Feel free to add them back if you have a specific need, but it's unnecessary here.

Step 2: Adjust Your Mapper Interface Method #

You can set the method's return type to match your ID's data type (e.g., Long, Integer) to directly get the returned ID:

Long createRecord(Map<String, Object> params);

When you call this method, it will return the ID of the newly inserted record or the existing record that was updated.

Bonus: Populate the ID Back into Your Input Map #

If you want the returned ID to be automatically added back to the Map you passed in, the keyProperty="id" in the <insert> tag takes care of that. After executing the method, your input map will have the correct ID under the id key—no extra work needed!

Quick Checks #

• Make sure your PostgreSQL JDBC driver version is up-to-date (most modern versions fully support the RETURNING clause).
• Double-check that the constraint (name, creator) exists on your MYTABLE—the ON CONFLICT clause relies on this to detect duplicates.

内容的提问来源于stack exchange，提问作者derek