Connection pooling is the handling and maintenance of a group of connections for the reuse of java applications in an application server. The implementation of connection pooling is a best practice to improve performance in Java Database Connectivity (JDBC). A database connection requires resources to create the connection, maintain and then release it when it is not needed.
In web based applications, the database overhead is particularly high because the users connect and disconnect more frequently. A ‘javax.sql.ConnectionPoolDataSource’ interface act as a resource manager for ‘java.sql.Connection’ objects. Generally, gui interactions are shorter because of the surfing nature of users. Also, the connection requests can arise from anywhere, thus the usage volumes are difficult to predict.
Connection pool allows several users to utilise connection from pool and thereby avoids the overhead of creating a new connection. If a connection is not available then server creates a new connection. Here the newly created connection objects can be added to a collection such as Vector, HashTable etc. When the request is received and after it the processing finishes, the resource is returned to the connection pool after returning the response to the user and thus avoids the overhead of disconnection. Connection pool can improve the response time of the applications that require connections, especially for web based applications.
Another way to improve the web-based application’s response time is the caching of prepared statements. If available, a previously prepared statement is returned when a new prepared statement is requested from a connection. This reduces the creation of number of costly prepared statements, thereby improves the response time. It is very useful for an application that tends to use the same statement again and again.
When using connection pool we can use JDBC 2.0 interfaces, PooledConnection and ConnectionPoolDataSource or we can create our own connection pool if we are not using any JDBC 2.0 compatible driver or application server. The number of connections to be given in the connection pool depends on our application’s requirement. That means how many clients can access our database concurrently and also it depends on our database’s capacity to maintain number of database connections. We use ‘com.sun.jndi.ldap.connect.timeout’ property to mention how long to wait for a pooled connection and if we ignore this property, the application will wait indefinitely.
The connection pools should be notified about the shutdown so that the connections can be closed and release other resources properly.