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?
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
First of all, the JSON data you provided here is not valid as Anirban said and make sure for integer data value(i.e. age here) don't wrap it under double quote.
You can also use Dataweave transformer as most of mule developer preferred this for transform mule messages instead of json-to-object transformer. Obviously json-to-object transformer will work properly also but Dataweave most of the developer use now.
The Config xml file is as below:-
<?xml version="1.0" encoding="UTF-8"?>
<mule xmlns:metadata="http://www.mulesoft.org/schema/mule/metadata" xmlns:dw="http://www.mulesoft.org/schema/mule/ee/dw"
xmlns:db="http://www.mulesoft.org/schema/mule/db"
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.mulesoft.org/schema/mule/ee/dw http://www.mulesoft.org/schema/mule/ee/dw/current/dw.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/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd
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:listener-config name="HTTP_Listener_Configuration" host="0.0.0.0" port="8081" connectionIdleTimeout="3000000" doc:name="HTTP Listener Configuration"/>
<db:mysql-config name="MySQL_Configuration" host="localhost" port="3306" database="provide your schema/database name here" doc:name="MySQL Configuration" password="provide username here" user="provide password here"/>
<flow name="muleFlow">
<http:listener config-ref="HTTP_Listener_Configuration" path="/postEmp" allowedMethods="POST" doc:name="HTTP"/>
<dw:transform-message metadata:id="35bfe913-8de7-4b3c-9ba9-98f375a2873e" doc:name="Transform Message">
<dw:input-payload mimeType="application/json"/>
<dw:set-payload><![CDATA[%dw 1.0
%output application/java
---
payload map ((payload01 , indexOfPayload01) -> {
id: payload01.id,
name: payload01.name,
designation: payload01.designation,
salary: payload01.Salary
})]]></dw:set-payload>
</dw:transform-message>
<foreach doc:name="For Each">
<db:insert config-ref="MySQL_Configuration" doc:name="Database-Insert">
<db:parameterized-query><![CDATA[insert into fulltime_employee values (#[payload.id], #[payload.name], #[payload.designation], #[payload.salary])]]></db:parameterized-query>
</db:insert>
</foreach>
</flow>
</mule>
When you work with Transform message transformer it will ask for source and destination meta data. Here the source meta data will be list of json and destination meta data will be list of map. To set source metadata just click on add meta data blue link of source part and a new popup window will be opened and from here add a new meta data by click on green plus symbol and give a name and set its type to JSON and set example from below dropdown list and browse your json file and it will automatically populate the metadata props. For destination meta data choose MAP as type and add name,salary and age props by click on green symbol from the below box.Remember this properties name should be same what you mention as db column name.
something like this..
- Set Your Data into payload
- convert json to Object list
- loop them using for
- save each object into DB
For more details refer to mule documentation For Example: https://docs.mulesoft.com/mule-user-guide/v/3.7/native-support-for-json
<flow name="testFlow">
<!--set json data as payload>
<json:json-to-object-transformer returnClass="java.util.List" doc:name="JSON to Object"/>
<!--loop list-->
<!-- save each record-->
<jdbc:query key="insert"
value="INSERT INTO ${db.env}.YOUR_TABLE (COLUMN1, COLUMN2)
VALUES (#[message.payload.getName()] , #[message.payload.getSurname()] )" />
</flow>
Create your mule flow wit the below way:
forEach.
