Home » Enterprise Java » HikariCP » HikariCP Connection Pooling Example

About Chandan Singh

Chandan holds a degree in Computer Engineering and is a passionate software programmer. He has good experience in Java/J2EE Web-Application development for Banking and E-Commerce Domains.

HikariCP Connection Pooling Example

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.

Fig 1 : New Maven project

Fig 1 : New Maven project

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.

3. Download the Source Code

Here, we demonstrated how we can configure C3P0 for connection pooling in our applications.

Download
You can download the source code of this example here: HikariCPExample.zip

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

 

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
anup rane
anup rane
3 years ago

I just ran the mentioned source code . getting following exception
Exception in thread “main” java.lang.AbstractMethodError: com.sybase.jdbc3.jdbc.SybConnection.isValid(I)Z
at com.zaxxer.hikari.pool.PoolBase.checkDriverSupport(PoolBase.java:411)
at com.zaxxer.hikari.pool.PoolBase.setupConnection(PoolBase.java:382)
at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:346)
at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:193)
at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:428)
at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:499)

DuSk
DuSk
3 years ago
Reply to  anup rane

Yo bro, i had the same issue at work you just need to put into hikari a validation query.
In db2 it should look like this:
HikariConfig config = new HikariConfig(“/hikari.properties”); config.setConnectionTestQuery(“select 1 from sysibm.sysdummy1”);
hope it helps