|  |  |  | License Codehaus OpenEJB OpenJMS OpenORB Tyrex

Old releases
General
Release 1.3
Release 1.3rc1
Release 1.2

Main
Home
About
Features
Download
Dependencies
Reference guide
Publications
JavaDoc
Maven 2 support
Maven 2 archetypes
DTD & Schemas
Recent HTML changes
News Archive
RSS news feed
Project Wiki

Development/Support
Mailing Lists
SVN/JIRA
Contributing
Support
Continuous builds
Prof. services

Related projects
Spring ORM support
Spring XML factories
WS frameworks

XML
XML

XML Code Generator
XML Code Generator

JDO
Introduction
First steps
Using JDO
JDO Config
Types
JDO Mapping
JDO FAQ
JDO Examples
JDO HOW-TOs
Tips & Tricks
Other Features
JDO sample JAR

Tools
Schema generator

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

DDL Generator
Using DDL Generator
Properties
Ant task
Type Mapping

More
The Examples
3rd Party Tools
JDO Tests
XML Tests
Configuration

About
License
User stories
Contributors
Marketplace
Status, Todo
Changelog
Library
Contact
Project Name

## Using Pooled Database Connections

News
Pooling Agents
Standard Database Connections
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
Prepared statement pooling

## 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 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.

## 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)

## 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 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>

### 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>

### Prepared statement pooling

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.

<notice> Copyright © 1999-2005 ExoLab Group, Intalio Inc., and Contributors. All rights reserved. </notice>
<notice> 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. </notice>