Spring HikariCP

last modified July 13, 2020

In this tutorial, we show how to use HikariCP connection pool in a classic Spring application. In the application, we connect to the MySQL database with Spring JdbcTemplate. We use FreeMarker as template engine. The application is deployed on Tomcat server.

Spring is a popular Java application framework for developing enterprise applications in Java. It is also a very good integration system that helps glue together various enterprise components.

HikariCP is solid high-performance JDBC connection pool. A connection pool is a cache of database connections maintained by the database system for reusing connections when future requests to the database are required. With a connection pool we can significantly reduce the overall resource usage.

JdbcTemplate is a Spring library that helps programmers create applications that work with relational databases and JDBC. It takes care of many tedious and error-prone low-level details such as handling transactions, cleaning up resources, and correctly handling exceptions. JdbcTemplate is shipped in Spring's spring-jdbc module.

-- SQL for the Cars table

START TRANSACTION;
DROP TABLE IF EXISTS Cars;

CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name VARCHAR(50), Price INTEGER);
INSERT INTO Cars VALUES(1, 'Audi', 52642);
INSERT INTO Cars VALUES(2, 'Mercedes', 57127);
INSERT INTO Cars VALUES(3, 'Skoda', 9000);
INSERT INTO Cars VALUES(4, 'Volvo', 29000);
INSERT INTO Cars VALUES(5, 'Bentley', 350000);
INSERT INTO Cars VALUES(6, 'Citroen', 21000);
INSERT INTO Cars VALUES(7, 'Hummer', 41400);
INSERT INTO Cars VALUES(8, 'Volkswagen', 21600);
COMMIT;

In the code example, we use this table.

mysql> source cars.sql

With the mysql command line tool and its source command, we create the Cars table. The MySQL tutorial provides more information about how to set up and use MySQL database.

├── pom.xml
└── src
    ├── main
    │   ├── java
    │   │   └── com
    │   │       └── zetcode
    │   │           ├── bean
    │   │           │   └── Car.java
    │   │           ├── service
    │   │           │   └── CarService.java
    │   │           └── web
    │   │              └── MyController.java
    │   ├── resources
    │   │   └── application-context.xml
    │   └── webapp
    │       ├── META-INF
    │       │   └── context.xml
    │       └── WEB-INF
    │           ├── spring-servlet.xml
    │           ├── views
    │           │   ├── allCars.ftl
    │           │   └── index.ftl
    │           └── web.xml
    └── test
        └── java

This is the project structure.

<?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.zetcode</groupId>
    <artifactId>SpringJdbcTemplateWebEx</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>war</packaging>

    <name>SpringJdbcTemplateWebEx</name>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>        
        <spring-version>4.3.7.RELEASE</spring-version>
    </properties>
    
    <dependencies>
        
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.40</version>
        </dependency>              
        
        <dependency>
            <groupId>org.freemarker</groupId>
            <artifactId>freemarker</artifactId>
            <version>2.3.25-incubating</version>
        </dependency>   
        
         <!--Needed for freemarker FreeMarkerConfigurer--> 
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context-support</artifactId>
            <version>${spring-version}</version>
        </dependency>                
        
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>2.5.1</version>
        </dependency>      
        
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-simple</artifactId>
            <version>1.7.22</version>
        </dependency>          
        
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>${spring-version}</version>
        </dependency>         
        
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring-version}</version>
        </dependency>              
        
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-war-plugin</artifactId>
                <version>2.3</version>
                <configuration>
                    <failOnMissingWebXml>false</failOnMissingWebXml>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

In this Maven build file, we provide dependencies for the core of the Spring application, HikariCP connection pool, FreeMarker template engine, JdbcTemplate library, and MySQL driver.

<?xml version="1.0" encoding="UTF-8"?>

<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee 
                 http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
         version="3.1">
    
    <servlet>
        <servlet-name>spring</servlet-name>
        <servlet-class>
            org.springframework.web.servlet.DispatcherServlet
        </servlet-class>
        <load-on-startup>1</load-on-startup>
    </servlet>
    
    <servlet-mapping>
        <servlet-name>spring</servlet-name>
        <url-pattern>*.html</url-pattern>
    </servlet-mapping>    
    
    <session-config>
        <session-timeout>
            30
        </session-timeout>
    </session-config>
</web-app>

In the web.xml file, we set up the Spring DispatcherServlet. DispatcherServlet is a central dispatcher for HTTP request handlers.

<?xml version="1.0" encoding="UTF-8"?>
<Context path="/SpringJdbcTemplateWebEx">

    <Resource name="jdbc/myDs" auth="Container"
              factory="com.zaxxer.hikari.HikariJNDIFactory"
              dataSourceClassName="com.mysql.jdbc.jdbc2.optional.MysqlDataSource"
              dataSource.url="jdbc:mysql://localhost/testdb?useSSL=false"
              type="javax.sql.DataSource"
              minimumIdle="5" 
              maximumPoolSize="10"
              connectionTimeout="300000"
              database="testdb"
              server="localhost"
              dataSource.user="testuser"
              dataSource.password="test623"
              dataSource.cachePrepStmts="true"
              dataSource.prepStmtCacheSize="250"
              dataSource.prepStmtCacheSqlLimit="2048"
              closeMethod="close"
    />
    
</Context>

Tomcat's context.xml file contains the data source definition. The data source uses HikariCP connection pool.

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:mvc="http://www.springframework.org/schema/mvc"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd
   http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
   http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

    <context:component-scan base-package="com.zetcode" />
    <import resource="classpath:application-context.xml" />
    
    <bean id="freemarkerConfig" class="org.springframework.web.servlet.view.freemarker.FreeMarkerConfigurer">
        <property name="templateLoaderPath" value="/WEB-INF/views/"/>
    </bean>

    <bean id="viewResolver" class="org.springframework.web.servlet.view.freemarker.FreeMarkerViewResolver">
        <property name="cache" value="true"/>
        <property name="prefix" value=""/>
        <property name="suffix" value=".ftl"/>
    </bean>   
                
</beans>

In the spring servlet context XML file we define two beans: freemarkerConfig and viewResolver. These are configuration beans for FreeMarker. The spring-servlet.xml is located in the WEB-INF subdirectory.

<context:component-scan base-package="com.zetcode" />

We enable component scanning for com.zetcode package.

<import resource="classpath:application-context.xml" />

We import another context file, called application-context.xml. It is located on the classpath—in the src/main/resources directory.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans 
                          http://www.springframework.org/schema/beans/spring-beans.xsd">

    <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
        <property name="jndiName" value="java:comp/env/jdbc/myDs"/>
    </bean>    
    
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>    

</beans>

In the application-context.xml we define two beans: dataSource and jdbcTemplate.

package com.zetcode.bean;

public class Car {
    
    private int Id;
    private String Name;
    private int Price;

    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 getPrice() {
        return Price;
    }

    public void setPrice(int Price) {
        this.Price = Price;
    }

    @Override
    public String toString() {
        return "Car{" + "Id=" + Id + ", Name=" + 
                Name + ", Price=" + Price + '}';
    }
}

This is a Car class. A row from the database table will be mapped to this class.

package com.zetcode.service;

import com.zetcode.bean.Car;
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.Service;

@Service
public class CarService {

    @Autowired
    public JdbcTemplate jdbcTemplate;

    public List<Car> getAllCars() {

        String sql = "SELECT * FROM Cars";

        List<Car> cars = jdbcTemplate.query(sql, 
                new BeanPropertyRowMapper(Car.class));
        return cars;
    }
}

The CarService is a service class that contains a method to retrieve all cars from the database.

@Autowired
public JdbcTemplate jdbcTemplate;

The JdbcTemplate is injected with the @Autowired annotation.

List<Car> cars = jdbcTemplate.query(sql, 
        new BeanPropertyRowMapper(Car.class));

With the JdbcTemplate's query method we execute the SQL query. The result object is mapped to the Car object using the BeanPropertyRowMapper.

package com.zetcode.web;

import com.zetcode.bean.Car;
import com.zetcode.service.CarService;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;

@Controller
public class MyController {

    @Autowired
    private CarService carService;

    @RequestMapping("/index")
    public String index(Model model) {

        return "index";
    }

    @RequestMapping(value = "/all", method = RequestMethod.GET)
    public ModelAndView all() {

        List<Car> cars = carService.getAllCars();

        ModelAndView model = new ModelAndView("allCars");
        model.addObject("cars", cars);

        return model;
    }
}

MyController is a controller class. It has mappings for two request URLs: /index and /all.

@Autowired
private CarService carService;

The CarService is injected.

@RequestMapping("/index")
public String index(Model model) {

    return "index";
}

This request is resolved by returning index.ftl file. The views are located in the WEB-INF/views directory.

@RequestMapping(value = "/all", method = RequestMethod.GET)
public ModelAndView all() {

    List<Car> cars = carService.getAllCars();

    ModelAndView model = new ModelAndView("allCars");
    model.addObject("cars", cars);

    return model;
}

Here we call the getAllCars method of the CarService and create a ModelAndView object. The retrieved data is sent to the allCars.ftl template.

<!DOCTYPE html>
<html>
    <head>
        <title>Home page</title>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
    </head>
    <body>
        <p>Showing <a href="all.html">all cars</a></p>
    </body>
</html>

This is the index.ftl file.

<!DOCTYPE html>
<html>
    <head>
        <title>Cars</title>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        </head>
    <body>
        
        <table>
            <tr>
                <th>Id</th>  
                <th>Name</th>  
                <th>Price</th>
            </tr>        

            <#list cars as car>
                <tr>
                    <td>${car.id}</td> 
                    <td>${car.name}</td> 
                    <td>${car.price}</td>
                </tr>
            </#list>        
        </table>                
    </body>
</html>

This template file processes data sent from the database.

<#list cars as car>

The #list directive lists a collection of data.

The data from the MySQL database is displayed in Opera browser.

In this tutorial, we have created a classic Spring application that executed SQL statements with JdbcTemplate against MySQL database. We used HikariCP connection pool. The Spring application was using FreeMarker template engine and was deployed on Tomcat server.