Home » Java » Enterprise Java » Adding C3PO Connection Pooling in Spring JDBC

About Ch Shan Arshad

Ch Shan Arshad

Adding C3PO Connection Pooling in Spring JDBC

Connection pooling is an operation in which a system pre-initializes the connections to be used in the future. This is done since creating connections at the time of use is an expensive operation. In this post we will learn how we can create C3P0 connection pooling in Spring JDBC (somebody is not using hibernate).





Spring Context File (applicaitonContext-persistance.xml)

Now we need to prepare a JDBC context file for spring. We need to define a Data source for the DB with all its credentials.

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"






	<!-- Employee DB data source. -->
	<bean id="employeeDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
		<property name="driverClass" value="${jdbc.driverClassName}" />
		<property name="jdbcUrl" value="${jdbc.employee_db_url}" />
		<property name="user" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />
		<property name="maxPoolSize" value="${jdbc.maxPoolSize}" />
		<property name="minPoolSize" value="${jdbc.minPoolSize}" />
		<property name="maxStatements" value="${jdbc.maxStatements}" />
		<property name="testConnectionOnCheckout" value="${jdbc.testConnection}" />
	<context:component-scan base-package="com.javapitshop.dao">


In the example above we have created a C3P0 data source for the Employee DB with all its credentials and appropriate parameters. All credentials and settings aren’t mentioned in the context file. I have been using a dedicated properties file for that. Now this bean can be auto-wired in any DAO class as a DataSource object.




BaseDao Class

The Base DAO class is to define any abstract method or any common functionality which we need to use in all child classes. We can make it abstract or whatever we like according to our needs. Also note that I have overloaded its constructor to implement Logging. Now every child class needs to provide its class definition.

package com.icsmobile.faadplatform.dao;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;

 * Its the parent Dao class of the all the daos defined in the System.
 * @author JavaPitShop
public class BaseDao extends SimpleJdbcDaoSupport {

	//common logger for all the classes in the dao layer
	protected Logger logger;
	 * @param daoClass
	public BaseDao(Class<?> daoClass) {
		logger = Logger.getLogger(daoClass);



EmployeeJdbcDao is extending BaseDao and in its constructor it is autowiring the “employeeDataSource” which we have defined in the context bean.

public class EmployeeJdbcDAO extends BaseDao {

	 * Instantiates a new employee jdbc dao.
	 * @param userDataSource the employee data source
	public ApplicationJdbcDAO(DataSource employeeDataSource) {
	public EmployeeBO getEmployeeById(final int employeeId) {
		logger.debug("getEmployeeById(" + employeeId + ")");
		EmployeeBO employeeBO = null;
		StringBuilder queryString = new StringBuilder();

		queryString.append(" SELECT ")
				   .append( "*" )	
				   .append(" FROM employee ")
				   .append(" WHERE employee_id = ? ");

		Object[] parameterList = { employeeId };
		// execute query
		SqlRowSet dataRow = getJdbcTemplate().queryForRowSet(queryString.toString(), parameterList);
		if (dataRow.next()) {
			// create application object
			employeeBO = getEmployeeBusinessObjectFromRowSet(dataRow);

		return employeeBO;
Reference: Adding C3PO Connection Pooling in Spring JDBC from our JCG partner Ch Shan Arshad at the Java My G.Friend blog.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!


1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design


and many more ....


Receive Java & Developer job alerts in your Area from our partners over at ZipRecruiter



  1. Will a new connection object be required every time a sql query is executed?

  2. Hi,

    How can we do the same data source bean creation in java code @configuration.
    Instead of have xml based configuration. I have feature credential which needs to be fetched from environment variable.

    so if you have the same example with annotation version. please with share me.
    I’m using Spring JPA Repository so i don’t want to disturb other peace of code.
    If i able to create Data source that will be fine.


Leave a Reply

Your email address will not be published. Required fields are marked *


Want to take your Java skills to the next level?

Grab our programming books for FREE!

Here are some of the eBooks you will get:

  • Spring Interview QnA
  • Multithreading & Concurrency QnA
  • JPA Minibook
  • JVM Troubleshooting Guide
  • Advanced Java
  • Java Interview QnA
  • Java Design Patterns