Thursday, June 15, 2017

JDBC Database Connection Pool in Spring Framework – How to Setup Example

Setting up JDBC Database Connection Pool in Spring framework is easy for any Java application, just matter of changing a few configurations in the 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 a nice choice but if you have the luxury of having web server and managed J2EE Container, consider using Connection pool managed by J2EE server those are a 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 set up 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 manages session using Spring security  If you haven’t read those articles then you may find them useful.

By the way, if you are new to Spring framework then I also suggest you join a comprehensive and up-to-date course to learn Spring in depth. If you need recommendations, I highly suggest you take a look at Spring Framework 5: Beginner to Guru, one of the comprehensive and hands-on course to learn modern Spring. It' also most up-to-date and covers Spring 5.

 It's also very affordable and you can buy in just $10 on Udemy sales which happen every now and then. 

Spring Example JDBC Database Connection Pool

Spring framework provides a convenient JdbcTemplate class for performing all Database related operations. if you are not using Hibernate than using Spring's JdbcTemplate is a 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 the 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 the setup Data source you will require the 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. 

The above example is creating a 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

Database connection pool Spring example codeBelow is a complete code example of DAO class which uses Spring JdbcTemplate to execute a SELECT query against the database using database connection from the 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 a certain number of SQL connection and then it executes 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 your classpath.
2. Apache DBCP and commons-pool jar in the application classpath.

That's all on how to configure the JDBC Database connection pool in the Spring framework. As I said its pretty easy using Apache DBCP library. Just matter of few configurations in spring applicationContext.xml and you are ready. If you want to configure the JDBC Connection pool on tomcat (JNDI connection pool) and want to use in spring than see here.


Further Reading
Spring Framework 5: Beginner to Guru
Spring Master Class - Beginner to Expert
Introduction to Spring MVC 4 By Bryan Hansen
Spring in Action 4th edition by Craig Walls


Other Java JDBC tutorials you may like

P.S. - If you are an experienced Java/JEE Program and want to learn Spring Security end-to-end, I recommend the Learn Spring Security course by Eugen Paraschiv, The definitive guide to secure your Java application. It's useful for both junior and experienced Java Web developers.

He is also the author of REST with Spring course, one of the best online courses to learn RESTful WebServices using the Spring framework.

8 comments :

Jirka Pinkas said...

I use C3P0 instead of DBCP. DBCP has memory leaks (maybe they fixed it, but I don't know that for sure because I already jumped the ship) :)

And for Oracle database it's best to use DataSource from their JDBC driver, it has also connection pooling and better performance.

Anonymous said...

ComboPooledDataSource from c3po jar is my preferred data source. c3po has proper default values for many configuration requires for connection pool as well. you just need to change your bean class to com.mchange.v2.c3p0.ComboPooledDataSource and include c3p0-0.9.1.jar

Yogesh said...

nice info...

Anonymous said...

can we use transaction manager if we are using c3po i spring. Because c3po is not closing my connections after i say trsansactionmanager.commit(trsansactionstatus)

Harsh said...

java.lang.UnsupportedOperationException: Not supported by BasicDataSource at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:899)

I am trying to save data thorugh a form(index.jsp) but this exception continuously coming, do you have a solution for this ?? Thanks !!

javin paul said...

Hello @Harsh, I am not sure which version of DBCP you are using, but in latest version getConnection(User,Password) method is not supported and that's why it's throwing java.lang.UnsupportedOperationException. It's better now to move to another connection pool like C3PO or tomcat connection pool. If you decides to use tomcat connection pool, then use org.apache.tomcat.jdbc.pool.DataSource class and tomcat-jdbc JAR as dependency.

See this for more details https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html

Anonymous said...

HikariCP is the best JDBC connection pool out there.

Jayshri Dorge said...

I am new to the spring framework.
while import log4j and springFramework i am getting error "cannot be resolved" is showing on eclipse


Step to use Log4j springFramework:

I have added two jar for the Log4j in dependency repo
and for spring framework as i have added two jar in dependency repo.

please revert what else i have to do to get run the application or make it error free?

Post a Comment