database - What is the benefit of Connection and Statement Pooling?

Question

Can someone explain what is Connection and Statement Pooling and what is the benefit over unpooled DataSources? I am trying to understand when it is a good idea to use a technology like c3p0 or proxool in a project. I need first to understand what they do and when it interesting to use them. Thank you very much.

Answer 1

The Happy Connection

It's so easy to create a new connection every time. One line: that's all it takes. Nothing much to think about. Great life.

Hold on. Do you eat on a plate?

Do you throw away your plate after each use?

No, you wash it and put it on the dish rack, so you can use it again on your next meal. Buying new plates everytime is out of the question. If you did that, you will have wasted enough money to buy a new iPad in one year.

Think about connection pools again.

But this time, the connections are your plates, the connection pool is your dish rack. Your wallet and your energy represent the system resources (memory and bandwidth).

Wash or Spend?

What would you rather do:

a. wash the dishes

b. or run to the mall every meal and buy new plates?

While there are tasks involved in connection pooling, it's less taxing in the long run compared to creating new connections every time. The key is in knowing how many plates (connections) your family (application) will need in any given day.

Pools can be used for database connections, threads, entity beans and other factory-derived objects.