Generated keys with batch executor about mybatis HOT 25 CLOSED

This is a known limitation of batch execution.  Furthermore, because generated 
keys need to be stored in the parameter object, there's no place to store 
multiple keys.  I'm sure we could think of something, but this would be a 
feature request and probably would never see the light of day given the 
awkwardness it would add to the API (even if a list of keys was returned, how 
would they be mapped to the resulting objects?).   I'm open to recommendations 
though. 

Hello. I want to make multiple row insert, but I don't know how can I retrieve 
all generated keys. Insert statement:
<insert id="copy" useGeneratedKeys="true" keyProperty="clone.clonedId" 
parameterType="map">
    INSERT INTO objects (parameters, owner_id)  SELECT parameters, #{newOwnerId} FROM objects WHERE id IN
    <foreach collection="clones" item="clone" open="(" separator="," close=")">
    #{clone.sourceId}
    </foreach>
</insert>

The clones structure is list of CloneParameter class:

public class CloneParameter {
    @SuppressWarnings({"UnusedDeclaration"})
    private Integer clonedId;
    private final Integer sourceId;

    public CloneParameter(Integer sourceId) {
        Assert.notNull(sourceId);
        this.sourceId = sourceId;
    }

    public Integer getClonedId() {
        return clonedId;
    }

    public Integer getSourceId() {
        return sourceId;
    }
}

Found mysql-specific answer:
http://stackoverflow.com/questions/5054240/mysql-mulitple-row-insert-select-stat
ement-with-last-insert-id

Issue 341 has been merged into this issue.

Can any one test the snapshot? I did a change in r4613 to fix this issue but 
unfortunately neither hsqldb nor derby like generating keys with batches.

Thanks in advance!

[deleted comment]

[deleted comment]

[deleted comment]

[deleted comment]

Workaround we've used is broken after update to 3.1.0. Statement and class 
we've used is attached. In previous versions setClonedId was called 1 time per 
inserted row. Now it's called only one time per batch statement.

Dirty fix for this issue. Basically it's the same functionality as it was 
before. 
I liked previous non-working version :)

Alex. Thanks for the report. Can you please open a new issue for this?

After having a deeper look I am wondering if you need a workaround. Have you 
tried removing the cloneid stuff and accessing directly to creatorId or 
whatever the generated parameter is?

Actually generated parameter is clonedId. The problem is that it's set only 1 
time, instead of N, where N is number of inserts

Do we still need to create another issue?

Yes please. I think issue #33 is fixed. In your case you are requesting to 
JDBC3 support this sentence:

INSERT INTO TABLE values ('Value1'),('Value2'),('Value3')

For the time being, it should work batching the inserts "in the classic way". I 
mean

INSERT INTO TABLE values ('Value1')
INSERT INTO TABLE values ('Value2')
INSERT INTO TABLE values ('Value3')

And I suppose the performance will be similar, given that statements are 
batched.

Could you try that?

Created issue 543 to move conversation there

    In your case you are requesting to JDBC3 support this sentence:

    INSERT INTO TABLE values ('Value1'),('Value2'),('Value3')

    For the time being, it should work batching the inserts "in the classic way". I mean

    INSERT INTO TABLE values ('Value1')
    INSERT INTO TABLE values ('Value2')
    INSERT INTO TABLE values ('Value3')

    And I suppose the performance will be similar, given that statements are batched.

    Could you try that?

I can try, but I don't want to enable batching for all mappers. Can I do for 1 
statement only?

I suppose that. Is is a session parameter so you can create a session, execute 
that statement and close it. 

[deleted comment]

[deleted comment]

For the record I stumbled across a similar issue that is not due to MyBatis, 
but due to the driver.

H2 JDBC driver doesn't return all the generated keys, only the last one. See : 
https://groups.google.com/forum/?fromgroups=#!topic/h2-database/hoM_bQPMvq0

That means you cannot batch insert entities that have relations in a batch mode 
using H2 ! MyBatis or not. And even worse, depending on your mapping choices 
and java orchestration, you can end up with entities that are associated to the 
wrong records, as only the last generated key is returned mybatis will use this 
value on the first item in the list.

However looking at the MySQL Jdbc driver this 'feature' is supported since 
years. Other JDBC drivers might as well.

Hi Team,

Can you please let me know in which version of mybatis this is fixed.

I want a similar functionality where in I want to do batch execution and set 
the generated keys in to object.

Thanks,
Sravanthi

Hi. This one was marked as fixed in 3.1.0. So you can use anyone from it to the 
latest 3.2.5.

hi
so now i can do it like [#10 [email protected]] recommend ?