Read a REST service that returns a JSON object and store the same into a Text Column of PostgreSQL.

Introduction

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?

We need

1. PostgreSQL
2. PostgreSQL JDBC driver
3. Download Mule ESB

4. 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"
   		(
   		   "JsonDataStorage" text
   		) 
   		

5. 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

   

6. 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 flow

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])"/>
</jdbc:connector>
<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"/>
	
</flow>

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

Conclusion

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.