C3P0 Connection Pooling Java Example

In this post we’ll see how to configure connection pooling in your application using C3P0.

Maven dependency for MChange C3P0

If you are using Maven then you can add the following dependency in pom.xml

<dependency>
  <groupId>com.mchange</groupId>
  <artifactId>c3p0</artifactId>
  <version>0.9.5.5</version>
</dependency>

You can also download the jars directly from the following location and put them in application’s classpath.

https://sourceforge.net/projects/c3p0/

Jars you need to have in classpath are lib/c3p0-0.9.5.5.jar and lib/mchange-commons-java-0.2.19.jar

C3P0 Connection pooling Java example

In the example DB used is MySQL it connects to knpcode schema and table is EMPLOYEE with columns as id, FIRST_NAME, LAST_NAME and DEPARTMENT.

Best way to create a c3p0 pooling DataSource is to instantiate an instance of ComboPooledDataSource class and provide the properties for connecting to DB and for connection pool.

DB related configurations that you need to set are Driver class, URL, user name and password.

connection pool related configuration out of the following-

• acquireIncrement- Determines how many Connections a c3p0 pool will attempt to acquire when the pool has run out of Connections. Default is 3.
• initialPoolSize- Number of Connections a pool will try to acquire upon startup. Default is 3.
• maxPoolSize- Maximum number of Connections a pool will maintain at any given time. Default is 15.
• maxIdleTime- Seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire. Default is 0.
• minPoolSize- Minimum number of Connections a pool will maintain at any given time. Default is 3.

DB credentials and connection pool related configuration properties are kept in a properties file (db.properties).

DB.DRIVER_CLASS=com.mysql.cj.jdbc.Driver
DB.DB_URL=jdbc:mysql://localhost:3306/knpcode
DB.DB_USER=root
DB.DB_PASSWORD=admin
DB.INITIAL_POOL_SIZE=5
DB.MAX_POOL_SIZE=5

Following class is used to create a ComboPooledDataSource.

import java.beans.PropertyVetoException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DSCreator {
  private static ComboPooledDataSource pooledDS;
  static {
    try {
    	pooledDS = new ComboPooledDataSource();
      Properties properties = new Properties();
      // Loading properties file from classpath
      InputStream inputStream = DSCreator.class
                                         .getClassLoader()
                                         .getResourceAsStream("db.properties");
      if(inputStream == null){
        throw new IOException("File not found");
      }
      properties.load(inputStream);	
      pooledDS.setDriverClass(properties.getProperty("DB.DRIVER_CLASS"));
      pooledDS.setJdbcUrl(properties.getProperty("DB.DB_URL"));
      pooledDS.setUser(properties.getProperty("DB.DB_USER"));
      pooledDS.setPassword(properties.getProperty("DB.DB_PASSWORD"));

      pooledDS.setInitialPoolSize(Integer.parseInt(properties.getProperty("DB.INITIAL_POOL_SIZE")));
      // Default anyway
      pooledDS.setAcquireIncrement(3);
      pooledDS.setMaxPoolSize(Integer.parseInt(properties.getProperty("DB.MAX_POOL_SIZE")));
    }catch(IOException | PropertyVetoException e) {
      e.printStackTrace();
    }
  }
  public static DataSource getDataSource() {
    return pooledDS;
  } 
}

Test class that creates a connection using the returned DataSource object and queries the DB.

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;

public class DSDemo {
  public static void main(String[] args) {
    DSDemo dsDemo = new DSDemo();
    dsDemo.displayEmployeeById(16);
  }
  private void displayEmployeeById(int id){
    Connection connection = null; 
    String selectSQL = "SELECT * FROM EMPLOYEE WHERE id = ?";
    PreparedStatement prepStmt = null;
    try {
      DataSource ds = DSCreator.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"));
        System.out.println("First Name: " + rs.getString("FIRST_NAME"));
        System.out.println("Last Name: " + rs.getString("LAST_NAME"));
        System.out.println("Department: " + rs.getString("DEPARTMENT"));
      }
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally{
      if(connection != null){
        try {
          connection.close();
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      }
    }
  }
}

That's all for the topic C3P0 Connection Pooling Java Example. If something is missing or you have something to share about the topic please write a comment.

---

You may also like

• Apache DBCP Connection Pooling Java Example
• HikariCP Connection Pooling Java Example
• JDBC Transaction Management and Savepoint Example
• map() Function in Java With Examples
• Supplier Functional Interface Java Examples
• Java Volatile Keyword With Examples
• Uber Task in YARN
• Spring depends-on Attribute and @DependsOn Annotation