how may i select the values based on where condition in Mule ESB

Question

I am new for MuleESB. I don't have much knowledge about flow. I couldn't find proper documentation for Mule. I am trying to fetch the data from database using below code, but I am unable fetch the data. There is no error shown, so I dont know what to do. Is my flow correct?

Here's my flow config.

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

<mule 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">
    <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="one-way" 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>
        <response>
            <http:response-builder status="200" contentType="application/json" doc:name="HTTP Response Builder"/>
        </response>
        <set-payload value="#[payload]" doc:name="Set Payload"/>
                <echo-component doc:name="Echo"/>
            </flow>
</mule>

I am sending request from curl client like this

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

It doesn't provide and response back.

I am expecting a response like the one below.

 {"Body":{"Datalist":{"firstname":"ff","lastname":"ggg","id":"5"}},"Status":"200"}}

Please help me acheive this.

Even the logger statement is not printing

<logger message="message of payload#[message.payload]" level="INFO" doc:name="Logger"/>

Appreciate any help.

Answer 1

You can better use <json:json-to-object-transformer returnClass="java.lang.Object" doc:name="JSON to Object"/> after http:inbound-endpoint and make sure if you are getting the id value from request and after payload after db

Now, if you are getting the data from your db and able to print it using a logger after db using

<logger message="message of payload #[message.payload]" level="INFO" doc:name="Logger"/>

You can explicitly set your response to your client in following way :-

<http:response-builder status="200" contentType="application/json" doc:name="HTTP Response Builder">
    <set-payload value="#[message.payload]" doc:name="Set Payload"/>
</http:response-builder>

and you can remove the <response/> tag from your flow, as you are setting your response payload using <http:response-builder/> as above

Answer 2

I can't answer your question fully, but can tell you few easy ways to inspect your muleMessage. the first and easiest way is to place a script transformer like this:

<script:transformer>
               <script:script engine="groovy">
                   <script:text>
                       System.out.println(payload);
                       return payload;
                   </script:text>
               </script:script>
</script:transformer>

namespace: xmlns:script="http://www.mulesoft.org/schema/mule/scripting"

schemaLocation: http://www.mulesoft.org/schema/mule/scripting http://www.mulesoft.org/schema/mule/scripting/current/mule-scripting.xsd

second option and harder one would be creating custom transformer. U'll need to create a java class, extend the AbstractMessageTransformer. Then debug your project and see what's the message hope, this will help

Answer 3

Couldn't get the complete issue you are facing. From the post I can see there are few changes to be made.

As you are looking for data back from Select Query change the exchange-pattern on the JBDC:Outbound to request-response

<jdbc-ee:outbound-endpoint exchange-pattern="request-response" queryKey="SELECT" queryTimeout="-1" connector-ref="Database" doc:name="Database">

Also there is no need of the <set-payload> at the end of the flow. As the payload is already available in the message, this is a redundant statement.

In the logger try using #[payload] as it is more simple to print your payload (response from JBDC query)

About documentation, MuleSoft website is the best place to find documentation about mule They have a detailed documentation for most of the things of Mule.

Mule User Guide and Documentation

Hope this helps.