How to read REST service that returns JSON object and store the same into PostgreSQL using Mule ESB, Mule Studio

Question

So I have a REST based service which is hosted at

http://localhost:35798/RestServiceImpl.svc/json/567

If I query that, I get the result as:

{"JSONDataResult":"You requested product 567"}

I need to store the whole JSON data into a PostgreSQL table:

CREATE TABLE "JsonTable"
(
  "StoreJsonObject" json
)

If I want to parse the value field i.e. "You requested product 567", the program works (here I am using a different table whose column type is text):

<jdbc:postgresql-data-source name="PostgreSQL_Data_Source" user="username" password="pwd" url="jdbc:postgresql://localhost:5432/TestDB" transactionIsolation="UNSPECIFIED" doc:name="PostgreSQL Data Source"/>
<jdbc:connector name="PostgreSQL_Connector" dataSource-ref="PostgreSQL_Data_Source" validateConnections="true" queryTimeout="-1" pollingFrequency="0" doc:name="Database">
    <jdbc:query key="InsertRecord" value="INSERT INTO &quot;AnotherJSonTable&quot;(&quot;StoreJsonObject&quot;) VALUES (#[message.payload])"/>
</jdbc:connector>
<flow name="testRestFlow1" doc:name="testRestFlow1">
    <http:inbound-endpoint exchange-pattern="request-response" address="http://localhost:8082/index.html"  doc:name="HTTP"/>
    <http:rest-service-component httpMethod="GET" serviceUrl="http://localhost:35798/RestServiceImpl.svc/json/567">
    </http:rest-service-component>
    <json:json-to-object-transformer returnClass="java.util.Map" doc:name="JSON to Object"/>
    <expression-transformer expression="#[message.payload.JSONDataResult]" doc:name="Expression"/>
    <jdbc:outbound-endpoint exchange-pattern="one-way" queryKey="InsertRecord" queryTimeout="-1" connector-ref="PostgreSQL_Connector" doc:name="Database"/>
</flow>

But how to store the entire JSON object ({"JSONDataResult":"You requested product 567"}).
What do I need to change in the "expression-transformer"?

If I do:

<jdbc:query key="InsertRecord" value="INSERT INTO &quot;JsonTable&quot;(&quot;StoreJsonObject&quot;) VALUES (#[message.payload])"/>

<expression-transformer expression="#[message.payload]" doc:name="Expression"/>

I receive exception:

Root Exception stack trace:
org.postgresql.util.PSQLException: No hstore extension installed.
    at org.postgresql.jdbc2.AbstractJdbc2Statement.setMap(AbstractJdbc2Statement.java:1713)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1916)
    at org.postgresql.jdbc3g.AbstractJdbc3gStatement.setObject(AbstractJdbc3gStatement.java:36)
    + 3 more (set debug level logging or '-Dmule.verbose.exceptions=true' for everything)
********************************************************************************

INFO  2012-12-24 15:48:31,945 [[testrest].connector.file.mule.default.receiver.01] org.mule.transport.file.FileMessageReceiver: Lock obtained on file: C:\Users\niladri.biswas\Desktop\input\all_winjs_ui_controls.txt
INFO  2012-12-24 15:48:31,945 [[testrest].testRestFlow1.stage1.02] org.mule.transport.http.components.RestServiceWrapper: Invoking REST service: http://localhost:35798/RestServiceImpl.svc/json/567
ERROR 2012-12-24 15:48:31,992 [[testrest].PostgreSQL_Connector.dispatcher.01] org.mule.exception.DefaultMessagingExceptionStrategy: 
********************************************************************************
Message               : Failed to route event via endpoint: DefaultOutboundEndpoint{endpointUri=jdbc://InsertRecord, connector=JdbcConnector
{
  name=PostgreSQL_Connector
  lifecycle=start
  this=15e7ea6
  numberOfConcurrentTransactedReceivers=4
  createMultipleTransactedReceivers=false
  connected=true
  supportedProtocols=[jdbc]
  serviceOverrides=<none>
}
,  name='endpoint.jdbc.InsertRecord', mep=ONE_WAY, properties={queryTimeout=-1}, transactionConfig=Transaction{factory=null, action=INDIFFERENT, timeout=0}, deleteUnacceptedMessages=false, initialState=started, responseTimeout=10000, endpointEncoding=UTF-8, disableTransportTransformer=false}. Message payload is of type: LinkedHashMap
Code                  : MULE_ERROR--2
--------------------------------------------------------------------------------

Also the record should be inserted only once after reading from the service and not multiple times.

Do you need to regularly fetch this JSON and insert it in DB? Or one time only? Or on demand? — David Dossot, Dec 22 '12 at 20:12
@David Dossot, Sir I am very close to my target. If I want to read the value only and want to store it is happening(provided the table column should be text).But how to store the entire JSON response object? I have updated the question entirely.Please guide. Thanks — priyanka.sarkar, Dec 24 '12 at 11:55

David Dossot · Accepted Answer · 2012-12-26T17:17:34.150

5

Since you want to store the whole JSON, there is no need to deserialize it as an object: I suggest you simply transform the HTTP-streamed payload into a java.lang.String and insert it as-is in the DB.

This would done like that:

<jdbc:postgresql-data-source name="PostgreSQL_Data_Source"
    user="username" password="pwd" url="jdbc:postgresql://localhost:5432/TestDB"
    transactionIsolation="UNSPECIFIED" doc:name="PostgreSQL Data Source" />

<jdbc:connector name="PostgreSQL_Connector" dataSource-ref="PostgreSQL_Data_Source"
    validateConnections="true" queryTimeout="-1" pollingFrequency="0"
    doc:name="Database">
    <jdbc:query key="InsertRecord"
        value="INSERT INTO &quot;AnotherJSonTable&quot;(&quot;StoreJsonObject&quot;) VALUES (CAST(#[message.payload] AS json))" />
</jdbc:connector>

<flow name="testRestFlow1" doc:name="testRestFlow1">
    <http:inbound-endpoint exchange-pattern="request-response"
        address="http://localhost:8082/index.html" doc:name="HTTP" />
    <http:rest-service-component httpMethod="GET"
        serviceUrl="http://localhost:35798/RestServiceImpl.svc/json/567" />
    <object-to-string-transformer />
    <jdbc:outbound-endpoint exchange-pattern="one-way"
        queryKey="InsertRecord" queryTimeout="-1" connector-ref="PostgreSQL_Connector"
        doc:name="Database" />
</flow>

edited Dec 26 '12 at 17:17

answered Dec 24 '12 at 18:31

David Dossot

33,403
4
38
72

Hi, this works well if the column type is text. But my column type is JSon. And henceforth it is failing.ERROR: column "StoreJsonObject" is of type json but expression is of type character varying Hint: You will need to rewrite or cast the expression. Position: 52(SQL Code: 0, SQL State: + 42804) (org.postgresql.util.PSQLException) org.postgresql.core.v3.QueryExecutorImpl:2157 (null) – priyanka.sarkar Dec 25 '12 at 02:08
I,however, tried with VALUES (#[json:message.payload]).But in this case only blank row is getting inserted.What is wrong? and what will be the solution – priyanka.sarkar Dec 25 '12 at 02:25
You need to cast the payload to the JSON type either in the SQL expression with `... VALUES(CAST(#[message.payload] AS json))` or with a globally defined `character varying -> json` cast (see: http://www.postgresql.org/docs/9.2/static/sql-createcast.html). – David Dossot Dec 26 '12 at 17:19
Thank you SIR for all your support. It has been solved now.Thanks a lot – priyanka.sarkar Dec 27 '12 at 02:40
Just 1 more thing to ask. Why cannot I directly cast from Object to JSON and from Object -> String -> JSON approach? If the first option is possible, can you please show me as how can I do so? – priyanka.sarkar Dec 27 '12 at 02:42
1

If you transform from JSON to Object in Mule the payload will become a `java.util.Map`, which will represent the JSON object coming from the remote HTTP endpoint. If you pass this `Map` payload directly to the JDBC outbound endpoint, JDBC will try to persist it as an serialized object in the database, which is not what you want. Dealing with `String`s only at Mule level avoids this problem. – David Dossot Dec 27 '12 at 03:07

How to read REST service that returns JSON object and store the same into PostgreSQL using Mule ESB, Mule Studio

1 Answers1

Linked