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.

<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/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>at.stefanproell</groupId>
  <artifactId>HibernateHikari</artifactId>
  <packaging>war</packaging>
  <version>1.0-SNAPSHOT</version>
  <name>HibernateHikari Maven Webapp</name>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
      <dependency>
          <groupId>org.apache.tomcat</groupId>
          <artifactId>tomcat-servlet-api</artifactId>
          <version>7.0.50</version>
      </dependency>
      <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <version>5.1.39</version>
      </dependency>
      <dependency>
          <groupId>org.hibernate</groupId>
          <artifactId>hibernate-core</artifactId>
          <version>5.2.0.Final</version>
      </dependency>
      <dependency>
          <groupId>com.zaxxer</groupId>
          <artifactId>HikariCP</artifactId>
          <version>2.4.6</version>
      </dependency>
  </dependencies>
    
  <build>
    <finalName>HibernateHikari</finalName>
      <plugins>
          <plugin>
              <groupId>org.apache.tomcat.maven</groupId>
              <artifactId>tomcat7-maven-plugin</artifactId>
              <version>2.0</version>
              <configuration>
                  <path>/testapp</path>
                  <update>true</update>

                  <url>http://localhost:8080/manager/text</url>
                  <username>admin</username>
                  <password>admin</password>

              </configuration>

          </plugin>
          <plugin>
              <groupId>org.apache.maven.plugins</groupId>
              <artifactId>maven-war-plugin</artifactId>
              <version>2.4</version>

          </plugin>
      </plugins>
  </build>
</project>

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:

CREATE TABLE `TestDB`.`RandomNumberTable` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `randomNumber` INT NOT NULL,
  PRIMARY KEY (`id`));

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.

@Entity
@Table(name="RandomNumberTable",
        uniqueConstraints={@UniqueConstraint(columnNames={"id"})})
public class RandomNumberPOJO {
    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    @Column(name="id", nullable=false, unique=true, length=11)
    private int id;

    @Column(name="randomNumber", nullable=false)
    private int randomNumber;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getRandomNumber() {
        return randomNumber;
    }

    public void setRandomNumber(int randomNumber) {
        this.randomNumber = randomNumber;
    }
}

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.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>

        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
        <property name="hibernate.connection.provider_class">com.zaxxer.hikari.hibernate.HikariConnectionProvider</property>
        <property name="hibernate.hikari.dataSource.url">jdbc:mysql://localhost:3306/TestDB?useSSL=false</property>
        <property name="hibernate.hikari.dataSource.user">testuser</property>
        <property name="hibernate.hikari.dataSource.password">sEcRet</property>
        <property name="hibernate.hikari.dataSourceClassName">com.mysql.jdbc.jdbc2.optional.MysqlDataSource</property>
        <property name="hibernate.hikari.dataSource.cachePrepStmts">true</property>
        <property name="hibernate.hikari.dataSource.prepStmtCacheSize">250</property>
        <property name="hibernate.hikari.dataSource.prepStmtCacheSqlLimit">2048</property>
        <property name="hibernate.hikari.dataSource.useServerPrepStmts">true</property>
        <property name="hibernate.current_session_context_class">thread</property>

    </session-factory>
</hibernate-configuration>

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.

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.servlet.annotation.WebListener;

import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
import org.jboss.logging.Logger;

@WebListener
public class HibernateSessionFactoryListener implements ServletContextListener {

    public final Logger logger = Logger.getLogger(HibernateSessionFactoryListener.class);

    public void contextDestroyed(ServletContextEvent servletContextEvent) {
        SessionFactory sessionFactory = (SessionFactory) servletContextEvent.getServletContext().getAttribute("SessionFactory");
        if(sessionFactory != null && !sessionFactory.isClosed()){
            logger.info("Closing sessionFactory");
            sessionFactory.close();
        }
        logger.info("Released Hibernate sessionFactory resource");
    }

    public void contextInitialized(ServletContextEvent servletContextEvent) {
        Configuration configuration = new Configuration();
        configuration.configure("hibernate.cfg.xml");
        // Add annotated class
        configuration.addAnnotatedClass(RandomNumberPOJO.class);

        ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder().applySettings(configuration.getProperties()).build();
        logger.info("ServiceRegistry created successfully");
        SessionFactory sessionFactory = configuration
                .buildSessionFactory(serviceRegistry);
        logger.info("SessionFactory created successfully");

        servletContextEvent.getServletContext().setAttribute("SessionFactory", sessionFactory);
        logger.info("Hibernate SessionFactory Configured successfully");
    }

}

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.

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import javax.persistence.TypedQuery;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import java.io.IOException;
import java.io.PrintWriter;

import java.util.List;
import java.util.Random;


public class HelloServlet extends HttpServlet {
    public void doGet (HttpServletRequest req,
                       HttpServletResponse res)
            throws ServletException, IOException
    {
        PrintWriter out = res.getWriter();
        addRandomNumber(req);
        out.println("There are " + countNumbers(req) + " random numbers");


        List<RandomNumberPOJO> numbers = getAllRandomNumbers(req,res);

        out.println("Random Numbers:");
        out.println("----------");

        for(RandomNumberPOJO record:numbers){
            out.println("ID: " + record.getId() + "\t :\t" + record.getRandomNumber());
        }

        out.close();

    }

    /**
     * Create a new random number and store it the database
     * @param request
     */
    private void addRandomNumber(HttpServletRequest request){
        SessionFactory sessionFactory = (SessionFactory) request.getServletContext().getAttribute("SessionFactory");

        Session session = sessionFactory.getCurrentSession();
        Transaction tx = session.beginTransaction();
        RandomNumberPOJO randomNumber = new RandomNumberPOJO();
        Random rand = new Random();
        int randomInteger = 1 + rand.nextInt((999) + 1);

        randomNumber.setRandomNumber(randomInteger);
        session.save(randomNumber);
        tx.commit();
        session.close();





    }

    /**
     * Get a list of all RandomNumberPOJO objects
     * @param request
     * @param response
     * @return
     */
    private List<RandomNumberPOJO> getAllRandomNumbers(HttpServletRequest request, HttpServletResponse response){
        SessionFactory sessionFactory = (SessionFactory) request.getServletContext().getAttribute("SessionFactory");
        Session session = sessionFactory.getCurrentSession();
        Transaction tx = session.beginTransaction();
        TypedQuery<RandomNumberPOJO> query = session.createQuery(
                "from RandomNumberPOJO", RandomNumberPOJO.class);

        List<RandomNumberPOJO> numbers =query.getResultList();



        tx.commit();
        session.close();

        return numbers;


    }

    /**
     * Count records
     * @param request
     * @return
     */
    private int countNumbers(HttpServletRequest request){
        SessionFactory sessionFactory = (SessionFactory) request.getServletContext().getAttribute("SessionFactory");
        Session session = sessionFactory.getCurrentSession();
        Transaction tx = session.beginTransaction();

        String count = session.createQuery("SELECT COUNT(id) FROM RandomNumberPOJO").uniqueResult().toString();

        int rowCount = Integer.parseInt(count);

        tx.commit();
        session.close();
        return rowCount;
    }
}

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.

<web-app xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
         version="2.4">
<description>HikariCP Test App</description>

    <servlet>
        <servlet-name>hello</servlet-name>
        <servlet-class>HelloServlet</servlet-class>
    </servlet>

    <servlet-mapping>
        <servlet-name>hello</servlet-name>
        <url-pattern>/hello</url-pattern>
    </servlet-mapping>
</web-app>

Compile and Run

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

mvn clean install org.apache.tomcat.maven:tomcat7-maven-plugin:2.0:deploy -e

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

 

Continue reading


Laufrunde 15: Höttinger-Höfe-Weg

Auf dem Lauf und Walk Portal Run and Walk Innsbruck sind 45 Rundwege zur sportlichen Ertüchtigung in und rund um Innsbruck beschrieben. Die Runden selbst sind durch kleine Schilder gekennzeichnet und bieten viel Abwechslung im Bezug auf Bodenbeschaffenheit, Höhenprofil und Länge. Besonders nett ist beispielsweise der Höttinger Höfe Weg (aka. Laufrunde 15), die vorbei an architektonisch wunderschönen bis skurrilen Gebäuden der Höttinger.

Gleich zu Beginn geht es hoch zum Grauer-Stein-Weg und weiter entlang der Karwendelbahn bis zur Bahnhaltestelle Allerheiligenhöfe. Ab dem Berchtoldshof führt die Strecke in den Wald, über einen Wurzelsteig und am Spielplatz Grüner Boden vorbei. Über den Butterer Bichl gelangt man zum Schießstand, sowie über anspruchsvolle Pfade zum Burgstadl, von wo es zum Ziel am Botanischen Institut vorbei und nur noch hinab geht.

(Zitat Run and Walk)

 

Continue reading


Stadtwanderung Von Igls via Lans nach Innsbruck

Innsbrucks Feriendörfer sind an Idylle (und Kitsch) schwer zu überbieten und genau deswegen immer einen Besuch wert. Mit der Linie J ist man vergleichsweise schnell mitten im Mittelgebirge und beinahe genau so schnell wieder in der Stadt. Von Igls aus führen viele Wege durch das Lanser Moor, um den Lanser See, um den Mühlsee und über den Poltenweg wieder runter nach Innsbruck.

Continue reading


Stadtwanderung Rauschbrunnen Alm

Der Alpengasthof Rauschbrunnen befindet sich in 1060m Seehöhe im Nordwesten der Landeshauptstadt und bietet einen Panoramablick über die Stadt. Freunde von Flughäfen werden ebenfalls belohnt, da sich die Start- und Landevorgänge des Innsbucker Flughafens entspannt beobachten lassen. Wir waren Mitte März zwar etwas vor der Saison dort, sind aber trotzdem mit Knödeln verköstigt worden. Sehr empfehlenswert!

Die Anreise gestaltet sich auch ohne Auto sehr bequem, der Stadtbus A bis zur Haltestelle Sadrach bietet eine günstige Ausgangslage für eine schnelle Wanderung. Eine Beschreibung der Wanderung über das Höttinger Bild findet sich hier eine Wundwanderung ist hier beschrieben.

Continue reading


Stadtwanderung Umbrüggler Alm

Die Umbrüggler Alm ist Innsbrucks neueste Einkehrattraktion und aufgrund ihrer sehr guten Erreichbarkeit ein beliebtes Ausflugsziel für Stadtwanderer. Die Alm ist ab der Hungerburg oder auch über das Höttinger Bild auf beschilderten Wegen sehr leicht zu erwandern. Die öffentliche Anbindung der ausgangspunkte Hunberburg bzw. Sadrach ist sehr gut. Während der Weg von der Hungerburg aus nicht gerade durch seine Schönheit besticht, ist der Ausblick auf die Stadt atemberaubend.
Eine sehr gut gemachter Überblick über die Geschichte der Umbrüggler Alm findet sich hier. Von der auf 1123m gelegenen Umbrüggler Alm ist es nur ein Katzensprung zur Arzler Alm (1067m), die ebenfalls ein gleichsam beliebtes wie frequentiertes Ausflugsziel der Stadtbevölkerung ist.

Die Architektur der Umbrüggler Alm unterscheidet sich auf erfrischende Weise von den (natürlich älteren) Almen rund um Innsbruck und ist alleine schon aufgrund der modernen Bauweise sehenswert. Tipps für Rundwanderwege finden sich zum Beispiel hier. Die Wegweiser zeigen den Weg zur Umbrückler Alm, angeblich gab es hier mehrere Schreibweisen. Ein TT Artikel liefert noch ein paar Hintergrundinformationen.

Continue reading