5.5.4 Database Connection Pools
The classical way of accessing a database is a three step procedure:
(1) The database driver establishes a connection
(2) An SQL statement is executed, and a result set is retrieved
(3) The database driver closes the connection.
With this procedure, a new database connection object is created with each new client request.
This is very time consuming, since the database engine must allocate communication and
memory resources, authenticate the user, and set up a security context (this can easily take one
or two seconds). Therefore, the performance of database access can be significantly improved
by avoiding the opening of new connections with each client request. This is done by
implementing database connection pools.
The concept of database connection pooling can be described as follows:
An application gets a reference to the connection pool, or an object managing many
pools (depending on the complexity of an application, it may be necessary to implement
a pool manager that maintains multiple instances of connection pools).
The application obtains a connection object from the connection pool. The connection
pool maintains a collection of many database connections, which are kept in an open
state to save the time that is required to establish a connection.
The connection is used, i.e. a database statement is executed with the connection
The connection is returned to the pool, and is therefore free for the next use.
Connection pools benefit most server applications. Performance can be improved significantly if
the following conditions are satisfied (according to Professional Java Server Programming):
Users access the database through a limited set of generic database user accounts, as
opposed to a specific account per user.
A database connection is only used for the duration of a single request, as opposed to
the combined duration of multiple requests from the same client.
There are plenty of connection pool implementations described in computer literature. The book
Professional Java Server Programming presents three different implementations of varying
complexity to cover basic as well as advanced needs for connection pooling. Enterprise
JavaBeans also offer database connection pooling.