To configure c3p0 as a JDBC connection pool, you typically:

1. create a DataSource (usually ComboPooledDataSource)
2. tune pool parameters (min/max pool size, idle time, timeouts, statement cache)
3. use the DataSource everywhere instead of DriverManager.getConnection(...)
4. close Connection/Statement/ResultSet normally (c3p0 returns connections to the pool on close())

Below are the common ways to do it.

---

1) Programmatic configuration (most common for plain JDBC)

package org.kodejava.jdbc;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public final class C3p0DataSourceFactory {
    private static final ComboPooledDataSource ds = new ComboPooledDataSource();

    static {
        try {
            ds.setDriverClass("org.postgresql.Driver");
        } catch (PropertyVetoException e) {
            throw new ExceptionInInitializerError(e);
        }

        ds.setJdbcUrl("jdbc:postgresql://localhost:5432/<db>");
        ds.setUser("<db_user>");
        ds.setPassword("<db_password>");

        // Pool sizing
        ds.setMinPoolSize(5);
        ds.setInitialPoolSize(5);
        ds.setMaxPoolSize(20);
        ds.setAcquireIncrement(2);

        // Timeouts / lifecycles
        ds.setCheckoutTimeout(10_000);          // ms to wait for a connection
        ds.setMaxIdleTime(300);                 // seconds before idle conns are culled
        ds.setMaxConnectionAge(0);              // 0 = no limit (consider setting in prod)
        ds.setIdleConnectionTestPeriod(60);     // seconds between idle tests

        // Validation (pick ONE strategy; this is the most reliable)
        ds.setPreferredTestQuery("SELECT 1");
        ds.setTestConnectionOnCheckout(false);
        ds.setTestConnectionOnCheckin(true);

        // Statement caching (optional)
        ds.setMaxStatements(200);
        ds.setMaxStatementsPerConnection(20);
    }

    private C3p0DataSourceFactory() {}

    public static DataSource dataSource() {
        return ds;
    }

    // Optional: call on app shutdown (desktop apps / simple main())
    public static void shutdown() {
        ds.close();
    }

    // Example usage
    public static void main(String[] args) throws Exception {
        try (Connection c = dataSource().getConnection();
             PreparedStatement ps = c.prepareStatement("SELECT now()");
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                System.out.println(rs.getObject(1));
            }
        } finally {
            shutdown();
        }
    }
}

Notes on the key settings

• minPoolSize / maxPoolSize: your main capacity knobs.
• checkoutTimeout: prevents threads from waiting forever when the pool is exhausted.
• Connection testing:
  • Prefer preferredTestQuery + testConnectionOnCheckin=true (or periodic tests) to avoid “broken connection” surprises.
  • testConnectionOnCheckout=true is safer but adds latency to every borrow.
• Statement cache helps if you repeatedly run the same SQL.

---

2) Configure via c3p0.properties (cleaner config)

Create src/main/resources/c3p0.properties:

# Basic
c3p0.jdbcUrl=jdbc:postgresql://localhost:5432/<db>
c3p0.user=<db_user>
c3p0.password=<db_password>
c3p0.driverClass=org.postgresql.Driver

# Pool sizing
c3p0.minPoolSize=5
c3p0.initialPoolSize=5
c3p0.maxPoolSize=20
c3p0.acquireIncrement=2

# Timeouts / health checks
c3p0.checkoutTimeout=10000
c3p0.maxIdleTime=300
c3p0.idleConnectionTestPeriod=60
c3p0.preferredTestQuery=SELECT 1
c3p0.testConnectionOnCheckin=true

# Statement cache (optional)
c3p0.maxStatements=200
c3p0.maxStatementsPerConnection=20

Then your Java becomes minimal:

package org.kodejava.jdbc;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;

public final class DataSources {
    private static final ComboPooledDataSource ds = new ComboPooledDataSource(); // reads c3p0.properties

    public static DataSource c3p0() {
        return ds;
    }

    public static void shutdown() {
        ds.close();
    }
}

c3p0 will auto-load configuration from:

• c3p0.properties on the classpath, or
• c3p0-config.xml (more advanced, supports named configs)

---

3) Important usage rule: always close connections

With pooling, connection.close() does not close the physical DB connection; it returns it to the pool. So keep using try-with-resources:

try (Connection con = dataSource.getConnection();
     PreparedStatement ps = con.prepareStatement("SELECT * FROM t WHERE id = ?");
) {
  ps.setLong(1, 123L);
  // ...
}

---

4) Common pitfalls (quick checklist)

• Don’t create a new ComboPooledDataSource per query. Create one and reuse it.
• Set checkoutTimeout so you fail fast under load.
• Use a validation query if your DB/network closes idle connections.
• Tune pool sizes to match DB limits (max connections on the server) and your concurrency.

---

Maven dependencies

<dependencies>
  <dependency>
    <groupId>com.mchange</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.11.1</version>
  </dependency>

  <dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.7</version>
  </dependency>
</dependencies>