Contact Us 1-800-596-4880
  1. Homepage
  2. Mule Runtime (3.7)
  3. Developing
  4. Publishing and Consuming APIs with Mule
  5. Consuming a REST API

  6. REST API Examples

REST API Examples

Mule Runtime Engine versions 3.5, 3.6, and 3.7 reached End of Life on or before January 25, 2020. For more information, contact your Customer Success Manager to determine how you can migrate to the latest Mule version.

This page provides three examples of consuming REST APIs: a Simple Example: Consuming a REST API that simply details how to retrieve data from a REST API, and two examples – one for Extracting XML Data from a REST API and Inserting it in an External DB and one for JSON to Object Transformer – showing how to consume a REST API and insert selected data into an external database.

Simple Example: Consuming a REST API

This is a humorous but fully functional example designed for quick and effortless setup and testing. It queries the Bacon Ipsum REST API, which generates lorem ipsum text, and is convenient because it does not require HTTPS or setting up an account to use. The example consists of only one flow, shown below.

rest+api+1

View the XML

<mule xmlns:file="http://www.mulesoft.org/schema/mule/file" xmlns:http="http://www.mulesoft.org/schema/mule/http" 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"
    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/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd
http://www.mulesoft.org/schema/mule/file http://www.mulesoft.org/schema/mule/file/current/mule-file.xsd">
    <http:listener-config name="HTTP_Listener_Configuration" host="localhost" port="8081" doc:name="HTTP Listener Configuration"/>
    <http:request-config name="HTTP_Request_Configuration" host="baconipsum.com"  doc:name="HTTP Request Configuration" basePath="api"/>
    <flow name="test2Flow1" >
        <http:listener config-ref="HTTP_Listener_Configuration" path="/" doc:name="HTTP"/>
        <http:request config-ref="HTTP_Request_Configuration" path="/" method="GET" doc:name="HTTP">
          <http:request-builder>
                <http:query-param paramName="type" value="#[message.inboundProperties.'http.query.params'.type]"/>
                <http:query-param paramName="sentences" value="#[message.inboundProperties.'http.query.params'.sentences]"/>
            </http:request-builder>
        </http:request>
      <file:outbound-endpoint path="/tmp/" outputPattern="out.json" responseTimeout="10000" doc:name="File"/>
    </flow>
</mule>

The first element, an HTTP listener, listens on localhost port 8081 (the default) for incoming GET requests. Hitting the listener triggers the flow. Requests to the HTTP listener must take the form:

http://localhost:8081?<query>

The <query> part of the request consists of the parameters accepted by the REST API. When the HTTP listener receives the HTTP request, the <query> part of the URL is recorded as a set of inbound properties. The HTTP listener passes these properties to the next element in the flow, the HTTP request connector. This outbound connector is configured to query the remote REST API at http://baconipsum.com/api. The HTTP request connector uses a couple of simple MEL expressions to extract the query parameters from the message it received from the listener, and to construct the full URL for the remote API, including the query parameters.

For example, if you hit the HTTP listener with ` http://localhost:8081?type=meat-and-filler`, the application queries http://baconipsum.com/api/?type=meat-and-filler.

The full configuration for this example is detailed below.

Configuring this Example

HTTP Listener

Studio Visual Editor

Set up the Properties editor of the Connector:

http+listener+basic
Parameter Value

Display Name

HTTP

Path

/

Create a new Connector Configuration Element and set up the port and host:

http+config+1
Parameter Value

Name

HTTP_Listener_Configuration

Host

localhost

Port

8081

Standalone XML

Configure the HTTP connector as follows:

<http:listener config-ref="HTTP_Listener_Configuration" path="/" doc:name="HTTP"/>
Attribute Value

doc:name

HTTP

config-ref

HTTP_Listener_Configuration

path

/

For this element to work, you must reference an abstract element called a Connector Configuration, which contains several of the high level necessary configuration properties. The config-ref attribute in the connector references this connector configuration element. You must now create an element outside the flow that matches the referenced name.

<http:listener-config name="HTTP_Listener_Configuration" host="localhost" port="8081" doc:name="HTTP Listener Configuration"/>
Attribute Value

name

HTTP_Listener_Configuration

host

localhost

port

8081

doc:name

HTTP Listener Configuration

HTTP Request Connector

Studio Visual Editor

  1. Create a new Connector Configuration Element and set up the Host and Base Path:

    http+global+element+baconipsum
    Parameter Value

    Name

    `HTTP_Request_Connector `

    Host

    baconipsum.com

    Base Path

    api

    With this configuration, requests will be sent out to http://baconipsum.com/api

  2. Save changes and return to the connector’s properties editor by clicking ok. Then set up the path and method:

    http+connector+basic
    Parameter Value

    Display Name

    HTTP

    Path

    /

    Method

    GET

  3. Click the Add Parameter button twice to add two parameters. Leave both as the default type query-param. For the value field of each, write a mule expression that takes the equivalent value from the query parameters of the request that first reached the HTTP Listener, these are transformed into inbound properties by the time they reach the HTTP Request Connector.

    request+connector+w+params
    Parameter Value

    Type

    query-param

    Name

    type

    Value

    #[message.inboundProperties.'http.query.params'.type]
    Parameter Value

    Type

    query-param

    Name

    sentences

    Value

    #[message.inboundProperties.'http.query.params'.sentences]

    Configured in this way, the query params that reach the HTTP listener are forwarded unchanged to the baconipsum API.

Standalone XML

Configure the HTTP connector as follows:

<http:request config-ref="HTTP_Request_Configuration" path="/" method="GET" doc:name="HTTP">
          <http:request-builder>
                <http:query-param paramName="type" value="#[message.inboundProperties.'http.query.params'.type]"/>
                <http:query-param paramName="sentences" value="#[message.inboundProperties.'http.query.params'.sentences]"/>
            </http:request-builder>
        </http:request>
Attribute Value

doc:name

HTTP

config-ref

HTTP_Request_Configuration

path

`/ `

method

GET

As you can see above, there are a series of child elements of the connector, these define two query parameters that take their values from inbound properties of the message. Enclosing the two elements that define these query parameters, is a request-builder element that is always necessary when adding parameters to a request.

Parameter Value

type

http:query-param

paramName

type

value

#[message.inboundProperties.'http.query.params'.type]
Parameter Value

type

http:query-param

paramName

sentences

value

#[message.inboundProperties.'http.query.params'.sentences]

Configured in this way, the query params that reach the HTTP listener are forwarded unchanged to the baconipsum API.

For this element to work, you must reference an abstract element called a Connector Configuration, which contains several of the high level necessary configuration properties. The config-ref attribute in the connector references this connector configuration element. You must now create an element outside the flow that matches the referenced name.

<http:request-config name="HTTP_Request_Configuration" host="baconipsum.com"  doc:name="HTTP Request Configuration" basePath="api"/>
Attribute Value

name

HTTP_Request_Configuration

host

baconipsum.com

basePath

api

doc:name

HTTP Request Configuration

File Outbound Endpoint

You can set this outbound endpoint with whatever parameters you wish, or use the values provided below. The configuration shown below outputs the resulting JSON to the file /tmp/out.json.

Studio Visual Editor

General Tab

Parameter Value Configuration window image

Display Name

File

f1.elem3

Path

/tmp/

Output Pattern

out.json

The rest of the tabs for this endpoint (Advanced, References, and Notes) are set with their default values; no configuration is necessary.

Standalone XML

<file:outbound-endpoint path="/tmp/" outputPattern="out.json"

Running this Example

To trigger the flow in this application, use a Web browser or an HTTP client such as the curl command-line utility to hit the HTTP listener on localhost port 8081.

browser1 
curl 'http://localhost:8081?type=meat-and-filler'

Run the example as a Mule application, then hit the HTTP listener with your query. The Bacon Ipsum API page contains a list of parameters you can use, although because of the way you set up your connector, you can only use type and sentences.

type=meat-and-filler
sentences=<num>

To use any additional query parameter, you must add it to your HTTP Connector first.

sentences determines the number of sentences to return in the JSON response. It is optional, you may not include it in your requests.

The first parameter=value pair must be preceded by the ? operator. To insert additional parameter=value pairs, use the & operator.

Example queries sent to the HTTP listener on localhost port 8081:

http://localhost:8081?type=meat-and-filler
http://localhost:8081?sentences=2
http://localhost:8081?type=all-meat&sentences=3

The first example, with operation type=meat-and-filler, returns the output shown below.

["Doner ullamco ea non, porchetta incididunt brisket ball tip in chuck ex bresaola beef tongue.  Et aute ham hock kielbasa chuck fatback short ribs.  Kevin in reprehenderit est esse, ham bacon ut ball tip.  Laborum ut nulla ex irure t-bone flank, biltong cupidatat venison proident aliquip pork belly ham hock.  In consequat proident, cillum labore pariatur nisi.  Reprehenderit boudin beef ribs, frankfurter cillum enim pork loin consectetur kielbasa laboris.  Hamburger prosciutto nisi, jerky biltong ex pork chop venison.","Fatback tongue anim, irure ut ut cupidatat occaecat eiusmod ham hock laborum commodo.  Anim pig shank kielbasa, drumstick corned beef esse nostrud ham salami id laborum ribeye aute.  Duis pancetta sunt magna occaecat dolor leberkas, short loin meatloaf flank enim pastrami.  Prosciutto proident landjaeger deserunt tenderloin short loin.  Adipisicing aute in bresaola meatball, ut frankfurter pastrami shoulder porchetta turducken strip steak doner.  In filet mignon bresaola, sed deserunt pariatur eu mollit commodo shankle laborum.  Andouille aliqua jowl pork chop jerky sed consequat turkey voluptate bacon pastrami.","Ground round elit boudin reprehenderit.  Brisket shankle esse, leberkas veniam andouille rump proident drumstick.  Consequat sausage do ut prosciutto nostrud andouille tongue ullamco bacon est exercitation.  Do fugiat biltong est tempor short ribs reprehenderit adipisicing shoulder.  Tail venison shank incididunt, hamburger adipisicing voluptate corned beef fugiat sirloin fatback in tri-tip nisi ut.  Tail non excepteur, fugiat veniam corned beef dolore ex pig pork belly sint mollit chuck pork.","Pig hamburger dolore proident brisket landjaeger in boudin kielbasa ut elit.  Velit incididunt boudin qui.  Fatback anim adipisicing, pig jowl voluptate sirloin drumstick chicken esse.  Strip steak consequat tenderloin pastrami, ullamco brisket hamburger bacon beef adipisicing.  Tri-tip ham hock eu non et, flank dolore kevin.  Et duis frankfurter, ut ullamco do non quis boudin andouille aliqua venison ham.  Ut aliqua shoulder, aliquip pariatur bacon spare ribs irure.","Aliqua jerky frankfurter, swine ham in ground round sed qui laborum cow.  Sint turducken shank ut ea id.  Kevin dolore pig excepteur, anim ut magna.  Enim consequat short ribs corned beef ham hock nostrud fugiat chuck.  Tail spare ribs dolore boudin, andouille incididunt laboris occaecat strip steak.  Cow frankfurter capicola, landjaeger cupidatat porchetta ad ground round voluptate."]

Extracting XML Data from a REST API and Inserting it in an External DB

This example application consumes XML data from an external REST API, then extracts values from the XML and inserts them into an external Postgres database. The application uses the Yahoo! REST API for financial services, which is free to use and does not require a user account. This API allows you to retrieve financial quotes using the SQL-based Yahoo Query Language, whose statements you include in the URL itself.

This application hits the Yahoo! REST API at http://query.yahooapis.com/v1/public/yql with whatever query you specify. The format of the reply is determined in the query itself, with the format=<format> parameter. This example uses format=XML.

When it receives the reply in XML, the application extracts some values such as stock name, date, price, etc. and inserts them into a table in an external Postgres database.

The application contains only one flow, shown below.

yahoo+xml

View the XML

<mule xmlns:tracking="http://www.mulesoft.org/schema/mule/ee/tracking" xmlns:http="http://www.mulesoft.org/schema/mule/http" xmlns:db="http://www.mulesoft.org/schema/mule/db" 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"
    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/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd
http://www.mulesoft.org/schema/mule/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd
http://www.mulesoft.org/schema/mule/ee/tracking http://www.mulesoft.org/schema/mule/ee/tracking/current/mule-tracking-ee.xsd">

    <db:generic-config name="Postgres" url="jdbc:postgresql://dbserver/stock" driverClassName="org.postgresql.Driver" doc:name="Generic Database Configuration"/>
    <db:template-query name="insert_into_current" doc:name="Template Query">
        <db:parameterized-query><![CDATA[INSERT INTO current("name", "date", "bookvalue") VALUES(:name,:date,:bookvalue);]]></db:parameterized-query>
        <db:in-param name="name" defaultValue="#[xpath3('//Name').text]"/>
        <db:in-param name="date" type="DATE" defaultValue="#[xpath3('//LastTradeDate').text]"/>
        <db:in-param name="bookvalue" defaultValue="#[xpath3('//BookValue').text])"/>
    </db:template-query>

    <http:listener-config name="HTTP_Listener_Configuration" host="localhost" port="8081" doc:name="HTTP Listener Configuration"/>
    <http:request-config name="HTTP_Request_Configuration" host="query.yahooapis.com" basePath="v1/public/yql" doc:name="HTTP Request Configuration"/>
    <flow name="financeapiFlow1" >
        <http:listener config-ref="HTTP_Listener_Configuration" path="/" doc:name="HTTP"/>
        <http:request config-ref="HTTP_Request_Configuration" path="/" method="GET" followRedirects="true" doc:name="HTTP">
            <http:request-builder>
                <http:query-param paramName="q" value="#[message.inboundProperties.'http.query.params'.q]"/>
                <http:query-param paramName="env" value="#[message.inboundProperties.'http.query.params'.env]"/>
                <http:query-param paramName="format" value="#[message.inboundProperties.'http.query.params'.format]"/>
            </http:request-builder>
        </http:request>
        <logger level="INFO" doc:name="Logger"/>

        <byte-array-to-string-transformer doc:name="Byte Array to String"/>
        <splitter expression="#[xpath3('//results')]" doc:name="Splitter"/>
        <db:insert config-ref="Postgres" doc:name="Database">
            <db:dynamic-query><![CDATA[INSERT INTO mystock("name", "date", "bookvalue") VALUES(#[xpath3('//Name').text], #[xpath3('//LastTradeDate').text], #[xpath3('//BookValue').text]);]]></db:dynamic-query>
        </db:insert>
    </flow>
</mule>

The first element, an HTTP listener , listens on localhost port 8081 (the default) for incoming GET requests. Hitting the listener triggers the flow. Requests to the HTTP listener must take the form:

http://localhost:8081?<query>

The <query> part of the request consists of the parameters accepted by the REST API. When the HTTP listener receives the HTTP request, the <query> part of the URL becomes a set of inbound properties. The HTTP lsitener passes the message to the next element in the flow, the HTTP request connector. The HTTP request connector uses a set of MEL expressions to extract the query parameters from the message, and to construct the full URL for the remote API, including the query parameters.

For example, if you hit the HTTP listener with the following:

http://localhost?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20(%22BAC%22)%0A%09%09&env=http%3A%2F%2Fdatatables.org%2Falltables.env&format=XML

The application queries the URL listed below:

http://query.yahooapis.com/v1/public/yql?q=select * from yahoo.finance.quotes where symbol in ("BAC")%0A%09%09&env=http%3A%2F%2Fdatatables.org%2Falltables.env&format=XML

The above query retrieves information for ticker symbol BAC, for Bank of America. The REST API returns an XML, which you can see by clicking below.

View the XML returned by the REST API

<query yahoo:count="1" yahoo:created="2014-04-04T16:35:37Z" yahoo:lang="en"><results><quote symbol="BAC"><Ask/><AverageDailyVolume>107198000</AverageDailyVolume><Bid/><AskRealtime>17.00</AskRealtime><BidRealtime>16.99</BidRealtime><BookValue>20.708</BookValue><Change_PercentChange>-0.158 - -0.92%</Change_PercentChange><Change>-0.158</Change><Commission/><ChangeRealtime>-0.158</ChangeRealtime><AfterHoursChangeRealtime>N/A - N/A</AfterHoursChangeRealtime><DividendShare>0.04</DividendShare><LastTradeDate>4/4/2014</LastTradeDate><TradeDate/><EarningsShare>0.903</EarningsShare><ErrorIndicationreturnedforsymbolchangedinvalid/><EPSEstimateCurrentYear>1.09</EPSEstimateCurrentYear><EPSEstimateNextYear>1.60</EPSEstimateNextYear><EPSEstimateNextQuarter>0.34</EPSEstimateNextQuarter><DaysLow>16.96</DaysLow><DaysHigh>17.22</DaysHigh><YearLow>11.23</YearLow><YearHigh>18.03</YearHigh><HoldingsGainPercent>- - -</HoldingsGainPercent><AnnualizedGain/><HoldingsGain/><HoldingsGainPercentRealtime>N/A - N/A</HoldingsGainPercentRealtime><HoldingsGainRealtime/><MoreInfo>cnsprmiIed</MoreInfo><OrderBookRealtime/><MarketCapitalization>179.6B</MarketCapitalization><MarketCapRealtime/><EBITDA>0</EBITDA><ChangeFromYearLow>+5.762</ChangeFromYearLow><PercentChangeFromYearLow>+51.31%</PercentChangeFromYearLow><LastTradeRealtimeWithTime>N/A - <b>16.992</b></LastTradeRealtimeWithTime><ChangePercentRealtime>N/A - -0.92%</ChangePercentRealtime><ChangeFromYearHigh>-1.038</ChangeFromYearHigh><PercebtChangeFromYearHigh>-5.76%</PercebtChangeFromYearHigh><LastTradeWithTime>12:20pm - <b>16.992</b></LastTradeWithTime><LastTradePriceOnly>16.992</LastTradePriceOnly><HighLimit/><LowLimit/><DaysRange>16.96 - 17.22</DaysRange><DaysRangeRealtime>N/A - N/A</DaysRangeRealtime><FiftydayMovingAverage>16.9586</FiftydayMovingAverage><TwoHundreddayMovingAverage>15.602</TwoHundreddayMovingAverage><ChangeFromTwoHundreddayMovingAverage>+1.39</ChangeFromTwoHundreddayMovingAverage><PercentChangeFromTwoHundreddayMovingAverage>+8.91%</PercentChangeFromTwoHundreddayMovingAverage><ChangeFromFiftydayMovingAverage>+0.0334</ChangeFromFiftydayMovingAverage><PercentChangeFromFiftydayMovingAverage>+0.20%</PercentChangeFromFiftydayMovingAverage><Name>Bank of America C</Name><Notes/><Open>17.21</Open><PreviousClose>17.15</PreviousClose><PricePaid/><ChangeinPercent>-0.92%</ChangeinPercent><PriceSales>2.12</PriceSales><PriceBook>0.83</PriceBook><ExDividendDate>Mar  5</ExDividendDate><PERatio>18.99</PERatio><DividendPayDate>Mar 28</DividendPayDate><PERatioRealtime/><PEGRatio>0.76</PEGRatio><PriceEPSEstimateCurrentYear>15.73</PriceEPSEstimateCurrentYear><PriceEPSEstimateNextYear>10.72</PriceEPSEstimateNextYear><Symbol>BAC</Symbol><SharesOwned/><ShortRatio>1.10</ShortRatio><LastTradeTime>12:20pm</LastTradeTime><TickerTrend>&nbsp;==+--+&nbsp;</TickerTrend><OneyrTargetPrice>17.54</OneyrTargetPrice><Volume>38512660</Volume><HoldingsValue/><HoldingsValueRealtime/><YearRange>11.23 - 18.03</YearRange><DaysValueChange>- - -0.92%</DaysValueChange><DaysValueChangeRealtime>N/A - N/A</DaysValueChangeRealtime><StockExchange>NYSE</StockExchange><DividendYield>0.23</DividendYield><PercentChange>-0.92%</PercentChange></quote></results></query><!-- total: 22 --><!-- engine3.yql.bf1.yahoo.com -->

The HTTP request connector passes the XML it received from the API to a byte-array-to-string transformer, which converts the message payload to a string. Next, a splitter splits the message into parts. The splitter is configured to split the incoming message using the XML element <results> as delimiter. The reason for this is that the REST API uses <results> to delimit the information for each stock symbol retrieved, as shown below.

<results><quote symbol="BAC"><Ask/><AverageDailyVolume>107198000</AverageDailyVolume><Bid/>
...
</results>

So if you were to use this example application to query several stock symbols at once, the splitter would split the incoming XML into chunks corresponding to each queried symbol, then feed each chunk to the next message processor.

To split the incoming XML, the splitter uses the Mule Expression Language expression #[xpath3('//results')], which as you can see contains the XPath expression that actually retrieves the XML element <results>.

The last element in the flow, a Database (JDBC) connector, receives each XML chunk corresponding to each queried symbol. The JDBC connector is configured to run the following SQL query:

INSERT INTO mystock("name", "date", "bookvalue") VALUES(#[xpath3('//Name').text], #[xpath3('//LastTradeDate').text], #[xpath3('//BookValue').text]);

The full configuration for this example is detailed below. To see the application in action, skip to Running This Example.

Configuring this Example

HTTP Listener

Studio Visual Editor

  1. Set up the Properties editor of the Connector:

    http+listener+basic
    Parameter Value

    Display Name

    HTTP

    Path

    /

  2. Create a new Connector Configuration Element and set up the port and host:

    http+config+1
    Parameter Value

    Name

    HTTP_Listener_Configuration

    Host

    localhost

    Port

    8081

Standalone XML

Configure the HTTP connector as follows:

<http:listener config-ref="HTTP_Listener_Configuration" path="/" doc:name="HTTP"/>
Attribute Value

doc:name

HTTP

config-ref

HTTP_Listener_Configuration

path

/

For this element to work, you must reference an abstract element called a Connector Configuration, which contains several of the high level necessary configuration properties. The config-ref attribute in the connector references this connector configuration element. You must now create an element outside the flow that matches the referenced name.

<http:listener-config name="HTTP_Listener_Configuration" host="localhost" port="8081" doc:name="HTTP Listener Configuration"/>
Attribute Value

name

HTTP_Listener_Configuration

host

localhost

port

8081

doc:name

HTTP Listener Configuration

HTTP Request Connector

Studio Visual Editor

  1. Create a new Connector Configuration Element and set up the Host and Base Path:

    yahoo+global+element
    Parameter Value

    Name

    `HTTP_Request_Connector `

    Host

    query.yahooapis.com

    Base Path

    v1/public.yql

    With this configuration, requests are sent to query.yahooapis.com/v1/public.yql

  2. Save changes and return to the connector’s properties editor by clicking OK. Then set up the path and method:

    http+connector+basic
    Parameter Value

    Display Name

    `HTTP `

    Path

    /

    Method

    GET

  3. Click the Add Parameter button three times to add three parameters. Leave all as the default type query-param. For the value field of each, write a mule expression that takes the equivalent value from the query parameters of the request that first reached the HTTP Listener, these are transformed into inbound properties by the time they reach the HTTP Request Connector.

    yahoo+http+w+params
    Parameter Value

    Type

    `query-param `

    Name

    q

    Value

    #[message.inboundProperties.'http.query.params'.q]
    Parameter Value

    Type

    query-param

    Name

    env

    Value

    #[message.inboundProperties.'http.query.params'.env]
    Parameter Value

    Type

    query-param

    Name

    format

    Value

    #[message.inboundProperties.'http.query.params'.format]

    Configured in this way, the query params that reach the HTTP listener are forwarded unchanged to the yahoo API.

  4. Select the Advanced tab on the left side panel of the connector, tick the checkbox labeled Follow Redirects

Standalone XML

Configure the HTTP connector as follows:

<http:request config-ref="HTTP_Request_Configuration" path="/" method="GET" followRedirects="true" doc:name="HTTP">
            <http:request-builder>
                <http:query-param paramName="q" value="#[message.inboundProperties.'http.query.params'.q]"/>
                <http:query-param paramName="env" value="#[message.inboundProperties.'http.query.params'.env]"/>
                <http:query-param paramName="format" value="#[message.inboundProperties.'http.query.params'.format]"/>
            </http:request-builder>
        </http:request>
Attribute Value

doc:name

HTTP

config-ref

HTTP_Request_Configuration

path

`/ `

method

GET

followRedirects

true

As you can see above, there are a series of child elements of the connector, these define three query parameters that take their values from inbound properties of the message. Enclosing the three elements that define these query parameters, is a request-builder element that is always necessary when adding parameters to a request.

Parameter Value

type

` http:query-param`

paramName

q

value

#[message.inboundProperties.'http.query.params'.q]
Parameter Value

type

` http:query-param `

paramName

env

value

#[message.inboundProperties.'http.query.params'.env]
Parameter Value

type

` http:query-param`

paramName

format

value

#[message.inboundProperties.'http.query.params'.format]

Configured in this way, the query params that reach the HTTP listener are forwarded unchanged to the yahoo API.

For this element to work, you must reference a Connector Configuration. The config-ref attribute in the connector references this connector configuration element. You must now create an element outside the flow that matches the referenced name.

<http:request-config name="HTTP_Request_Configuration" host="baconipsum.com"  doc:name="HTTP Request Configuration" basePath="api"/>
Attribute Value

name

HTTP_Request_Configuration

host

baconipsum.com

basePath

api

doc:name

HTTP Request Configuration

Byte Array to String Transformer

This transformer is set with its default values; the full configuration is shown below.

Studio Visual Editor

General Tab

Parameter Value Configuration window image

Display Name

Byte Array to String

byte_array_to_str

Return Class

-

Ignore Bad Input

no

Encoding

-

MIME Type

-

Standalone XML

General Tab

<http:request-config name="HTTP_Request_Configuration" host="baconipsum.com"  doc:name="HTTP Request Configuration" basePath="api"/>

Splitter

The splitter splits the incoming message into parts, dividing them with a user-defined expression.

Studio Visual Editor

General Tab

Parameter Value Configuration window image

Display Name

Byte Array to String

splitter gentab

Enable Correlation

IF_NOT_SET (default)

Message Info Mapping

-

Expression

#[xpath3('//results')]

The Advanced tab is set to its default values; no configuration is necessary.

Standalone XML

General Tab

<splitter expression="#[xpath3('//results')]" doc:name="Splitter"/>

Database Connector

Studio Visual Editor

General Tab

Parameter Value Configuration window image

Display Name

Database

db_conn_general_tab

Connector configuration

Postgres
(This is the DB global element referenced by this connector; for configuration details see Database Global Connector.)

Operation

Insert

Query type

Dynamic

*Dynamic query

 
INSERT INTO mystock("name", "date", "bookvalue") VALUES(#[xpath3('//Name').text], #[xpath3('//LastTradeDate').text], #[xpath3('//BookValue').text]);

Advanced Tab

The Advanced tab is set to its default values; no configuration is necessary.

db_conn_adv_tab

Standalone XML

<db:insert config-ref="Postgres" doc:name="Database">
   <db:dynamic-query><![CDATA[INSERT INTO mystock("name", "date", "bookvalue") VALUES(#[xpath3('//Name').text], #[xpath3('//LastTradeDate').text], #[xpath3('//BookValue').text]);]]>
   </db:dynamic-query>
</db:insert>

Database Global Connector

The database global connector is a global element that contains the connection configuration for the desired database. It is referenced by the database connector in the application flow.

Note that you must install the appropriate driver for your database in your Studio application. For details, see the Adding the Database Driver section in Database Connector.

Studio Visual Editor

Parameter Value Configuration window image

Name

Postgres

db_global_elem

Database URL

jdbc:postgresql://dbserver/stock

Driver Class Name

org.postgresql.Driver

Use XA Transactions

No (default)

Enable DataSense

Yes (default)

Standalone XML

<db:generic-config name="Postgres" url="jdbc:postgresql://dbserver/stock" driverClassName="org.postgresql.Driver" doc:name="Generic Database Configuration"/>

Running This Example

To trigger the flow in this application, use a Web browser or an HTTP client such as the curl command-line utility to hit the HTTP listener on localhost port 8081.

browser2 
curl 'http://localhost?q=select%20*%20from%20yahoo.finance.quotes%20where
%20symbol%20in%20(%22BAC%22)%0A%09%09&env=http%3A%2F%2Fdatatables.org%2
Falltables.env&format=XML'

Run the example as a Mule application, then hit the HTTP listener with your query. For a list of parameters you can use, consult the Yahoo! Query Language page, which allows you to build your REST query in an online console.

In this example, to retrieve a quote for Bank of America (ticker symbol BAC), we hit the application’s HTTP listener with:

http://localhost?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20
(%22BAC%22)%0A%09%09&env=http%3A%2F%2Fdatatables.org%2Falltables.env&format=XML

Check the Mule Console output to see the application’s progress:

INFO  2014-04-08 15:42:33,531 [main] org.mule.module.launcher.MuleDeploymentService:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ Started app 'financeapi'                                 +
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
INFO  2014-04-08 15:43:09,155 [[financeapi].financeapiFlow1.stage1.02] org.mule.transport.service.DefaultTransportServiceDescriptor: Loading default outbound transformer: org.mule.transport.http.transformers.ObjectToHttpClientMethodRequest
INFO  2014-04-08 15:43:09,167 [[financeapi].financeapiFlow1.stage1.02] org.mule.transport.service.DefaultTransportServiceDescriptor: Loading default response transformer: org.mule.transport.http.transformers.MuleMessageToHttpResponse
INFO  2014-04-08 15:43:09,168 [[financeapi].financeapiFlow1.stage1.02] org.mule.transport.service.DefaultTransportServiceDescriptor: Loading default outbound transformer: org.mule.transport.http.transformers.ObjectToHttpClientMethodRequest
INFO  2014-04-08 15:43:09,168 [[financeapi].financeapiFlow1.stage1.02] org.mule.lifecycle.AbstractLifecycleManager: Initialising: 'connector.http.mule.default.dispatcher.1157186244'. Object is: HttpClientMessageDispatcher
INFO  2014-04-08 15:43:09,171 [[financeapi].financeapiFlow1.stage1.02] org.mule.lifecycle.AbstractLifecycleManager: Starting: 'connector.http.mule.default.dispatcher.1157186244'. Object is: HttpClientMessageDispatcher
INFO  2014-04-08 15:43:10,591 [[financeapi].financeapiFlow1.stage1.02] org.mule.routing.ExpressionSplitter: The expression does not evaluate to a type that can be split: org.dom4j.tree.DefaultElement
INFO  2014-04-08 15:43:10,597 [[financeapi].financeapiFlow1.stage1.02] org.mule.lifecycle.AbstractLifecycleManager: Initialising: 'Database.dispatcher.1108267618'. Object is: EEJdbcMessageDispatcher
INFO  2014-04-08 15:43:10,622 [[financeapi].financeapiFlow1.stage1.02] org.mule.lifecycle.AbstractLifecycleManager: Starting: 'Database.dispatcher.1108267618'. Object is: EEJdbcMessageDispatcher
INFO  2014-04-08 15:43:11,105 [[financeapi].financeapiFlow1.stage1.02] com.mulesoft.mule.transport.jdbc.sqlstrategy.UpdateSqlStatementStrategy: Executing SQL statement: 1 row(s) updated

The image below shows the data inserted in the database row as shown in pgAdmin III, Postgres’s GUI interface.

pgadmin

Extracting JSON Data from a REST API and Inserting it in an External DB

This example application consumes JSON data from an external REST API, then extracts values from the JSON and inserts them into an external Postgres database. The application uses the Yahoo! REST API for financial services, which is free to use and does not require a user account. This API allows you to retrieve financial quotes using the SQL-based Yahoo Query Language, whose statements you include in the URL itself.

This application hits the Yahoo! REST API at http://query.yahooapis.com/v1/public/yql with whatever query you specify. The format of the reply is determined in the query itself, with the format=<format> parameter. This example uses format=json.

When it receives the reply in JSON, the application extracts some values such as stock name, date, price, etc. and inserts them into a table in an external Postgres database.

The application contains only one flow, shown below.

yahoo+flow+2

View the XML

<mule xmlns:json="http://www.mulesoft.org/schema/mule/json" xmlns:http="http://www.mulesoft.org/schema/mule/http" xmlns:db="http://www.mulesoft.org/schema/mule/db" 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"
    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/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd
http://www.mulesoft.org/schema/mule/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd
http://www.mulesoft.org/schema/mule/json http://www.mulesoft.org/schema/mule/json/current/mule-json.xsd">
    <db:generic-config name="Postgres" url="jdbc:postgresql://dbserver/stock" driverClassName="org.postgresql.Driver" doc:name="Generic Database Configuration"/>
    <db:template-query name="insert_into_current" doc:name="Template Query">
        <db:parameterized-query><![CDATA[INSERT INTO current("name", "date", "bookvalue") VALUES(:name,:date,:bookvalue);]]></db:parameterized-query>
        <db:in-param name="name" defaultValue="#[xpath3('//Name').text]"/>
        <db:in-param name="date" type="DATE" defaultValue="#[xpath3('//LastTradeDate').text]"/>
        <db:in-param name="bookvalue" defaultValue="#[xpath3('//BookValue').text])"/>
    </db:template-query>

     <http:listener-config name="HTTP_Listener_Configuration" host="localhost" port="8081" doc:name="HTTP Listener Configuration"/>
    <http:request-config name="HTTP_Request_Configuration" host="query.yahooapis.com" basePath="v1/public/yql" doc:name="HTTP Request Configuration"/>
    <flow name="financeapiFlow1" >
        <http:listener config-ref="HTTP_Listener_Configuration" path="/" doc:name="HTTP"/>
         <http:request config-ref="HTTP_Request_Configuration" path="/" method="GET" followRedirects="true" doc:name="HTTP">
            <http:request-builder>
                <http:query-param paramName="q" value="#[message.inboundProperties.'http.query.params'.q]"/>
                <http:query-param paramName="env" value="#[message.inboundProperties.'http.query.params'.env]"/>
                <http:query-param paramName="format" value="#[message.inboundProperties.'http.query.params'.format]"/>
            </http:request-builder>
        </http:request>
        <json:json-to-object-transformer returnClass="java.util.Map" doc:name="JSON to Object"/>
        <db:insert config-ref="Postgres" doc:name="Database">
            <db:dynamic-query><![CDATA[INSERT INTO mystock("name", "date", "bookvalue") VALUES('#[message.payload.query.results.quote.symbol]','#[message.payload.query.results.quote.LastTradeDate]','#[message.payload.query.results.quote.BookValue]');]]></db:dynamic-query>
        </db:insert>
    </flow>
</mule>

The first element, an HTTP listener, listens on localhost port 8081 (the default) for incoming GET requests. Hitting the listener triggers the flow. Requests to the HTTP listener must take the form:

http://localhost:8081?<query>

The <query> part of the request consists of the parameters accepted by the REST API. When the HTTP listener receives the HTTP request, the <query> part of the URL becomes a set of inbound properties. The HTTP lsitener passes the message to the next element in the flow, the HTTP request connector. The HTTP request connector uses a set of MEL expressions to extract the query parameters from the message, and to construct the full URL for the remote API, including the query parameters.

For example, if you hit the HTTP listener with the following:

http://localhost?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in
(%22GE%22)%0A%09%09&env=http%3A%2F%2Fdatatables.org%2Falltables.env&format=json

The application will query the URL listed below:

http://query.yahooapis.com/v1/public/yql?q=select
 * from yahoo.finance.quotes where symbol in
("GE")%0A%09%09&env=http%3A%2F%2Fdatatables.org%2Falltables.env&format=json

The above query retrieves information for ticker symbol GE, for General Electric. The REST API returns a JSON, which you can see by clicking below.

View the JSON returned by the REST API

{"query":{"count":1,"created":"2014-04-10T16:33:09Z","lang":"en","results":{"quote":{"symbol":"GE","Ask":null,"AverageDailyVolume":"36983100","Bid":null,"AskRealtime":"25.87","BidRealtime":"25.86","BookValue":"12.978","Change_PercentChange":"-0.09 - -0.35%","Change":"-0.09","Commission":null,"ChangeRealtime":"-0.09","AfterHoursChangeRealtime":"N/A - N/A","DividendShare":"0.82","LastTradeDate":"4/10/2014","TradeDate":null,"EarningsShare":"1.264","ErrorIndicationreturnedforsymbolchangedinvalid":null,"EPSEstimateCurrentYear":"1.70","EPSEstimateNextYear":"1.82","EPSEstimateNextQuarter":"0.40","DaysLow":"25.80","DaysHigh":"26.11","YearLow":"21.11","YearHigh":"28.09","HoldingsGainPercent":"- - -","AnnualizedGain":null,"HoldingsGain":null,"HoldingsGainPercentRealtime":"N/A - N/A","HoldingsGainRealtime":null,"MoreInfo":"cn","OrderBookRealtime":null,"MarketCapitalization":"259.5B","MarketCapRealtime":null,"EBITDA":"28.875B","ChangeFromYearLow":"+4.75","PercentChangeFromYearLow":"+22.50%","LastTradeRealtimeWithTime":"N/A - <b>25.86</b>","ChangePercentRealtime":"N/A - -0.35%","ChangeFromYearHigh":"-2.23","PercebtChangeFromYearHigh":"-7.94%","LastTradeWithTime":"12:18pm - <b>25.86</b>","LastTradePriceOnly":"25.86","HighLimit":null,"LowLimit":null,"DaysRange":"25.80 - 26.11","DaysRangeRealtime":"N/A - N/A","FiftydayMovingAverage":"25.6261","TwoHundreddayMovingAverage":"25.9491","ChangeFromTwoHundreddayMovingAverage":"-0.0891","PercentChangeFromTwoHundreddayMovingAverage":"-0.34%","ChangeFromFiftydayMovingAverage":"+0.2339","PercentChangeFromFiftydayMovingAverage":"+0.91%","Name":"General Electric","Notes":null,"Open":"26.09","PreviousClose":"25.95","PricePaid":null,"ChangeinPercent":"-0.35%","PriceSales":"1.79","PriceBook":"2.00","ExDividendDate":"Feb 20","PERatio":"20.53","DividendPayDate":"Apr 25","PERatioRealtime":null,"PEGRatio":"1.80","PriceEPSEstimateCurrentYear":"15.26","PriceEPSEstimateNextYear":"14.26","Symbol":"GE","SharesOwned":null,"ShortRatio":"2.50","LastTradeTime":"12:18pm","TickerTrend":"&nbsp;======&nbsp;","OneyrTargetPrice":"28.73","Volume":"13263903","HoldingsValue":null,"HoldingsValueRealtime":null,"YearRange":"21.11 - 28.09","DaysValueChange":"- - -0.35%","DaysValueChangeRealtime":"N/A - N/A","StockExchange":"NYSE","DividendYield":"3.16","PercentChange":"-0.35%"}}}}

The HTTP Request Connector passes the JSON it received from the API to a JSON-to-object transformer configured to output a java.util.Map object. The transformer sends this object, which contains the JSON data as key=value pairs, to the last element in the flow, a database connector. This connector uses an SQL query with embedded Mule Expression Language expressions to extract specific values from the JSON and insert them into the external database. The full SQL query is shown below.

{"query":{"count":1,"created":"2014-04-10T16:33:09Z","lang":"en","results":{"quote":{"symbol":"GE","Ask":null,"AverageDailyVolume":"36983100","Bid":null,"AskRealtime":"25.87","BidRealtime":"25.86","BookValue":"12.978","Change_PercentChange":"-0.09 - -0.35%","Change":"-0.09","Commission":null,"ChangeRealtime":"-0.09","AfterHoursChangeRealtime":"N/A - N/A","DividendShare":"0.82","LastTradeDate":"4/10/2014","TradeDate":null,"EarningsShare":"1.264","ErrorIndicationreturnedforsymbolchangedinvalid":null,"EPSEstimateCurrentYear":"1.70","EPSEstimateNextYear":"1.82","EPSEstimateNextQuarter":"0.40","DaysLow":"25.80","DaysHigh":"26.11","YearLow":"21.11","YearHigh":"28.09","HoldingsGainPercent":"- - -","AnnualizedGain":null,"HoldingsGain":null,"HoldingsGainPercentRealtime":"N/A - N/A","HoldingsGainRealtime":null,"MoreInfo":"cn","OrderBookRealtime":null,"MarketCapitalization":"259.5B","MarketCapRealtime":null,"EBITDA":"28.875B","ChangeFromYearLow":"+4.75","PercentChangeFromYearLow":"+22.50%","LastTradeRealtimeWithTime":"N/A - <b>25.86</b>","ChangePercentRealtime":"N/A - -0.35%","ChangeFromYearHigh":"-2.23","PercebtChangeFromYearHigh":"-7.94%","LastTradeWithTime":"12:18pm - <b>25.86</b>","LastTradePriceOnly":"25.86","HighLimit":null,"LowLimit":null,"DaysRange":"25.80 - 26.11","DaysRangeRealtime":"N/A - N/A","FiftydayMovingAverage":"25.6261","TwoHundreddayMovingAverage":"25.9491","ChangeFromTwoHundreddayMovingAverage":"-0.0891","PercentChangeFromTwoHundreddayMovingAverage":"-0.34%","ChangeFromFiftydayMovingAverage":"+0.2339","PercentChangeFromFiftydayMovingAverage":"+0.91%","Name":"General Electric","Notes":null,"Open":"26.09","PreviousClose":"25.95","PricePaid":null,"ChangeinPercent":"-0.35%","PriceSales":"1.79","PriceBook":"2.00","ExDividendDate":"Feb 20","PERatio":"20.53","DividendPayDate":"Apr 25","PERatioRealtime":null,"PEGRatio":"1.80","PriceEPSEstimateCurrentYear":"15.26","PriceEPSEstimateNextYear":"14.26","Symbol":"GE","SharesOwned":null,"ShortRatio":"2.50","LastTradeTime":"12:18pm","TickerTrend":"&nbsp;======&nbsp;","OneyrTargetPrice":"28.73","Volume":"13263903","HoldingsValue":null,"HoldingsValueRealtime":null,"YearRange":"21.11 - 28.09","DaysValueChange":"- - -0.35%","DaysValueChangeRealtime":"N/A - N/A","StockExchange":"NYSE","DividendYield":"3.16","PercentChange":"-0.35%"}}}}

In the query shown above, the MEL expressions expand to the text of each specified element within the JSON data. For example, #[message.payload.query.results.quote.symbol] retrieves the value of symbol from the JSON structure shown below.

{
  "query":    {
        "count":1,
        "created":"2014-04-10T16:33:09Z",
        "lang":"en",
        "results":    {
                "quote":    {
                    "symbol":"GE",

The full configuration for this example is detailed below. To see the application in action, skip to Running This Example.

Configuring This Example

HTTP Listener

Studio Visual Editor

  1. Set up the Properties editor of the Connector:

http+listener+basic
Parameter Value

Display Name

HTTP

Path

/

  1. Create a new Connector Configuration Element and set up the port and host:

http+config+1
Parameter Value

Name

HTTP_Listener_Configuration

Host

localhost

Port

8081

Standalone XML

Configure the HTTP connector as follows:

<http:listener config-ref="HTTP_Listener_Configuration" path="/" doc:name="HTTP"/>
Attribute Value

doc:name

HTTP

config-ref

HTTP_Listener_Configuration

path

`/ `

For this element to work, you must reference an abstract element called a Connector Configuration, which contains several of the high level necessary configuration properties. The config-ref attribute in the connector references this connector configuration element. You must now create an element outside the flow that matches the referenced name.

<http:listener-config name="HTTP_Listener_Configuration" host="localhost" port="8081" doc:name="HTTP Listener Configuration"/>
Attribute Value

name

HTTP_Listener_Configuration

host

localhost

port

8081

doc:name

HTTP Listener Configuration

HTTP Request Connector

Studio Visual Editor

  1. Create a new Connector Configuration Element and set up the Host and Base Path:

    yahoo+global+element
    Parameter Value

    Name

    HTTP_Request_Connector

    Host

    query.yahooapis.com

    Base Path

    v1/public.yql

    With this configuration, requests will be sent out to query.yahooapis.com/v1/public.yql

  2. Save changes and return to the connector’s properties editor by clicking ok. Then set up the path and method:

    http+connector+basic
    Parameter Value

    Display Name

    HTTP

    Path

    /

    Method

    GET

  3. Click the Add Parameter button three times to add three parameters. Leave all as the default type query-param. For the value field of each, write a mule expression that takes the equivalent value from the query parameters of the request that first reached the HTTP Listener, these are transformed into inbound properties by the time they reach the HTTP Request Connector.

    yahoo+http+w+params
    Parameter Value

    Type

    query-param

    Name

    q

    Value

    #[message.inboundProperties.'http.query.params'.q]
    Parameter Value

    Type

    query-param

    Name

    env

    Value

    #[message.inboundProperties.'http.query.params'.env]
    Parameter Value

    Type

    query-param

    Name

    format

    Value

    #[message.inboundProperties.'http.query.params'.format]

    Configured in this way, the query params that reach the HTTP listener are forwarded unchanged to the yahoo API.

  4. Select the Advanced tab on the left side panel of the connector, tick the checkbox labeled Follow Redirects

XML Standalone

Configure the HTTP connector as follows:

<http:request config-ref="HTTP_Request_Configuration" path="/" method="GET" followRedirects="true" doc:name="HTTP">
            <http:request-builder>
                <http:query-param paramName="q" value="#[message.inboundProperties.'http.query.params'.q]"/>
                <http:query-param paramName="env" value="#[message.inboundProperties.'http.query.params'.env]"/>
                <http:query-param paramName="format" value="#[message.inboundProperties.'http.query.params'.format]"/>
            </http:request-builder>
        </http:request>
Attribute Value

doc:name

HTTP

config-ref

HTTP_Request_Configuration

path

`/ `

method

GET

followRedirects

true

As you can see above, there are a series of child elements of the connector, these define three query parameters that take their values from inbound properties of the message. Enclosing the three elements that define these query parameters, is a request-builder element that is always necessary when adding parameters to a request.

Parameter Value

type

http:query-param

paramName

q

value

#[message.inboundProperties.'http.query.params'.q]
Parameter Value

type

http:query-param

paramName

env

value

#[message.inboundProperties.'http.query.params'.env]
Parameter Value

type

http:query-param

paramName

format

value

#[message.inboundProperties.'http.query.params'.format]

Configured in this way, the query params that reach the HTTP listener are forwarded unchanged to the yahoo API.

<http:request-config name="HTTP_Request_Configuration" host="baconipsum.com"  doc:name="HTTP Request Configuration" basePath="api"/>

For this element to work, you must reference a Connector Configuration. The config-ref attribute in the connector references this connector configuration element. You must now create an element outside the flow that matches the referenced name.

Attribute Value

name

HTTP_Request_Configuration

host

baconipsum.com

basePath

api

doc:name

HTTP Request Configuration

JSON to Object Transformer

Studio Visual Editor

General Tab

Parameter Value Configuration window image

Display Name

JSON to Object

json.to.object.general

Mapper Reference

-

Advanced Tab

Parameter Value Configuration window image

Return Class

java.util.Map

json.to.object-adv_tab

Ignore Bad Input

no

Encoding

-

Standalone XML

<json:json-to-object-transformer doc:name="JSON to Object" returnClass="java.util.Map"/>

Database Connector

Studio Visual Editor

General Tab

Parameter Value Configuration window image

Display Name

Database

db_conn_json_gral_tab

Connector configuration

Postgres

Operation

insert

Query Type

Dynamic

Advanced Tab

The Advanced tab is set to its default values; no configuration is necessary.

db_conn_adv_tab

XML Standalone

<db:insert config-ref="Postgres" doc:name="Database">
   <db:dynamic-query><![CDATA[INSERT INTO mystock("name", "date", "bookvalue") VALUES('#[message.payload.query.results.quote.symbol]','#[message.payload.query.results.quote.LastTradeDate]','#[message.payload.query.results.quote.BookValue]');]]></db:dynamic-query>
</db:insert>

Database Global Connector

The database global connector is a global element that contains the connection configuration for the desired database. It is referenced by the database connector in the application flow.

Note that you must install the appropriate driver for your database in your Studio application. For details, see the Adding the Database Driver section in the Database Connector.

Studio Visual Editor

Parameter Value Configuration window image

Name

Postgres

db_global_elem

Database URL

jdbc:postgresql://dbserver/stock

Driver Class Name

org.postgresql.Driver

Use XA Transactions

No (default)

Enable DataSense

Yes (default)

Standalone XML

<db:generic-config name="Postgres" url="jdbc:postgresql://dbserver/stock" driverClassName="org.postgresql.Driver" doc:name="Generic Database Configuration"/>

Running This Example

To trigger the flow in this application, use a Web browser or an HTTP client such as the curl command-line utility to hit the HTTP listener on localhost port 8081.

browser2 
curl 'http://localhost?q=select%20*%20from%20yahoo.finance.quotes%20where
%20symbol%20in%20(%22BAC%22)%0A%09%09&env=http%3A%2F%2Fdatatables.org%2
Falltables.env&format=json'

When using whitespace characters as command parameters on the command line, make sure to adequately escape special characters as necessary (for example on Unix systems with the use of single quotes as shown above).

Run the example as a Mule application, then hit the HTTP listener with your query. For a list of parameters you can use, consult the Yahoo! Query Language page, which allows you to build your REST query in an online console.

In this example, to retrieve a quote for General Electric (ticker symbol GE), we hit the application’s HTTP Listener with:

http://localhost?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20(%22GE%22)%0A%09%09&env=http%3A%2F%2Fdatatables.org%2Falltables.env&format=json

Check the Mule Console output to see the application’s progress:

INFO  2014-04-14 18:22:54,315 [main] org.mule.module.launcher.MuleDeploymentService:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ Started app 'financeapi'                                 +
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
INFO  2014-04-14 18:23:04,573 [[financeapi].financeapiFlow2.stage1.02] org.mule.transport.service.DefaultTransportServiceDescriptor: Loading default outbound transformer: org.mule.transport.http.transformers.ObjectToHttpClientMethodRequest
INFO  2014-04-14 18:23:04,583 [[financeapi].financeapiFlow2.stage1.02] org.mule.transport.service.DefaultTransportServiceDescriptor: Loading default response transformer: org.mule.transport.http.transformers.MuleMessageToHttpResponse
INFO  2014-04-14 18:23:04,583 [[financeapi].financeapiFlow2.stage1.02] org.mule.transport.service.DefaultTransportServiceDescriptor: Loading default outbound transformer: org.mule.transport.http.transformers.ObjectToHttpClientMethodRequest
INFO  2014-04-14 18:23:04,584 [[financeapi].financeapiFlow2.stage1.02] org.mule.lifecycle.AbstractLifecycleManager: Initialising: 'connector.http.mule.default.dispatcher.208700779'. Object is: HttpClientMessageDispatcher
INFO  2014-04-14 18:23:04,588 [[financeapi].financeapiFlow2.stage1.02] org.mule.lifecycle.AbstractLifecycleManager: Starting: 'connector.http.mule.default.dispatcher.208700779'. Object is: HttpClientMessageDispatcher
INFO  2014-04-14 18:23:05,955 [[financeapi].financeapiFlow2.stage1.02] org.mule.lifecycle.AbstractLifecycleManager: Initialising: 'Database.dispatcher.209950951'. Object is: EEJdbcMessageDispatcher
INFO  2014-04-14 18:23:05,957 [[financeapi].financeapiFlow2.stage1.02] org.mule.lifecycle.AbstractLifecycleManager: Starting: 'Database.dispatcher.209950951'. Object is: EEJdbcMessageDispatcher
INFO  2014-04-14 18:23:06,239 [[financeapi].financeapiFlow2.stage1.02] com.mulesoft.mule.transport.jdbc.sqlstrategy.UpdateSqlStatementStrategy: Executing SQL statement: 1 row(s) updated

The image below shows the data inserted in the database row as shown in pgAdmin III, Postgres’s GUI interface.

pgadmin json