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. Connection Pooling can increase the performance of the application significantly. In order to display the data to the user, the application typically performs the following process creates a connection, connects to the database, fetches the result and close the connection. However creating a new connection and closing them every time is quite an expensive process, this takes a heavy toll when the number of connection opened and closed is high for each request. In order to overcome this problem we, will be using Connection Pooling. In our Spring Boot JDBC Connection Pool example, we will learn how to implement Connection Pool using Apache commons dbcp2
Connection pooling addresses the above problem by creating the connection pool and maintaining the connection objects. Whenever the user request for the data, the idle connection in the connection pool will be used to retrieve data. When there is no idle connection and the maximum pool limit is not reached, then a new connection object is created and the request will be served. When there is no idle connection and the maximum pool limit is reached, then the user’s request will be queued up and will be served when any other request releases the connection.
Creating table
Create STUDENT Table, simply Copy and Paste the following SQL query in the query editor to get the table created.
CREATE TABLE STUDENT
(
ID INT(10) NOT NULL,
NAME VARCHAR(255),
AGE INT(10),
PRIMARY KEY(ID)
);
insert into STUDENT values (1,'JIP1',11);
insert into STUDENT values (2,'JIP2',22);
insert into STUDENT values (3,'JIP3',33);
insert into STUDENT values (4,'JIP4',44);
Folder Structure:
- Create a Maven project (maven-archetype-quickstart) “SpringBootConnectionPool” 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>SpringBootConnectionPool</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <name>SpringBootConnectionPool</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> <exclusions> <exclusion> <groupId>org.apache.tomcat</groupId> <artifactId>tomcat-jdbc</artifactId> </exclusion> </exclusions> </dependency> <!-- MySQL JDBC driver --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.6</version> </dependency> <!-- Apache Commons DBCP connection pool --> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-dbcp2</artifactId> <version>2.1.1</version> </dependency> </dependencies> </project>
- Create the Java classes StudentApp.java, Student.java, StudentDAO.java, StudentDAOImpl.java, StudentController.java and StudentMapper.java under com.javainterviewpoint folder.
- Create application.properties file under src/main/resources directory
# 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 # dbcp2 settings spring.datasource.dbcp2.initial-size=10 spring.datasource.dbcp2.max-total=25 spring.datasource.dbcp2.pool-prepared-statements=true
Spring Boot JDBC Connection Pool Example
Dependency Tree
[INFO] com.javainterviewpoint:SpringBootConnectionPool: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:slf4j-api:jar:1.7.22: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] +- mysql:mysql-connector-java:jar:5.1.6:compile [INFO] \- org.apache.commons:commons-dbcp2:jar:2.1.1:compile [INFO] +- org.apache.commons:commons-pool2:jar:2.4.2:compile [INFO] \- commons-logging:commons-logging:jar:1.2:compile
StudentDAO.java
package com.javainterviewpoint;
import java.util.List;
public interface StudentDAO
{
List<Student> getAllStudents();
Student getStudentById(int id);
void addStudent(Student student);
void updateStudent(Student student);
void deleteStudent(int id);
}
StudentDAO interface contains the method for performing the CRUD operation and the implementation will be provided by StudentDAOImpl.
StudentDAOImpl.java
package com.javainterviewpoint;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
public class StudentDAOImpl implements StudentDAO
{
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public List<Student> getAllStudents()
{
String sql = "select id, name, age from Student";
List studentList = jdbcTemplate
.query(sql, new StudentMapper());
return studentList;
}
@Override
public Student getStudentById(int id)
{
String sql = "select id, name, age from Student where id =?";
Student student = jdbcTemplate
.queryForObject(sql, new StudentMapper(), id);
return student;
}
@Override
public void addStudent(Student student)
{
String sql = "insert into Student (id, name, age) values (?, ?, ?)";
jdbcTemplate.update(sql, student.getId(), student.getName(), student.getAge());
}
@Override
public void updateStudent(Student student)
{
String sql = "update Student set name = ?, age = ? where id = ?";
jdbcTemplate.update(sql, student.getName(), student.getAge(), student.getId());
}
@Override
public void deleteStudent(int id)
{
String sql = "delete from Student where id = ?";
jdbcTemplate.update(sql, id);
}
}
- We have implemented the StudentDAO interface and overriden the methods of it.
- Using @Autowired annotation we have injected the jdbcTemplate, using which we will be querying the database.
StudentController.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 StudentController
{
@Autowired
StudentDAOImpl studentDAOImpl;
@GetMapping("/students")
public ResponseEntity<List<Student>> getAllStudents()
{
List<Student> studentList = studentDAOImpl.getAllStudents();
return new ResponseEntity<List<Student>>(studentList, HttpStatus.OK);
}
@GetMapping("/student/{id}")
public ResponseEntity<Student> getStudentById(@PathVariable("id") Integer id)
{
Student student = studentDAOImpl.getStudentById(id);
return new ResponseEntity<Student>(student, HttpStatus.OK);
}
@PutMapping("/student")
public ResponseEntity<Student> updateArticle(@RequestBody Student student)
{
studentDAOImpl.updateStudent(student);
return new ResponseEntity<Student>(student, HttpStatus.OK);
}
@PostMapping("/student")
public ResponseEntity<Void> addArticle(@RequestBody Student student)
{
studentDAOImpl.addStudent(student);
return new ResponseEntity<Void>(HttpStatus.NO_CONTENT);
}
@DeleteMapping("/student/{id}")
public ResponseEntity<Void> deleteArticle(@PathVariable("id") Integer id)
{
studentDAOImpl.deleteStudent(id);
return new ResponseEntity<Void>(HttpStatus.NO_CONTENT);
}
}
- We have annotated our “StudentController” 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.
StudentMapper.java
package com.javainterviewpoint;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
public class StudentMapper implements RowMapper<Student>
{
@Override
public Student mapRow(ResultSet row, int rowNum) throws SQLException
{
Student student = new Student();
student.setId(row.getInt("id"));
student.setName(row.getString("name"));
student.setAge(row.getInt("age"));
return student;
}
}
In our StudentMapper 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 StudentMapper while querying the database using JdbcTemplate
StudentApp.java
package com.javainterviewpoint;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class StudentApp
{
public static void main(String[] args)
{
SpringApplication.run(StudentApp.class, args);
}
}
The StudentApp class main() method is the triggering point of our application, it in-turn calls Spring Boot’s SpringApplication class run() method which bootstrap our StudentApp application and starts the tomcat server. We need to pass our StudentApp.class as an argument to our run() method.
Student.java
package com.javainterviewpoint;
public class Student
{
private int id;
private String name;
private int age;
public Student()
{
super();
}
public Student(int id, String name, int age)
{
super();
this.id = id;
this.name = name;
this.age = age;
}
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;
}
@Override
public String toString()
{
return "Student [id=" + id + ", name=" + name + ", age=" + age + "]";
}
}
Our Student class is a simple POJO consisting getters and setters of Student properties id, name, age.
Output:
Run the spring boot application using “mvn spring-boot:run”
Hit on the url using Postman to validate
StudentRestTemplateClient.java – RestTemplate
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 StudentRestTemplateClient
{
public static final String REST_BASE_URI = "http://localhost:8080";
static RestTemplate restTemplate = new RestTemplate();
/** POST **/
public static void createStudent()
{
Student student = new Student();
student.setId(4);
student.setName("JIP4");
student.setAge(44);
HttpHeaders headers = new HttpHeaders();
headers.setContentType(MediaType.APPLICATION_JSON);
// headers.setAccept(Arrays.asList(MediaType.APPLICATION_JSON));
HttpEntity entity = new HttpEntity<>(student, headers);
restTemplate.postForObject(REST_BASE_URI + "/student", entity, Student.class);
}
/** GET **/
private static void getStudent(int id)
{
Student student = restTemplate.getForObject(REST_BASE_URI + "/student/" + id, Student.class);
System.out.println("**** Student with id : " + id + "****");
System.out
.println("Id :" + student.getId() + " Name : " + student.getName() + " Age : " + student.getAge());
}
public static void getAllStudents()
{
List<Map<String, Object>> studentList = restTemplate.getForObject(REST_BASE_URI + "/students", List.class);
if (studentList != null)
{
System.out.println("**** All Students ****");
for (Map<String, Object> map : studentList)
{
System.out.println(
"Id : id=" + map.get("id") + " Name=" + map.get("name") + " Age=" + map.get("age"));
}
} else
{
System.out.println("No Students exist!!");
}
}
/** PUT **/
public static void updateStudent()
{
Student student = new Student();
student.setId(5);
student.setName("JIP555555");
student.setAge(55);
HttpHeaders headers = new HttpHeaders();
headers.setContentType(MediaType.APPLICATION_JSON);
HttpEntity entity = new HttpEntity<>(student, headers);
restTemplate.put(REST_BASE_URI + "/student", entity, Student.class);
}
/** DELETE **/
public static void deleteStudent(int id)
{
restTemplate.delete(REST_BASE_URI + "/student/" + id);
}
public static void main(String args[])
{
createStudent();
getAllStudents();
getStudent(2);
updateStudent();
deleteStudent(5);
}
}
Output :
21:47:51.543 [main] DEBUG org.springframework.web.client.RestTemplate - Created POST request for "http://localhost:8080/student" 21:47:51.618 [main] DEBUG org.springframework.web.client.RestTemplate - Setting request Accept header to [application/json, application/*+json] 21:47:51.646 [main] DEBUG org.springframework.web.client.RestTemplate - Writing [Student [id=4, name=JIP4, age=44]] as "application/json" using [org.springframework.http.converter.json.MappingJackson2HttpMessageConverter@1723e30] 21:47:51.809 [main] DEBUG org.springframework.web.client.RestTemplate - POST request for "http://localhost:8080/student" resulted in 204 (null) 21:47:51.812 [main] DEBUG org.springframework.web.client.RestTemplate - Created GET request for "http://localhost:8080/students" 21:47:51.833 [main] DEBUG org.springframework.web.client.RestTemplate - Setting request Accept header to [application/json, application/*+json] 21:47:52.002 [main] DEBUG org.springframework.web.client.RestTemplate - GET request for "http://localhost:8080/students" resulted in 200 (null) 21:47:52.002 [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@1723e30] **** All Students **** Id : id=1 Name=JIP1 Age=11 Id : id=2 Name=JIP222 Age=22222 Id : id=4 Name=JIP4 Age=44 21:47:52.029 [main] DEBUG org.springframework.web.client.RestTemplate - Created GET request for "http://localhost:8080/student/2" 21:47:52.029 [main] DEBUG org.springframework.web.client.RestTemplate - Setting request Accept header to [application/json, application/*+json] 21:47:52.041 [main] DEBUG org.springframework.web.client.RestTemplate - GET request for "http://localhost:8080/student/2" resulted in 200 (null) 21:47:52.041 [main] DEBUG org.springframework.web.client.RestTemplate - Reading [class com.javainterviewpoint.Student] as "application/json;charset=UTF-8" using [org.springframework.http.converter.json.MappingJackson2HttpMessageConverter@1723e30] **** Student with id : 2**** Id :2 Name : JIP222 Age : 22222 21:47:52.042 [main] DEBUG org.springframework.web.client.RestTemplate - Created PUT request for "http://localhost:8080/student" 21:47:52.043 [main] DEBUG org.springframework.web.client.RestTemplate - Writing [Student [id=5, name=JIP555555, age=55]] as "application/json" using [org.springframework.http.converter.json.MappingJackson2HttpMessageConverter@1723e30] 21:47:52.067 [main] DEBUG org.springframework.web.client.RestTemplate - PUT request for "http://localhost:8080/student" resulted in 200 (null) 21:47:52.068 [main] DEBUG org.springframework.web.client.RestTemplate - Created DELETE request for "http://localhost:8080/student/4" 21:47:52.123 [main] DEBUG org.springframework.web.client.RestTemplate - DELETE request for "http://localhost:8080/student/4" resulted in 204 (null)
PCD Pharma says
Awesome explanation. Very helpful.
Thank you.