This is a question for Jack Shirazi. I want to know , is there any other competitive way to utilise open database connection(s), if connection pooling is not used? And also I want to know what are the drawbacks of using Connection Pooling?
i'm not jack, obviously, but here's a sort-of drawback i ran into last week. i am actually just using jakarta's commons-pool to pool postgresql connections. commons-pool allows you to add a validate() method and tell the pool to validate the object via the method at any of three points - when the object is pulled from the pool, when it is returned to the pool, and/or at random "idle" times. the nice thing is that if the validate() method returns false, commons-pool destroys the object and creates a new one. so i wrote a validate method that checks if the jdbc connection is still valid. i tested it by running some sql in a loop and restarting postgresql in the middle of the run. here's what i found. after restarting postgresql, Connection.isClosed() returns false (bad). Connection.createStatement() does not fail, nor does Connection.prepareStatement("SOME SQL") - remember, i want it to fail so i can throw away the old bad Connection and get a new one. the only thing that actually finally fails is running some actual sql, e.g. "select current_timestamp". so now my implementation of the connection pool has to run an extra sql statement *each time* i get a connection. it's either that or force a restart of the application if postgresql gets restarted (and that's not acceptable).
Hi John, I know that Connection pool is a pool to holding pre-created connection. Do you mind to tell me how to add a connection to the pool? I still not really understand the flow and what should we code to create the pool? Thank you. steffy
well, i am using the generic jakarta commons-pool for my connection pool. for that you need only write an object factory that implements an interface the pool expects and provide it to the pool. see http://jakarta.apache.org/commons/pool/ for some good documentation. others have noted that jakarta also has a database connection pool project, so i am starting myself to look at that. see http://jakarta.apache.org/commons/dbcp/
how to add a connection to the pool? ... and what should we code to create the pool?
There are many, third-party connection pools available -- one of which is the one that John has said he is using, from the Apache group's Jakarta project: http://jakarta.apache.org And then again, you can always write your own (as we have done at my place of work). Naturally, each of the implementations will have similarities and differences between them. We wrote our own mainly due to historical circumstances -- not necessarily because it was more appropriate for our application (but that's another story ;-) You don't mention whether you are using a third-party connection pool or one that you have developed yourself. I assume it is one you have developed yourself, due to the questions you are asking -- since a third-party implementation should have documentation that answers your questions. In my opinion -- assuming you are writing your own connection pool -- it is probably better to use a third-party implementation, since it is probably going to be better than something you would do (unless you are in the business of making such utilities, of-course ;-), and there are enough of them, that there is a good chance that you will find one that is appropriate for your needs. By the way, if you are interested in a connection pool as part of some application you are developing, you are aware that most application servers today do include their own connection pool implementations. So if you are already using an application server, I suggest that you investigate the possibility of utilizing its connection pool (if you haven't done so already). I hope this has helped to answer your questions. Good Luck, Avi.
As you look at (or write) a pool manager, consider its policies. Do you load a fixed number of members and stay with that number all day? Can it dynamically add members (perhaps through a reference to a factory) and grow? Is there a "high water" limit? What happens when you hit it? Maybe the client requester waits, maybe it fails. Does it shrink again as demand goes down? Is there a "low water" limit?
A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi