Mybatis-3: Support batch inserts

[realcnbs]

AFAIK, currently the only way to do a batch insert is using something like this:

<insert id="insertBatch" parameterType="com.example.Category" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO category (created_at, name, description)
        VALUES
        <foreach collection="entities" item="entity" separator=",">
            (#{entity.createdAt}, #{entity.name}, #{entity.description})
        </foreach>
</insert>

But this won't assign back ids to the every collection element.
Is there currently a way to achieve that which I am not aware of?

[jeffgbutler]

This is not a batch - this is one giant insert statement. There are much better ways to do batch inserts with MyBatis. You can look here for examples:

https://github.com/mybatis/mybatis-3/tree/master/src/test/java/org/apache/ibatis/submitted/batch_keys

Please use the user mailing list for further questions.

[rickcr]

On Wed, Oct 7, 2015 at 5:36 PM, Vitali Carbivnicii <[email protected]

wrote:

AFAIK, currently the only way to do a batch insert is using something like
this:

INSERT INTO category (created_at, name, description)
VALUES

(#{entity.createdAt}, #{entity.name}, #{entity.description})

But this won't assign back ids to the every collection element.
Is there currently a way to achieve that which I am not aware of?

For batch stuff, and someone can correct me if I'm doing it wrong (but it
seems to work) is I create a single mapper insert

for (Upc upc : upcs) {
productBatchMapper.saveUPC(upc);
}

You have to make sure you set up your sqlSessionTemplate for batch though
eg:

class="org.mybatis.spring.mapper.MapperFactoryBean">
value="com.ncs.data.services.batchmapper.ProductBatchMapper"/>

—
Reply to this email directly or view it on GitHub
https://github.com/mybatis/mybatis-3/issues/484.

Rick R

[rickcr]

(oh and the below needs to be in a Transaction.)

I'll shut up now, forgot this wasn't the mailing list. ha

On Wed, Oct 7, 2015 at 5:48 PM, Rick R [email protected] wrote:

On Wed, Oct 7, 2015 at 5:36 PM, Vitali Carbivnicii <
[email protected]> wrote:

AFAIK, currently the only way to do a batch insert is using something
like this:

INSERT INTO category (created_at, name, description)
VALUES

(#{entity.createdAt}, #{entity.name}, #{entity.description})

But this won't assign back ids to the every collection element.
Is there currently a way to achieve that which I am not aware of?

For batch stuff, and someone can correct me if I'm doing it wrong (but it
seems to work) is I create a single mapper insert

for (Upc upc : upcs) {
productBatchMapper.saveUPC(upc);
}

You have to make sure you set up your sqlSessionTemplate for batch though
eg:

class="org.mybatis.spring.mapper.MapperFactoryBean">
value="com.ncs.data.services.batchmapper.ProductBatchMapper"/>

—
Reply to this email directly or view it on GitHub
https://github.com/mybatis/mybatis-3/issues/484.

Rick R

Rick R

[realcnbs]

So you both suggesting to simply iterate the collection and call an insert query for each of them?

[jeffgbutler]

Yes - but the key is to use the BATCH executor. Then the JDBC driver can optimize these bulk inserts, and properly handle the generated keys.

[realcnbs]

Is it ok to have ExecutorType.BATCH this applied to all the queries not only batch ones?

[Shuddh]

It can be done, there is a bug. Please see this:

http://stackoverflow.com/questions/42160637/mybatis-usegeneratedkeys-for-nested-object-in-batch-insert

[dimashi]

@jeffgbutler, I doubt BATCH executor is a better way to handle it, JDBC driver will make multiple network calls, and in most cases this will be slower than making one call with large SQL statement having N sets to insert. N should be the same for better performance.
@Shuddh, I tried solution in stackoverflow, it returns only very first ID, others are not assigned.

[Shuddh]

@dimashi it returns very first only if the "obj2" (in the answer) is a list, otherwise it works fine. If your inner object is not a list then it properly gives the result.
If you need help with inner list do let me know.

[dimashi]

Thank you, @Shuddh! I have simpler case: I am inserting list of objects and trying to get autogenerated keys assigned to these objects:

 <insert id="insertList" useGeneratedKeys="true" keyProperty="pid">
        INSERT INTO postalcode (pid, name)
        VALUES
        <foreach item="item" collection="list" separator="," >
            (#{item.pid}, #{item.name})
        </foreach>

The result is that only first object in the list gets ID assigned.

[Shuddh]

@dimashi Which db are you using plus why are you inserting pid manually?

[dimashi]

I use mySQL 5.6 and in this test case I do not assign pids manually. If pid is 0 or null it forces autogeneration.

[harawata]

Hi @dimashi ,

I also recommend using batch executor especially when the number of rows/columns is large. Single huge statement is bad in terms of memory usage and there also are limitations in some drivers [1].
With MySQL, using BATCH executor with rewriteBatchedStatements=true probably is the best option.

If you still want to solve your problem and use multi-row insert, I would suggest creating a MCVE with CREATE TABLE statement so that we can reproduce the issue.

[1] A few examples that I know of.

• MySQL : the packet size must be within the value of max_alloweed_packet.
• PostgreSQL : the number of placeholders must be lower than 32768.

[dimashi]

Thank you, @harawata, I will look into batch executor to understand the details. I do not use really huge statements. Single insert with 10 rows already improves network latency by x10 or so.

[dulong]

Hi, @harawata , I have the same question

Is it ok to have ExecutorType.BATCH this applied to all the queries not only batch ones?

could you give me some suggestions ?

[harawata]

Hi @dulong ,
Do you want to use ExecutorType.BATCH for non-batch operations?
There may be some disadvantages because batch executor performs extra tasks that are useful only in batch operation.
Why do you want to do that?

[dulong]

Hi @harawata ,thanks for your reply.

I want use the BatchExecutor only for efficient insertion operations. And other operations should use a ReuseExecutor.

I'm using mybatis-spring in my project. How to config mybatis to meet the needs above？It seems that I can't manipulate the SqlSession objects in the spring managed mybatis environment. Is there some manner to tell mybatis-spring to use BatchExecutor for insertion and ReuseExecutor for other operations ?

[harawata]

@dulong ,

MyBatis does not switch executor type automatically.
The common approach, I think, is to create a separate mapper for batch operation.
Something like this.

Someone might know a better answer if you ask it on the mailing list.

[dulong]

@harawata , Is there any way to specify an executor type on a mapper's method telling Mybatis (or mybatis-spring ?) to open a SqlSession with the specified executor ?

Or is it possible to implement a feature like this ?

[Dreampie]

how can return generaterKey for batch insert with annotation？@Insert("")

[harawata]

@dulong ,

Is there any way to specify an executor type on a mapper's method telling Mybatis (or mybatis-spring ?) to open a SqlSession with the specified executor ?

I don't think there is.

Or is it possible to implement a feature like this ?

I am not sure, but it won't be a small change and I wonder if there is much advantage over the solution using a separate mapper.

@Dreampie ,
You seemed to get your answer on #350 .

[inetfuture]

This is not a batch - this is one giant insert statement

@jeffgbutler May I ask, what's difference between batch and 'one giant insert statement' ?

[mallim]

Is there a default keyword in Mysql ? refer to https://stackoverflow.com/questions/8753371/how-to-insert-data-to-mysql-having-auto-incremented-primary-key for more details.

FYI under BATCH execution, Oracle JDBC will only issue one statement even you iterate 100k times. But do test out and see whether the JDBC driver supports it appropriately.

[jeffgbutler]

@inetfuture a batch is an optimization in the JDBC driver for bulk inserts/updates. The driver can prepare a single statement and reuse that prepared statement for each batched insert/update. One significant benefit of using a batch is that auto generated keys can be retrieved for each insert. See this FAQ for how to code a batch insert: https://github.com/mybatis/mybatis-3/wiki/FAQ#how-do-i-code-a-batch-insert

The example shown in the initial post is just one prepared statement with many parameters. MyBatis cannot easily figure out the generated keys in this case. Another issue is that many databases have limits on the number of prepared statement parameters allowed - so this method may not work for larger data sets. You would not have this issue when using a batch.

[inetfuture]

@jeffgbutler Got it, thanks!