Spring Boot with different Connection Pooling

In my previous post, I talked about spring boot and why we should use it. This post will cover how to implement different connection pooling with spring boot. Before going into the implementation let’s have a brief overview about connection pooling.

Connection Pooling

connection pool is a cache of maintained database connections so that the connections can be reused when future requests to the database are required.

Database connections are often expensive to create because of the overhead of establishing a network connection and initializing a database connection session in the back end database. In turn, connection session initialization often requires time consuming processing to perform user authentication, establish other aspects of the session that are required for subsequent database usage. – Wikipedia

Connection pools are used to enhance the performance of executing commands on a database. In connection pooling, after a connection is created, it is placed in the pool and it is used again so that a new connection does not have to be established. If all the connections are being used, a new connection is made and is added to the pool. Connection pooling also cuts down on the amount of time a user must wait to establish a connection to the database.

Let’s talk about the various ways to implement connection pooling.

There are various ways provided by many organizations to implement connection pooling. Following are some of them:

  • Common DBCP – It is provided by Apache foundations and used to interact with databases to maintain connection pooling. To know more click here
  • C3P0 – It is again a way to implement connection pooling. To know more click here
  • Hikari CP – HikariCP is a very fast lightweight Java connection pool. The API and overall code base is relatively small (A good thing) and highly optimized. It also does not cut corners for performance like many other Java connection pool implementations. The Wiki is highly informative and dives really deep.

Following are the maven dependencies for each type of connection pooling:

Common DBCP:

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

C3P0:

<dependency>
	<groupId>c3p0</groupId>
	<artifactId>c3p0</artifactId>
	<version>0.9.1.2</version>
</dependency>

Hikari CP:

<dependency>
	<groupId>com.zaxxer</groupId>
	<artifactId>HikariCP</artifactId>
	<version>2.6.0</version>
</dependency>

Above are the required dependencies for each connection pool.

Following is the step by step process to implement each type of connection pooling with spring boot:

Create Maven project

Create maven based project in eclipse with Archetype web and update pom.xml as below:

<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/maven-v4_0_0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>springboot</groupId>
	<artifactId>application</artifactId>
	<packaging>jar</packaging>
	<version>0.0.1</version>
	<name>Application Maven Webapp</name>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>1.5.2.RELEASE</version>
	</parent>

	<properties>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
			<exclusions>
				<exclusion>
					<groupId>org.apache.tomcat</groupId>
					<artifactId>tomcat-jdbc</artifactId>
				</exclusion>
			</exclusions>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>
	</dependencies>
	<build>
		<finalName>application</finalName>
	</build>
</project>

Above pom.xml doesn’t include any dependency of connection pooling so add dependency whatever you want to use in your project.

In this post, I used MySql database so let’s create following table in your database:

CREATE TABLE `employee` (
  `emp_id` INT,
  `employee_name` VARCHAR(45) NULL,
  `employee_designation` VARCHAR(45) NULL,
  `employee_salary` INT NULL
  PRIMARY KEY (`emp_id`));

And Insert following data into the table as below:

insert into employee values (1, 'Ashish kumar', 'Java developer', '10000');
insert into employee values(2, 'Rahul sharma', 'QA', '20000');
insert into employee values (3, 'Arjun kumar', 'Android developer', '15000');

Create application.properties file and put it under src/main/resources folder as below:

spring.datasource.url=jdbc:mysql://localhost:3306/sys?useSSL=false
spring.datasource.username=xxxx
spring.datasource.password=xxxxxxx
spring.datasource.max-total=30
spring.jmx.enabled=false

#DBCP2 configurations
#spring.datasource.dbcp2.initial-size=7
#spring.datasource.dbcp2.max-total=20
#spring.datasource.dbcp2.pool-prepared-statements=true

#Hikari configurations
#spring.datasource.type=com.zaxxer.hikari.HikariDataSource
#spring.datasource.maximum-pool-size=30

#C3P0 configurations
#spring.datasource.type = com.mchange.v2.c3p0.ComboPooledDataSource

As of now, I have commented out the connection pooling properties for each connection pool so whatever connection pooling you want to use in the project, uncomment configuration for the same.

In case of C3P0 connection pooling, we only have to define type in application.properties file and we have to make c3p0.properties file under /src/main/resources as below:

#Define required properties in c3p0.properties file
c3p0.initialPoolSize = 10
c3p0.maxPoolSize = 10
c3p0.maxStatements = 20
c3p0.maxIdleTime = 20000

Create following model class for employee:

package com.threadminions.model;

public class Employee {

	private int empId;
	private String employeeName;
	private String employeeDesignation;
	private int employeeSalary;

	public int getEmpId() {
		return empId;
	}
	public void setEmpId(int empId) {
		this.empId = empId;
	}
	public String getEmployeeName() {
		return employeeName;
	}
	public void setEmployeeName(String employeeName) {
		this.employeeName = employeeName;
	}
	public String getEmployeeDesignation() {
		return employeeDesignation;
	}
	public void setEmployeeDesignation(String employeeDesignation) {
		this.employeeDesignation = employeeDesignation;
	}
	public int getEmployeeSalary() {
		return employeeSalary;
	}
	public void setEmployeeSalary(int employeeSalary) {
		this.employeeSalary = employeeSalary;
	}
}

Create Extractor class for parsing result set to employee model using RowMapper interface as below:

package com.threadminions.service;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import com.threadminions.model.Employee;

public class Extractor implements RowMapper<Employee>
{
	@Override
	public Employee mapRow(ResultSet rs, int arg1) throws SQLException
	{
	      Employee emp = new Employee();
	      emp.setEmpId(rs.getInt("emp_id"));
	      emp.setEmployeeName(rs.getString("employee_name"));
              emp.setEmployeeDesignation(rs.getString("employee_designation"));
	      emp.setEmployeeSalary(rs.getInt("employee_salary"));

	      return emp;
	}
}

Create following classes to print employees and about the connection pooling details:

package com.threadminions.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import com.threadminions.model.Employee;

@Component
public class EmployeeServiceLayer {

	@Autowired
	JdbcTemplate jdbcTemplate;

	public void printEmployees()
	{
		List<Employee> allEmps = jdbcTemplate.query("select * from
                                employee", new Extractor());

		allEmps.forEach(emp ->
		{
			System.out.println("Employee Name: " +
                        emp.getEmployeeName());

                        System.out.println("Employee Salary: " +
                        emp.getEmployeeSalary());
		});
	}
}

DataBaseRepository class to print connection pooling details:

package com.threadminions.service;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class DatabaseRepository {

	@Autowired
	DataSource dataSource;

	/***
	 * If you are using DBCP connection pooling then use this method to get the connection details
	 */
	/*public void printDBCPConnectionDetails()
	{
		BasicDataSource basicDataSource = (BasicDataSource) dataSource;
		System.out.println("Instace of DBCP basic data source: " + basicDataSource);
		System.out.println("Driver class name: " + basicDataSource.getDriverClassName());
		System.out.println("Max idle connection: " + basicDataSource.getMaxIdle());
		System.out.println("Total connection: " + basicDataSource.getMaxTotal());
	}*/

	/***
	 * If using Hikari connection pooling
	 */
	/*public void printHikariConnectionDetails()
	{
		HikariDataSource ds = (HikariDataSource)dataSource;
		System.out.println("Instace of DBCP basic data source: " + ds);
		System.out.println("Driver class name: " + ds.getDriverClassName());
		System.out.println("Connection Pool size : " + ds.getMaximumPoolSize());
		System.out.println("Url: " + ds.getJdbcUrl());

	}*/

	/***
	 * If using C3P0 connection pooling
	 */
	/*public void printC3P0ConnectionDetails()
	{
		ComboPooledDataSource ds = (ComboPooledDataSource) dataSource;
		System.out.println("Instace of DBCP basic data source: " + ds);
		System.out.println("Connection Pool size : " + ds.getMaxPoolSize());
		System.out.println("Min connection pool size: " + ds.getMinPoolSize());
		System.out.println("Max statements: " + ds.getMaxStatements());
		System.out.println("Url: " + ds.getJdbcUrl());
	}*/
}

This class will print connection pooling details. I have commented out all the code because every method in this class depends on the pooling which you will use. So just uncomment method according to connection pooling you are using.

Now, finally create a configuration class SpringBootConfig as below:

package com.threadminions;

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

import com.threadminions.service.DatabaseRepository;
import com.threadminions.service.EmployeeServiceLayer;

@SpringBootApplication
public class SpringBootConfig implements CommandLineRunner
{
	@Autowired
	EmployeeServiceLayer employeeService;

	@Autowired
	DatabaseRepository databaseRepository;

	public static void main(String[] ar)
	{
		SpringApplication.run(SpringBootConfig.class, ar);
	}

	@Override
	public void run(String... arg0) throws Exception
	{
		employeeService.printEmployees();
	}
}

Here in this class, it will autowire EmployeeService and DatabaseRepository class and will invoke the method printEmployee().

Invoke method of DataBaseRepository method to see the connection details.

All things has been set. Now, its time to run the program so run it as java application and following output will be shown in following cases:

If using DBCP connection pooling:

spring_boot_dbcp

If using Hikari CP:

spring_boot_hikari

If using C3P0:

spring_boot_c3p0

As you can see in all the output about the details of connection pooling.

Above example use auto configuration mechanism of spring boot which means that spring boot will automatically configure required configuration for every bean when it sees any jars in its class path as in this example datasource was automatically configured.

But if we don’t want to use this feature of spring boot then we can customize the way of initializing the datasource.

In my next post, will describe the way of how we can customize the way of initializing the datasource and implement connection pooling. Stay tuned. !!

3 thoughts on “Spring Boot with different Connection Pooling

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s