Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
The moose likes Servlets and the fly likes URGENT: Help Correctly Using ConnectionPoolDataSource in Servlet! Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Java » Servlets
Bookmark "URGENT: Help Correctly Using ConnectionPoolDataSource in Servlet!" Watch "URGENT: Help Correctly Using ConnectionPoolDataSource in Servlet!" New topic

URGENT: Help Correctly Using ConnectionPoolDataSource in Servlet!

Robert Paris
Ranch Hand

Joined: Jul 28, 2002
Posts: 585
I have a JDBC driver with a class that implements ConnectionPoolDataSource. I have two questions:
1. Do I cast the object to a "javax.sql.DataSource" and call "getConnection()" or do I cast it to "ConnectionPoolDataSource" and call "getPooledConnection()"? (If it's the latter, that's a pretty dumb way to implement this in the jdbc api)
2. Do I have to save the connections/connection pool in the servletcontext?
When I create a connection (both getPooled.. and the regular way) and don't save it in ServletContext, BUT I've saved the DataSource itself in ServletContext, there's no performance gain.
Please help! There's almost nothing out there on ConnectionPoolDataSource!!
Robert Paris
Ranch Hand

Joined: Jul 28, 2002
Posts: 585
OK, little update here. I was able to figure out the following:
1. PooledConnection is a physical connection
2. Connection should be retrieved NOT from the datasource, but from a PooledConnection Object.
3. If you close a Connection, it returns to the pool.
4. If you close a PooledConnection, you're closing the physical Connection.
Now I found some code to show how you might use this (but READ my comments at the end! they're important here)

In a loop, you might do it like this:

Now, when I did this in a loop of 50 times (and ran the test a number of times) the pooled connection version took about 901ms to run. The non-pooled version took 7190ms. Big difference. BUT - here's the note - my test was only inside one servlet. I did not do a test of 10 people all hitting that servlet at once to see if they were all using the same pool of connections. I'm guessing not, but I don't know. I get the PooledConnection from a datasource that's saved in ServletContext, so everytime someone hits a servlet, they're all getting a PooledConnection from that same ServletContext. However, I don't know if I need to additionally save the PooledConnection object into ServletContext or not. Anyone know?
Robert Paris
Ranch Hand

Joined: Jul 28, 2002
Posts: 585
Please! Anyone who can help!!
I'm using the PooledConnection (*See end of this post for info) with a MySQL JDBC Driver and the MS SQL Server JDBC Driver. Both fully implement this interface. However, both also have a major lack of documentation (MS's driver doesn't even have JavaDoc so I actually had to GUESS at their interfaces!! - there's non-ConnectionPoolDataSource methods I needed to call to set it up)
I'm unable to find what exactly is supposed to happen when I call getPooledConnection and getConnection. What I mean is: when I call PooledConnection from different Threads, do they still pull from the same pool, or does each different Threaded call to getPooledConnection() return a different pool? OR, does EVERY call (even in the same Thread) to getPooledConnection() return a different physical connection pool? In other words, in that last scenario, I should save the PooledConnection in ServletContext and just call "getConnection()" on THAT same instance from many different threads. Is it thread safe?
If a pool can only be applied to one Thread, then I get no benefit out of this as every new request will generate a new pool, thus going back to the whole: one new request, one new connection paradigm.
Please, anyone help!
"A factory for PooledConnection objects...When a DataSource
class is implemented to work with a ConnectionPoolDataSource
implementation, all of the connections produced by instances
of that DataSource class will automatically be pooled
"An object that provides hooks for connection pool management.
A PooledConnection object represents a physical connection to
a data source. The connection can be recycled rather than being
closed when an application is finished with it, thus reducing
the number of connections that need to be made.
"An application programmer does not use the PooledConnection
interface directly; rather, it is used by a middle tier
infrastructure that manages the pooling of connections."
(The quotes are from Sun)
Bhupinder Dhillon
Ranch Hand

Joined: Oct 12, 2000
Posts: 124
Robert, the ConnectionPoolDataSource is intended to be used by a "pool manager". Vendors of application servers and/or database drivers may provide a built in "pool manager" but they are not required to do so.
Now, unless your db driver or app server has a built in pool manager that you can use - that's great. Otherwise, you'll likely have to create your own pool manager that uses ConnectionPoolDataSources. I've yet to see a free source pool manager with datasoruce implementation. For further information read up the JDBC Optional API specifications
Robert Paris
Ranch Hand

Joined: Jul 28, 2002
Posts: 585
THANKS!! That document helps IMMENSELY! I completely understand now. I have to manage the connection pooling myself because the middle-tier I have (tomcat 3.2.3) does not, and I can't use an app server (shucks). I will create my own DataSource implementation that holds the ConnectionPoolDataSource and manages the PooledConnections by listening to its events. Thanks again!
BTW, you wouldn't happen to have any sample code of such a thing would you? All the connection pools I've seen are for the old JDBC connections, not for working with javax.sql. In particular, my handling for Threads is what I'm not 100% certain on.
Mike Curwen
Ranch Hand

Joined: Feb 20, 2001
Posts: 3695

If you can update your Tomcat to 4.1.x series, it comes with db connection pool support "built-in". That means... it comes with all the required 'commons' jars to provide a pooled datasource provider. "Commons" is the jakarta project wherein these packages are found.
More from the source:

Here is an article I found that describes the commons classes and how it works with Tomcat:

You might even be able to get 3.x series Tomcat to work with some of the commons packages. After all, it's only jars.

There is another package I know of called tyrex. It has the added plus of being a TX-aware (XA) DataSource provider.

Both are open-source, so if you still want to write your own, you can look at their code for tips.
Robert Paris
Ranch Hand

Joined: Jul 28, 2002
Posts: 585
OK, I FINALLY made my own Connection Pool Manager!! It implements DataSource and ConnectionEventListener. It's not too bad, but obviously I need to spend time fixing it for optimum performance adn double checking all my Thread handling. It's too late to post any code right now (4am), but maybe later.
I did some testing with it and what I found was pretty interesting.
(My Tests were all done with creating a connection, checking its status, and closing it inside a loop of 200 times)
1. When I had one request going to the servlet (that has the Pool Manager):

Regular getPooledConnection().getConnection()'s Avg Total Duration (for loop of 200): 3360ms
My Pool Manager's getConnection()'s Avg Total Duration (for loop of 200): 3221ms
In other words, almost the same.

2. When I had 2 simultaneous requests going to the server for the loop of 200.

Regular getPooledConnection().getConnection()'s Avg Total Duration (for loop of 200): 5673ms
My Pool Manager's getConnection()'s Avg Total Duration (for loop of 200): 3318ms
My connection pool held up very well.

3. When I had 3 simultaneous connections to the server for loop of 200.

Regular getPooledConnection().getConnection()'s Avg Total Duration (for loop of 200): 8509ms
My Pool Manager's getConnection()'s Avg Total Duration (for loop of 200): 3278ms
Now remember the "regular" way is NOT the same as opening a new connection every time through the loop. I used PooledConnection's getConnection() so it reuses the same connection all through the loop. Calling close simply raised an event (that no one caught). So it's already magnitude's faster than opening a new connection every time through the loop. HOWEVER, the servlet opens a new connection for each request. So when I had three simultaneous requests, it HAD to open AT LEAST three connections.
Whereas mine did not, since it could pool whatever it already had for every call to getConnection();
Anyways, while my pool manager might have bugs and whatnot, at least I know it's pretty fast and degrades pretty well (in my tests so far).
Anyone have any other suggestions or things I may have forgotten?
[ January 30, 2003: Message edited by: Robert Paris ]
wood burning stoves
subject: URGENT: Help Correctly Using ConnectionPoolDataSource in Servlet!