Hikari Connection Pooling with a MySQL Backend, Hibernate and Maven

Conection Pooling?

JDBC connection pooling is a great concept, which improves the performance of database driven applications by reusing connections. The benefit from connection pools is that the cost of creating and closing connections is avoided, by reusing connections from a pool of available connections. Database systems such as MySQL also assign database resources by limiting simultaneous connections. This is another reason, why connection pools have benefits in contrast to opening and closing individual connections.

Dipping into Pools

There exists a selection of different JDBC compatible connection pools which can be used more or less interchangeable. The most widely used pools are:

Most of these pools work in a very similar way. In the following tutorial, we are going to take out HikariCP for a spin. It is simple to use and claims to be very fast. In the following we are going to setup a small project using the following technologies:

  • Java 8
  • Tomcat 8
  • MySQL 5.7
  • Maven 3
  • Hibernate 5

and of course an IDE of your choice (I have become quite fond of IntelliJ IDEA Community Edition).

Project Overview

In this small demo project, we are going to write a minimalistic Web application, which simply computes a new random number for each request and stores the result in a database table. We use Java and store the data by using the Hibernate ORM framework.We also assume, that you have a running Apache Tomcat Servlet Container and also a running MySQL instance available.

In the first step, I created a basic Web project by selecting the Maven Webapp archetype, which then creates a basic structure we can work with.

hikari

Adding the Required Libraries

After we created the initial project, we need to add the required libraries. We can achieve this easily with Maven, by adding the dependency definitions to our pom.xml file. You can find these definitions at maven central. The build block contains the plugin for deploying the application at the Tomcat server.

Now we have all the libraries we need available and we can begin with implementing the functionality.

The Database Table

As we want to persist random numbers, we need to have a database table, which will store the data. Create the following table in MySQL and ensure that you have a test user available:

POJO Mojo: The Java Class to be Persisted

Hibernate allows us to persist Java objects in the database, by annotating the Java source code. The following Java class is used to store the random numbers that we generate.

The code and also the annotations are straight forward. Now we need to define a way how we can connect to the database and let Hibernate handle the mapping between the Java class and the database schema we defined before.

Hibernate Configuration

Hibernate looks for the configuration in a file called hibernate.cfg.xml by default. This file is used to provide the connection details for the database.

The file above contains the most essential settings. We specify the database dialect that we speak ( org.hibernate.dialect.MySQLDialect ), define the connection provider class (the Hikari CP) with com.zaxxer.hikari.hibernate.HikariConnectionProvider and provide the URL to our MySQL database ( jdbc:mysql://localhost:3306/TestDB?useSSL=false) including the username and password for the database connection. Alternatively, you can also define the same information in the hibernate.properties file.

The Session Factory

We need to have a session factory, which initializes the database connection and the connection pool as well as handles the interaction with the database server. We can use the following class, which provides the session object for these tasks.

This class provides two so called contexts, where the session gets initialized and a second one where it gets destroyed. The Tomcat Servlet container automatically calls these depending on the state of the session. You can see that the filename of the configuration file is provided ( configuration.configure("hibernate.cfg.xml");) and that we tell Hibernate, to map our RandomNumberPOJO file ( configuration.addAnnotatedClass(RandomNumberPOJO.class);). Now all that is missing is the Web component, which is waiting for our requests.

The Web Component

The last part is the Web component, which we kept as simple as possible.

This class provides the actual servlet and is executed whenever a user calls the web application. First, a new RandumNumberPOJO object is instantiated and persisted. We then count how many numbers we already have and then we fetch a list of all existing records.

The last step before we can actually run the application is the definition of the web entry points, which we can define in the file called web.xml. This file is already generated by the maven achetype and we only need to add a name for our small web service and provide a mapping for the entry class.

Compile and Run

We can then  compile and deploy the application with the following command:

This will compile and upload the application to the Tomcat server and we can then use our browser, open the URL  http://localhost:8080/testapp/hello  to create and persist random numbers by refreshing the page. The result will look similar like this:

hello

 

One thought on “Hikari Connection Pooling with a MySQL Backend, Hibernate and Maven

  1. Hi,

    This tutorial is nice. But since i work more with Spring Boot with all Java Configuration. Could you please give tutorials on that. Will be very helpful.

Leave a Reply

Your email address will not be published. Required fields are marked *