Sample 360: Introduction to dblookup Mediator

Objective: Introduction to the dblookup mediator

<definitions xmlns="http://ws.apache.org/ns/synapse">

    <sequence name="myFaultHandler">
        <makefault>
            <code value="tns:Receiver" xmlns:tns="http://www.w3.org/2003/05/soap-envelope"/>
            <reason expression="get-property('ERROR_MESSAGE')"/>
        </makefault>

        <property name="RESPONSE" value="true"/>
        <header name="To" expression="get-property('ReplyTo')"/>
        <send/>
        <drop/>
    </sequence>

    <sequence name="main" onError="myFaultHandler">
        <in>
            <log level="custom">
                <property name="text"
                          value="** Looking up from the Database **"/>
            </log>
            <dblookup xmlns="http://ws.apache.org/ns/synapse">
                <connection>
                    <pool>
                        <driver>org.apache.derby.jdbc.ClientDriver</driver>
                        <url>jdbc:derby://localhost:1527/esbdb;create=false</url>
                        <user>esb</user>
                        <password>esb</password>
                    </pool>
                </connection>
                <statement>
                    <sql>select * from company where name =?</sql>
                    <parameter expression="//m0:getQuote/m0:request/m0:symbol"
                               xmlns:m0="http://services.samples" type="VARCHAR"/>
                    <result name="company_id" column="id"/>
                </statement>
            </dblookup>

            <switch source="get-property('company_id')">
                <case regex="c1">
                    <log level="custom">
                        <property name="text"
                                  expression="fn:concat('Company ID - ',get-property('company_id'))"/>
                    </log>
                    <send>
                        <endpoint>
                            <address uri="http://localhost:9000/services/SimpleStockQuoteService"/>
                        </endpoint>
                    </send>
                </case>
                <case regex="c2">
                    <log level="custom">
                        <property name="text"
                                  expression="fn:concat('Company ID - ',get-property('company_id'))"/>
                    </log>
                    <send>
                        <endpoint>
                            <address uri="http://localhost:9000/services/SimpleStockQuoteService"/>
                        </endpoint>
                    </send>
                </case>
                <case regex="c3">
                    <log level="custom">
                        <property name="text"
                                  expression="fn:concat('Company ID - ',get-property('company_id'))"/>
                    </log>
                    <send>
                        <endpoint>
                            <address uri="http://localhost:9000/services/SimpleStockQuoteService"/>
                        </endpoint>
                    </send>
                </case>
                <default>
                    <log level="custom">
                        <property name="text" value="** Unrecognized Company ID **"/>
                    </log>
                    <makefault>
                        <code value="tns:Receiver"
                              xmlns:tns="http://www.w3.org/2003/05/soap-envelope"/>
                        <reason value="** Unrecognized Company ID **"/>
                    </makefault>
                    <property name="RESPONSE" value="true"/>
                    <header name="To" action="remove"/>
                    <send/>
                    <drop/>
                </default>
            </switch>
            <drop/>
        </in>

        <out>
            <send/>
        </out>

    </sequence>

</definitions>

Prerequisites:

Setting up Derby database as explained above.
Start the Synapse configuration numbered 360: i.e. wso2esb-samples -sn 360
Start the Axis2 server and deploy the SimpleStockQuoteService if not already done

This sample demonstrates simple database read operations through ESB. When a message arrives at dblookup mediator, it opens a connection to the database and executes the SQL query. The SQL query use '?' character for attributes that will be filled at runtime. The parameters define how to calculate the value of those attributes at runtime. In this sample a dblookup mediator has been used to extract 'id' of the company from the company database using the symbol which is evaluated using an xpath against the SOAP envelope. Then 'id' base switching will be done by a switch mediator.

When the IBM stock quote is requested,

ant stockquote -Daddurl=http://localhost:9000/services/SimpleStockQuoteService -Dtrpurl=http://localhost:8280/ -Dsymbol=IBM

ESB console shows

INFO LogMediator text = ** Looking up from the Database **INFO LogMediator text = Company ID ? c1

For the SUN stock quote,

ant stockquote -Daddurl=http://localhost:9000/services/SimpleStockQuoteService -Dtrpurl=http://localhost:8280/ -Dsymbol=SUN

ESB console shows

INFO LogMediator text = ** Looking up from the Database **INFO LogMediator text = Company ID ? c2

and for the MSFT stock quote,

ant stockquote -Daddurl=http://localhost:9000/services/SimpleStockQuoteService -Dtrpurl=http://localhost:8280/ -Dsymbol=MSFT
INFO LogMediator text = ** Looking up from the Database **
INFO LogMediator text = Company ID ? c2

For any other symbols, ESB console shows

INFO LogMediator text = ** Unrecognized Company ID **

and the client gets a response which has following message.

** Unrecognized Company ID **