CODEWITHSUNDEEP
arraylistmuleesb
Jaguar
Jaguar

Reputation: 419

Insert Arraylist in Database in Mule

I am trying to insert the contents of an ArrayList to a SQL Server database using Mule ESB. The ArrayList looks like 

[
    {Id=a1o90000001muvWAAQ, Billing_Number__c=1000005, type=Call_Log__c}, 
    {Id=a1o90000001muvXAAQ, Billing_Number__c=1000006, type=Call_Log__c}
]

How should I insert this into the database in an optimized way?

My mule flow is as below. 

<sfdc:query config-ref="Salesforce__Basic_authentication" query="#[flowVars.query]" 
        doc:name="Salesforce"/>
<set-variable variableName="result" value="#[new java.util.ArrayList()]" 
        doc:name="Variable"/>
<foreach doc:name="For Each">
    <set-variable variableName="itrresult" value="#result.add(message.payload)]" 
            doc:name="Variable"/>
</foreach>
<splitter expression="#[flowVars.result]" doc:name="Splitter"/>
<logger message="#[payload['Id']]" level="INFO" doc:name="Logger"/>

#[payload['Id']] gives me the Id LoggerMessageProcessor: a1o90000001mzgzAAA in log.

I am not sure what i am doing is the right way though. I am just on the beginning of the learning curve in Mule.

Upvotes: 2

Views: 7272

Answers (4)

Ben Asmussen
Ben Asmussen

Reputation: 994

Mule support the bulk mode to insert a collection of data. I prefer the db:in-param Element with labels within the SQL statement. Configuration has been tested with Mule 3.8.5.

<db:insert config-ref="databaseConfiguration" bulkMode="true" doc:name="Database">
    <db:parameterized-query>
    <![CDATA[INSERT INTO T_ORDER (ORDER_NO,CUSTOMER_NO) VALUES (:orderNo, :customerNo)]]>
    </db:parameterized-query>
    <db:in-param name="orderNo"  value="#[payload.orderNo]" />
    <db:in-param name="customerNo"  value="#[payload.customerNo]" />
</db:insert>

The bulk mode offers a much better performance. Make sure your DBMS is configured correctly for this use case.

Collection with huge entries can be partioned, for example to sub collection of 100 entries, before calling the db:insert component.

<!-- Sample: Collection with 1.000 entries -->
<!-- will be chunked into sub collections with 100 entries -->
<foreach batchSize="100" />

Upvotes: 0

ericbn
ericbn

Reputation: 10998

You don't need a Splitter or the Foreach scope. The Database connector has a bulk mode that makes the connector accept a collection as payload.

Enable to submit collections of data with one query, [...]. Enabling bulk mode improves the performance of your applications as it reduces the number of individual query executions. Bulk mode requires a parameterized query with at least one parameter.

Enable it with bulkMode="true" in an insert, update or delete operation.

EDIT: Use this right after your Salesforce Query component:

    <sfdc:query config-ref="" query="#[flowVars.query]" doc:name="Salesforce"/>
    <db:insert config-ref="" bulkMode="true" doc:name="Database">
        <db:parameterized-query>
            <![CDATA[INSERT INTO TABLE (ID, BILLING_NUMBER__C, TYPE)  
            VALUES (#[payload.Id], #[payload.Billing_Number__c], #[payload.type]);]]>
        </db:parameterized-query>
    </db:insert>

The payload returned by the Database Insert component is an array with the number of rows changed for each query executed. In your case, an array of one's with the size of how many items the Salesforce Query component returned, like [1, 1, 1, ...].

If you still need to log, do this:

    <sfdc:query config-ref="" query="#[flowVars.query]" doc:name="Salesforce"/>
    <foreach doc:name="For Each">
       <logger message="#[payload.Id]" level="INFO" doc:name="Logger"/>
    </foreach>
    <db:insert config-ref="" bulkMode="true" doc:name="Database">
        <db:parameterized-query><!-- the query --></db:parameterized-query>
    </db:insert>

Or this:

    <sfdc:query config-ref="" query="#[flowVars.query]" doc:name="Salesforce"/>
    <foreach doc:name="For Each">
       <logger message="#[payload.Id]" level="INFO" doc:name="Logger"/>
        <db:insert config-ref="" doc:name="Database"><!-- bulkMode disabled -->
            <db:parameterized-query><!-- the query --></db:parameterized-query>
        </db:insert>
    </foreach>

Upvotes: 5

chetan singhal
chetan singhal

Reputation: 948

You can iterate over the ArrayList using a for, inserting values one by one using db:dynamic-query.

Upvotes: 0

Alaa Abed
Alaa Abed

Reputation: 181

Use splitter to split your array and insert individually:

    <splitter expression="#[payload]" />
    <db:insert config-ref="Config" doc:name="Database">
        <db:parameterized-query><![CDATA[insert into xx(Id, Billing_Number__c, type) values('#[payload['Id']]','#[payload['Billing_Number__c']]','#[payload['type']]');]]></db:parameterized-query>
    </db:insert>

Upvotes: 1

Related Questions