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).

 
 
 
 
 
 

Pom.xml

<dependency>
	<groupId>c3p0</groupId>
	<artifactId>c3p0</artifactId>
	<version>0.9.1.2</version>
</dependency>

 

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"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="http://www.springframework.org/schema/beans

http://www.springframework.org/schema/beans/spring-beans-3.0.xsd


http://www.springframework.org/schema/context


http://www.springframework.org/schema/context/spring-context-3.0.xsd


http://www.springframework.org/schema/tx

http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">

	<!-- Employee DB data source. -->
	<bean id="employeeDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
		destroy-method="close">
		<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}" />
	</bean>
	
	<context:component-scan base-package="com.javapitshop.dao">
	</context:component-scan>
</beans>

 

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.

jdbc.properties

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.employee_db_url=jdbc:mysql://localhost:3306/employee
jdbc.username=root
jdbc.password=root
jdbc.maxPoolSize=50
jdbc.minPoolSize=10
jdbc.maxStatements=100
jdbc.testConnection=true

 

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.Java

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

@Repository
public class EmployeeJdbcDAO extends BaseDao {

	/**
	 * Instantiates a new employee jdbc dao.
	 *
	 * @param userDataSource the employee data source
	 */
	@Autowired
	public ApplicationJdbcDAO(DataSource employeeDataSource) {
		super(ApplicationJdbcDAO.class);
		this.setDataSource(userDataSource);
	}
	
	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 };
		
		logger.debug(queryString.toString());
		
		// 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.
Related Whitepaper:

Functional Programming in Java: Harnessing the Power of Java 8 Lambda Expressions

Get ready to program in a whole new way!

Functional Programming in Java will help you quickly get on top of the new, essential Java 8 language features and the functional style that will change and improve your code. This short, targeted book will help you make the paradigm shift from the old imperative way to a less error-prone, more elegant, and concise coding style that’s also a breeze to parallelize. You’ll explore the syntax and semantics of lambda expressions, method and constructor references, and functional interfaces. You’ll design and write applications better using the new standards in Java 8 and the JDK.

Get it Now!  

Leave a Reply


three + = 5



Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy
All trademarks and registered trademarks appearing on Java Code Geeks are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries.
Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.

Sign up for our Newsletter

20,709 insiders are already enjoying weekly updates and complimentary whitepapers! Join them now to gain exclusive access to the latest news in the Java world, as well as insights about Android, Scala, Groovy and other related technologies.

As an extra bonus, by joining you will get our brand new e-books, published by Java Code Geeks and their JCG partners for your reading pleasure! Enter your info and stay on top of things,

  • Fresh trends
  • Cases and examples
  • Research and insights
  • Two complimentary e-books