Performing batch insert - how to use foreach?

[Aaron Daubman]

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

[Eduardo Macarron]

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

[Aaron Daubman]

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

[Jeff Butler]

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

[KEN]

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

[giriraj]

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)

--
View this message in context: http://mybatis-user.963551.n3.nabble.com/Performing-batch-insert-how-to-use-foreach-tp3888834p3980322.html
Sent from the mybatis-user mailing list archive at Nabble.com.