Sample 362: DBReport and DBLookup Mediators Together

Note that WSO2 EI is shipped with the following changes to what is mentioned in this documentation:

<PRODUCT_HOME>/repository/samples/ directory that includes all Integration profile samples is changed to <EI_HOME>/samples/service-bus/.
<PRODUCT_HOME>/repository/samples/resources/ directory that includes all artifacts related to the Integration profile samples is changed to <EI_HOME>/samples/service-bus/resources/.

Objective: Demonstrate the use of the DBReport and DBLookup mediators together.

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

    <sequence name="main">
        <in>
            <send>
                <endpoint>
                    <address uri="http://localhost:9000/services/SimpleStockQuoteService"/>
                </endpoint>
            </send>
        </in>

        <out>
            <log level="custom">
                <property name="text"
                          value="** Reporting to the Database **"/>
            </log>

            <dbreport 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>update company set price=? where name =?</sql>
                    <parameter expression="//m0:return/m1:last/child::text()"
                               xmlns:m0="http://services.samples" xmlns:m1="http://services.samples/xsd" type="DOUBLE"/>
                    <parameter expression="//m0:return/m1:symbol/child::text()"
                               xmlns:m0="http://services.samples" xmlns:m1="http://services.samples/xsd" type="VARCHAR"/>
                </statement>
            </dbreport>
            <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:return/m1:symbol/child::text()"
                               xmlns:m0="http://services.samples" xmlns:m1="http://services.samples/xsd" type="VARCHAR"/>
                    <result name="stock_price" column="price"/>
                </statement>
            </dblookup>
            <log level="custom">
                <property name="text"
                          expression="fn:concat('Stock price - ',get-property('stock_price'))"/>
            </log>
            <send/>
        </out>
    </sequence>

</definitions>

Prerequisites:

Create the above configuration and deploy it in the ESB profile.
Start the Axis2 server and deploy the SimpleStockQuoteService if not already done

In this sample, the DBReport mediator works the same as the above sample. It updates the price for the given company using the response messages content. Then the DBLookup mediator reads the last updated value from the company database and logs it.

When running the client as follows:

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

The ESB console shows:

INFO LogMediator text = ** Reporting to the Database **
...
INFO LogMediator text = ** Looking up from the Database **
...
INFO LogMediator text = Stock price - 153.47886496064808