How to transform the JDBC reponse to HTTP response In MULE ESB

Question

I am trying to select data from database using mule studio i have done all the jdbc connection and getting data from database.But unable to respond to client with JSON my client is CURL client sending data to mule in JSON like this

 curl -H "Content-Type: application/json" -d '{"id":"1"}' http://localhost:8081/selectdb

and created flow with below configuration i am getting response from database in my esb log but unable to pass the client my config is like this

<?xml version="1.0" encoding="UTF-8"?>

<mule xmlns:cxf="http://www.mulesoft.org/schema/mule/cxf" xmlns:scripting="http://www.mulesoft.org/schema/mule/scripting" xmlns:tracking="http://www.mulesoft.org/schema/mule/ee/tracking" xmlns:jdbc-ee="http://www.mulesoft.org/schema/mule/ee/jdbc" xmlns:http="http://www.mulesoft.org/schema/mule/http" xmlns:json="http://www.mulesoft.org/schema/mule/json" xmlns:mulexml="http://www.mulesoft.org/schema/mule/xml" xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:doc="http://www.mulesoft.org/schema/mule/documentation" xmlns:spring="http://www.springframework.org/schema/beans" version="EE-3.4.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-current.xsd
http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
http://www.mulesoft.org/schema/mule/xml http://www.mulesoft.org/schema/mule/xml/current/mule-xml.xsd
http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd
http://www.mulesoft.org/schema/mule/ee/jdbc http://www.mulesoft.org/schema/mule/ee/jdbc/current/mule-jdbc-ee.xsd
http://www.mulesoft.org/schema/mule/json http://www.mulesoft.org/schema/mule/json/current/mule-json.xsd
http://www.mulesoft.org/schema/mule/ee/tracking http://www.mulesoft.org/schema/mule/ee/tracking/current/mule-tracking-ee.xsd
http://www.mulesoft.org/schema/mule/scripting http://www.mulesoft.org/schema/mule/scripting/current/mule-scripting.xsd
http://www.mulesoft.org/schema/mule/cxf http://www.mulesoft.org/schema/mule/cxf/current/mule-cxf.xsd">
    <jdbc-ee:postgresql-data-source name="PostgreSQL_Data_Source" user="youtilitydba" password="Youtility11" url="jdbc:postgresql://localhost:5432/sample" transactionIsolation="UNSPECIFIED" doc:name="PostgreSQL Data Source"/>
    <jdbc-ee:connector name="Database" dataSource-ref="PostgreSQL_Data_Source" validateConnections="true" queryTimeout="-1" pollingFrequency="0" doc:name="Database"/>
    <flow name="selectfromdbFlow1" doc:name="selectfromdbFlow1">
        <http:inbound-endpoint exchange-pattern="request-response" host="localhost" port="8081" path="selectdb" doc:name="HTTP"/>
        <json:json-to-object-transformer returnClass="java.util.Map" doc:name="JSON to Object"/>

        <jdbc-ee:outbound-endpoint exchange-pattern="request-response" queryKey="SELECT" queryTimeout="-1" connector-ref="Database" doc:name="Database">
            <jdbc-ee:query key="SELECT" value="select  firstname,lastname,id  from users where id =#[message.payload.id]"/>
        </jdbc-ee:outbound-endpoint>
        <logger message="message before set httpresponse #[payload]" level="INFO" doc:name="Logger"/>
        <response>
            <logger message="after expression #[payload]" level="INFO" doc:name="Logger"/>
        </response>
        <response>
            <expression-transformer expression="#[payload]" doc:name="Expression"/>
        </response>
            </flow></mule>

and i logged it twice but same message logging but unable to send to client my logs like this

INFO  2013-11-29 12:57:12,589 [[selectfromdb].connector.http.mule.default.receiver.02] org.mule.lifecycle.AbstractLifecycleManager: Initialising: 'Database.dispatcher.1260873373'. Object is: EEJdbcMessageDispatcher
INFO  2013-11-29 12:57:12,589 [[selectfromdb].connector.http.mule.default.receiver.02] org.mule.lifecycle.AbstractLifecycleManager: Starting: 'Database.dispatcher.1260873373'. Object is: EEJdbcMessageDispatcher
INFO  2013-11-29 12:57:12,637 [[selectfromdb].connector.http.mule.default.receiver.02] org.mule.api.processor.LoggerMessageProcessor: message before set httpresponse [{lastname=ff, firstname=ff, id=5}]
INFO  2013-11-29 12:57:12,639 [[selectfromdb].connector.http.mule.default.receiver.02] org.mule.api.processor.LoggerMessageProcessor: after expression [{lastname=ff, firstname=ff, id=5}]

but not geting from client side tried with setpayload,echo,httpresponsebuilder,expression-transfomer also but i am unable to get this

posted before question http://stackoverflow.com/questions/20263844/how-to-respond-custom-json-to-http-request-in-mule-esb — faisal shaik, Nov 29 '13 at 07:55

score 0 · Answer 1 · answered Nov 29 '13 at 17:49

After the query you have to use an Object to JSON Transformer then add an HTTP Response Builder with the following properties: contentType=application/json and status=200.

Here is an example:

<flow name="DB-Read-TestFlow1" doc:name="DB-Read-TestFlow1">
    <http:inbound-endpoint exchange-pattern="request-response" host="localhost" port="8081" path="selectdb" doc:name="HTTP"/>
    <json:json-to-object-transformer returnClass="java.util.Map" doc:name="JSON to Object"/>

    <jdbc-ee:outbound-endpoint exchange-pattern="request-response" queryKey="SELECT" queryTimeout="-1" connector-ref="Database" doc:name="Database">
        <jdbc-ee:query key="SELECT" value="select  firstname,lastname,id  from users where id =#[message.payload.id]"/>
    </jdbc-ee:outbound-endpoint>
    <logger message="message before set httpresponse #[payload]" level="INFO" doc:name="Logger"/>
    <response>
        <logger message="after expression #[payload]" level="INFO" doc:name="Logger"/>
    </response>
    <json:object-to-json-transformer doc:name="Object to JSON"/>
    <response>
        <http:response-builder status="200" contentType="application/json" doc:name="HTTP Response Builder"/>
    </response>
</flow>

score 0 · Answer 2 · answered Aug 05 '15 at 06:27

You can directly use <http:response-builder/> at the end with your status code and set you message as payload
Something like the following :-

<http:response-builder status="200" contentType="application/json" doc:name="HTTP Response Builder">
<set-payload value="Your Message" />
</http:response-builder>

How to transform the JDBC reponse to HTTP response In MULE ESB

2 Answers2