Understanding C3P0 Connection Pool

C3P0 is an open-source JDBC connection pooling library widely adopted by frameworks like Hibernate and Spring. Connection pooling maintains a collection of database connections that can be reused, reducing the overhead of establishing new connections for each request. Creating database connections is resource-intensive and often takes longer than executing SQL queries.

Configuration Methods

C3P0 supports two primary configuration approaches: programmatic configuration through setter methods and external configuration via XML or properties files.

Programmatic Configuration

import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;

public class DatabaseConnector {
    private static ComboPooledDataSource poolSource = new ComboPooledDataSource();
    
    public static void setupDataSource() {
        try {
            poolSource.setDriverClass("com.mysql.cj.jdbc.Driver");
            poolSource.setJdbcUrl("jdbc:mysql://localhost:3306/SchoolDB");
            poolSource.setUser("admin");
            poolSource.setPassword("securepass");
            poolSource.setInitialPoolSize(5);
            poolSource.setMaxPoolSize(15);
            poolSource.setMinPoolSize(3);
            poolSource.setAcquireIncrement(2);
        } catch (PropertyVetoException ex) {
            ex.printStackTrace();
        }
    }
    
    public static Connection obtainConnection() {
        setupDataSource();
        try {
            return poolSource.getConnection();
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
    }
}

XML Configuration

Place c3p0-config.xml in the classpath (typically src directory):

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <default-config>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/SchoolDB</property>
        <property name="user">admin</property>
        <property name="password">securepass</property>
        <property name="initialPoolSize">5</property>
        <property name="maxPoolSize">15</property>
        <property name="minPoolSize">3</property>
        <property name="acquireIncrement">2</property>
    </default-config>
    
    <named-config name="customConfig">
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/TestDB</property>
        <property name="user">tester</property>
        <property name="password">testpass</property>
        <property name="initialPoolSize">10</property>
        <property name="maxPoolSize">20</property>
        <property name="minPoolSize">5</property>
        <property name="acquireIncrement">3</property>
    </named-config>
</c3p0-config>

Java code using named configuration:

public class DatabaseManager {
    private static ComboPooledDataSource poolSource = 
        new ComboPooledDataSource("customConfig");
    
    public static Connection getConnection() {
        try {
            return poolSource.getConnection();
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
    }
}

Essential Configuration Parameters

Basic Connection Parameters

Pool Sizing Parameters

Connection Testinng Parameters

Timeout and Recovery Parameters