在 Mule 的数据库中插入 Arraylist [英] Insert Arraylist in Database in Mule

问题描述

我正在尝试使用 Mule ESB 将 ArrayList 的内容插入到 SQL Server 数据库中.ArrayList 看起来像

<预><代码>[{Id=a1o90000001muvWAAQ, Billing_Number__c=1000005, type=Call_Log__c},{Id=a1o90000001muvXAAQ, Billing_Number__c=1000006, type=Call_Log__c}]

我应该如何以优化的方式将其插入到数据库中?

我的骡子流程如下.

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

#[payload['Id']] 给了我日志中的 Id LoggerMessageProcessor:a1o90000001mzgzAAA.

我不确定我在做什么是正确的方法.我刚刚开始学习 Mule.

解决方案

您不需要 Splitter 或 Foreach 作用域.数据库连接器有一个批量模式，使连接器接受一个集合作为有效负载.

<块引用>

允许通过一个查询提交数据集合，[...].启用批量模式可以提高应用程序的性能，因为它减少了单个查询执行的次数.批量模式需要至少一个参数的参数化查询.

在插入、更新或删除操作中使用 bulkMode="true" 启用它.

在您的 Salesforce 查询组件之后立即使用:

 <sfdc:query config-ref="" query="#[flowVars.query]" doc:name="Salesforce"/><db:insert config-ref=""bulkMode="true" doc:name="Database"><db:parameterized-query><![CDATA[插入表(ID，BILLING_NUMBER__C，类型)值 (#[payload.Id], #[payload.Billing_Number__c], #[payload.type]);]]></db:parameterized-query></db:insert>

Database Insert 组件返回的有效负载是一个数组，每个查询执行的行数都发生了变化.在您的情况下，一个数组，其大小与 Salesforce 查询组件返回的项目数相同，例如 [1, 1, 1, ...].

如果您仍然需要登录，请执行以下操作:

 <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><!-- 查询 --></db:parameterized-query></db:insert>

或者这个:

 <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 --><db:parameterized-query><!-- 查询 --></db:parameterized-query></db:insert></foreach>

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.

解决方案

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>

这篇关于在 Mule 的数据库中插入 Arraylist的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！