What is database pooling?

Question

Hi , I just wanted to know the concept of database pooling.How it is achieved.

Answer 1

Database connection pooling is a method used to keep database connection open so they can be reused by others.

Typically, opening a database connection is an expensive operation, so pooling keeps the connections active so that, when a connection is later requested, one of the active ones is used in preference to opening another one.

In it's simplest form, it's just a similar API call to the real open-connection API which first checks the pool for a suitable connection. If one is available, that's given to the client, otherwise a new one is created.

Similarly, there's a close API call which doesn't actually call the real close-connection, rather it puts the connection into the pool for later use.

That's a pretty simplistic explanation. Real implementations may be able to handle connections to multiple servers, may pre-allocate some baseline of connections so some are ready immediately, may actually close old connections when the usage pattern quietens down, and so on.

So, something like the following:

  +---------+
  |         |
  | Clients |
+---------+ |
|         |-+        +------+          +----------+
| Clients | ===#===> | Open | =======> | RealOpen |
|         |    |     +------+          +----------+
+---------+    |         ^
               |         |
               |     /------\
               |     | Pool |
               |     \------/
               |         ^
               |         |
               |     +-------+         +-----------+
               #===> | Close | ======> | RealClose |
                     +-------+         +-----------+

Answer 2

Images speak a thousand words (paxdiablo gave an awesome description):

Source

Answer 3

You can use the apache commons library for connection pooling implementation transparently : http://commons.apache.org/dbcp/

DBCP is also a supported Hibernate pool : http://www.informit.com/articles/article.aspx?p=353736&seqNum=4