This site contains the documentation that is relevant to older WSO2 product versions and offerings.
For the latest WSO2 documentation, visit https://wso2.com/documentation/.
Sample 363: Reusable Database Connection Pools
- Former user (Deleted)
Owned by Former user (Deleted)
Objective: Demonstrate the use of reusable database connection pools
<!-- Reusable database connection pool -->
<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>
<connection>
<pool>
<dsName>lookupdb</dsName>
<icClass>com.sun.jndi.rmi.registry.RegistryContextFactory</icClass>
<url>rmi://localhost:2199</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>
<log level="custom">
<property name="text"
value="** Reporting to the Database **"/>
</log>
<dbreport>
<connection>
<pool>
<dsName>reportdb</dsName>
<icClass>com.sun.jndi.rmi.registry.RegistryContextFactory</icClass>
<url>rmi://localhost:2199</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>
<connection>
<pool>
<dsName>reportdb</dsName>
<icClass>com.sun.jndi.rmi.registry.RegistryContextFactory</icClass>
<url>rmi://localhost:2199</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: 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