Understanding Connection Pooling

Abhimanyu PrasadNovember 21st, 2016Last Updated: November 20th, 2016

0 247 3 minutes read

1. Introduction

Connection Pooling is a technique to boost application’s performance where N connections to the database are opened and managed in a pool. The application just asks for a connection, uses it and then drops it back to the pool. When the application demands for a connection, the ready connections are kept available to used in the pool. The pool manages the connection lifecycle, such that the developer actually doesn’t need to wait for the connection to get established and filter out the stale ones.

Connection Pooling mechanism saves from the expensive operation to establish network connection at application-runtime and eventually initialize the database session at the backend.

2. Connection Pool implementation in Tomcat

Tomcat, the leading application server, is packaged with Commons DBCP Datasource as the default JNDI Datasource, unless we explicitly specify the DataSourceFactory as –

To use Apache Commons DBCP connection pool –

<Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
	maxActive="100" maxIdle="30" maxWait="10000"
	username="javauser" password="javadude" driverClassName="com.mysql.jdbc.Driver"
	url="jdbc:mysql://localhost:3306/javatest" 
        factory="org.apache.commons.dbcp.BasicDataSourceFactory"/>

To use Tomcat JDBC Connection pool –

<Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
	maxActive="100" maxIdle="30" maxWait="10000"
	username="javauser" password="javadude" driverClassName="com.mysql.jdbc.Driver"
	url="jdbc:mysql://localhost:3306/javatest" 
        factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"/>

factory is required, and type should always be javax.sql.DataSource

The beauty of DBCP is that it can be used with so many applications or frameworks and it works with almost all databases in the market.

3. Comparing Pooling Mechanisms

When using Commons DBCP connection pool mechanism, Tomcat fetches the Commons DBCP sources (the version depends on the Tomcat version, for instance Tomcat 7.0.27 uses Commons DBCP 1.4), and does package name replacement (org.apache.commons -> org.apache.tomcat.dbcp) and builds the result as tomcat-dbcp.jar. This is done so that the internal Tomcat JDBC pools never conflict with possible application uses of the Commons DBCP classes. This avoids many potential classloading issues. The “dbcp” packages are about datasource management.

Similarly, Tomcat JDBC connection pool mechanism (org.apache.tomcat.jdbc.pool.*) is an alternative upgraded implementation of a database connection pool that competes with Apache Commons DBCP. It is a separate project from Tomcat (Under Tomcat Modules -> jdbc-pool), but it debuted with Tomcat 7 (starting with 7.0.19 in July 2011). There are pros and cons of both implementations, however Apache Commons DBCP is still being more popularly used.

In either case, the respective JARs (commons-dbcp.jar or tomcat-jdbc.jar) need to be included into the build path.

Older versions of Apache Commons DBCP (i.e. version 1.2) had some nasty thread-safety issues under high load conditions, making it unsuitable for that kind of usage, which was why the Tomcat JDBC Connection pool was re-written.

The usage of Tomcat JDBC Connection Pool is also very simple and for people who are already familiar with DBCP, the transition is quite very simple.

4. Connection Pooling in a Hibernate Based Application

Hibernate uses its magic to identify which connection pool provider to use — based on the properties you configure.

For c3p0 –

<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.max_size">20</property>
<property name="hibernate.c3p0.timeout">300</property>
<property name="hibernate.c3p0.max_statements">50</property>
<property name="hibernate.c3p0.idle_test_period">3000</property>

For Apache Commons DBCP –

<property name="hibernate.dbcp.initialSize">8</property>
<property name="hibernate.dbcp.maxActive">20</property>
<property name="hibernate.dbcp.maxIdle">20</property>
<property name="hibernate.dbcp.minIdle">0</property>

The corresponding JAR file needs to be in place in the lib directory, manually or using Maven.

We can also explictly specify the connection provider with the hibernate.connection.provider_class property, though its not actually needed.

<property name="hibernate.connection.provider_class">org.hibernate.c3p0.internal.C3P0ConnectionProvider</property>
<property name="hibernate.c3p0.min_size">1</property>
<property name="hibernate.c3p0.max_size">19</property>
<property name="hibernate.c3p0.timeout">120</property>
<property name="hibernate.c3p0.max_statements">10</property>
<property name="hibernate.c3p0.idle_test_period">3000</property>

If we do not configure a connection pool with Hibernate, the default is used. It is visible in the log or console output when we start the application –

org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure

The default connection pool implementation in Hibernate is NOT suitable for production use, primarily because of it’s configuration constraints.

If you are using an application server, you may wish to use the built-in pool (typically a connection is obtained using JNDI).

To use the server’s built-in pool with Hibernate using JNDI configuration, we need to set the following property into the Hibernate configuration file –

hibernate.connection.datasource=java:/comp/env/jdbc/TestDB

– assuming TestDB is the JNDI name of Tomcat JDBC Connection Pool DataSource (refer snippet in Section 2 above).

If you can’t or don’t wish to use your application server’s built-in connection pool, Hibernate supports several other connection pools such as –

c3p0
Proxool

After Apache DBCP, the second most preferred connection pool implementation is c3p0, which easily integrates with Hibernate, and is said to deliver good performance.