In this Spring Boot Tomcat JDBC Connection Pool Example, we will learn how to implement Tomcat JDBC Connection Pool in a Spring Boot application. The Tomcat JDBC Connection Pool is an alternative to Apache Commons DBCP connection pool. Tomcat JDBC Connection Pool is extremely simple due to the very simplified implementation, the line count and source file count are very low when compared with other Connection Pooling libraries.
Whenever we use spring-boot-starter-jdbc module then it implicitly pulls the tomcat-jdbc-{version}.jar which is used to configure the DataSource bean.
Our Restful Web Service does the below
- Create Student (POST) : Create a new Student (/student)
- Get Student By Id (GET) : Get a Student based on id (/student/1)
- List of All Students (GET) : Get all the students (/students)
- Update Student (PUT) : Update a Student (/student)
- Delete Student (DELETE) : Delete a Student (/student/1)
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" NUMBER(10,0) NOT NULL ENABLE,
"NAME" VARCHAR2(255 CHAR),
"AGE" NUMBER(10,0) NOT NULL ENABLE,
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) “SpringBootTomcatJDBC” 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>SpringBootTomcatJDBC</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <name>SpringBootTomcatJDBC</name> <url>http://maven.apache.org</url> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <oracle.connector.version>11.2.0</oracle.connector.version> </properties> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>1.5.9.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> </dependency> <!-- Oracle Dependency --> <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc14</artifactId> <version>${oracle.connector.version}</version> </dependency> <!-- <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>3.8.1</version> <scope>test</scope> </dependency> --> </dependencies> <build> <finalName>SpringBootTomcatJDBC</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 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=oracle.jdbc.driver.OracleDriver spring.datasource.url=jdbc:oracle:thin:@rsh2:40051:dev spring.datasource.username=root spring.datasource.password=root # Tomcat JDBC settings spring.datasource.tomcat.initial-size=10 spring.datasource.tomcat.max-active=50 spring.datasource.tomcat.max-idle=5 spring.datasource.tomcat.max-wait=2000 spring.datasource.tomcat.test-on-connect=true spring.datasource.tomcat.test-on-borrow=true spring.datasource.tomcat.test-on-return=true
Spring Boot Tomcat JDBC Connection Pool Example
Dependency Tree
[INFO] com.javainterviewpoint:SpringBootConnectionPool:jar:0.0.1-SNAPSHOT [INFO] ------------------------------------------------------------------------ [INFO] Building SpringBootTomcatJDBC 0.0.1-SNAPSHOT [INFO] ------------------------------------------------------------------------ [INFO] [INFO] --- maven-dependency-plugin:2.10:tree (default-cli) @ SpringBootTomcatJDBC --- [INFO] com.javainteriviewpoint:SpringBootTomcatJDBC:jar:0.0.1-SNAPSHOT [INFO] +- org.springframework.boot:spring-boot-starter:jar:1.5.9.RELEASE:compile [INFO] | +- org.springframework.boot:spring-boot:jar:1.5.9.RELEASE:compile [INFO] | | \- org.springframework:spring-context:jar:4.3.13.RELEASE:compile [INFO] | +- org.springframework.boot:spring-boot-autoconfigure:jar:1.5.9.RELEASE:compile [INFO] | +- org.springframework.boot:spring-boot-starter-logging:jar:1.5.9.RELEASE:compile [INFO] | | +- ch.qos.logback:logback-classic:jar:1.1.11:compile [INFO] | | | +- ch.qos.logback:logback-core:jar:1.1.11:compile [INFO] | | | \- org.slf4j:slf4j-api:jar:1.7.25:compile [INFO] | | +- org.slf4j:jcl-over-slf4j:jar:1.7.25:compile [INFO] | | +- org.slf4j:jul-to-slf4j:jar:1.7.25:compile [INFO] | | \- org.slf4j:log4j-over-slf4j:jar:1.7.25:compile [INFO] | +- org.springframework:spring-core:jar:4.3.13.RELEASE:compile [INFO] | \- org.yaml:snakeyaml:jar:1.17:runtime [INFO] +- org.springframework.boot:spring-boot-starter-web:jar:1.5.9.RELEASE:compile [INFO] | +- org.springframework.boot:spring-boot-starter-tomcat:jar:1.5.9.RELEASE:compile [INFO] | | +- org.apache.tomcat.embed:tomcat-embed-core:jar:8.5.23:compile [INFO] | | | \- org.apache.tomcat:tomcat-annotations-api:jar:8.5.23:compile [INFO] | | +- org.apache.tomcat.embed:tomcat-embed-el:jar:8.5.23:compile [INFO] | | \- org.apache.tomcat.embed:tomcat-embed-websocket:jar:8.5.23:compile [INFO] | +- org.hibernate:hibernate-validator:jar:5.3.6.Final:compile [INFO] | | +- javax.validation:validation-api:jar:1.1.0.Final:compile [INFO] | | +- org.jboss.logging:jboss-logging:jar:3.3.1.Final:compile [INFO] | | \- com.fasterxml:classmate:jar:1.3.4:compile [INFO] | +- com.fasterxml.jackson.core:jackson-databind:jar:2.8.10:compile [INFO] | | +- com.fasterxml.jackson.core:jackson-annotations:jar:2.8.0:compile [INFO] | | \- com.fasterxml.jackson.core:jackson-core:jar:2.8.10:compile [INFO] | +- org.springframework:spring-web:jar:4.3.13.RELEASE:compile [INFO] | | +- org.springframework:spring-aop:jar:4.3.13.RELEASE:compile [INFO] | | \- org.springframework:spring-beans:jar:4.3.13.RELEASE:compile [INFO] | \- org.springframework:spring-webmvc:jar:4.3.13.RELEASE:compile [INFO] | \- org.springframework:spring-expression:jar:4.3.13.RELEASE:compile [INFO] +- org.springframework.boot:spring-boot-starter-jdbc:jar:1.5.9.RELEASE:compile [INFO] | +- org.apache.tomcat:tomcat-jdbc:jar:8.5.23:compile [INFO] | | \- org.apache.tomcat:tomcat-juli:jar:8.5.23:compile [INFO] | \- org.springframework:spring-jdbc:jar:4.3.13.RELEASE:compile [INFO] | \- org.springframework:spring-tx:jar:4.3.13.RELEASE:compile [INFO] \- com.oracle:ojdbc14:jar:11.2.0: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 override 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
Spring RestTemplate communicates with the HTTP server using REST principals. It uses HTTP verbs such as GET, POST, HEAD, PUT, DELETE etc. RestTemplate provides different methods to communicate that will accept URI template, URI variables, response type and request object as arguments.
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 :
16:29:41.159 [main] DEBUG org.springframework.web.client.RestTemplate - Created POST request for "http://localhost:8080/student" 16:29:41.227 [main] DEBUG org.springframework.web.client.RestTemplate - Setting request Accept header to [application/json, application/*+json] 16:29:41.258 [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@91e32e] 16:29:41.402 [main] DEBUG org.springframework.web.client.RestTemplate - POST request for "http://localhost:8080/student" resulted in 204 (null) 16:29:41.404 [main] DEBUG org.springframework.web.client.RestTemplate - Created GET request for "http://localhost:8080/students" 16:29:41.446 [main] DEBUG org.springframework.web.client.RestTemplate - Setting request Accept header to [application/json, application/*+json] 16:29:41.460 [main] DEBUG org.springframework.web.client.RestTemplate - GET request for "http://localhost:8080/students" resulted in 200 (null) 16:29:41.460 [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@91e32e] **** All Students **** Id : id=1 Name=JIP1 Age=11 Id : id=2 Name=JIP2 Age=22 Id : id=3 Name=JIP3 Age=33 Id : id=4 Name=JIP4 Age=44 16:29:41.490 [main] DEBUG org.springframework.web.client.RestTemplate - Created GET request for "http://localhost:8080/student/2" 16:29:41.490 [main] DEBUG org.springframework.web.client.RestTemplate - Setting request Accept header to [application/json, application/*+json] 16:29:41.496 [main] DEBUG org.springframework.web.client.RestTemplate - GET request for "http://localhost:8080/student/2" resulted in 200 (null) 16:29:41.497 [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@91e32e] **** Student with id : 2**** Id :2 Name : JIP2 Age : 22 16:29:41.498 [main] DEBUG org.springframework.web.client.RestTemplate - Created PUT request for "http://localhost:8080/student" 16:29:41.499 [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@91e32e] 16:29:41.532 [main] DEBUG org.springframework.web.client.RestTemplate - PUT request for "http://localhost:8080/student" resulted in 200 (null) 16:29:41.533 [main] DEBUG org.springframework.web.client.RestTemplate - Created DELETE request for "http://localhost:8080/student/5" 16:29:41.540 [main] DEBUG org.springframework.web.client.RestTemplate - DELETE request for "http://localhost:8080/student/5" resulted in 204 (null)
Leave a Reply