程序员面试之设计题

 question:Implement a connection pool

answer:

Create a ConnectionPool class. A Properties class can be used as the initial parameter for the constructor of the ConnectionPool class. The properties may include database connection information, pool size, adjusting pool size, pooled connection timeout and lent out connection timeout.

ConnectionPool should be able to maintain a list of Connection object including creating connections, renew connections, adjusting pool size and taking back overtimed connections which have been lent out.

The ConnectionPool class should provide a getConnection() method to lend out a connection to client program and a returnConnection(Connection conn) method for the client program to return the connection. When getConnection() is invoked, an unused database connection object will be marked as used and returned to client program. When there are no available database connection objects in the pool, one strategy is to increase the pool size and return new created connection object. We can also let the client thread wait() on the ConnectionPool object until there is connection object available again. We can also add a tryGetConnection(long millionseconds) method to try to get a connection in a certain time if needed.

For concurrent programming, synchronized can be added to getConnection() method and returnConnection() method. Or we can use a Semaphore inside ConnectionPool to indicate avaliable connection objects.

Create a ConnectionWrapper class which implements java.sql.Connection class.
When the client program invokes getConnection() method, the database connection object will be encapsulated in the ConnectionWrapper class. ConnectionWrapper object also keeps a reference of the ConnectionPool object.

The client program will still get a reference of the java.sql.Connection from getConnection() method. But all methods invoked on the ConnectionWrapper object will be transfered onto the actual database connection object except for the close() method. The close() method of the ConnectionWrapper object will return the database connection to the connection pool without closing it.