Wednesday, December 16, 2020

Connection Pooling Using C3P0 Spring Example

This post shows how to provide JDBC connection pooling using C3P0 data source in Spring framework. DB used in this example is MySQL.

Maven dependency for C3P0

Alternatively you can download the following jars and put them in the application’s classpath.

C3P0 Connection pooling Spring example

For configuring datasource you need to set up some properties. It is better to use a properties file for storing those properties and refer that properties file while configuring datasource.


Description for the properties used here is as-

driver class name is the JDBC driver for the DB used. Since MYSQL is used here so the jdbc driver for the same (com.mysql.jdbc.Driver) is provided.

Url – You need to provide url to access your DB server. I have created a schema called netjs and DB is running on the same system so url is- jdbc:mysql://localhost:3306/netjs.

Username and password for the DB.

IntialSize is the initial size of the connection pool. It is given as 5 so initially 5 connections will be created and stored in the pool.

In this example Spring JDBCTemplate is used to query the DB. If you are using Spring XML configuration then configuration for DataSource and JDBCTemplate is as follows.

Spring XML Configuration (appContext.xml)

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns=""
  <context:component-scan base-package="org.netjs.daoimpl" />
  <!--  For reading properties files --> 
  <context:property-placeholder location="classpath:config/" />
  <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">  
    <property name="dataSource" ref="dataSource"></property>  
  <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass" value = "${db.driverClassName}" />
    <property name="jdbcUrl" value = "${db.url}" />
    <property name="user" value = "${db.username}" />
    <property name="password" value = "${db.password}" />
    <property name="initialPoolSize" value = "${pool.initialSize}" />

In this XML configuration, <context:property-placeholder> tag is used to give the path to file.

For C3P0, datasource implementing class is “com.mchange.v2.c3p0.ComboPooledDataSource”.

DataSource bean has to be provided as a reference in JDBCTemplate.


Database table used in this example.

CREATE TABLE `employee` (
  `name` varchar(35) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)

Java classes

EmployeeDAO interface

public interface EmployeeDAO {
  public List<Employee> findAllEmployees();

public class EmployeeDAOImpl implements EmployeeDAO {
  private JdbcTemplate jdbcTemplate; 
  public List<Employee> findAllEmployees() {
    final String SELECT_ALL_QUERY = "SELECT * from EMPLOYEE";
    return this.jdbcTemplate.query(SELECT_ALL_QUERY, new EmployeeMapper());

  private static final class EmployeeMapper implements RowMapper<Employee> {
    public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
      Employee emp = new Employee();
      return emp;

public class Employee {
 private int empId;
 private String empName;
 private int age;
 public int getEmpId() {
  return empId;
 public void setEmpId(int empId) {
  this.empId = empId;
 public String getEmpName() {
  return empName;
 public void setEmpName(String empName) {
  this.empName = empName;
 public int getAge() {
  return age;
 public void setAge(int age) {
  this.age = age;

You can run this example using the following code.

public class App {
  public static void main(String[] args) {
    ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext

    EmployeeDAO dao = (EmployeeDAO)context.getBean("employeeDAOImpl");  
    List<Employee> empList = dao.findAllEmployees();
    System.out.println("Name - "+ empList.get(0).getEmpName() + 
      " Age - " + empList.get(0).getAge());

C3P0 datasource with Spring Java config

If you want to use Spring Java Configuration then you can create an object of ComboPooledDataSource and set the properties.

 public ComboPooledDataSource comboPooledDataSource(){
  ComboPooledDataSource ds = new ComboPooledDataSource();
  return ds;

That's all for this topic Connection Pooling Using C3P0 Spring Example. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Spring Tutorial Page

Related Topics

  1. Connection Pooling With Apache DBCP Spring Example
  2. Spring Batch Processing With List of Objects in batchUpdate() Method
  3. Spring NamedParameterJdbcTemplate Select Query Example
  4. Configuring DataSource in Spring Framework
  5. Spring Transaction Management Example - @Transactional Annotation and JDBC

You may also like-

  1. Spring MVC Example With @PathVaribale - Creating Dynamic URL
  2. Wiring Collections in Spring
  3. Spring depends-on Attribute
  4. ApplicationContextAware And BeanNameAware Interfaces in Spring Framework
  5. Difference Between ArrayList And LinkedList in Java
  6. Java ReentrantReadWriteLock With Examples
  7. Is String Thread Safe in Java
  8. Compress And Decompress File Using GZIP Format in Java