Sample 363: 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"
expression="//m0:return/m0:last/child::text()" type="DOUBLE"/>
<parameter xmlns:m0="http://services.samples"
expression="//m0:return/m0: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"
expression="//m0:return/m0: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>
Objective
Demonstrate how to setup reusable connection pools for the dblookup and dbreport mediators
Pre-requisites
- Setup a Derby database and the Synapse data sources as described in the database setup guide
- Deploy the SimpleStockQuoteService in the sample Axis2 server and start Axis2
-
Start Synapse using the configuration numbered 363 (repository/conf/sample/synapse_sample_363.xml)
Unix/Linux: sh synapse.sh -sample 363
Windows: synapse.bat -sample 363
Executing the Client
This sample employs two instances of the dblookup mediator and a single instance of the dbreport mediator. The two dblookup mediators are sharing the same database connection pool named 'lookupdb'. The dbreport mediator makes use of a different connection pool named 'dbreport'. Synapse uses Apache DBCP to create and manage the corresponding data sources and connection pools.
Run this sample by invoking the client as follows.
ant stockquote -Daddurl=http://localhost:9000/services/SimpleStockQuoteService -Dtrpurl=http://localhost:8280/ -Dsymbol=IBM
Synapse will log the following output as it reads from and writes to the database.
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