[CAMEL-5143] Add a new camel-mybatis statementType of 'InsertList' (similar to SelectOne versus SelectList) to allow for mybatis foreach driven batch insert statements

• Type: Improvement
• Status: Resolved
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 2.10.0
• Fix Version/s: 2.10.0
• Component/s: camel-mybatis
• Labels: batch foreach insert mybatis
• Patch Info: Patch Available
• Estimated Complexity: Unknown
• Assignee: Claus Ibsen
• Reporter: Aaron Daubman
• Created: 06/Apr/12 03:45
• Updated: 08/Apr/12 08:05
• Resolved: 06/Apr/12 08:11

Description

The camel-mybatis code will iterate over any list passed in and attempt to insert each item individually, bypassing foreach support in statements like:
<insert id="batchInsertdataCore" parameterType="java.util.List">
INSERT INTO CORE_DATA (
<include refid="dataCoreColumns"/>
)
VALUES (
<foreach item="dataCore" collection="_parameter" open="" close="" separator="),(">
#

{dataCore.event_id}

, #

{dataCore.start_time_val}

, #

{dataCore.end_time_val}

</foreach>
)
</insert>

This results in mybatis generating the following error even when the route is receiving a list of objects as desired:

1. 1. 1. Error updating database. Cause: org.apache.ibatis.builder.BuilderException: Error evaluating expression '_parameter'. Return value (dataCore{
         event_id=111222333,
         start_time_val=Thu Mar 01 02:03:04 EST 2001,
         end_time_val=Thu Mar 01 02:03:05 EST 2001,
         }
         ) was not iterable.
      2. The error may exist in mybatis/dataCore.xml
      3. The error may involve dataCore.batchInsertdataCore
      4. The error occurred while executing an update

---from camel/trunk/components/camel-mybatis/src/main/java/org/apache/camel/component/mybatis/MyBatisProducer.java —
private void doInsert(Exchange exchange) throws Exception {
SqlSessionFactory client = endpoint.getSqlSessionFactory();
SqlSession session = client.openSession();
try {
Object result;
Object in = exchange.getIn().getBody();
if (in != null) {
// lets handle arrays or collections of objects
Iterator<?> iter = ObjectHelper.createIterator(in);
while (iter.hasNext()) {
Object value = iter.next();
LOG.trace("Inserting: {} using statement: {}", value, statement);
result = session.insert(statement, value);
doProcessResult(exchange, result);
}
} else {
LOG.trace("Inserting using statement: {}", statement);
result = session.insert(statement);
doProcessResult(exchange, result);
}
} finally

{ session.commit(); session.close(); }

}
---from camel/trunk/components/camel-mybatis/src/main/java/org/apache/camel/component/mybatis/MyBatisProducer.java —

It should be simple to add a new statementType = 'InsertList' (similar to SelectOne versus SelectList) and add it to the switch statement further up in the MyBatisProducer.java code.

Then a new doInsertBatch would be created, copying the code above and just emoving the iterator related code, simply calling:
result = session.insert(statement, in);