What exactly are the *ConnectionPoolDataSource classes for?

[Alan Burlison <Al...@sun.com>]

Hi,

I'm embedding Derby inside Tomcat, but running it in Server mode so I 
can connect to it externally whilst Tomcat is running - I have all that 
working OK.

I'm now trying to figure out the best way to get access to the database 
from JSPs.  I can either push a DataSource into the ServletContext as an 
attribute and reference that in the JSP (which works) or set up a JNDI 
resource, which I think is a better solution.

What's confusing me is the different DataSource classes that Derby has. 
  The example code I've seen via google uses javax.sql.DataSource in the 
JDNI resource definition, but Derby offers 
ClientConnectionPoolDataSource, ClientDataSource, 
EmbeddedConnectionPoolDataSource and EmbeddedDataSource.  I've read the 
Derby docs that discuss these classes, but they sort of assume you 
already know how it all works!

Do the ConnectionPoolDataSource variants actually provide connection 
pooling, and if so how do I configure a JDNI reference to use them?  How 
do they interact with the Commons DBCP connection pooling that's usually 
used with Tomcat - or do they just replace it?  If the Derby classes 
*do* provide connection pooling, how is it configured?

Basically, help!

-- 
Alan Burlison
--

Re: What exactly are the *ConnectionPoolDataSource classes for?

[Alan Burlison <Al...@sun.com>]

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

Re: What exactly are the *ConnectionPoolDataSource classes for?

[Knut Anders Hatlen <Kn...@Sun.COM>]

Alan Burlison <Al...@Sun.COM> writes:

> Hi,
>
> I'm embedding Derby inside Tomcat, but running it in Server mode so I
> can connect to it externally whilst Tomcat is running - I have all
> that working OK.
>
> I'm now trying to figure out the best way to get access to the
> database from JSPs.  I can either push a DataSource into the
> ServletContext as an attribute and reference that in the JSP (which
> works) or set up a JNDI resource, which I think is a better solution.
>
> What's confusing me is the different DataSource classes that Derby
> has. The example code I've seen via google uses javax.sql.DataSource
> in the JDNI resource definition, but Derby offers
> ClientConnectionPoolDataSource, ClientDataSource,
> EmbeddedConnectionPoolDataSource and EmbeddedDataSource.  I've read
> the Derby docs that discuss these classes, but they sort of assume you
> already know how it all works!
>
> Do the ConnectionPoolDataSource variants actually provide connection
> pooling, and if so how do I configure a JDNI reference to use them?
> How do they interact with the Commons DBCP connection pooling that's
> usually used with Tomcat - or do they just replace it?  If the Derby
> classes *do* provide connection pooling, how is it configured?

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,
-- 
Knut Anders