Issue with useGeneratedKeys in MyBatis When Using foreach and ON DUPLICATE KEY UPDATE

[Jacob-Aiden-Son]

Dear MyBatis Team,

I hope this message finds you well. Thank you for your continued efforts in maintaining and improving MyBatis. I’d like to report an issue I’ve encountered and request your guidance on it.

Description:

When using MyBatis for an INSERT statement with foreach and ON DUPLICATE KEY UPDATE, the useGeneratedKeys option only generates keys for the first object in the list. Subsequent objects in the list do not have their generated keys populated. This issue significantly impacts batch operations that rely on the generated key values for further processing.

Environment:

• MyBatis version: 
  • mybatis spring boot starter 2.3.2
  • mybatis 3.5.14
  • mybatis spring 2.1.2
• Database: mysql-connector-j 8.0.33 (spring boot BOM)
• Java version: 11

Steps to Reproduce:

1. Configure a MyBatis mapper with the following SQL statement:

   INSERT INTO table_name (column1, column2)
   VALUES
   <foreach collection="list" item="item" separator=",">
       (#{item.column1}, #{item.column2})
   </foreach>
   ON DUPLICATE KEY UPDATE column2 = VALUES(column2)

2. Enable useGeneratedKeys in the MyBatis configuration.
3. Pass a list of objects to the mapper method for insertion.

Expected Behavior:
useGeneratedKeys should populate the generated keys for all objects in the list, not just the first one.

Actual Behavior:
Only the first object in the list receives its generated key value. The rest remain unpopulated.

Workaround:
Currently, there appears to be no straightforward workaround for this issue without either disabling ON DUPLICATE KEY UPDATE or implementing manual logic to retrieve the keys.

Additional Information:
This behavior might be related to how MyBatis processes batch inserts and how it retrieves the generated keys when ON DUPLICATE KEY UPDATE is involved.

Request:
Please investigate this issue and provide guidance or a fix. If additional information or a sample project is required, I can provide it.

Thank you!

[harawata]

Hello @Jacob-Aiden-Son ,

This is a frequently asked question.
Please see my answer on the recent one #3206 .

[Jacob-Aiden-Son]

Hello @harawata ,
Thank you for the guidance,
I will refer to the provided answer.