How to manage a custom db connection pool

Question

I came across this interview question : How will you manage a db connection pool? My thought was: I will create an ArrayBlockingQueue<Connection>, create come connection objects and put them in the queue when ajvm starts up. Then wrap this in some form of an enum singleton so there is only one such queue and it stays alive for the life of the JVM.

Then use some kind of utility/driver class that will take connections from the queue and return them back to the queue.

I am thinking what else i need to say to this? Do i need to make the queue thread safe so that multiple requests dont have the same connection?

Answer 1

In my opninion you're missing several points here:

1. Connections should be returned to the initial state when returning back to the pool. For instance, connection.setAutocommit(...); should definitely be reverted

2. I't a good idea to wrap native connection into your own implementation of javax.sql.Connection interface to control and monitor actions performed on the connection. With this pattern you can also implement a valuable feature: return the connection back to pool on close(); call

3. You need some menans to control the number of the connections in a pool based on the actual pool utilization. Take a look at how "capacity" and "load factor" are implemented in Java collection to get a rough implementation idea

4. Connections should be monitored if they're alive. It's not that easy to archive for all possible databases.