MyBatis useGeneratedKeys for nested object in batch insert

Question

While batch insertion and getting the generated key, getting the error. Batch insertion works fine.

Object Structure:

Object 1:

Long id, String name, Obj2 obj

Object 2: (Obj2)

Long id, String value

Both the objects are stored in different tables.

Table object1

id | name  | object2_id (Foreign Key)

Table object2

id | value

Now I have a list of Object 1 to insert.

The process will be insert Object 2 get the id, and insert Object 1 with " id " of Object2 (as foreign key).

While inserting Object2, the insert block in Mapper.xml

Case 1:

<insert id="batchInsert" parameterType="list" useGeneratedKeys="true" keyProperty="obj1s.obj2.id">
<!-- obj1s is name of the list --> 
    insert into object2 (value) values 
        <foreach collection="obj1s" item="obj1" separator=",">
            (#{obj1.obj2.id})
        </foreach>
</insert>

ERROR: Error getting generated key or setting result to parameter object.

Case 2:

<insert id="batchInsert" parameterType="list" useGeneratedKeys="true" keyProperty="obj1.obj2.id">
<!-- obj1 so as to access the object of foreach loop --> 
    insert into object2 (value) values 
        <foreach collection="obj1s" item="obj1" separator=",">
            (#{obj1.obj2.id})
        </foreach>
</insert>

ERROR: Error getting generated key or setting result to parameter object. Cause: org.apache.ibatis.binding.BindingException: Parameter 'obj1' not found. Available parameters are [obj1s, param1]

Case 3:

<insert id="batchInsert" parameterType="list" useGeneratedKeys="true" keyProperty="obj2.id">
<!-- obj2 is the object with variable id to store generated key --> 
    insert into object2 (value) values 
        <foreach collection="obj1s" item="obj1" separator=",">
            (#{obj1.obj2.id})
        </foreach>
</insert>

ERROR: Error getting generated key or setting result to parameter object. Cause: org.apache.ibatis.binding.BindingException: Parameter 'obj2' not found. Available parameters are [obj1s, param1]

Is there anyway to achieve this? Maybe using selectKey, but selectkey is used for DBs not supporting Autogenerated key.

Using MyBatis 3.3.1 and Mysql.

please clarify the mapping class/properties <=> table/columns — blackwizard, Feb 10 '17 at 13:46
@blackwizard The mapping can be figured out by column and property names. — Shuddh, Feb 10 '17 at 13:55

score 12 · Accepted Answer · answered Feb 10 '17 at 15:20

So, I figured it out. There is this bug with MyBatis for multi-row insert and useGenerated key. The Bug is the list variable name must be "list" when doing batch insertion and getting generated key. Then access the object accordingly. So for above emxample the code will look like this:

<insert id="batchInsert" parameterType="list" useGeneratedKeys="true" keyProperty="obj2.id">
<!-- obj2 is the object with variable id to store generated key --> 
insert into object2 (value) values 
    <foreach collection="list" item="obj1" separator=",">
        (#{obj1.obj2.id})
    </foreach>

and the mapper.java method declaration will look like this:

public Integer batchInsert(@Param("list")List<Obj1> obj1);

The name of Variable must be list. Nothing else.

And thanks @blackwizard, I got to revisit and check the bug, which landed me to this answer.

It seems they designed it this way. I might have to search for the link. Discussed with the developers a year ago. — Shuddh, Feb 06 '18 at 15:29

score 0 · Answer 2 · edited May 23 '17 at 12:24

0

You must access obj.id, obj is the property name, Obj2 is the type name.

Furthermore, retrieving the generated key works/makes sense only for individual inserts. Indeed: you want to insert N records, but your code will generate and execute a single (giant) statement. This is not batch.

Iterate over your list in the Java, call simpler insert statement (no more foreach there) in the loop, then every generated key can be bound to the matching object. Open the SqlSession with ExecutorType.REUSE so that the statement is prepared only once, only parameters are sent at every iteration.

I have already answered this kind of question.

edited May 23 '17 at 12:24

Community

1
1

answered Feb 10 '17 at 14:07

blackwizard

2,034
1
9
21

I am accessing via property name only. And when I use single (giant) statement, I am able to get the generated keys for all rows, but not for nested objects for the list. – Shuddh Feb 10 '17 at 14:35
You are right, I've just reviewed the doc, retrieving the auto-generated keys is indeed possible when using multi-row inserts. – blackwizard Feb 10 '17 at 15:06
I meant, according to your mybatis snippets, you access a property named _obj2_ while the property name is _obj_ in class definition: `Obj2 obj `, just a typo that would explain at least the BindingException. – blackwizard Feb 10 '17 at 15:10
Is the method [SelectKeyGenerator#processGeneratedKeys](http://grepcode.com/file/repo1.maven.org/maven2/org.mybatis/mybatis/3.2.6/org/apache/ibatis/executor/keygen/SelectKeyGenerator.java) executed to manage the generated key? If you can check by break point there, firt the simple case, and then multi-row insert. – blackwizard Feb 10 '17 at 15:20
Thanks I got the answer. you can check that, and can try if you are working on mybatis anytime, as its working for me – Shuddh Feb 10 '17 at 15:22

MyBatis useGeneratedKeys for nested object in batch insert

2 Answers2