How to open a connection pool to a database?

Hi everybody, I am chaitanya.
I used to open a new connection to database in each and every jsp at first, now I am using listeners, I will open it only one when the application starts, the connection is available at the application level, I thought that every thing was going nice. Recently I found it is a bad idea.
The solution is I have to use connection pool. What is a connection pool? can anybody tell me? How to open a connection pool.

Thank you all in advance.

When you ask questions on a forum, instead of just asking "What is X?" where X is a concept which is well-known to others but not yet to you, you could just use a search engine and look for X. In this case the first link returned for my search is the Wikipedia article about connection pools:

http://en.wikipedia.org/wiki/Connection_pool

So read that for a start.

Now, how to use it in your JSP? First of all you should not use a database connection in your JSP, no matter where it comes from. Use it in a servlet which connects to the database and collects up the data which the JSP will later display. Aside from that, the usual thing to do is to use the features built into your Java EE container where the JSP runs. One of those features is to configure a pool of connections for use in your application. You should find out how to do that in its documentation, or in its administration application if it has one.

Thank you Paul, I mistakenly mentioned jsp in my question, I do follow MVC architecture. I was supposed to say servlet, but I forgot. Anyhow thank you Paul.

Connection Pools can be configured in your Application server. Mostly all application servers provide a console GUI to configure the data-source and the connection pooling.

once you have a data-source with connection pooling you need to use a JNDI Look up instead of normal JDBC connection.

for more hint's specify which app server you are using.

Hi Vishnu, I am using Apache Tomcat6.0 webserver, can I do this using a web server? Please can you help me how I can do this in detail?

Thank you in advance.

I've found that the Wikipedia is a surprisingly good place to get a basic overview of a technology. The articles are just detailed enough to give an idea of what's available, without being to detailed that you get lost in the forest.

You don't "open" a connection pool. You open connections. A connection pool is a repository for open connections, and the benefit of having one is that by recycling open connections, you cut down on overhead and delays, since creating a JDBC connection from scratch is relatively expensive. Instead of "opening" a connection pool, you instruct the application server to construct one (or more) and make that pool available to the webapp(s), which is normally done by having the app look up the pool by its JNDI name. You don't open connections, you request connections from the pool, and the pool opens connections if needed - or returns an already-opened but unused connection. It is, of course, critical that you return connections to the pool (close them) when you're done using them.

Apache is not an application server, just a web server, so it has no built-in database capabilities and therefore no database connection pooling. However, Apache does support a number of plug-in application frameworks that can work with pools, such as PHP. In those cases, the framework provides its own pooling mechanisms.

https://sec1.woopra.com/docs/jndi-datasource-examples-howto.html

Duplicate post - hence removing.

Thank you Vishnu, thank you very much. I have done it.