1. Overview

Example of spring boot common dbcp2 connection pool example.  dbcp2 (Data Base Connection Pooling) is a very popular library to manage the connection pool, dbcp2 is the project of apache.

Let’s try to understand requirements of connection pooling in simple words, To communicate with the database requires the database connection and create the connection with the database is heavy operation. While performing each operation requires to open connection and after perform, database operation closes the connection but this process will take too much time for opening and closing connection every database operations. To solve this issue database connection pooling is used.

The connection pool can maintain multiple connections with the database when the demand of  Connection object at that time return object from the pool instead of creating a new connection every time.

So now let’s try to understand how to configure dbcp2 connection pooling with spring boot application.

Step 1: Remove tomcat-jdbc connection pool

While working with spring-boot-starter-jdbc, Spring boot will default used a tomcat-jdbc connection pool. so first of all need to remove this dependency:

<dependency>
           <groupId>org.springframework.boot</groupId>
           <artifactId>spring-boot-starter-jdbc</artifactId>   <!--It contains database base related classes-->
           <exclusions>
               <exclusion>
                   <groupId>org.apache.tomcat</groupId>
                   <artifactId>tomcat-jdbc</artifactId>
               </exclusion>
           </exclusions>
</dependency>

Step 2: Add commons-dbcp2 dependency

Add commons-dbcp2 in maven dependency or classpath as like:

<dependency>
      <groupId>org.apache.commons</groupId>
      <artifactId>commons-dbcp2</artifactId>
      <version>2.2.0</version>
</dependency>

2. Example

This is the complete example of Spring boot DBCP2 with MySQL.

Spring boot common dbcp2 connection pool example

Spring boot common dbcp2 connection pool example

2.1 pom.xml

Here tomcat-jdbc default connection pool has been removed and added a dbcp2 dependency.  Here is version specific note:

  • DBCP 2 compiles and runs under Java 7 only (JDBC 4.1)
  • DBCP 1.4 compiles and runs under Java 6 only (JDBC 4)
  • DBCP 1.3 compiles and runs under Java 1.4-5 only (JDBC 3)
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>spring-boot-example</groupId>
    <artifactId>spring-boot-datasource-example</artifactId>
    <description>spring boot common dbcp2 connection pool</description>
    <version>1.0-SNAPSHOT</version>
    <!-- Inherit defaults from Spring Boot -->
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.8.RELEASE</version>
    </parent>
    <properties>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>
    <dependencies>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>   <!--It contains database base related classes-->
            <exclusions>
                <exclusion>
                    <groupId>org.apache.tomcat</groupId>
                    <artifactId>tomcat-jdbc</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.2.0</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>

    </dependencies>

    <!-- Package as an executable jar -->
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

2.2 application.properties

Here are spring data source properties which contain database related configuration. DBCP related properties can be configurations using  spring.datasource.dbcp2.* properties like

spring.datasource.dbcp2.max-idle, spring.datasource.dbcp2.initial-size here are all other configurations properties related DBCP

spring.datasource.url=jdbc:mysql://localhost/demo_database
spring.datasource.username=root
spring.datasource.password=
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

2.3 SpringBootConfig

Here is spring boo main class, In command like runner we have checked that which connection pool has been used by Spring boot. Here it will print org.apache.commons.dbcp2.BasicDataSource which means that spring boot is using DBCP instead of tomcat connection pooling.

package com.javadeveloperzone;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;

import javax.sql.DataSource;

/**
 * Created by JavaDeveloperZone on 19-07-2017.
 */

@SpringBootApplication
@ComponentScan
// Using a root package also allows the @ComponentScan annotation to be used without needing to specify a basePackage attribute
public class SpringBootConfig implements CommandLineRunner {

    @Autowired
    private DataSource dataSource;

    public static void main(String[] args) throws Exception {
        SpringApplication.run(SpringBootConfig.class, args);            // it wil start application
    }

    @Override
    public void run(String... args) throws Exception {
        System.out.println("Connection Polling datasource : "+ dataSource);  // check connection pooling
    }
}

2.4 EmployeeController

package com.javadeveloperzone.controller;

import com.javadeveloperzone.dao.EmployeeDAO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.sql.DataSource;

/**
 * Created by JavaDeveloperZone on 19-07-2017.
 */
@RestController
public class EmployeeController {

    @Autowired
    private EmployeeDAO employeeDAO;

    @RequestMapping(value = "getEmployeeList")
    public java.util.List getEmployeeList() {
        return employeeDAO.getEmployeeList();
    }
}

2.5 EmployeeDAO

package com.javadeveloperzone.dao;

import com.javadeveloperzone.model.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * Created by JavaDeveloperZone on 03-08-2017.
 */
@Component
public class EmployeeDAO {

    @Autowired
    private JdbcTemplate jdbcTemplate;


    public java.util.List<Employee> getEmployeeList() {
        List<Map<String, Object>> employees = jdbcTemplate.queryForList("select * from employee");
        return employees.stream().map(e -> {
            Employee employee = new Employee();
            employee.setEmployeeName(e.get("employeeName").toString());
            employee.setEmployeeId((Integer) e.get("employeeId"));
            employee.setEmployeeRole((String) e.get("employeeRole"));
            return employee;
        }).collect(Collectors.toList());
    }
}

2.6 Employee

package com.javadeveloperzone.model;

/**
 * Created by JavaDeveloperZone on 03-08-2017.
 */
public class Employee {
    private int employeeId;
    private String employeeName;
    private String employeeRole;

    public int getEmployeeId() {
        return employeeId;
    }

    public void setEmployeeId(int employeeId) {
        this.employeeId = employeeId;
    }

    public String getEmployeeRole() {
        return employeeRole;
    }

    public void setEmployeeRole(String employeeRole) {
        this.employeeRole = employeeRole;
    }

    public String getEmployeeName() {
        return employeeName;
    }

    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }
}

2.7 Run Application

Here Connection Polling datasource : [email protected] is printed which means at data source is managed by DBCP connection pooling.

2018-03-31 22:56:13.093  INFO 5104 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup
2018-03-31 22:56:13.110  INFO 5104 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Bean with name 'dataSource' has been autodetected for JMX exposure
2018-03-31 22:56:13.126  INFO 5104 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Located MBean 'dataSource': registering with JMX server as MBean [org.apache.commons.dbcp2:name=dataSource,type=BasicDataSource]
2018-03-31 22:56:13.254  INFO 5104 --- [           main] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat started on port(s): 8080 (http)
Connection Polling datasource : [email protected]
2018-03-31 22:56:13.263  INFO 5104 --- [           main] com.javadeveloperzone.SpringBootConfig   : Started SpringBootConfig in 9.217 seconds (JVM running for 10.426)

3. Conclusion

In this example, we have seen that how we can configure DBCP with spring boot application, We have used MySQL and Tomcat as a web server in above example and also seen that how the connection pool will help us reduce main connection with database which help us to improve performance of application in context of database communicate.

4. References

Was this post helpful?
Let us know, if you liked the post. Only in this way, we can improve us.
Yes
No

Leave a Reply

Your email address will not be published. Required fields are marked *