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"
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>
Objective
Demonstrate how to setup reusable connection pools for the dblookup and dbreport
mediators
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
Back to Catalog
