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

 <context:annotation-config/>
 <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"/>
 </bean>
 

</beans>

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;
@Component
public class EmpDaoImpl {
 
 @Autowired
 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();
  
  if(rs.next()){
   emp = new Employee(empid, rs.getString("employee_name"));
  }
  rs.close();
  ps.close();
  
  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 org.springframework.context.support.ClassPathXmlApplicationContext;

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
   e.printStackTrace();
  }
  
  System.out.println(emp.getEmpName());
 }

}

Output