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)
Leave a Reply