Creating a database connection pool

Question

Need information on creating a connection pool to the database (irrespective of the database) , and how efficient they are? What are the conditions where they can enhance performance.

How to create it explicitly?

Answer 1

Your question is a bit ambiguous:

Do you want to homegrow a connection pool implementation? If so, this is a nice starting point: http://java.sun.com/developer/onlineTraining/Programming/JDCBook/conpool.html But this is highly discouraged for production environments. Better use an existing and thoroughly tested connection pooling API, like DBCP or C3P0.

Or do you want to know how to use a connection pool? If so, the answer depends on the connection pooling API you're using. It's fortunately usually available at the website of the API in question.

Or do you want to know when/why to use a connection pool? If so, it will surely enhance connecting performance if you have a long-living application (e.g. a webapplication) and you need to connect the database more than often. The normal JDBC practice is namely: acquire and close the Connection, Statement and ResultSet in the shortest possible scope (i.e. inside the very same method block). Because connecting is fairly expensive and can take up to 200ms of time or even more, using a connection pool is much faster. It gives connections on demand and takes care about actually closing the connection. That does however not mean that you may change the way you write JDBC, you still need to acquire and close them in the shorest possible scope. The only thing you need to change is the way you acquire the connection. E.g. change from

connection = driverManager.getConnection();

to

connection = connectionPool.getConnection();

No more changes are needed as long as your JDBC code is well-written.

Answer 2

The intro page to Apache DBCP sums it up nicely:

Creating a new connection for each user can be time consuming (often requiring multiple seconds of clock time), in order to perform a database transaction that might take milliseconds. Opening a connection per user can be unfeasible in a publicly-hosted Internet application where the number of simultaneous users can be very large. Accordingly, developers often wish to share a "pool" of open connections between all of the application's current users. The number of users actually performing a request at any given time is usually a very small percentage of the total number of active users, and during request processing is the only time that a database connection is required. The application itself logs into the DBMS, and handles any user account issues internally.

How efficient are they ? Depends on the implementation. Typically I would expect a pool to instantiate connections either at start-up or on request. The first connection will require a real connection to the database, and thereafter when you request a connection, you're given an existing pooled connection. So the first connection request will take the most time, and afterwards you're just pulling objects from a collection (very fast).