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

Introduction

In this article, we will look into as how to store a JSON object to a JSON column in 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 "MyJsonStorage" should be created as under

   		-- Table Creation Script
   		CREATE TABLE "MyJsonStorage"
   		(
   		   "StoreJSONObject" json
   		) 
   		

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 object 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 "MyJsonStorage"("StoreJSONObject") VALUES(CAST(#[message.payload] AS json))"/> <!-- Cast string to JSON  -->
</jdbc:connector>
<flow name="TestStoreJSONObjectFlow1" doc:name="TestStoreJSONObjectFlow1">

	<!-- Step 1: This will start the service.This may be replaced with an actual input page like a Html page  -->    
	<http:inbound-endpoint exchange-pattern="request-response" host="localhost" port="8081" doc:name="HTTP"/>
	
	<!-- Step 2: This will read the REST service data  -->        
	<http:rest-service-component httpMethod="GET" serviceUrl="http://localhost:12186/RestServiceImpl.svc/EmployeeList" />
	
	<!-- Step 3: Transform the HTTP-streamed payload into a java.lang.String   -->
	<object-to-string-transformer doc:name="Object to String"/>
	
	<!-- Step 4: Dump into the destination Database -->     
	<jdbc:outbound-endpoint exchange-pattern="one-way" queryKey="InsertRecord" queryTimeout="-1" connector-ref="PostgreSQL_JDBC_Connector" doc:name="Destination"/>
	
</flow>

We want to store the whole JSON object into a JSON column type.So first we are converting the Object into String and then we are converting the String to JSON object and inserting into the database.

A question that may arise is then why not directly cast from Object to JSON ?

The reason is that, if we transform from JSON to Object in Mule, then the payload will become a java.util.Map, which will represent the JSON object coming from the remote HTTP endpoint. If we pass this Map payload directly to the JDBC outbound endpoint, JDBC will try to persist it as an serialized object in the database.This is not the stuff that we are looking for.Henceforth, the better approach is to CAST the string to JSON and then store it into the database.

Run the application

We are now almost done.Next we need to run the application.Right click on "TestStoreJSONObject.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 as well as the configuration XML file.