This documentation is for WSO2 Enterprise Service Bus version 4.7.0 . View documentation for the latest release.

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migrated to Confluence 5.3

Objective: Introduction to the dblookup mediator

Code Block
languagehtml/xml
<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/xsd" 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,

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

ESB console shows

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

For the SUN stock quote,

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

ESB console shows

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

and for the MSFT stock quote,

Code Block
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

Code Block
INFO LogMediator text = ** Unrecognized Company ID **

and the client gets a response which has following message.

Code Block
** Unrecognized Company ID **