Generated keys with batch executor

[GoogleCodeExporter]

Hello!

MyBatis version in use is 3.0.1.

Generated keys in <insert>s (both defined by @useGeneratedKeys and
<selectKey>) apparently do not work with BATCH executor: the setter for
@keyProperty is not invoked.

As far as I understand MyBatis code, in both cases the batch executor is
not flushed (contrary to regular <select>s):
 * SelectKeyGenerator uses separate executor for its <selectKey> query,
leaving batch executor's queue unaffected;
 * Jdbc3KeyGenerator invokes Statement.getGeneratedKeys() prior to the
statement's actual execution, since the statement is still in the batch
executor's queue.

Changing the executor type to SIMPLE or REUSE immediately makes the
generates keys work fine. If required, I will try to prepare a simple
project illustrating the issue.

Thanks!

Original issue reported on code.google.com by s.volchkov on 7 Jun 2010 at 6:14

[GoogleCodeExporter]

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. 

Original comment by clinton....@gmail.com on 11 Jun 2010 at 7:54

• Added labels: Type-Feature, Version-Release3.x, Component-SqlMaps
• Removed labels: Type-Defect

[GoogleCodeExporter]

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;
    }
}

Original comment by aa.vasil...@gmail.com on 12 Aug 2011 at 10:00

[GoogleCodeExporter]

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

Original comment by aa.vasil...@gmail.com on 12 Aug 2011 at 10:50

[GoogleCodeExporter]

Issue 341 has been merged into this issue.

Original comment by eduardo.macarron on 27 Jan 2012 at 5:37

[GoogleCodeExporter]

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!

Original comment by eduardo.macarron on 28 Jan 2012 at 10:52

• Changed state: Accepted

Attachments:

• issue33.patch

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

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.

Original comment by aa.vasil...@gmail.com on 13 Mar 2012 at 11:12

Attachments:

• stmt.xml
• CloneMagic.java

[GoogleCodeExporter]

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

Original comment by aa.vasil...@gmail.com on 13 Mar 2012 at 11:39

Attachments:

• batching_multikeys.patch

[GoogleCodeExporter]

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

Original comment by eduardo.macarron on 14 Mar 2012 at 2:36

[GoogleCodeExporter]

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?

Original comment by eduardo.macarron on 14 Mar 2012 at 2:42

[GoogleCodeExporter]

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

Original comment by aa.vasil...@gmail.com on 14 Mar 2012 at 8:26

[GoogleCodeExporter]

Do we still need to create another issue?

Original comment by aa.vasil...@gmail.com on 14 Mar 2012 at 2:25

[GoogleCodeExporter]

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?

Original comment by eduardo.macarron on 14 Mar 2012 at 2:41

[GoogleCodeExporter]

Created issue 543 to move conversation there

Original comment by aa.vasil...@gmail.com on 14 Mar 2012 at 6:10

[GoogleCodeExporter]

    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?

Original comment by aa.vasil...@gmail.com on 14 Mar 2012 at 6:14

[GoogleCodeExporter]

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

Original comment by eduardo.macarron on 14 Mar 2012 at 6:26

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

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.

Original comment by brice.du...@gmail.com on 21 Dec 2012 at 10:45

[GoogleCodeExporter]

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

Original comment by sravanth...@gmail.com on 21 Mar 2014 at 9:27

[GoogleCodeExporter]

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

Original comment by eduardo.macarron on 21 Mar 2014 at 10:06

[GoogleCodeExporter]

hi
so now i can do it like [#10 aa.vasil...@gmail.com] recommend ?

Original comment by babybear...@gmail.com on 21 Mar 2014 at 5:40