Sample 363: Reusable Database Connection Pools

Objective: Demonstrate the use of reusable database connection pools

<definitions xmlns="http://ws.apache.org/ns/synapse">
    <sequence name="myFaultHandler">
        <makefault response="true">
            <code xmlns:tns="http://www.w3.org/2003/05/soap-envelope" value="tns:Receiver"/>
            <reason expression="get-property('ERROR_MESSAGE')"/>
        </makefault>
        <send/>
        <drop/>
    </sequence>

    <sequence name="main" onError="myFaultHandler">
        <in>
            <log level="custom">
                <property name="text" value="** Looking up from the Database **"/>
            </log>
            <dblookup>
                <connection>
                    <pool>
                        <dsName>lookupdb</dsName>
                    </pool>
                </connection>
                <statement>
                    <sql>select * from company where name =?</sql>
                    <parameter xmlns:m0="http://services.samples" expression="//m0:getQuote/m0:request/m0:symbol"
                               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 response="true">
                        <code xmlns:tns="http://www.w3.org/2003/05/soap-envelope" value="tns:Receiver"/>
                        <reason value="** Unrecognized Company ID **"/>
                    </makefault>
                    <send/>
                    <drop/>
                </default>
            </switch>
            <drop/>
        </in>

        <out>
            <log level="custom">
                <property name="text" value="** Reporting to the Database **"/>
            </log>
            <dbreport>
                <connection>
                    <pool>
                        <dsName>reportdb</dsName>
                    </pool>
                </connection>
                <statement>
                    <sql>update company set price=? where name =?</sql>
                    <parameter xmlns:m0="http://services.samples" xmlns:m1="http://services.samples/xsd"
                               expression="//m0:return/m1:last/child::text()" type="DOUBLE"/>
                    <parameter xmlns:m0="http://services.samples" xmlns:m1="http://services.samples/xsd"
                               expression="//m0:return/m1:symbol/child::text()" type="VARCHAR"/>
                </statement>
            </dbreport>
            <log level="custom">
                <property name="text" value="** Looking up from the Database **"/>
            </log>
            <dblookup>
                <connection>
                    <pool>
                        <dsName>reportdb</dsName>
                    </pool>
                </connection>
                <statement>
                    <sql>select * from company where name =?</sql>
                    <parameter xmlns:m0="http://services.samples" xmlns:m1="http://services.samples/xsd"
                               expression="//m0:return/m1:symbol/child::text()" 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: Setting up DataBase and DataSources according to the sample setup guide.

Start the Synapse configuration numbered 363: i.e. wso2esb-samples -sn 363
Start the Axis2 server and deploy the SimpleStockQuoteService if not already done

Runs the client as follows

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

Then the console output

 INFO LogMediator text = ** Looking up from the Database **
    ...
 INFO LogMediator text = Company ID - c1
    ...
 INFO LogMediator text = ** Reporting to the Database **
    ...
 INFO LogMediator text = ** Looking up from the Database **
    ...
 INFO LogMediator text = Stock price - 183.3635460215262