Spring Boot Tomcat Connection Pool

By Arvind Rai, June 19, 2018

This page will walk through Spring Boot Tomcat JDBC connection pool example. Tomcat connection pool supports for highly concurrent environments with high performance. Tomcat connection pool is extremely simple because of its very simplified implementation. Tomcat connection pool has ability to configure custom interceptors, better idle connection handling and performs asynchronous connection retrieval. In Spring Boot 1.x, Tomcat connection pool was the default connection pool but in Spring Boot 2.x HikariCP is the default connection pool. Here on this page we will provide complete example of Tomcat JDBC connection pool with Spring Boot Data and MySQL. We will create a demo application in which we will perform create and read operation in database. We will configure Tomcat connection pool properties for example initialSize, maxWait, maxActive, maxIdle, minIdle, defaultAutoCommit and testOnBorrow in application.properties file.

Technologies Used
Tomcat JDBC Dependency
Tomcat Connection Pool Configurations
Spring Boot Data + Tomcat JDBC Connection Pool + MySQL Example
Test Application
References
Download Source Code

Technologies Used

Find the technologies being used in our example.
1. Java 9
2. Spring 5.0.7.RELEASE
3. Spring Boot 2.0.3.RELEASE
4. Maven 3.5.2
5. MySQL 5.5
6. Eclipse Oxygen

Tomcat JDBC Dependency

Before using Tomcat connection pool, we need to make sure that we have resolved the Tomcat JDBC dependency. If we are using Maven we can use following dependency.

<dependency>
     <groupId>org.apache.tomcat</groupId>
     <artifactId>tomcat-jdbc</artifactId>
     <version>9.0.8</version>
</dependency>

tomcat-jdbc is used with either spring-boot-starter-data-jpa or spring-boot-starter-jdbc. To resolve spring-boot-starter-data-jpa we can use Maven dependency as given below.

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

To resolve spring-boot-starter-jdbc, use Maven dependency as given below.

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

In the older version of Spring Boot 1.x before Spring Boot 2.0 release, we need not to include tomcat-jdbc in pom.xml or build.gradle ourselves because spring-boot-starter-jdbc and spring-boot-starter-data-jpa resolve it by default.

Tomcat Connection Pool Configurations

For the Tomcat connection pool configuration, we enable it by using spring.datasource.type and assigning it fully qualified name of the Tomcat connection pool implementation in application.properties file as following.

spring.datasource.type = org.apache.tomcat.jdbc.pool.DataSource

If we are using Spring Boot 1.x versions, Spring Boot selects Tomcat datasource by default and we need not to configure above line.
Now to configure Tomcat specific connection pool settings, Spring Boot provides spring.datasource.tomcat.* prefix to be used in application.properties file. We will discuss here some frequently used configurations.
1. initialSize
initialSize is the initial number of connections that are created when the pool is started. It is configured as following.

spring.datasource.tomcat.initial-size=15

2. maxWait
maxWait is the maximum number of milliseconds that a pool will wait to return a connection before throwing an exception. It is configured as following.

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. It is configured as following.

spring.datasource.tomcat.max-active=50

4. maxIdle
maxIdle is the maximum number of connections that should be kept in the pool at all times. It is configured as following.

spring.datasource.tomcat.max-idle=15

5. minIdle
minIdle is the minimum number of connections that should be kept in the pool at all times. It is configured as following.

spring.datasource.tomcat.min-idle=8

6. defaultAutoCommit
defaultAutoCommit property configures the default auto-commit state of connections created by this pool. It is boolean and default value is true.

spring.datasource.tomcat.default-auto-commit=true

7. testOnBorrow
testOnBorrow is the boolean value that indicates whether objects will be validated before being borrowed from the pool. Default is false.

spring.datasource.tomcat.test-on-borrow=false

Spring Boot Data + Tomcat JDBC Connection Pool + MySQL Example

We will create a Spring Boot REST web service with Spring Boot Data, Tomcat JDBC Connection Pool and MySQL. We will use CrudRepository to query database. We will also create a REST client using RestTemplate 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;

Find the Maven file to resolve dependencies.
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 Java class to run the application. To ensure that we are using Tomcat JDBC connection pool, we are printing datasource 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 following message in console at the end of 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 following output.
Output

Id:1, Title:Spring REST Security using Hibernate, Category: Spring

Test Application

To test the application, first create table in MySQL as given in the article and configure your database credentials in application.properties file. Then we can run REST web service in following ways.
1. Using Maven Command: Download the project source code. Go to the root folder of the project using command prompt and run the command.

mvn spring-boot:run

Tomcat server will be started.

2. Using Eclipse: Download the project source code using the download link given at the end of article. Import the project into eclipse. Using command prompt, go to the root folder of the project and run.

mvn clean eclipse:eclipse

and then refresh the project in eclipse. Run Main class SpringBootAppStarter by clicking Run as -> Java Application. Tomcat server will be started.

3. Using Executable JAR: Using command prompt, go to the root folder of the project and run the command.

mvn clean package

We will get executable JAR spring-boot-app-0.0.1-SNAPSHOT.jar in target folder. Run this JAR as

java -jar target/spring-boot-app-0.0.1-SNAPSHOT.jar

Tomcat server will be started.

Now we are ready to test the application. To run web service client, go to the RestClientUtil class in eclipse and click on Run as -> Java Application.