In this article, we will look into as how to read Data from REST based Web Services and store the entire JSON object in a Text Column of PostgreSQL database
In this article, we will look into as how to read Data from REST based Web Services and store the entire JSON object in a Text Column of PostgreSQL database.
What we need to have for doing the experiment?
- PostgreSQL JDBC driver
- Download Mule ESB
A database to be created in PostgreSQL say "TestDB" where atleast one Table say "tblJson" should be created as under
-- Table Creation Script
CREATE TABLE "tblJson"
A REST service (I have made it using Dotnet.You are free to make it in your favourite langauge) that will return a json file as under
- Everything should be up and running properly
Let us start the experiment
First create a Mule Project.Then import the Postgre SQL JDBC Driver properly.Next create a Datasource.After that, create the Connector
All these steps are covered in my previous Mule articles and henceforth are not repeated here
The Configuration File
<jdbc:postgresql-data-source name="PostgreSQL_Data_Source" user="postgres" password="niladri_123" url="jdbc:postgresql://localhost:5432/TestDB" transactionIsolation="UNSPECIFIED" doc:name="PostgreSQL Data Source"/>
<jdbc:connector name="PostgreSQL_JDBC_Connector" dataSource-ref="PostgreSQL_Data_Source" validateConnections="true" queryTimeout="-1" pollingFrequency="0" doc:name="Database">
<jdbc:query key="InsertRecord" value="INSERT INTO "tblJson"("JsonDataStorage") VALUES (#[message.payload])"/>
<flow name="REST_JsonFlow1" doc:name="REST_JsonFlow1">
<http:inbound-endpoint exchange-pattern="request-response" host="localhost" port="8082" doc:name="HTTP"/>
<http:rest-service-component httpMethod="GET" serviceUrl="http://localhost:35798/RestServiceImpl.svc/EmployeeList" />
<object-to-string-transformer doc:name="Object to String"/>
<jdbc:outbound-endpoint exchange-pattern="one-way" queryKey="InsertRecord" queryTimeout="-1" connector-ref="PostgreSQL_JDBC_Connector" doc:name="Destination"/>
Since we want to store the whole JSON , we simply transform the HTTP-streamed payload into a java.lang.String and insert it as it is in the DB.
Run the application
We are now almost done.Next we need to run the application.Right click on "REST_JSON.mflow" and from the context menu, choose Run As->Mule Application.
We will encounter the below screen at some point of time
Because it is looking for the input data.So open up the browser, and type "http://localhost:8082/".Press Enter.
Come back to the mule studio and we can observer the below in the console window
If we now look into the tblJson, we will find that 1 record has been inserted
Hope you have enjoyed the tutorial.More to come soon.Zipped file for the experiment is attached herewith.It contains the dotnet REST_WCF service which was used to create the JSON object.