Codehaus     ExoLab     OpenEJB     OpenJMS     OpenORB     Castor     Tyrex     
 

Main
  Home
  About
  Features
  Download
  API
  Schema
  Castor Forums
  Mailing Lists
  CVS / Bugzilla
  Support
  CastorWiki

XML
  Using XML
  Source Generator
  Schema Support
  XML Mapping
  XML FAQ
  Custom Handlers

JDO
  Using JDO
  JDO Config
  Types
  JDO Mapping
  JDO FAQ
  Other Features

Advanced JDO
  OQL
  Trans. & Locks
  Design
  KeyGen
  Long Trans.
  Nested Attrs.
  Pooling Examples
  LOBs

More
  Presentations
  The Examples
  3rd Party Tools
  JDO Tests
  XML Tests
  Configuration
  Tips & Tricks
  Full JavaDoc
  CastorWiki
  
  

About
  License
  Contributors
  Status, Todo
  Changelog
  Library
  Contact
  Project Name

  



Using Pooled Database Connections


News
Pooling Agents
Standard Database Connections
PoolMan Configuration
Pooling and JDBC DataSources
    PostgreSQL 7.3 and later
    Oracle
    mySQL
Configuring JDBC DataSources in Tomcat to be used with Castor
Jakarta Commons DBCP - BasicDataSource


News

-10/22/2004: Added JDBC Datasource configuration for mySQL.
-9/14/2004: Added section about using Jakarta's DBCP with Castor.

Pooling Agents

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 PoolMan, Proxool and Jakarta's DBCP project.

Although PoolMan is no longer being maintained, it is still one of the best open-source JDBC poolers that can turn almost any JDBC driver into a pooled data source. The last released version that is recommended is version 2.0.4, as the 2.1 version was still in development when the project stopped. 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 PoolMan. I'll include how to configure the upcoming 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.

Standard Database Connections

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)

PoolMan Configuration

Now, the standard PostgresqlDataSource is not a pooling data source, so it doesn't provide us much use here. Instead, we'll install poolman, and create a poolman.xml entry for our database connection to get the pooling behavior. In the poolman.xml, our connection information would look like this:

    <datasource>
      <dbname>appPool</dbname>
      <driver>org.postgresql.Driver</driver>
      <url>jdbc:postgresql://localhost/app</url>
      <username>john</username>
      <password>smith</password>
      <initialConnections>2</initialConnections>
      <minimumSize>2</minimumSize>
      <maximumSize>10</maximumSize>
      <cacheEnabled>false</cacheEnabled>
    </datasource>

In this sample, poolman will have a datasource called appPool, that will have a min of 2 connections for the database, and will max out at 10 connections. Now, can use poolman with Castor in two ways. The first is as a driver, and the second is as a data source. Here are the two possible entries for the jdo-conf.xml file... first, the driver version:

  <driver class-name="com.codestudio.sql.PoolMan"
          url="jdbc:poolman://appPool"/>

and now the data-source version:

  <data-source class-name="com.codestudio.sql.PoolManDataSource">
    <param name="database-name" value="appPool" />
  </data-source>

As you can see, we have placed all of the connection information in the poolman.xml configuration file, so Castor just needs to connect to poolman. Either configuration will work, as Castor does not care from where it gets the connection from.

Pooling and JDBC DataSources

PostgreSQL 7.3 and later

In the 7.3 release of PostgreSQL, they will start providing a pooling mechanism with their driver. The Castor CVS 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 PoolMan, 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>

Oracle

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>

mySQL

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>

Configuring JDBC DataSources in Tomcat to be used with Castor

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"/>

Jakarta Commons DBCP - BasicDataSource

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>

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

 
   
  
   
 


Copyright ? 1999-2005 ExoLab Group, Intalio Inc., and Contributors. All rights reserved.
 
Java, EJB, JDBC, JNDI, JTA, Sun, Sun Microsystems are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and in other countries. XML, XML Schema, XSLT and related standards are trademarks or registered trademarks of MIT, INRIA, Keio or others, and a product of the World Wide Web Consortium. All other product names mentioned herein are trademarks of their respective owners.