Using Pooled Database Connections

There is no mechanism within Castor JDO to provide pooling of JDBC drivers. Rather, Castor JDO relies on the drivers or external driver wrappers to implement a pooling mechanism. Some drivers, such as Oracle, provides a pooling mechanism in the driver. For those that do not, there are tools such as Proxool and Jakarta's DBCP project.

Here, I'll go over the various usage of the PostgreSQL driver with Castor. We start with the most basic configurations that do not use any pooling, to those with pooling via DBCP. I'll include how to configure the pooling version of the PostgreSQL JDBC driver ths will be usable with PostgreSQL 7.3 and later, how to setup a Tomcat JNDI context that Castor can use to get a pooled JDBC connection. Finally, I'll explain how to configure a BasicDataSource from the DBCP package using the <data-source> element.

A standard jdo-conf.xml entry for using PostgreSQL without pooling looks like this:

  <driver class-name="org.postgresql.Driver"
          url="jdbc:postgresql://localhost/app">
    <param name="user" value="smith"/>
    <param name="password" value="secret" />
  </driver>

On the other hand, if you wanted to use the PostgresqlDataSource, you would use the data-source tag instead, and the connection entry would look like this:

        
  <data-source class-name="org.postgresql.PostgresqlDataSource">
   <param name="server-name" value="localhost" />
   <param name="database-name" value="app" />
   <param name="user" value="smith" />
   <param name="password" value="secret" />
  </data-source>

(Note that only versions before 7.3 of the PostgreSQL JDBC driver include this class)

In the 7.3 release of PostgreSQL, they will start providing a pooling mechanism with their driver. The Castor SVN repository includes a beta version of the driver with this functionality. Here is the 'current' configuration needed for the upcoming 7.3 release of PostgreSQL. (Unless they change it.) Note that in this pooling mechanism currently lacks some features of standrd pooling packages such as DBCP, such as timing out idle connections and removing failed connections from the pool. In this case, we can create the following data-source entry in the jdo-conf.xml file to provide for our connections with Castor.

        
  <data-source class-name="org.postgresql.jdbc2.optional.PoolingDataSource">
   <param name="server-name" value="localhost" />
   <param name="database-name" value="app" />
   <param name="initial-connections" value="2" />
   <param name="max-connections" value="10" />
   <param name="user" value="smith" />
   <param name="password" value="secret" />
  </data-source>

Here is the configuration needed for using a connection pool with the Oracle JDBC DataSource implementations.

        
   <data-source class-name="oracle.jdbc.pool.OracleConnectionCacheImpl">
      <param name="URL" value="jdbc:oracle:thin:@localhost:1521:TEST" />
      <param name="user" value="scott" />
      <param name="password" value="tiger" />
   </data-source>

Here is the configuration needed for using a connection pool with the mySQL JDBC DataSource implementations.

        
   <data-source class-name="com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource">
      <param name="server-name" value="localhost" />
      <param name="port" value="3306" />
      <param name="user" value="scott" />
      <param name="password" value="tiger" />
      <param name="database-name" value="test" />
   </data-source>

Finally, I want to show the configuration for using a pooling data-source for Castor which is retrieved from a JNDI context that Apache fills. The first example is using the PostgreSQL pooling data-source, and the second is using Castor. The information to gain here is that we did not need to change the jdo-conf.xml file or the webapp's web.xml file to achieve this.

First, we modify the deployment context for the webapp in Tomcat >= 4.0 for our webapp in the conf/server.xml directory. (With Tomcat/Catalina releases 4.0 and higher there's more than one way of adding a <Resource> entry. Please consult with the manuals for more and more detailed information).

We add the following information (using the PostgreSQL JDBC DataSource implementations as introduced above.):

        
<Context path="/webapp" docBase="test" debug="10">
 <Resource name="jdbc/appDb" auth="Container"
            type="org.postgresql.jdbc2.optional.PoolingDataSource"/>
   <ResourceParams name="jdbc/appDb">
    <parameter>
      <name>factory</name>
      <value>org.postgresql.jdbc2.optional.PGObjectFactory</value>
    </parameter>
    <parameter>
     <name>dataSourceName</name>
     <value>appDb</value>
    </parameter>
    <parameter>
     <name>initialConnections</name>
     <value>2</value>
    </parameter>
    <parameter>
      <name>maxConnections</name>
      <value>5</value>
    </parameter>
    <parameter>
      <name>databaseName</name>
      <value>app</value>
    </parameter>
    <parameter>
      <name>user</name>
      <value>smith</value>
    </parameter>
    <parameter>
      <name>password</name>
      <value>secret</value>
    </parameter>
    <parameter>
      <name>serverName</name>
      <value>localhost</value>
    </parameter>
  </ResourceParams>
</Context>

     

Here, we are using the PostgreSQL PGObjectFactory which provides the JNDI server (Tomcat) the ability to create the correct data source. Now, the web.xml file for the webapp needs to be updated too.

        
        
<resource-env-ref>
  <description>PostgreSQL pooling check</description>
  <resource-env-ref-name>jdbc/appDb</resource-env-ref-name>
  <resource-env-ref-type>javax.sql.DataSource</resource-env-ref-type>
</resource-env-ref>

      

Note that we are only calling the ref type a DataSource object, not using the PostgreSQL class name. This will enable us to make changes easily. Now, in the jdo-conf.xml file that Castor uses, we no longer list the driver or data-source tag, but use the JNDI one, and it is simply this:

         
    <jndi name="java:comp/env/jdbc/appDb"/>

     

Commons-DBCP provides database connection pooling services, and together with Commons-Pool it is the default JNDI datasource provider for Tomcat.

With release 1.1 of the Jakarta Commons DBCP component, one of the major new features of the JDBC 3.0 API has (finally) been added to BasicDataSource, support for prepared statement pooling.

To configure Castor for the use of DBCP, please provide the following <data-source> entry in the jdo-conf.xml file.

  <data-source class-name="org.apache.commons.dbcp.BasicDataSource">
    <param name="driver-class-name" value="com.mysql.jdbc.Driver" />
    <param name="username" value="test" />
    <param name="password" value="test" />
    <param name="url" value="jdbc:mysql://localhost/test" />
    <param name="max-active" value="10" />
  </data-source>

As mentioened above, please note that with DBCP 1.1 and later releases, support for prepared statement pooling has been added to DBCP. As Castor JDO does not implement prepared statement pooling itself, you will to configure DBCP explicitely to enable this feature.

To configure Castor for the use of DBCP, and to turn prepared statement pooling on, please provide the following <data-source> entry in the jdo-conf.xml file.

  <data-source class-name="org.apache.commons.dbcp.BasicDataSource">
    <param name="driver-class-name" value="com.mysql.jdbc.Driver" />
    <param name="username" value="test" />
    <param name="password" value="test" />
    <param name="url" value="jdbc:mysql://localhost/test" />
    <param name="max-active" value="10" />
    <param name="pool-prepared-statements" value="true" />
  </data-source>

There's plenty of information on configuration of BasicDataSource, and examples are provided here.