Spring JDBC Database connection pool setup

Setting up JDBC Database Connection Pool in Spring framework is easy for any Java application, just matter of changing few configuration in spring configuration file.If you are writing core java application and not running on any web or application server like Tomcat or Weblogic, Managing Database connection pool using Apache Commons DBCP and Commons Pool along-with Spring framework is nice choice but if you have luxury of having web server and managed J2EE Container, consider using Connection pool managed by J2EE server those are better option in terms of maintenance, flexibility and also help to prevent java.lang.OutofMemroyError:PermGen Space in tomcat by avoiding loading of JDBC driver in web-app class-loader, Also keeping JDBC connection pool information in Server makes it easy to change or include settings for JDBC over SSL. In this article we will see how to setup Database connection pool in spring framework using Apache commons DBCP and commons pool.jar

Spring framework provides convenient JdbcTemplate class for performing all Database related operation. if you are not using Hibernate than using Spring’s JdbcTemplate is good option. JdbcTemplate requires a DataSource which is javax.sql.DataSource implementation and you can get this directly using spring bean configuration or by using JNDI if you are using J2EE web server or application server for managing Connection Pool. See How to setup JDBC connection Pool in tomcat and Spring for JNDI based connection pooling for more details. In order to setup Data source you will require following configuration in your applicationContext.xml (spring configuration) file:

//Datasource connection settings in Spring
<bean id="springDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" >
  <property name="url" value="jdbc:oracle:thin:@localhost:1521:SPRING_TEST" />
  <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
  <property name="username" value="root" />
  <property name="password" value="root" />
  <property name="removeAbandoned" value="true" />
  <property name="initialSize" value="20" />
  <property name="maxActive" value="30" />
</bean>

//Dao class configuration in spring
<bean id="EmployeeDatabaseBean" class="com.test.EmployeeDAOImpl">
   <property name="dataSource" ref="springDataSource"/>
</bean>       
            

Java Code for using Connection pool in Spring

//Code for DAO Class using Spring JdbcTemplate
package com.test
import javax.sql.DataSource;
import org.log4j.Logger;
import org.log4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;

/**
 * Java Program example to use DBCP connection pool with Spring framework
 * @author Javin Paul
 */
public class EmployeeDAOImpl implements EmployeeDAO {

    private Logger logger = LoggerFactory.getLogger(EmployeeDAOImpl.class);
    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Override
    public boolean isEmployeeExists(String emp_id) {
        try {
            logger.debug("Checking Employee in EMP table using Spring Jdbc Template");
            int number = this.jdbcTemplate.queryForInt("select count(*) from EMP where emp_id=?", emp_id);
            if (number > 0) {
                return true;
            }
        } catch (Exception exception) {
            exception.printStackTrace();
        }
        return false;
    }
}