HikariCP Connection Pooling Example

Chandan SinghMay 27th, 2016Last Updated: May 26th, 2016

2 4,302 2 minutes read

In the past examples we have demonstrated what is connection pooling and what are its benefits. We have studied how we can configure a connection pool using C3P0 and DBCP libraries. Continuing our series further on connection pooling libraries, we will demonstrate how we can configure HikariCP.

1. Project Set-Up

Create a simple Maven Project in Eclipse IDE by selecting the Skip Archetype Selection checkbox from the New Maven Project Pop-up.

We are using the below pom.xml to manage the dependencies for HikariCP and the JDBC Driver.

pom.xml

<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>HikariCPExample</groupId>
	<artifactId>com.jcg.examples.HikariCPExample</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<dependencies>
		<dependency>
			<groupId>com.zaxxer</groupId>
			<artifactId>HikariCP</artifactId>
			<version>2.4.3</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.37</version>
		</dependency>
	</dependencies>
	<build>
		<sourceDirectory>src</sourceDirectory>
		<plugins>
			<plugin>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.3</version>
				<configuration>
					<source>1.8</source>
					<target>1.8</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

Eclipse will download the required JAR files and add the dependencies in the project classpath.
Next step is creating a table. As you can already see, we are using the MySql Database server for this example.

Here’s the script for the table we shall be using:

createTable.sql

CREATE TABLE `account` (
  `Account_Number` bigint(20) NOT NULL,
  `Account_Type` varchar(45) DEFAULT NULL,
  `Person_name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`Account_Number`)

Now that the project is setup and dependencies imported, we can begin writing the actual code.

2. Implementation

Let’s implement a basic HikariCP Datasource for our application.

HikariCPTest.java

package com.jcg.examples;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class HikariCPTest
{
		
		private static DataSource datasource;
		
		public static DataSource getDataSource()
		{
				if(datasource == null)
				{
						HikariConfig config = new HikariConfig();
						
		        config.setJdbcUrl("jdbc:mysql://localhost/test");
		        config.setUsername("root");
		        config.setPassword("password");

		        config.setMaximumPoolSize(10);
		        config.setAutoCommit(false);
		        config.addDataSourceProperty("cachePrepStmts", "true");
		        config.addDataSourceProperty("prepStmtCacheSize", "250");
		        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
		        
		        datasource = new HikariDataSource(config);
				}
				return datasource;
		}
		
		public static void main(String[] args)
		{
				      
        Connection connection = null;
				PreparedStatement pstmt = null;
				ResultSet resultSet = null;
				try
				{
						DataSource dataSource = HikariCPTest.getDataSource();
						connection = dataSource.getConnection();
						pstmt = connection.prepareStatement("SELECT * FROM account");
						
						System.out.println("The Connection Object is of Class: " + connection.getClass());
						
						resultSet = pstmt.executeQuery();
						while (resultSet.next())
						{
								System.out.println(resultSet.getString(1) + "," + resultSet.getString(2) + "," + resultSet.getString(3));
						}

				}
				catch (Exception e)
				{
						try
						{
								connection.rollback();
						}
						catch (SQLException e1)
						{
								e1.printStackTrace();
						}
						e.printStackTrace();
				}
        
		}

}

We create a simple datasource of the type : com.zaxxer.hikari.HikariDataSource. The connection parameters like URL, username and password are the necessary fields which we need to provide to initiate the Datasource.
Apart from these fields we have some optional fields in the HikariDataSource which we can use for finer control over it. Here’s their brief description:

MaxPoolSize : The maximum number of connections, idle or busy, that can be present in the pool.

cachePrepStmts : Whether to cache Prepared Statements or not.

prepStmtCacheSize : Number of Prepared Statements to be cached per connection. This property will work only if the cachePrepStmts switch is turned-on.

prepStmtCacheSqlLimit : The maximum length of the SQL Statement that the connection will cache.

Here’s a sample output of the above program:

The Connection Object is of Class: class com.zaxxer.hikari.pool.HikariProxyConnection
123456,Savings,ABC
433445,Current,XYZ

HikariCP integrates smoothly with Spring Container as well so that it can be used seamlessly in Spring Based Applications. Here’s a basic configuration for the datasource bean :

spring-beans.xml

<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource"
		destroy-method="close">
		<property name="driverClass" value="com.mysql.jdbc.Driver" />
		<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/test" />
		<property name="username" value="root" />
		<property name="password" value="password" />
</bean>

As the reader might notice, HikariCP offers much finer control to the developer as to how the connection pool gets initialized and how it runs. Also, it is one of the fastest Connection Pooling libraries which makes it a must try-out connection pooling library for every application developer.