Camel - camel-sql batch insert using useMessageBodyForSql

Question

I read in the documentation here that

"If batch is set to true, then the interpretation of the inbound message body changes slightly – instead of an iterator of parameters, the component expects an iterator that contains the parameter iterators; the size of the outer iterator determines the batch size."

"From Camel 2.16 onwards you can use the option useMessageBodyForSql that allows to use the message body as the SQL statement, and then the SQL parameters must be provided in a header with the key SqlConstants.SQL_PARAMETERS. This allows the SQL component to work more dynamic as the SQL query is from the message body."

I have the following route:

  from("direct:processLine")
            .setHeader(SqlConstants.SQL_PARAMETERS, simple("${body}"))
            .setBody(constant("INSERT INTO SOME_TABLE "
                    " (Param1, Param2) " +
                    " values " +
                    " (:?Param1,:?Param2)"))
            .to("sql://query?useMessageBodyForSql=true&batch=true&dataSource=dataSource");

The body is a map of key/values matching the parameter list.

This works, but not as expected.

Each line is inserted one by one, instead of the expected batch insert. Is this combination of options even possible?

If not possible, what would be an alternative way of accomplishing this?

Answer 1

When you use sql it will trigger multiple insert statements to process one after other. Try using orm like mybatis or ibatis.

Even you can create a dao class. Invoke the sqlSession pass all map values to orm for inserting as a batch.