What is Connection pool?

[Ken Shamrock]

Hi,
What is the use of connection pool?
If I need to build a internal web system for office use, around 15-20 staff, platform is WinXP, tomcat with MySQL, do I need to use connection pool? Thanks

[Chris Stewart]

A connection pool is a large collection of JDBC connections waiting for use. You probably don't need to have one, but it is good practice.

[Arun Boraiah]

Connection pooling is a process where specified connections to the database i opened and same is reused to make database calls.
Well if number of database call to your appliation is high. Even if number of user is less. Better use the conneciton pooling approch.
-arun

[Ken Shamrock]

I'm not an expert of Servlet, I always do like this when database connection needed.
Servlet A calls another Servlet which is a database manager in charge of querying, and pass back the result to Servlet A.
If I need to add a connection pool, do I need to modify too many things for Servlet A and the database manager Servlet?
Also, I'm still not too clear about how the connection pool works, is there any example of "db connection with connection pool" out there? Thanks very much!

[Varun Khanna]

Checkout this site:
http://archive.coreservlets.com/Chapter18.html
(the last four shows "a way" of doing)
Hope it helps.
-Varun.

[Ken Shamrock]

Thanks varun Khanna,
for practial use, what connection pool you guys usually use? those provided by coreservlets or any other recommendations? Thanks
PS: im using tomcat.

[Varun Khanna]

Ken,
y don't u try out for solutions ? will help you in knowing pros and cons ? As there is never any best solutions in s/w

still y don u start with creating a seperate java class managing all ur pools and stuff ... you may also need to find the size of ur connection pool.
But,still I would strongly recommend you to "start", may be then ppl out here can suggest you better.

[Ken Shamrock]

>Connection pooling is a process where specified
>connections to the database i opened and same is
>reused to make database calls.
I've read several times, seems that I can get only one idea about connection pool, it is used to limited the connection to the db at the same time, but about "reused", I 've no idea, you mean reuse the content of connection object so we don't need to query the same sql query again or..?

[Pat Wallwork]

Ken,
Connection pooling is a container of existing, live database connections. As obtaining a connection to a database is a somewhat 'expensive' operation, sites that make fairly heavy use of a database benefit from keeping a pool of connections open and ready for use.
When using a connection pool you just grab an existing connection and do your query or update. This saves you the overhead of first creating a connection and then doing the query or update. Once your done, the connection you used is returned to the pool to be used again.
There are quite a few different connection pool classes out there to choose from, or you can write your own. In your case though, I doubt you'll realize any real benefit from implementing one and might as well continue the way you already are.
-Pat

[Ken Shamrock]

Thanks Pat.
I've read some samples from http://archive.coreservlets.com/Chapter18.html
I see one of their servlet contains the statement
"private ConnectionPool connectionPool;"
then it will get the connection from the pool:
"Connection connection = connectionPool.getConnection();"
But in this way, Will every Servlet create its own pool? Since every servlet create a connectionpool object by "private ConnectionPool connectionPool;"
Or this is just a sample, in real case, i had to move the pool creation statement "private ConnectionPool connectionPool;" to a seperated class (maybe database manager)?
I had to understand this as i've already messed up. Thank you.

[Anthony Nolan]

Hi,
for a pretty good implementation of a database connection pool try turbine from jakarta. We use this in a high volume production environment and have found significant performance improvements. Its not difficult to use, some libraries in the tomcat common folder, a properties file called TurbineResources.properties (which contains the username and passwords etc.) and then use some standard methods to get a connection, return it to the pool etc.
There are other pools out there, and using JNDI to look up connections is probably a good direction to be heading. I don't think turbine allows this. Another product which may be worth a look is tyrex. It does support jndi.
Anthony Nolan
Big Picture Solutions