Efficient database access is critical for high‑performance applications, and connection pooling using C3P0 in Java is one of the most reliable ways to achieve it. In this guide, we’ll configure a C3P0 datasource to connect a Java application with MySQL, ensuring faster connections and better resource management.
Jars needed for C3P0
If you are using Maven then you can add the following dependency to your pom.xml (Version should match your Java and database setup).
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.12.0</version>
<scope>compile</scope>
</dependency>
Connection pooling using C3P0 - Java Example
Properties file that is used to read DB configuration. Create a db.properties file under resources/ to store your database connection details.
resources/db.properties
DRIVER_CLASS=com.mysql.cj.jdbc.Driver DB_CONNECTION_URL=jdbc:mysql://localhost:3306/netjs DB_USER=root DB_PWD=admin
In the Java example code for connection pooling using C3P0 there are two Java classes. We have a PooledDataSource class with a static block to create an instance of C3P0's ComboPooledDataSource.
There is another class DSConnection where we get the instance of ComboPooledDataSource and use it to get the Connection object.
PooledDataSource.java
import java.beans.PropertyVetoException;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class PooledDataSource {
private static ComboPooledDataSource cpds;
static {
try {
cpds = new ComboPooledDataSource();
Properties properties = new Properties();
// Loading properties file
InputStream inputStream = new FileInputStream("resources/db.properties");
properties.load(inputStream);
cpds.setDriverClass(properties.getProperty("DRIVER_CLASS")); //loads the jdbc driver
cpds.setJdbcUrl(properties.getProperty("DB_CONNECTION_URL"));
cpds.setUser(properties.getProperty("DB_USER"));
cpds.setPassword(properties.getProperty("DB_PWD"));
// the settings below are optional
// c3p0 can work with defaults
cpds.setInitialPoolSize(5);
cpds.setMinPoolSize(5);
cpds.setAcquireIncrement(5);
cpds.setMaxPoolSize(20);
}catch(IOException | PropertyVetoException e) {
e.printStackTrace();
}
}
public static javax.sql.DataSource getDataSource() {
return cpds;
}
}
In this class, apart from setting the DB properties, we have set some of the parameters for the connection pool like setMinPoolSize() that sets the initial size of the connection pool. These many connection will immediately be created and put to connection pool, setMaxPoolSize() to set the maximum limit on the connection pool.
DSConnection.java
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
public class DSConnection {
public static void main(String[] args) throws PropertyVetoException {
DSConnection dsCon = new DSConnection();
try {
dsCon.displayEmployee(37);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private void displayEmployee(int id) throws SQLException{
Connection connection = null;
String selectSQL = "Select * from employee where id = ?";
PreparedStatement prepStmt = null;
try {
DataSource ds = PooledDataSource.getDataSource();
connection = ds.getConnection();
prepStmt = connection.prepareStatement(selectSQL);
prepStmt.setInt(1, id);
ResultSet rs = prepStmt.executeQuery();
while(rs.next()){
System.out.println("id: " + rs.getInt("id") + " Name: "
+ rs.getString("name") + " Age: " + rs.getInt("age"));
}
}finally{
if(prepStmt != null){
prepStmt.close();
}
if(connection != null){
connection.close();
}
}
}
}
That's all for this topic Connection Pooling Using C3P0 in Java. If you have any doubt or any suggestions to make please drop a comment. Thanks!
>>>Return to Java Programs Page
Related Topics
You may also like-