Database connections are critical, finite, and expensive resources, especially in multi-user web applications. Managing these connections effectively impacts an application's scalability, robustness, and performance. A connection pool addresses this by managing the allocation and release of connections, allowing reuse instead of repeated creation. It can also reclaim idle connections that exceed a maximum idle time to prevent resource leaks.

Upon initializasion, a pool creates a minimum number of connections, maintaining at least that count. A maximum connection limit caps the pool's size; requests exceeding this limit enter a waiting queue. Configuring these limits involves trade-offs: a high minimum wastes resources under low load, while a low maximum can cause queueing delays. A large gap between min and max means initial requests benefit, but surplus connections are managed within the pool.

Commonly used Java connection pools include DBCP, C3P0, and Proxool. Spring applications can obtain connections through a DataSource, configured via JNDI (for application server resources) or directly within the Spring container.

DBCP Data Source Configuration

DBCP relies on the Apache Commons Pool library. Include commons-dbcp.jar and commons-pool.jar.

<bean id="dbPool" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="org.postgresql.Driver" />
    <property name="url" value="jdbc:postgresql://localhost:5432/appdb" />
    <property name="username" value="admin" />
    <property name="password" value="securePwd" />
</bean>

Key configuration properties:

• defaultAutoCommit: Sets auto-commit for connections (default true).
• defaultReadOnly: Sets connections to read-only mode (default false).
• maxActive: Maximum active connections (0 for unlimited).
• maxIdle: Maximum idle connections.
• maxWait: Maximum milliseconds to wait for a connection before throwing an error.
• validationQuery: SQL query to validate a connection (e.g., SELECT 1).
• removeAbandoned: Enables removal of abandoned connections.
• removeAbandonedTimeout: Seconds before an abandoned connection is removed.
• logAbandoned: Logs stack traces for abandoned connections.

C3P0 Data Source Configuration

C3P0 implements JDBC 3 and JDBC 2 extension specifications for connection and statement pooling.

<bean id="appDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="org.postgresql.Driver"/>
    <property name="jdbcUrl" value="jdbc:postgresql://localhost:5432/appdb"/>
    <property name="user" value="admin"/>
    <property name="password" value="securePwd"/>
</bean>

Notable C3P0 properties:

• acquireIncrement: Number of new connections created when the pool is exhausted.
• initialPoolSize: Number of connections created at pool startup.
• minPoolSize: Minimum number of connections maintained.
• maxPoolSize: Maximum number of connections allowed.
• maxIdleTime: Seconds a connection can remain idle before being discarded.
• idleConnectionTestPeriod: Interval in seconds to test idle connections.
• preferredTestQuery: SQL statement used to test connection validity.
• checkoutTimeout: Milliseconds a client waits for a connection before a timeout exception.

Proxool Data Source Configuraton

Proxool is known for robustness and provides monitoring capabiliteis to detect connection leaks.

<bean id="pooledSource" class="org.logicalcobwebs.proxool.ProxoolDataSource">
    <property name="driver" value="org.postgresql.Driver" />
    <property name="driverUrl" value="jdbc:postgresql://localhost:5432/appdb?user=admin&password=securePwd" />
    <property name="user" value="admin" />
    <property name="password" value="securePwd" />
    <property name="alias" value="AppDB_Pool" />
    <property name="maximumConnectionCount" value="50" />
    <property name="minimumConnectionCount" value="5" />
    <property name="houseKeepingTestSql" value="SELECT 1" />
</bean>

Important Proxool settings:

• maximumConnectionCount: Upper limit for connections in the pool.
• minimumConnectionCount: Lower limit for connections.
• houseKeepingSleepTime: Milliseconds the housekeeping thread sleeps between checks.
• houseKeepingTestSql: SQL statement used to test idle connections.
• simultaneousBuildThrottle: Limits the number of connections built concurrently.
• testBeforeUse / testAfterUse: Enables connection testing before or after use.

Accessing JNDI Data Sources

In environments with application servers (e.g., WebLogic, WebSphere), you can reference the server's managed data source via JNDI.

Using JndiObjectFactoryBean:

<bean id="serverDataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="jndiName" value="java:comp/env/jdbc/AppDataSource"/>
</bean>

Using the jee namespace for a cleaner configuration:

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:jee="http://www.springframework.org/schema/jee"
       xsi:schemaLocation="...">
    <jee:jndi-lookup id="serverDataSource" jndi-name="java:comp/env/jdbc/AppDataSource"/>
</beans>

Spring's Simple DataSource Implementation

For testing or simple applications, Spring provides DriverManagerDataSource. It does not pool connections, creating a new one for each getConnection() call.

import org.springframework.jdbc.datasource.DriverManagerDataSource;
import java.sql.Connection;

DriverManagerDataSource simpleDs = new DriverManagerDataSource();
simpleDs.setDriverClassName("org.postgresql.Driver");
simpleDs.setUrl("jdbc:postgresql://localhost:5432/testdb");
simpleDs.setUsername("tester");
simpleDs.setPassword("test123");
Connection conn = simpleDs.getConnection();