Mule - how to parse the JSON response of REST webservice and update database

0
votes

I want to write a mule application which will read the database for unprocessed records, club them in JSON payload and then hit a REST webservice, REST webservice(Hosted on different server) will process the records and return the JSON output. Now I have to parse the JSON and update the database for the processed flag.

I am able to get the response from the REST webservice, I have used Byte array to string transform to log in the logger.

Do I need to write foreach component to process the payload one by one and update the database?

here is my configuration XML

<?xml version="1.0" encoding="UTF-8"?>

<mule xmlns:oauth2="http://www.mulesoft.org/schema/mule/oauth2" xmlns:db="http://www.mulesoft.org/schema/mule/db" xmlns:json="http://www.mulesoft.org/schema/mule/json" xmlns:http="http://www.mulesoft.org/schema/mule/http" xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:doc="http://www.mulesoft.org/schema/mule/documentation"
    xmlns:spring="http://www.springframework.org/schema/beans" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-current.xsd
http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
http://www.mulesoft.org/schema/mule/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd
http://www.mulesoft.org/schema/mule/json http://www.mulesoft.org/schema/mule/json/current/mule-json.xsd
http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd
http://www.mulesoft.org/schema/mule/oauth2 http://www.mulesoft.org/schema/mule/oauth2/current/mule-oauth2.xsd">
    <db:mysql-config name="MySQL_Configuration" host="localhost" port="3306" user="root" database="my_db_name" doc:name="MySQL Configuration"/>
    <http:request-config name="HTTP_Request_Configuration" protocol="HTTPS" host="example.net" port="8000" basePath="Salvage" doc:name="HTTP Request Configuration"/>
    <flow name="cwg_clientFlow">
        <poll doc:name="Poll">
            <db:select config-ref="MySQL_Configuration" doc:name="Database">
                <db:parameterized-query><![CDATA[SELECT * FROM cwg_ws_data WHERE SyncFlag = 0]]></db:parameterized-query>
            </db:select>
        </poll>
        <logger message="#[payload]" level="INFO" doc:name="Logger"/>
        <json:object-to-json-transformer   doc:name="Object to JSON"  encoding="UTF-8" mimeType="application/json"/>
        <logger message="JSON Payload is #[payload]" level="INFO" doc:name="Logger"/>
        <http:request config-ref="HTTP_Request_Configuration" path="/muleCWG" method="POST" doc:name="HTTP">
            <http:request-builder>
                <http:header headerName="access_token" value="U9P4CjhCsadIQzfJi13dHYdQLCfhmAi9OxYM8d7c"/>
            </http:request-builder>
            <http:success-status-code-validator values="200"/>
        </http:request>
        <byte-array-to-string-transformer doc:name="Byte Array to String"/>
        <logger message="webservice response #[payload]" level="INFO" doc:name="Logger"/>
    </flow>
</mule>

Please advice.

-Paresh ([email protected])

2
databaseparsingforeachmuleresponse

2 Answers

0
votes

You can either use Bulk Update with mule bulk mode = "true" with parameterized query or Batch processing. But in your case bulk update will be helpful. No need to use for-each scope it will reduce the performance.

0
votes

It doesn,t matter what is the count of records Bulk update can works on thousands of records. You can try something like following

<flow name="testdbFlow">
    <http:listener config-ref="HTTP_Listener_Configuration" path="/test" doc:name="HTTP"/>
    <set-payload value="{&quot;1234567890123456&quot;:&quot;Y&quot;,&quot;1234567890123457&quot;:&quot;Y&quot;}" mimeType="application/json" doc:name="Set Payload"/>
    <dw:transform-message doc:name="Transform Message">
        <dw:input-payload />
        <dw:set-payload><![CDATA[%dw 1.0
            %output application/json
            ---
            (payload mapObject {    
                x: {
                    key : $$,
                    value : $
                }
            }).*x]]>
        </dw:set-payload>
    </dw:transform-message>
    <json:json-to-object-transformer returnClass="java.util.List" doc:name="JSON to Object"/>
    <db:update config-ref="MySQL_Configuration" bulkMode="true" doc:name="Database">
        <db:dynamic-query><![CDATA[UPDATE cwg_ws_data SET SyncFlag = '#[payload.value]' WHERE IMEI = '#[payload.key]']]></db:dynamic-query>
    </db:update>
</flow>

I have tested this with same input and its working fine. FYI dataweave is used for converting json to meaningful and accessible payload.

Hope this helps.