Spring Boot HikariCP Connection Pool Example | MySQL

HikariCP is a lightweight and highly optimized Java connection pool. Connection Pooling is a technique of creating and managing a pool of connections which is already created and ready for use by any process which needs them. In this Spring Boot HikariCP Connection Pool example, we will learn how to use HikariCP with Spring Boot.

Creating table

Create Employee Table, simply Copy and Paste the following SQL query in the query editor to get the table created.

CREATE TABLE EMPLOYEE(
  ID int(10) NOT NULL,
  NAME varchar(50) NOT NULL,
  AGE int(10) NOT NULL,
  DEPT varchar(20) NOT NULL,
  PRIMARY KEY EMPLOYEE(ID));

INSERT INTO EMPLOYEE VALUES (1,'JIP1',10,'IT');
INSERT INTO EMPLOYEE VALUES (2,'JIP2',20,'IT');
INSERT INTO EMPLOYEE VALUES (3,'JIP3',30,'IT');
INSERT INTO EMPLOYEE VALUES (4,'JIP4',40,'IT');

Folder Structure:

• Create a Maven project (maven-archetype-quickstart) “SpringBootHikariCP” and create a package for our source files “com.javainterviewpoint” under  src/main/java 
• Now add the following dependency in the POM.xml

  <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>com.javainterviewpoint</groupId>
  	<artifactId>SpringBootHikariCP</artifactId>
  	<version>0.0.1-SNAPSHOT</version>
  	<packaging>jar</packaging>
  
  	<name>SpringBootHikariCP</name>
  	<url>http://maven.apache.org</url>
  
  	<properties>
  		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  	</properties>
  
  
  	<parent>
  		<groupId>org.springframework.boot</groupId>
  		<artifactId>spring-boot-starter-parent</artifactId>
  		<version>1.5.1.RELEASE</version>
  	</parent>
  	<dependencies>
  
  		<dependency>
  			<groupId>org.springframework.boot</groupId>
  			<artifactId>spring-boot-starter</artifactId>
  		</dependency>
  		<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>
  
  			<!-- Excluding Tomcat Connection pooling in order to use HikariCP -->
  			<exclusions>
  				<exclusion>
  					<groupId>org.apache.tomcat</groupId>
  					<artifactId>tomcat-jdbc</artifactId>
  				</exclusion>
  			</exclusions>
  		</dependency>
  
  		<!-- HikariCP Dependency (Automatically configured by Spring Boot ) -->
  		<dependency>
  			<groupId>com.zaxxer</groupId>
  			<artifactId>HikariCP</artifactId>
  			<version>2.7.4</version>
  		</dependency>
  
  
  		<!-- MySQL JDBC Type 4 driver -->
  		<dependency>
      		<groupId>mysql</groupId>
      		<artifactId>mysql-connector-java</artifactId>
      		<version>5.1.45</version>
  		</dependency>
  
  		<!-- <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> 
  			<version>3.8.1</version> <scope>test</scope> </dependency> -->
  	</dependencies>
  
  	<build>
  		<finalName>SpringBootHikariCP</finalName>
  		<plugins>
  			<plugin>
  				<groupId>org.apache.maven.plugins</groupId>
  				<artifactId>maven-compiler-plugin</artifactId>
  				<configuration>
  					<source>1.8</source>
  					<target>1.8</target>
  				</configuration>
  			</plugin>
  		</plugins>
  	</build>
  </project>

• Create the Java classes EmployeeApp.java, Employee.java, EmployeeDAO.java, EmployeeDAOImpl.java, EmployeeController.java and EmployeeMapper.java under com.javainterviewpoint folder.
• Create application.properties file under src/main/resources directory. Provide the datasource and hikari connection pool settings in the application.properties

# Datasource settings
spring.datasource.initialize=true
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/mydb
spring.datasource.username=root
spring.datasource.password=root

# HikariCP Settings
spring.datasource.hikari.connection-timeout=10000
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=15
spring.datasource.hikari.pool-name=HikariConnectionPool

Spring Boot HikariCP Connection Pool Example

Dependency Tree

[INFO] Building SpringBootHikariCP 0.0.1-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO] 
[INFO] --- maven-dependency-plugin:2.10:tree (default-cli) @ SpringBootHikariCP ---
[INFO] com.javainterviewpoint:SpringBootHikariCP:jar:0.0.1-SNAPSHOT
[INFO] +- org.springframework.boot:spring-boot-starter:jar:1.5.1.RELEASE:compile
[INFO] |  +- org.springframework.boot:spring-boot:jar:1.5.1.RELEASE:compile
[INFO] |  |  \- org.springframework:spring-context:jar:4.3.6.RELEASE:compile
[INFO] |  +- org.springframework.boot:spring-boot-autoconfigure:jar:1.5.1.RELEASE:compile
[INFO] |  +- org.springframework.boot:spring-boot-starter-logging:jar:1.5.1.RELEASE:compile
[INFO] |  |  +- ch.qos.logback:logback-classic:jar:1.1.9:compile
[INFO] |  |  |  \- ch.qos.logback:logback-core:jar:1.1.9:compile
[INFO] |  |  +- org.slf4j:jcl-over-slf4j:jar:1.7.22:compile
[INFO] |  |  +- org.slf4j:jul-to-slf4j:jar:1.7.22:compile
[INFO] |  |  \- org.slf4j:log4j-over-slf4j:jar:1.7.22:compile
[INFO] |  +- org.springframework:spring-core:jar:4.3.6.RELEASE:compile
[INFO] |  \- org.yaml:snakeyaml:jar:1.17:runtime
[INFO] +- org.springframework.boot:spring-boot-starter-web:jar:1.5.1.RELEASE:compile
[INFO] |  +- org.springframework.boot:spring-boot-starter-tomcat:jar:1.5.1.RELEASE:compile
[INFO] |  |  +- org.apache.tomcat.embed:tomcat-embed-core:jar:8.5.11:compile
[INFO] |  |  +- org.apache.tomcat.embed:tomcat-embed-el:jar:8.5.11:compile
[INFO] |  |  \- org.apache.tomcat.embed:tomcat-embed-websocket:jar:8.5.11:compile
[INFO] |  +- org.hibernate:hibernate-validator:jar:5.3.4.Final:compile
[INFO] |  |  +- javax.validation:validation-api:jar:1.1.0.Final:compile
[INFO] |  |  +- org.jboss.logging:jboss-logging:jar:3.3.0.Final:compile
[INFO] |  |  \- com.fasterxml:classmate:jar:1.3.3:compile
[INFO] |  +- com.fasterxml.jackson.core:jackson-databind:jar:2.8.6:compile
[INFO] |  |  +- com.fasterxml.jackson.core:jackson-annotations:jar:2.8.0:compile
[INFO] |  |  \- com.fasterxml.jackson.core:jackson-core:jar:2.8.6:compile
[INFO] |  +- org.springframework:spring-web:jar:4.3.6.RELEASE:compile
[INFO] |  |  +- org.springframework:spring-aop:jar:4.3.6.RELEASE:compile
[INFO] |  |  \- org.springframework:spring-beans:jar:4.3.6.RELEASE:compile
[INFO] |  \- org.springframework:spring-webmvc:jar:4.3.6.RELEASE:compile
[INFO] |     \- org.springframework:spring-expression:jar:4.3.6.RELEASE:compile
[INFO] +- org.springframework.boot:spring-boot-starter-jdbc:jar:1.5.1.RELEASE:compile
[INFO] |  \- org.springframework:spring-jdbc:jar:4.3.6.RELEASE:compile
[INFO] |     \- org.springframework:spring-tx:jar:4.3.6.RELEASE:compile
[INFO] +- com.zaxxer:HikariCP:jar:2.7.4:compile
[INFO] |  \- org.slf4j:slf4j-api:jar:1.7.22:compile
[INFO] \- mysql:mysql-connector-java:jar:5.1.45:compile

EmployeeDAO.java

package com.javainterviewpoint;

import java.util.List;

public interface EmployeeDAO
{
    List<Employee> getAllEmployees();
    Employee getEmployeeById(int id);
    void addEmployee(Employee employee);
    void updateEmployee(Employee employee);
    void deleteEmployee(int id);
}

EmployeeDAO interface contains the method for performing the CRUD operation and the implementation will be provided by EmployeeDAOImpl.

EmployeeDAOImpl

package com.javainterviewpoint;

import java.util.List;

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

@Repository
public class EmployeeDAOImpl implements EmployeeDAO
{
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<Employee> getAllEmployees()
    {
        String sql = "select id, name, age, dept from Employee";
        List<Employee> employeeList = jdbcTemplate.query(sql, new EmployeeMapper());
        return employeeList;
    }

    @Override
    public Employee getEmployeeById(int id)
    {
        String sql = "select id, name, age, dept from Employee where id = ?";
        Employee employee = (Employee) jdbcTemplate.queryForObject(sql, new EmployeeMapper(), id);
        return employee;
    }

    @Override
    public void addEmployee(Employee employee)
    {
        String sql = "insert into Employee (id, name, age, dept) values (?, ?, ?, ?)";
        jdbcTemplate.update(sql, employee.getId(), employee.getName(), employee.getAge(), employee.getDept());
    }

    @Override
    public void updateEmployee(Employee employee)
    {
        String sql = "update Employee set name = ?, age = ?, dept = ? where id = ?";
        jdbcTemplate.update(sql, employee.getName(), employee.getAge(), employee.getDept(), employee.getId());
    }

    @Override
    public void deleteEmployee(int id)
    {
        String sql = "delete from Employee where id = ?";
        jdbcTemplate.update(sql, id);
    }
}

• We have implemented the EmployeeDAO interface and overriden the methods of it.
• Using @Autowired annotation we have injected the jdbcTemplate, using which we will be querying the database.

EmployeeController.java

package com.javainterviewpoint;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class EmployeeController
{
    @Autowired
    EmployeeDAOImpl employeeDAOImpl;
    
    @GetMapping("/employees")
    public ResponseEntity<List<Employee>> getAllEmployees()
    {
        List employeeList = employeeDAOImpl.getAllEmployees();
        return new ResponseEntity<List<Employee>>(employeeList, HttpStatus.OK);
    }
    
    @GetMapping("/employee/{id}")
    public ResponseEntity<Employee> getEmployeeById(@PathVariable("id") int id)
    {
        Employee employee = employeeDAOImpl.getEmployeeById(id);
        return new ResponseEntity<Employee>(employee, HttpStatus.OK);
    }
    
    @PostMapping("/employee")
    public ResponseEntity<Employee> addEmployee(@RequestBody Employee employee)
    {
        employeeDAOImpl.addEmployee(employee);
        return new ResponseEntity<Employee>(employee, HttpStatus.OK);
    }
    
    @PutMapping("/employee")
    public ResponseEntity<Employee> updateEmployee(@RequestBody Employee employee)
    {
        employeeDAOImpl.updateEmployee(employee);
        return new ResponseEntity<Employee>(employee, HttpStatus.OK);
    }
    
    @DeleteMapping("/employee/{id}")
    public ResponseEntity deleteEmployee(@PathVariable("id") Integer id)
    {
        employeeDAOImpl.deleteEmployee(id);
        return new ResponseEntity(HttpStatus.NO_CONTENT);
    }
}

• We have annotated our “EmployeeController” class with @RestController, In Spring 4 @RestController annotation is introduced it is a combination of @Controller + @ResponseBody. So when using @RestController, you do not need to use @ResponseBody it is optional now
• @RequestBody: This annotation tells Spring to bind the incoming HTTP request body( object passed in the request). HTTP Message converters convert the HTTP request body into domain object based on Accept header present in the request.
• @PathVariable: This annotation will bind method argument to the URI template variable.

EmployeeMapper.java

package com.javainterviewpoint;

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

import org.springframework.jdbc.core.RowMapper;

public class EmployeeMapper implements RowMapper
{
    @Override
    public Object mapRow(ResultSet rs, int row) throws SQLException
    {
        Employee employee = new Employee();
        
        employee.setId(rs.getInt(1));
        employee.setName(rs.getString(2));
        employee.setAge(rs.getInt(3));
        employee.setDept(rs.getString(4));
        
        return employee;
    }
}

In our EmployeeMapper class, we have implemented Spring JDBC RowMapper interface, the RowMapper is used to map the ResultSet row with a Java object. We will be passing our EmployeeMapper while querying the database using JdbcTemplate

EmployeeApp.java

package com.javainterviewpoint;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class EmployeeApp 
{
    public static void main(String[] args)
    {
        SpringApplication.run(EmployeeApp.class, args);
    }
}

The EmployeeApp class main() method is the triggering point of our application, it in-turn calls Spring Boot’s SpringApplication class run() method which bootstrap our EmployeeApp application and starts the tomcat server. We need to pass our EmployeeApp.class as an argument to our run() method.

Employee.java

package com.javainterviewpoint;

public class Employee
{
    private int id;
    private String name;
    private int age;
    
    private String dept;
    
    public Employee()
    {
        super();
    }

    public Employee(int id, String name, int age, String dept)
    {
        super();
        this.id = id;
        this.name = name;
        this.age = age;
        this.dept = dept;
    }
    
    public int getId()
    {
        return id;
    }
    public void setId(int id)
    {
        this.id = id;
    }
    public String getName()
    {
        return name;
    }
    public void setName(String name)
    {
        this.name = name;
    }
    public int getAge()
    {
        return age;
    }
    public void setAge(int age)
    {
        this.age = age;
    }
    public String getDept()
    {
        return dept;
    }
    public void setDept(String dept)
    {
        this.dept = dept;
    }
    @Override
    public String toString()
    {
        return "Employee [id=" + id + ", name=" + name + ", age=" + age + ", dept=" + dept + "]";
    }
    /*@Override
    public int hashCode()
    {
        final int prime = 31;
        int result = 1;
        result = prime * result + age;
        result = prime * result + ((dept == null) ? 0 : dept.hashCode());
        result = prime * result + id;
        result = prime * result + ((name == null) ? 0 : name.hashCode());
        return result;
    }
    @Override
    public boolean equals(Object obj)
    {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (getClass() != obj.getClass())
            return false;
        Employee other = (Employee) obj;
        if (age != other.age)
            return false;
        if (dept == null)
        {
            if (other.dept != null)
                return false;
        } else if (!dept.equals(other.dept))
            return false;
        if (id != other.id)
            return false;
        if (name == null)
        {
            if (other.name != null)
                return false;
        } else if (!name.equals(other.name))
            return false;
        return true;
    }*/
}

Our Employee class is a simple POJO consisting getters and setters of Employee properties id, name, age and dept.

Output:

Run the spring boot application using “mvn spring-boot:run”

I will be using POSTMAN client for testing my Restful Web services, you can choose any client of your choice

Hit on the URL : http://localhost:8080/employees

EmployeeRestTemplateClient.java

package com.javainterviewpoint;

import java.util.List;
import java.util.Map;

import org.springframework.http.HttpEntity;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.web.client.RestTemplate;

public class EmployeeRestTemplateClient
{
    public static final String REST_BASE_URI = "http://localhost:8080";

    static RestTemplate restTemplate = new RestTemplate();

    /** POST **/
    public static void createEmployee()
    {
        Employee employee = new Employee();
        employee.setId(5);
        employee.setName("JIP5");
        employee.setAge(50);
        employee.setDept("Blogging");

        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_JSON);
        // headers.setAccept(Arrays.asList(MediaType.APPLICATION_JSON));

        HttpEntity entity = new HttpEntity<>(employee, headers);
        restTemplate.postForObject(REST_BASE_URI + "/employee", entity, Employee.class);
    }

    /** GET **/
    private static void getEmployee(int id)
    {
        Employee employee = restTemplate.getForObject(REST_BASE_URI + "/employee/" + id, Employee.class);
        System.out.println("**** Employee with id : " + id + "****");
        System.out
                .println("Id :" + employee.getId() + "    Name : " + employee.getName() + "   Dept : " + employee.getDept()+"   Age : " + employee.getAge());
    }

    public static void getAllEmployees()
    {

        List<Map<String, Object>> employeeList = restTemplate.getForObject(REST_BASE_URI + "/employees", List.class);
        if (employeeList != null)
        {
            System.out.println("**** All Employees ****");
            for (Map<String, Object> map : employeeList)
            {
                System.out.println(
                        "Id : id=" + map.get("id") + "   Name=" + map.get("name") + "   Dept : " + map.get("dept")+ "   Age=" + map.get("age"));
            }
        } else
        {
            System.out.println("No Employee exist!!");
        }
    }

    /** PUT **/
    public static void updateEmployee()
    {
        Employee employee = new Employee();
        employee.setId(5);
        employee.setName("JIP5555");
        employee.setAge(5555);
        employee.setDept("Blogging");

        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_JSON);

        HttpEntity entity = new HttpEntity<>(employee, headers);

        restTemplate.put(REST_BASE_URI + "/employee", entity, Employee.class);
    }

    /** DELETE **/
    public static void deleteEmployee(int id)
    {
        restTemplate.delete(REST_BASE_URI + "/employee/" + id);
    }

    public static void main(String args[])
    {
        createEmployee();

        getAllEmployees();

        getEmployee(2);

        updateEmployee();

        deleteEmployee(5);
    }
}

Output:

16:43:28.033 [main] DEBUG org.springframework.web.client.RestTemplate - Created POST request for "http://localhost:8080/employee"
16:43:28.103 [main] DEBUG org.springframework.web.client.RestTemplate - Setting request Accept header to [application/json, application/*+json]
16:43:28.130 [main] DEBUG org.springframework.web.client.RestTemplate - Writing [Employee [id=5, name=JIP5, age=50, dept=Blogging]] as "application/json" using [org.springframework.http.converter.json.MappingJackson2HttpMessageConverter@12d3247]
16:43:28.253 [main] DEBUG org.springframework.web.client.RestTemplate - POST request for "http://localhost:8080/employee" resulted in 200 (null)
16:43:28.255 [main] DEBUG org.springframework.web.client.RestTemplate - Reading [class com.javainterviewpoint.Employee] as "application/json;charset=UTF-8" using [org.springframework.http.converter.json.MappingJackson2HttpMessageConverter@12d3247]
16:43:28.282 [main] DEBUG org.springframework.web.client.RestTemplate - Created GET request for "http://localhost:8080/employees"
16:43:28.293 [main] DEBUG org.springframework.web.client.RestTemplate - Setting request Accept header to [application/json, application/*+json]
16:43:28.299 [main] DEBUG org.springframework.web.client.RestTemplate - GET request for "http://localhost:8080/employees" resulted in 200 (null)
16:43:28.300 [main] DEBUG org.springframework.web.client.RestTemplate - Reading [interface java.util.List] as "application/json;charset=UTF-8" using [org.springframework.http.converter.json.MappingJackson2HttpMessageConverter@12d3247]
**** All Employees ****
Id : id=1   Name=JIP1   Dept : IT   Age=10
Id : id=2   Name=JIP2   Dept : IT   Age=20
Id : id=3   Name=JIP3   Dept : IT   Age=30
Id : id=4   Name=JIP4   Dept : IT   Age=40
Id : id=5   Name=JIP5   Dept : Blogging   Age=50
16:43:28.308 [main] DEBUG org.springframework.web.client.RestTemplate - Created GET request for "http://localhost:8080/employee/2"
16:43:28.309 [main] DEBUG org.springframework.web.client.RestTemplate - Setting request Accept header to [application/json, application/*+json]
16:43:28.314 [main] DEBUG org.springframework.web.client.RestTemplate - GET request for "http://localhost:8080/employee/2" resulted in 200 (null)
16:43:28.314 [main] DEBUG org.springframework.web.client.RestTemplate - Reading [class com.javainterviewpoint.Employee] as "application/json;charset=UTF-8" using [org.springframework.http.converter.json.MappingJackson2HttpMessageConverter@12d3247]
**** Employee with id : 2****
Id :2    Name : JIP2   Dept : IT   Age : 20
16:43:28.315 [main] DEBUG org.springframework.web.client.RestTemplate - Created PUT request for "http://localhost:8080/employee"
16:43:28.315 [main] DEBUG org.springframework.web.client.RestTemplate - Writing [Employee [id=5, name=JIP5555, age=5555, dept=Blogging]] as "application/json" using [org.springframework.http.converter.json.MappingJackson2HttpMessageConverter@12d3247]
16:43:28.374 [main] DEBUG org.springframework.web.client.RestTemplate - PUT request for "http://localhost:8080/employee" resulted in 200 (null)
16:43:28.374 [main] DEBUG org.springframework.web.client.RestTemplate - Created DELETE request for "http://localhost:8080/employee/5"
16:43:28.440 [main] DEBUG org.springframework.web.client.RestTemplate - DELETE request for "http://localhost:8080/employee/5" resulted in 204 (null)

   Download Source Code