Mule Oracle Database Connector SQL with IN OPERATOR

Question

I've problems with database connector of mule, which i was using for select query. I have an arraylist of string to give inside in parameter.

Mule Sql Query - passing parameters to the IN operator

Solution mentioned above doesn't work with 3.7.3 Mule ESB, I've tried in many ways and searched for that. Except this document there is no definite way which i've founded till this time.

I am using query below :

select * from db_table where id in (2,3,4)

On my example 2,3,4 is inside my flow variable which contains arraylist.

Any suggestions ?

Answer 1

You can do something like this. I am using MySQL as my example, just change it to Oracle.

    <flow name="dbqueryFlow">
        <http:listener config-ref="HTTP_Listener_Configuration" path="/db" doc:name="HTTP"/>
        <set-payload value="#[[1,3,5]]" doc:name="Mock Set of Ids"/>
        <expression-component doc:name="Set IDs to String"><![CDATA[ids = payload.toString().replace("[", "").replace("]", "").replace(", ", ",");
flowVars['ids'] = ids;]]></expression-component>
        <logger message="SID === #[flowVars.ids]" level="INFO" doc:name="Logger"/>
        <db:select config-ref="MySQL_Configuration" doc:name="Database" >
            <db:dynamic-query><![CDATA[SELECT * FROM bridge WHERE id IN (#[flowVars.ids])]]></db:dynamic-query>
        </db:select>
        <logger level="INFO" doc:name="Logger"/>
    </flow>

Hope this helps

Answer 2

<set-variable variableName="CHUNKVIBLIST" value="#[new ArrayList()]" doc:name="Set CHUNKVIBLIST Variable"/>
    <expression-component doc:name="Expression"><![CDATA[int chunkSize = 1000;
            for (int i = 0; i < flowVars.VIBLIST.size(); i += chunkSize) {      flowVars.CHUNKVIBLIST.add("'"+org.apache.commons.lang.StringUtils.join(flowVars.VIBLIST.subList(i, i + chunkSize >=  flowVars.VIBLIST.size() ?  flowVars.VIBLIST.size() : i + chunkSize),"','")+"'");
            }]]></expression-component>
        
    <foreach collection="#[flowVars.CHUNKVIBLIST]" doc:name="For Each matnr batch:">
    <db:select config-ref="Mule_DB_Configuration" doc:name="Select statement">
            <db:dynamic-query><![CDATA[SELECT DISTINCT(MATNR) FROM cated_prodrelease where MATNR IN (#[payload])]]></db:dynamic-query>
    </db:select>
    </foreach>

Update on 01.03.2021 Old answer was my beginner days, i just decided to update my answer and changed with above, you should create payload contains comma seperated string to use that answer which is using IN statement as dynamic query.