tomcat connection pooling

Question

Hi, I have a simple servlet running in Tomcat. Because the servlet connects to a database, I need to use connection pooling. However, all the examples on the internet assume that (the developer) will never change which database the servlet is connecting to.

For example, here is a sample context.xml file.

<?xml version="1.0" encoding="UTF-8"?>
<Context antiJARLocking="true" path="/feeds">
    <Resource name="jdbc/TestDB"
     auth="Container"
     type="javax.sql.DataSource"
     factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
     username="username"
     password="password"
     driverClassName="org.postgresql.Driver"
     url="jdbc:postgresql://example.com:1234/myDB"
     maxWait="1000"
     removeAbandoned="true"
     maxActive="30"
     maxIdle="10"
     removeAbandonedTimeout="60"
     logAbandoned="true"/>
</Context>

In my case, I have a test server (postgresql) and a production server (ms sql), so all the tutorials assuming that I can just hardcode settings in the context.xml won't work.

I have 2 different properties file's for the database url's, authentication etc., which I use, one for test server, one for production server, and that works great, but now if I want to use connection pooling, how would I integrate this into my servlet?

I want to be able to hit "build" in Netbeans 6.5, take the war in the dist directory, and drop it in the tomcat application dir of either server, without having to go around changing xml files after the new war has been deployed. The servlet knows where to get the properties file on each system, so if I can integrate the properties file with the connection pooling properties, I'd be all set.

Any ideas... ?

Answer 1

You could have 2 different connections, and detect on the fly which connection to use in the application.

Answer 2

I put all my datasource definitions in the conf/server.xml file of tomcat, so the war is completely independent of the datasource.

server.xml:

<GlobalNamingResources>
    <Resource name="mail/Mail" auth="Container" type="javax.mail.Session"
              mail.smtp.host="localhost"/>
    <Resource auth="Container" type="javax.sql.DataSource" name="jdbc/lagalerie"
              driverClassName="org.postgresql.Driver"
              url="jdbc:postgresql://localhost/lagalerie?charSet=LATIN1"
              maxActive="100" maxIdle="30" maxWait="10000"
              username="casashop" password="casashop"/>
</GlobalNamingResources>

context.xml:

  <ResourceLink global="jdbc/lagalerie" name="jdbc/lagalerie" type="javax.sql.DataSource"/>
  <ResourceLink global="mail/Mail" name="mail/Mail" type="javax.mail.Session"/>

web.xml:

<resource-ref>
    <description>The datasource</description>
    <res-ref-name>jdbc/DataSource</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref>
<resource-ref>
    <description>The mail session</description>
    <res-ref-name>mail/Mail</res-ref-name>
    <res-type>javax.mail.Session</res-type>
    <res-auth>Container</res-auth>
</resource-ref>