DBCP Connection Pool Spring

DBCP Connection Pool Spring

There are many API’s available which you can use for connection pooling. Many people prefer C3P0 for connection pooling but if you are using Spring with Hibernate C3PO does not works at all if you are using Spring for data source and hibernate get connection from Spring and make call to the database. In that case Apache DBCP is best option. Here I will show how to configure DBCP connection pooling with Spring application. To understand better I will demo Spring web application.

Tools needed:

• Eclipse
• MySQL
• JDK 1.8
• Maven 3.2

• Create sample table in MySQL database and insert some dummy data:

CREATE TABLE javahonk.person(
  firstName varchar(50) NOT NULL,
  lastName varchar(45) DEFAULT NULL,
  location varchar(45) DEFAULT NULL,
  PRIMARY KEY (firstName)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• Insert data some dummy data:

INSERT INTO `javahonk`.`person`
(`firstName`,
`lastName`,
`location`)
VALUES
('Java','Honk','NY'), ('Java2','Honk2','NY'),('Java3','Honk3','NY')

• Download and import maven project as you will see below:

DBCP Connection pooling configuration:

• dispatcher-servlet.xml:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="
        http://www.springframework.org/schema/beans     
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context 
        http://www.springframework.org/schema/context/spring-context.xsd
        http://www.springframework.org/schema/mvc
        http://www.springframework.org/schema/mvc/spring-mvc.xsd">

	<context:component-scan base-package="com.javahonk.controller" />
	<mvc:annotation-driven />
	<context:property-placeholder location="classpath:database/database.properties" />

	<bean
		class="org.springframework.web.servlet.view.InternalResourceViewResolver">
		<property name="prefix">
			<value>/WEB-INF/jsp/</value>
		</property>
		<property name="suffix">
			<value>.jsp</value>
		</property>
	</bean>

	<!-- bind messages.properties -->
	<bean class="org.springframework.context.support.ResourceBundleMessageSource"
		id="messageSource">
		<property name="basename" value="messages/messages" />
	</bean>

	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">	
		<property name="driverClassName" value="${jdbc.driver}"></property>
		<property name="url" value="${jdbc.url}"></property>
		<property name="username" value="${jdbc.username}"></property>
		<property name="password" value="${jdbc.password}"></property>		
		<property name="initialSize" value="10" />
		<property name="maxActive" value="20" />
		<property name="removeAbandoned" value="true" />		
	</bean>

	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>

	<bean id="personDao" class="com.javahonk.dao.PersonDAOImpl" />


</beans>

• database.properties: Don’t forget to change below properties with yours:

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/JavaHonk
jdbc.username=root
jdbc.password=admin

• SpringMVCController.java:

package com.javahonk.controller;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.javahonk.dao.IPersonDAO;

/**
 * @author Java Honk
 *
 */
@Controller
public class SpringMVCController {
	
	@Autowired 
	IPersonDAO personDao;
	
	@RequestMapping(value = "/databasetest")
	public @ResponseBody List<Map<String, Object>> databasetest() {
		
		return personDao.selectAllPerson();
	}

}

• IPersonDAO.java:

package com.javahonk.dao;

import java.util.List;
import java.util.Map;

/**
 * @author Java Honk
 *
 */
public interface IPersonDAO {
	
	List<Map<String, Object>> selectAllPerson();	

}

• PersonDAOImpl.java:

package com.javahonk.dao;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;

/**
 * @author Java Honk
 *
 */
public class PersonDAOImpl implements IPersonDAO {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public List<Map<String, Object>> selectAllPerson() {
		String sql = "SELECT * FROM person";
		List<Map<String, Object>> listOfPerson = jdbcTemplate.queryForList(sql);
		return listOfPerson;
	}


}

• Remaining files download project and include it. To run this project you could any servers. As I will be running this in tomcat with MYSQL you will see below:

• For more infomation please visit Apache DBCP documentation here

 Download Project: ConnectionPoolingDBCPSpring