Database connection pooling datastructure

Question

I would like to develop database connection pooling.

Could anyone please tell me about which data structure need to use to maintain the pool ?

Answer 1

Instead of developing your own why not use a library like Commons DBCP that is widely used and well tested.

Answer 2

Is an open source solution what you're looking for?

See Apache Database Connection Pool API

Answer 3

See this Sun Developer Tutorial: Connection Pooling:

In releases prior to JDBC 2.0 every database session requires a new connection and login even if the previous connection and login used the same table and user account. If you are using a JDBC release prior to 2.0 and want to improve performance, you can cache JDBC connections instead.

Answer 4

Not sure it's wise to create your own.

I've used c3p0 connection pooling (with Hibernate) across many projects with great success

Answer 5

See http://www.javaworld.com/jw-06-1998/jw-06-object-pool.html. Good luck!

Answer 6

It should be implemented using Object Pool desing pattern. You can read about it in Kircher, Michael; Prashant Jain; (2002-07-04). "Pooling Pattern". EuroPLoP 2002. Retrieved on 2007-06-09. or in Object Pool Design Pattern. Java implementation for ObjectPool and JDBCConnectionPool classes can be found here.

Object Pool is usually a singleton with two collections of objects (e.g. database connections) inside:

1. unlocked - for free objects, which can be provided to the client by request
2. locked - for locked objects, which are in use now

This collections can be implemented as Lists or HashTables or something else, depends on your needs. For simple ObjectPool - LinkedList structure will be good enough.

Answer 7

You generally need:

• some kind of wrapper around the "raw" Connection objects to manage things like when the connection was last given out, diagnostic information, possibly a cache of your prepared statements for that connection etc-- you define this to include what you need
• a collection to put the connection wrappers in that supports concurrent additions/removals-- any properly synchronized list would do, but a ConcurrentLinkedQueue would be a reasonable choice
• a way to manage allocations from the pool-- consider using the Semaphore class
• possibly, grouping various pools togther into some "pool management class" (e.g. so you can just call "getConnection(databaseName, readOnly)" and it goes to the relevant pool)

On top of this, you can then build whatever logging/monitoring you require.

There are also advocates for off-the-shelf connection pool frameworks. I know there are those who disagree, but I personally wouldn't go down this route-- a connection pool (a) really isn't difficult to write, (b) forms a key part of your system that you probably need to understand and customise.