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

This article is in continuation of my tutorials on spring framework and database like LDAP Authentication in J2EE with Spring Security and manage session using Spring security If you haven’t read those article than you may find them useful.

Spring Example JDBC Database Connection Pool

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>       
            
Below configuration of DBCP connection pool will create 20 database connection as initialSize is 20 and goes up to 30 Database connection if required as maxActive is 30. you can customize your database connection pool by using different properties provided by Apache DBCP library. Above example is creating connection pool with Oracle 11g database and we are using oracle.jdbc.driver.OracleDriver comes along with ojdbc6.jar or ojdbc6_g.jar , to learn more about how to connect Oracle database from Java program see the link.

Java Code for using Connection pool in Spring

Below is complete code example of DAO class which uses Spring JdbcTemplate to execute SELECT query against database using database connection from Connection pool. If you are not initializing Database connection pool on start-up than it may take a while when you execute your first query because it needs to create certain number of SQL connection and then it execute query but once connection pool is created subsequent queries will execute faster.
//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;
    }
}


Dependency:
1. you need to include oracle driver jar like ojdbc_6.jar in you classpath. 
2. Apache DBCP and commons pool jar in application classpath.
That’s all on how to configure JDBC Database connection pool in Spring framework. As I said its pretty easy using Apache DBCP library. Just matter of few configuration in spring applicationContext.xml and you are ready. If you want to configure JDBC Connection pool on tomcat (JNDI connection pool) and want to use in spring than see here.
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!  

One Response to "Spring JDBC Database connection pool setup"

  1. saisudha says:

    i want a perfect step by step process to connect the spring jdbc database connection.
    could you please help….
    or suggest any website…

    thank you….

Leave a Reply


+ eight = 9



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