Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: local policy)
Message-ID: <46F5133B.1050606@sun.com>
Date: Sat, 22 Sep 2007 14:06:03 +0100
From: Alan Burlison <Alan.Burlison@sun.com>
User-Agent: Thunderbird 2.0.0.4 (X11/20070814)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: What exactly are the *ConnectionPoolDataSource classes for?
References: <46F05666.2020405@sun.com> <wjo84pho5wsu.fsf@sun.com>
In-Reply-To: <wjo84pho5wsu.fsf@sun.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Knut Anders Hatlen wrote:

> Derby doesn't provide connection pooling, but ConnectionPoolDataSource
> can be used as a basic building block for a connection pool. A
> ConnectionPoolDataSource creates PooledConnections, whereas a DataSource
> creates Connections. A PooledConnection is just a wrapper around a
> physical connection so that you can open/close logical Connection
> objects without actually closing the underlying connection. Although
> ConnectionPoolDataSource makes it easier to implement a connection pool,
> it is possible to build a connection pool around DataSource too. I don't
> know how the connection pool in Tomcat works, but if the example code
> normally uses DataSource, I assume that it has implemented the wrapping
> of the physical connections itself and you'll be fine with DataSource.
> 
> HTH,

It did, thanks.

In the end I didn't use the connection pool built into Tomcat.  As far 
as I can tell it requires that you set up a JNDI data source to 
reference the pool.  The problem with that is you have to put a copy of 
the underlying JDBC driver into the global Tomcat /lib directory, as the 
pool is set up before Tomcat starts up any webapps.  This makes it 
impossible to provide an entirely stand-alone WAR file that uses the 
Tomcat connection pool.

Instead I used MiniConnectionPoolManager 
(http://www.source-code.biz/snippets/java/8.htm) and passed it an 
instance of the Derby ConnectionPoolDataSource class.  I then layered a 
custom implementation of DataSource on top of the pool manager, so I 
could then set the custom DataSource as the default JSP data source, and 
also push a reference to it into the webapp application scope. Here's 
what I ended up with:

Custom DataSource->MiniConnectionPoolManager->ConnectionPoolDataSource

The only wrinkle was that MiniConnectionPoolManager doesn't cache 
connections by user ID, so I had to make the
    getConnection(String username, String password)
method in the custom DataSource throw an exception if it was called. 
This was no big deal as my application only uses a single username to 
access the database.

-- 
Alan Burlison
--