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 JSON Column of PostgreSQL database
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?
- PostgreSQL JDBC driver
- Download Mule ESB
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"
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
- 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 "MyJsonStorage"("StoreJSONObject") VALUES(CAST(#[message.payload] AS json))"/> <!-- Cast string to JSON -->
<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"/>
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
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.