how to insert multiple rows in mule database connector?

I want to insert multiple rows in mule database connector at a time. Could anyone kindly please help me on this?.

I can successfully insert the below message as a post request to a mule flow.

{
    "patient_todo_id" : "2",
    "comment_date" : "2017-09-20 14:41:16",
    "comment_text" : "send me the steps to check the Pulse rate"
}

How to insert the below post message into a database in a mule flow?

[{
    "patient_todo_id" : "2",
    "comment_date" : "2017-09-20 14:41:16",
    "comment_text" : "send me the steps to check the Pulse rate"
},
{
    "patient_todo_id" : "2",
    "comment_date" : "2017-09-20 14:41:16",
    "comment_text" : "send me the steps to check the Pulse rate"
}]

Please find the below mule flow configuration file which has been configured to insert a single row at a time.

<flow name="carrotcube-patient-todo-commentFlow">
        <http:listener config-ref="HTTP_Listener_Configuration" path="${http.path.mrs.todo.comment}" doc:name="HTTP"/>
        <set-variable variableName="variable" value="#[payload]" mimeType="application/json" doc:name="Variable"/>
        <json:json-to-object-transformer returnClass="java.lang.Object" doc:name="JSON to Object"/>
        <logger message="#[payload.comment_text]" level="INFO" doc:name="Logger"/>
        <db:insert config-ref="MySQL_Configuration" doc:name="Database">
            <db:parameterized-query><![CDATA[insert into patient_todo_detail(patient_todo_id,comment_date,comment_text) values (#[payload.patient_todo_id],#[payload.comment_date],#[payload.comment_text])]]></db:parameterized-query>
        </db:insert>
</flow>

Solution

Ashok, I just crossed this hurdle, so you got really lucky here as I had to struggle through this for a while. Very surprising that such a common scenario was not to be found on the Internet. The steps are listed below ,and the flow is below too.

1) Use transform to convert the post payload to java list (application/java) – the output should just have payload (remove the curly braces and just put payload). This payload should come from the body.

2) Use for each scope and put the database insert statement there

3) In the database insert statement use [payload[‘username’]] kind of syntax to refer to the value of username in the current record (or whatever field names you have). I am using a stored prod to insert, but you get the idea.

I am not able to post the flow here .. it's cutting it off and showing it weird. I'll try to send you through email if you can share.

Now all I have to figure out is how to send a nice message back with the insert status. Anybody who already has done that.. appreciate your inputs!