How to dynamically insert json data into database in Mule

Question

Hi I have a json file as follow,

JSON:

[
    {
        "name": "Steve",
        "salary": "$1000",
        "age": "26"
    },
    {
        "name': "Laura",
        "salary': "$1500",
        "age": "28"
    },
    {
        "name': "Jack",
        "salary": "$2000",
        "age": "30"
    }
]

And I also have a database table call Employee and it has three columns name, age and salary. Here I want to process the Json data and store it into Employee table. So How can I do that in mule?

Answer 1

First of all your json request is invalid, you need to put " instead of ' in json keys. So, valid json will be :-

[
    {
        "name": "Steve",
        "salary": "$1000",
        "age": "26"
    },
    {
        "name": "Laura",
        "salary": "$1500",
        "age": "28"
    },
    {
        "name": "Jack",
        "salary": "$2000",
        "age": "30"
    }
]   

You need to pass the request from POSTMAN client of browser:-

Now, you can use following Mule flow to insert into your database:-

<http:listener-config name="HTTP_Listener_Configuration" host="0.0.0.0" port="8081" doc:name="HTTP Listener Configuration"></http:listener-config>

    <flow name="Flow"> 
        <http:listener config-ref="HTTP_Listener_Configuration" path="/set" doc:name="HTTP"></http:listener>  
        <json:json-to-object-transformer returnClass="java.util.List" doc:name="JSON to Object"></json:json-to-object-transformer>  
        <foreach collection="#[message.payload]" doc:name="For Each">
            <db:insert config-ref="Generic_Database_Configuration" doc:name="Database">
                <db:parameterized-query><![CDATA[INSERT into table1 (NAME, SALARY, AGE) VALUES (#[message.payload.name],#[message.payload.salary],#[message.payload.age]);]]></db:parameterized-query>
            </db:insert>
        </foreach>  
       <set-payload value="Inserted" doc:name="Set Payload"/>  
    </flow>

And remember to design you DB table where your salary and age will be String as you are passing

"salary": "$1000",
"age": "26"  

as String here