Friday, June 8, 2012

Spring : Working with DataSource and Database Connection Pooling

Getting Database connection through conventional JDBC is very tedious task which include a lot
Exception handling and repeative coding,Spring simplifies the whole process with very ease.
Spring provides the org.springframework.jdbc-3.1.0.RELEASE.jar whic contains the org.springframework.jdbc.datasource.DriverManagerDataSource to get the connection
object to the Database.

For the production environment we can use org.apache.commons.dbcp to implement the connection
pooling,here I summarize how we implement the connection pooling in spring framework.
Step 1: The very first step is to create the Java project and add the Spring Library as fallows

Step 2:Setup the database,in this example i am using MySQL as a database,ensure MySQL database driver jar
should be in the class path.
Step 3:Crate the spring.xml file in source folder as fallows

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

 <context:component-scan base-package="com.example"/>
 <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
  <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
  <property name="url" value="jdbc:mysql://localhost:3306/HibernateDb"/>
  <property name="username" value="root"/>
  <property name="password" value="root"/>
  <property name="initialSize" value="2"/>
  <property name="maxActive" value="5"/>


Step 4: Create your Model Class as fallows
package com.example.model;

public class Employee {
 private int empid;
 private String empName;
 public Employee(int empid,String empName){
  this.empid = empid;
  this.empName = empName;
 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;


Step 5: create DataAccessor Object to get the data from database

package com.example.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.example.model.Employee;
public class EmpDaoImpl {
 private DataSource dataSource;
 public Employee getEmployee(int empid) throws Exception{
  Connection conn = null;
  conn = dataSource.getConnection();
  PreparedStatement ps = conn.prepareStatement("select * from employee where empid = ?");
  ps.setInt(1, empid);
  Employee emp = null;
  ResultSet rs = ps.executeQuery();
   emp = new Employee(empid, rs.getString("employee_name"));
  return emp;

 public DataSource getDataSource() {
  return dataSource;

 public void setDataSource(DataSource dataSource) {
  this.dataSource = dataSource;


Step 6: Now test your prog by using your Main Class as shown

package com.example;

import org.springframework.context.ApplicationContext;

import com.example.dao.EmpDaoImpl;
import com.example.model.Employee;

public class Main {
 public static void main(String[] args) {
  ApplicationContext ctx = new ClassPathXmlApplicationContext("spring.xml");
  EmpDaoImpl empdl = ctx.getBean("empDaoImpl",EmpDaoImpl.class);
  Employee emp = null;
  try {
   emp = empdl.getEmployee(1);
  } catch (Exception e) {
   // TODO Auto-generated catch block


Post a Comment