mybatis-user

Performing batch insert - how to use foreach?

Classic

List

Threaded

6 messages Options

Aaron Daubman

Performing batch insert - how to use foreach?

Greetings,

I am attempting to perform a batch insert using a foreach block (attempting to do this from camel-mybatis).

My bean looks like:

public class dataCore {

private long event_id;

private Date start_time_val;

private Date end_time_val;

...

getters, settters, etc...

}

In my dataCore.xml mapper file I have:

event_id,

start_time_val,

end_time_val

...

</sql>

INSERT INTO CORE_DATA (

)

VALUES

#{dataCore.event_id}, #{dataCore.start_time_val}, #{dataCore.end_time_val}...

</foreach>

</insert>

When running, I get the following error:

org.apache.ibatis.exceptions.PersistenceException:

### Error updating database. Cause: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'list' in 'class customArcSightIngest.dataCore'

### The error may exist in mybatis/dataCore.xml

### The error may involve dataCore.batchInsertdataCore

### The error occurred while executing an update

I think the root of the problem is that I don't understand what "collection" should refer to in the foreach statement (even after reading: http://www.mybatis.org/core/dynamic-sql.html )

For this insertion, I am passing in a List<dataCore>.

My goal is to have MyBatis execute a batch insert along the lines of:

INSERT INTO CORE_DATA
(event_id, start_time_val, end_time_val)
VALUES
(100, 'Row 1 a', 'Row 1 b', 'Row 1 c'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),

...
(103, 'Name 4', 'Value 4', 'Other 4');

Any pointers, suggestions, working batch-insert examples, etc?

Thanks,

Aaron

Eduardo Macarron

Re: Performing batch insert - how to use foreach?

two hints:
- you do not need the open/close attributes

<foreach item="dataCore" collection="list" separator="," >
(#{dataCore.event_id}, #{dataCore.start_time_val},#{dataCore.end_time_val}...)
</foreach>

- and try passing an object or a map as a parameter with the list inside it

El día 6 de abril de 2012 00:15, Aaron Daubman <[hidden email]> escribió:

> Greetings,
>
> I am attempting to perform a batch insert using a foreach block (attempting
> to do this from camel-mybatis).
>
> My bean looks like:
> public class dataCore {
> private long event_id;
> private Date start_time_val;
> private Date end_time_val;
> ...
> getters, settters, etc...
> }
>
> In my dataCore.xml mapper file I have:
> <sql id="dataCoreColumns">
> event_id,
> start_time_val,
> end_time_val
> ...
> </sql>
>
> <insert id="batchInsertdataCore" parameterType="list">
> INSERT INTO CORE_DATA (
> <include refid="dataCoreColumns"/>
> )
> VALUES
> <foreach item="dataCore" collection="list" open="(" separator=","
> close=")">
> #{dataCore.event_id}, #{dataCore.start_time_val},
> #{dataCore.end_time_val}...
> </foreach>
> </insert>
>
> When running, I get the following error:
> org.apache.ibatis.exceptions.PersistenceException:
> ### Error updating database. Cause:
> org.apache.ibatis.reflection.ReflectionException: There is no getter for
> property named 'list' in 'class customArcSightIngest.dataCore'
> ### The error may exist in mybatis/dataCore.xml
> ### The error may involve dataCore.batchInsertdataCore
> ### The error occurred while executing an update
>
> I think the root of the problem is that I don't understand what "collection"
> should refer to in the foreach statement (even after
> reading: http://www.mybatis.org/core/dynamic-sql.html )
>
> For this insertion, I am passing in a List<dataCore>.
>
> My goal is to have MyBatis execute a batch insert along the lines of:
>
> INSERT INTO CORE_DATA
> (event_id, start_time_val, end_time_val)
> VALUES
> (100, 'Row 1 a', 'Row 1 b', 'Row 1 c'),
> (101, 'Name 2', 'Value 2', 'Other 2'),
> (102, 'Name 3', 'Value 3', 'Other 3'),
> ...
> (103, 'Name 4', 'Value 4', 'Other 4');
>
> Any pointers, suggestions, working batch-insert examples, etc?
>
> Thanks,
> Aaron

Aaron Daubman

Re: Performing batch insert - how to use foreach?

Thanks for the reply. I have the insert working now, however, I see something a little concerning being logged. When mybatis prepares the statement, it appears to be adding a row for every list of values that will eventually be inserted.

E.g., for the following xml config:

---snip---

INSERT INTO DATA_CORE

(<include refid="dataCoreColumns"/>)

VALUES

(#{dataCore.event_id}, #{dataCore.start_time_val}, #{dataCore.end_time_val})

</foreach>

</insert>

---snip---

When I send in a list with two dataCore objects, I see the following logged:

---snip---

DEBUG ==> Preparing:

INSERT INTO DATA_CORE

( event_id, start_time_val, end_time_val)

VALUES

(?, ?, ?) ,

(?, ?, ?)

---snip---

While this works, I am concerned about performance when I start passing in lists with 1000-10000 objects. Shouldn't the prepared statement always look like just:

---

INSERT INTO DATA_CORE

( event_id, start_time_val, end_time_val)

VALUES

(?, ?, ?)

---

Regardless of how many rows will eventually be inserted?

Am I misunderstanding prepared statements? Is there a way to configure it so prepared statements only include the single row?

(By the way, I contributed this fix to camel-mybatis to help resolve the issue I was emailing about below: https://issues.apache.org/jira/browse/CAMEL-5143 )

Thanks again,

Aaron

On Friday, April 6, 2012 2:42:37 AM UTC-4, Eduardo wrote:

two hints:
- you do not need the open/close attributes
<foreach item="dataCore" collection="list" separator="," >
(#{dataCore.event_id}, #{dataCore.start_time_val},#{dataCore.end_time_val}...)
</foreach>
- and try passing an object or a map as a parameter with the list inside it
El día 6 de abril de 2012 00:1

> Greetings,
>
> I am attempting to perform a batch insert using a foreach block (attempting
> to do this from camel-mybatis).
>
> My bean looks like:
> public class dataCore {
> private long event_id;
> private Date start_time_val;
> private Date end_time_val;
> ...
> getters, settters, etc...
> }
>
> In my dataCore.xml mapper file I have:
> <sql id="dataCoreColumns">
> event_id,
> start_time_val,
> end_time_val
> ...
> </sql>
>
> <insert id="batchInsertdataCore" parameterType="list">
> INSERT INTO CORE_DATA (
> <include refid="dataCoreColumns"/>
> )
> VALUES
> <foreach item="dataCore" collection="list" open="(" separator=","
> close=")">
> #{dataCore.event_id}, #{dataCore.start_time_val},
> #{dataCore.end_time_val}...
> </foreach>
> </insert>
>
> When running, I get the following error:
> org.apache.ibatis.exceptions.PersistenceException:
> ### Error updating database. Cause:
> org.apache.ibatis.reflection.ReflectionException: There is no getter for
> property named 'list' in 'class customArcSightIngest.dataCore'
> ### The error may exist in mybatis/dataCore.xml
> ### The error may involve dataCore.batchInsertdataCore
> ### The error occurred while executing an update
>
> I think the root of the problem is that I don't understand what "collection"
> should refer to in the foreach statement (even after
> reading: http://www.mybatis.org/core/dynamic-sql.html )
>
> For this insertion, I am passing in a List<dataCore>.
>
> My goal is to have MyBatis execute a batch insert along the lines of:
>
> INSERT INTO CORE_DATA
> (event_id, start_time_val, end_time_val)
> VALUES
> (100, 'Row 1 a', 'Row 1 b', 'Row 1 c'),
> (101, 'Name 2', 'Value 2', 'Other 2'),
> (102, 'Name 3', 'Value 3', 'Other 3'),
> ...
> (103, 'Name 4', 'Value 4', 'Other 4');
>
> Any pointers, suggestions, working batch-insert examples, etc?
>
> Thanks,
> Aaron

Jeff Butler

Re: Performing batch insert - how to use foreach?

MyBatis does exactly what you ask it to - no magic. The way you've
written the SQL, it will create a single prepared statement with A TON
of parameters. That is not a batch - it is a single statement.

A batch is when you execute the same statement multiple times. I
think that's what you probably want to do in this case. So rewrite
your SQL so it only inserts a single row, then call the insert
repeatedly in a loop in your Java service layer. (In other words - no
<foreach> in your XML).

Jeff Butler

On Sat, Apr 7, 2012 at 2:54 PM, Aaron Daubman <[hidden email]> wrote:

> Thanks for the reply. I have the insert working now, however, I see
> something a little concerning being logged. When mybatis prepares the
> statement, it appears to be adding a row for every list of values that will
> eventually be inserted.
>
> E.g., for the following xml config:
> ---snip---
> <insert id="batchInsertdataCore" parameterType="java.util.List">
> INSERT INTO DATA_CORE
> (<include refid="dataCoreColumns"/>)
> VALUES
> <foreach collection="list" item="dataCore" separator=",">
> (#{dataCore.event_id}, #{dataCore.start_time_val},
> #{dataCore.end_time_val})
> </foreach>
> </insert>
> ---snip---
>
> When I send in a list with two dataCore objects, I see the following logged:
> ---snip---
> DEBUG ==> Preparing:
> INSERT INTO DATA_CORE
> ( event_id, start_time_val, end_time_val)
> VALUES
> (?, ?, ?) ,
> (?, ?, ?)
> ---snip---
>
> While this works, I am concerned about performance when I start passing in
> lists with 1000-10000 objects. Shouldn't the prepared statement always look
> like just:
> ---
> INSERT INTO DATA_CORE
> ( event_id, start_time_val, end_time_val)
> VALUES
> (?, ?, ?)
> ---
> Regardless of how many rows will eventually be inserted?
>
> Am I misunderstanding prepared statements? Is there a way to configure it so
> prepared statements only include the single row?
>
> (By the way, I contributed this fix to camel-mybatis to help resolve the
> issue I was emailing about
> below: https://issues.apache.org/jira/browse/CAMEL-5143 )
>
> Thanks again,
> Aaron
>
> On Friday, April 6, 2012 2:42:37 AM UTC-4, Eduardo wrote:
>>
>> two hints:
>> - you do not need the open/close attributes
>>
>> <foreach item="dataCore" collection="list" separator="," >
>> (#{dataCore.event_id},
>> #{dataCore.start_time_val},#{dataCore.end_time_val}...)
>> </foreach>
>>
>> - and try passing an object or a map as a parameter with the list inside
>> it
>>
>> El día 6 de abril de 2012 00:1
>> > Greetings,
>> >
>> > I am attempting to perform a batch insert using a foreach block
>> > (attempting
>> > to do this from camel-mybatis).
>> >
>> > My bean looks like:
>> > public class dataCore {
>> > private long event_id;
>> > private Date start_time_val;
>> > private Date end_time_val;
>> > ...
>> > getters, settters, etc...
>> > }
>> >
>> > In my dataCore.xml mapper file I have:
>> > <sql id="dataCoreColumns">
>> > event_id,
>> > start_time_val,
>> > end_time_val
>> > ...
>> > </sql>
>> >
>> > <insert id="batchInsertdataCore" parameterType="list">
>> > INSERT INTO CORE_DATA (
>> > <include refid="dataCoreColumns"/>
>> > )
>> > VALUES
>> > <foreach item="dataCore" collection="list" open="("
>> > separator=","
>> > close=")">
>> > #{dataCore.event_id}, #{dataCore.start_time_val},
>> > #{dataCore.end_time_val}...
>> > </foreach>
>> > </insert>
>> >
>> > When running, I get the following error:
>> > org.apache.ibatis.exceptions.PersistenceException:
>> > ### Error updating database. Cause:
>> > org.apache.ibatis.reflection.ReflectionException: There is no getter for
>> > property named 'list' in 'class customArcSightIngest.dataCore'
>> > ### The error may exist in mybatis/dataCore.xml
>> > ### The error may involve dataCore.batchInsertdataCore
>> > ### The error occurred while executing an update
>> >
>> > I think the root of the problem is that I don't understand what
>> > "collection"
>> > should refer to in the foreach statement (even after
>> > reading: http://www.mybatis.org/core/dynamic-sql.html )
>> >
>> > For this insertion, I am passing in a List<dataCore>.
>> >
>> > My goal is to have MyBatis execute a batch insert along the lines of:
>> >
>> > INSERT INTO CORE_DATA
>> > (event_id, start_time_val, end_time_val)
>> > VALUES
>> > (100, 'Row 1 a', 'Row 1 b', 'Row 1 c'),
>> > (101, 'Name 2', 'Value 2', 'Other 2'),
>> > (102, 'Name 3', 'Value 3', 'Other 3'),
>> > ...
>> > (103, 'Name 4', 'Value 4', 'Other 4');
>> >
>> > Any pointers, suggestions, working batch-insert examples, etc?
>> >
>> > Thanks,
>> > Aaron

LI KEN

Re: Performing batch insert - how to use foreach?

Using a foreach block, it is only adapted to mysql, other database not
support it

giriraj

Re: Performing batch insert - how to use foreach?

In reply to this post by Aaron Daubman

Can you post the complete working example?
I am trying to achieve the same, but running into following errors:

; bad SQL grammar []; nested exception is java.sql.SQLException: Unexpected token: ( in statement [INSERT INTO My_Table
(My_TableKy)
VALUES

(?)

(?)]
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)