Table of Contents
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
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 : org.apache.commons.dbcp2.BasicDataSource@6e4f263e 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 : org.apache.commons.dbcp2.BasicDataSource@6e4f263e 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. That’s the only way we can improve.
