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 connecsions for each request. Creating database connections is resource-intensive and often takes longer than executing SQL queries.
Configuration Methods
C3P0 suports 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;
}
}
}