This page will introduce an example of Spring Boot Tomcat JDBC connection pool.
Tomcat connection pool supports high concurrency environment with high performance.
Tomcat connection pooling is very simple because its implementation is very simple.
Tomcat connection pool has the ability to configure custom interceptors to better handle idle connections and perform asynchronous connection retrieval.
In Spring Boot 1.x, Tomcat connection pool is the default connection pool, but in Spring Boot 2.x, HikariCP is the default connection pool.
On this page, we will provide a complete example of Tomcat JDBC connection pool with Spring Boot Data and MySQL. We will create a demo application where we will perform create and read operations in the database.
We will configure Tomcat connection pool properties in the application.properties file, such as initialSize, maxWait, maxActive, maxIdle, minIdle, defaultAutoCommit, and testonmirror.
Sample tool version
- Java 9
- Spring 5.0.7.RELEASE
- Spring Boot 2.0.3.RELEASE
- Maven 3.5.2
- MySQL 5.5
- Eclipse Oxygen
Tomcat JDBC dependency
Before using Tomcat connection pool, we need to ensure that we have solved the dependency of Tomcat JDBC.
If we use Maven, we can use the following dependencies.
<dependency>
<groupId>org.apache.tomcat</groupId>
<artifactId>tomcat-jdbc</artifactId>
<version>9.0.8</version>
</dependency>
Tomcat JDBC is used for spring boot starter data JPA or spring boot starter JDBC.
To solve the problem of spring boot starter data JPA, we can use Maven dependency, as shown below.
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
To solve the spring boot starter JDBC problem, use the Maven dependency given below.
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
In the old version of Spring Boot 1.x before the release of Spring Boot 2.0, we do not need to include Tomcat JDBC in pom.xml or build.gradle, because spring boot starter JDBC and spring boot starter data JPA introduce it by default.
Tomcat connection pool configuration
For the Tomcat connection pool configuration, we enable it by using spring.datasource.type and specify the fully qualified name of the Tomcat connection pool implementation in the application.properties file, as shown below.
spring.datasource.type = org.apache.tomcat.jdbc.pool.DataSource
If we use Spring Boot version 1. X, Spring Boot will select Tomcat data source by default. We do not need to configure the above lines.
Now to configure Tomcat specific connection pool settings, Spring Boot provides the prefix spring.datasource.tomcat. *, which can be used in the application.properties file. We will discuss some frequently used configurations here.
1. initialSize
initialSize is the number of initial connections created when the pool is started. Its configuration is as follows.
spring.datasource.tomcat.initial-size=15
2. maxWait
maxWait is the maximum number of milliseconds that the pool waits to return a connection before throwing an exception. Its configuration is as follows.
spring.datasource.tomcat.max-wait=20000
3. maxActive
maxActive is the maximum number of active connections that the pool can allocate at the same time. Its configuration method is as follows.
spring.datasource.tomcat.max-active=50
4. maxIdle
maxIdle is the maximum number of connections that should remain in the pool at any time. Its configuration is as follows.
spring.datasource.tomcat.max-idle=15
5. minIdle
minIdle is the minimum number of connections that should remain in the pool at any time. Its configuration is as follows.
spring.datasource.tomcat.min-idle=8
6. defaultAutoCommit
The defaultAutoCommit property is used to configure the default autocommit state of connections created by this pool. It is a boolean type and the default value is true.
spring.datasource.tomcat.default-auto-commit=true
7. testOnBorrow
Testonmirror is a boolean type indicating whether the object will be verified before accessing the connection from the pool. The default is false.
spring.datasource.tomcat.test-on-borrow=false
Spring Boot Data + Tomcat JDBC Connection Pool + MySQL usage example
We will create a Spring Boot REST network service with Spring Boot Data, Tomcat JDBC connection pool and MySQL.
We will use CrudRepository to query the database.
We will also use RestTemplate to create a REST client to test our application.
First, find the project structure of the demo application.
Find the MySQL table structure used in our example.
MySQL Table: articles
CREATE TABLE `articles` ( `article_id` INT(5) NOT NULL AUTO_INCREMENT, `title` VARCHAR(200) NOT NULL, `category` VARCHAR(100) NOT NULL, PRIMARY KEY (`article_id`) ) COLLATE='latin1_swedish_ci' ENGINE=InnoDB;
Found dependencies in Maven file.
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<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.concretepage</groupId>
<artifactId>spring-boot-app</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>spring-boot-app</name>
<description>Spring Boot Application</description>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.3.RELEASE</version>
<relativePath/>
</parent>
<properties>
<java.version>9</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.5</version>
</dependency>
<dependency>
<groupId>org.apache.tomcat</groupId>
<artifactId>tomcat-jdbc</artifactId>
<version>9.0.8</version>
</dependency>
<dependency>
<groupId>javax.xml.bind</groupId>
<artifactId>jaxb-api</artifactId>
<version>2.3.0</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<optional>true</optional>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
application.properties
spring.datasource.url=jdbc:mysql://localhost:3306/concretepage spring.datasource.username=root spring.datasource.password=cp spring.datasource.type = org.apache.tomcat.jdbc.pool.DataSource spring.datasource.tomcat.initial-size=15 spring.datasource.tomcat.max-wait=20000 spring.datasource.tomcat.max-active=50 spring.datasource.tomcat.max-idle=15 spring.datasource.tomcat.min-idle=8 spring.datasource.tomcat.default-auto-commit=true spring.datasource.tomcat.test-on-borrow=false spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLDialect spring.jpa.properties.hibernate.id.new_generator_mappings=false spring.jpa.properties.hibernate.format_sql=true
ArticleRepository.java
package com.concretepage.repository;
import org.springframework.data.repository.CrudRepository;
import com.concretepage.entity.Article;
public interface ArticleRepository extends CrudRepository<Article, Long> {
}
Article.java
package com.concretepage.entity;
import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name="articles")
public class Article implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name="article_id")
private long articleId;
@Column(name="title")
private String title;
@Column(name="category")
private String category;
public long getArticleId() {
return articleId;
}
public void setArticleId(long articleId) {
this.articleId = articleId;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getCategory() {
return category;
}
public void setCategory(String category) {
this.category = category;
}
}
IArticleService.java
package com.concretepage.service;
import java.util.List;
import com.concretepage.entity.Article;
public interface IArticleService {
List<Article> getAllArticles();
void addArticle(Article article);
}
ArticleService.java
package com.concretepage.service;
import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.concretepage.entity.Article;
import com.concretepage.repository.ArticleRepository;
@Service
public class ArticleService implements IArticleService {
@Autowired
private ArticleRepository articleRepository;
@Override
public List<Article> getAllArticles(){
List<Article> list = new ArrayList<>();
articleRepository.findAll().forEach(e -> list.add(e));
return list;
}
@Override
public void addArticle(Article article){
articleRepository.save(article);
}
}
ArticleInfo.java
package com.concretepage.controller;
import com.fasterxml.jackson.annotation.JsonInclude;
import com.fasterxml.jackson.annotation.JsonInclude.Include;
public class ArticleInfo {
@JsonInclude(Include.NON_NULL)
private long articleId;
@JsonInclude(Include.NON_NULL)
private String title;
@JsonInclude(Include.NON_NULL)
private String category;
public long getArticleId() {
return articleId;
}
public void setArticleId(long articleId) {
this.articleId = articleId;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getCategory() {
return category;
}
public void setCategory(String category) {
this.category = category;
}
}
ArticleController.java
package com.concretepage.controller;
import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.util.UriComponentsBuilder;
import com.concretepage.entity.Article;
import com.concretepage.service.IArticleService;
@RestController
@RequestMapping("user")
public class ArticleController {
@Autowired
private IArticleService articleService;
//Fetches all articles
@GetMapping(value= "articles")
public ResponseEntity<List<ArticleInfo>> getAllArticles() {
List<ArticleInfo> responseArticleList = new ArrayList<>();
List<Article> articleList = articleService.getAllArticles();
for (int i = 0; i < articleList.size(); i++) {
ArticleInfo ob = new ArticleInfo();
BeanUtils.copyProperties(articleList.get(i), ob);
responseArticleList.add(ob);
}
return new ResponseEntity<List<ArticleInfo>>(responseArticleList, HttpStatus.OK);
}
//Creates a new article
@PostMapping(value= "article")
public ResponseEntity<Void> addArticle(@RequestBody ArticleInfo articleInfo, UriComponentsBuilder builder) {
Article article = new Article();
BeanUtils.copyProperties(articleInfo, article);
articleService.addArticle(article);
HttpHeaders headers = new HttpHeaders();
headers.setLocation(builder.path("/article/{id}").buildAndExpand(article.getArticleId()).toUri());
return new ResponseEntity<Void>(headers, HttpStatus.CREATED);
}
}
Now find the Main method of the Java class to run the application.
To ensure that we use the Tomcat JDBC connection pool, we are printing the data source name.
SpringBootAppStarter.java
package com.concretepage;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class SpringBootAppStarter implements CommandLineRunner {
@Autowired
DataSource dataSource;
public static void main(String[] args) throws Exception {
SpringApplication.run(SpringBootAppStarter.class, args);
}
@Override
public void run(String... args) throws Exception {
System.out.println("DataSource = " + dataSource);
}
}
When we start our application, we can see the following information in the console at the end of the server log.
output
DataSource = org.apache.tomcat.jdbc.pool.DataSource@5437ca1b{ConnectionPool[defaultAutoCommit=true;
defaultReadOnly=null; defaultTransactionIsolation=-1; defaultCatalog=null;
driverClassName=com.mysql.jdbc.Driver; maxActive=50; maxIdle=15; minIdle=8;
initialSize=15; maxWait=20000; testOnBorrow=false; testOnReturn=false;
timeBetweenEvictionRunsMillis=5000; numTestsPerEvictionRun=0; minEvictableIdleTimeMillis=60000;
testWhileIdle=false; testOnConnect=false; password=********; url=jdbc:mysql://localhost:3306/concretepage;
username=root; validationQuery=/* ping */ SELECT 1; validationQueryTimeout=-1;
validatorClassName=null; validationInterval=3000; accessToUnderlyingConnectionAllowed=true;
removeAbandoned=false; removeAbandonedTimeout=60; logAbandoned=false; connectionProperties=null;
initSQL=null; jdbcInterceptors=null; jmxEnabled=true; fairQueue=true; useEquals=true;
abandonWhenPercentageFull=0; maxAge=0; useLock=false; dataSource=null; dataSourceJNDI=null;
suspectTimeout=0; alternateUsernameAllowed=false; commitOnReturn=false; rollbackOnReturn=false;
useDisposableConnectionFacade=true; logValidationErrors=false; propagateInterruptState=false;
ignoreExceptionOnPreLoad=false; useStatementFacade=true; }
Now find the REST client to test the application.
RestClientUtil.java
package com.concretepage.client;
import java.net.URI;
import org.springframework.http.HttpEntity;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpMethod;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.client.RestTemplate;
import com.concretepage.entity.Article;
public class RestClientUtil {
public void getAllArticlesDemo() {
HttpHeaders headers = new HttpHeaders();
headers.setContentType(MediaType.APPLICATION_JSON);
RestTemplate restTemplate = new RestTemplate();
String url = "http://localhost:8080/user/articles";
HttpEntity<String> requestEntity = new HttpEntity<String>(headers);
ResponseEntity<Article[]> responseEntity = restTemplate.exchange(url, HttpMethod.GET, requestEntity, Article[].class);
Article[] articles = responseEntity.getBody();
for(Article article : articles) {
System.out.println("Id:"+article.getArticleId()+", Title:"+article.getTitle()
+", Category: "+article.getCategory());
}
}
public void addArticleDemo() {
HttpHeaders headers = new HttpHeaders();
headers.setContentType(MediaType.APPLICATION_JSON);
RestTemplate restTemplate = new RestTemplate();
String url = "http://localhost:8080/user/article";
Article objArticle = new Article();
objArticle.setTitle("Spring REST Security using Hibernate");
objArticle.setCategory("Spring");
HttpEntity<Article> requestEntity = new HttpEntity<Article>(objArticle, headers);
URI uri = restTemplate.postForLocation(url, requestEntity);
System.out.println(uri.getPath());
}
public static void main(String args[]) {
RestClientUtil util = new RestClientUtil();
util.addArticleDemo();
util.getAllArticlesDemo();
}
}
When we run the client, we will get the following output.
output
Id:1, Title:Spring REST Security using Hibernate, Category: Spring
Test application
To test the application, first create a table in MySQL according to the method given in the article, and configure your database credentials in the application.properties file. Then, we can run REST network services in the following ways.
1. Use the Maven command
Download the source code of the project. Use the command prompt to go to the root folder of the project and run the command.
mvn spring-boot:run
The Tomcat server will be started.
2. Use Eclipse
Use the download link at the end of the article to download the source code of the project. Import the project into eclipse. Using the command prompt, go to the root folder of the project and run.
mvn clean eclipse:eclipse
Then refresh the project in eclipse. Click Run as - > java application to run the main class SpringBootAppStarter. The Tomcat server will be started.
3. Use executable jars
Using the command prompt, go to the root folder of the project and run the command.
mvn clean package
We will get the executable spring-boot-app-0.0.1-SNAPSHOT.jar in the target folder. Run the JAR as follows
java -jar target/spring-boot-app-0.0.1-SNAPSHOT.jar
The Tomcat server will be started.
Now we are ready to test the application. To run the Web service client, enter the RestClientUtil class in eclipse and click Run as - > java application.
reference
[1]Spring Boot Reference Guide
[2]Tomcat JDBC Connection Pool
[3]Spring Boot Tomcat Connection Pool