To configure c3p0 as a JDBC connection pool, you typically:
- create a
DataSource(usuallyComboPooledDataSource) - tune pool parameters (min/max pool size, idle time, timeouts, statement cache)
- use the
DataSourceeverywhere instead ofDriverManager.getConnection(...) - close
Connection/Statement/ResultSetnormally (c3p0 returns connections to the pool onclose())
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=trueis safer but adds latency to every borrow.
- Prefer
- 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.propertieson the classpath, orc3p0-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
ComboPooledDataSourceper query. Create one and reuse it. - Set
checkoutTimeoutso 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>
