Configure HikariCP with Spring Boot JPA Hibernate and PostgreSQL as a database

README.md

I came across HikariCP and I was amazed by the benchmarks and I wanted to try it instead of my default choice C3P0 and to my surprise I struggled to get the configurations right probably because the configurations differ based on what combination of tech stack you are using.

I have setup Spring Boot project with JPA, Web, Security starters (Using [Spring Initializer][1]) to use PostgreSQL as a database with HikariCP as connection pooling.
I have used Gradle as build tool and I would like to share what worked for me for the following assumptions:

1. Spring Boot Starter JPA (Web & Security - optional)
2. Gradle build tool
3. PostgreSQL running and setup with a database (i.e. schema, user, db)

This gist is related to SO post https://stackoverflow.com/questions/26490967/how-do-i-configure-hikaricp-in-my-spring-boot-app-in-my-application-properties-f

application.properties

server.contextPath=/
debug=true

# Spring data source needed for Spring boot to behave
# Pre Spring Boot v2.0.0.M6 without below Spring Boot defaults to tomcat-jdbc connection pool included 
# in spring-boot-starter-jdbc and as compiled dependency under spring-boot-starter-data-jpa
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.url=jdbc:postgresql://localhost:5432/somedb
spring.datasource.username=dbuser
spring.datasource.password=dbpassword

# Hikari will use the above plus the following to setup connection pooling
spring.datasource.hikari.minimumIdle=5
spring.datasource.hikari.maximumPoolSize=20
spring.datasource.hikari.idleTimeout=30000
spring.datasource.hikari.poolName=SpringBootJPAHikariCP
spring.datasource.hikari.maxLifetime=2000000
spring.datasource.hikari.connectionTimeout=30000

# Without below HikariCP uses deprecated com.zaxxer.hikari.hibernate.HikariConnectionProvider
# Surprisingly enough below ConnectionProvider is in hibernate-hikaricp dependency and not hibernate-core
# So you need to pull that dependency but, make sure to exclude it's transitive dependencies or you will end up 
# with different versions of hibernate-core 
spring.jpa.hibernate.connection.provider_class=org.hibernate.hikaricp.internal.HikariCPConnectionProvider

# JPA specific configs
spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.use_sql=true
spring.jpa.properties.hibernate.id.new_generator_mappings=false
spring.jpa.properties.hibernate.default_schema=littracker
spring.jpa.properties.hibernate.search.autoregister_listeners=false
spring.jpa.properties.hibernate.bytecode.use_reflection_optimizer=false

# Enable logging
logging.level.org.hibernate.SQL=DEBUG
# Enable logging for HikariCP to verify that it is used
logging.level.com.zaxxer.hikari.HikariConfig=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE 

build.gradle

buildscript {
	ext {
		springBootVersion = '1.5.8.RELEASE'
	}
	repositories {
		mavenCentral()
	}
	dependencies {
		classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
	}
}

apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'org.springframework.boot'
apply plugin: 'war'

group = 'com'
version = '1.0'
sourceCompatibility = 1.8

repositories {
	mavenCentral()
}


dependencies {
	compile('org.springframework.boot:spring-boot-starter-aop')
	
	// Exclude the tomcat-jdbc since it's used as default for connection pooling
  // This can also be achieved by setting the spring.datasource.type to HikariCP 
  // datasource see application.properties below
	compile('org.springframework.boot:spring-boot-starter-data-jpa') {
		exclude group: 'org.apache.tomcat', module: 'tomcat-jdbc'
	}
  
	compile('org.springframework.boot:spring-boot-starter-security')
	
  compile('org.springframework.boot:spring-boot-starter-web')
	
  runtime('org.postgresql:postgresql')
  
  testCompile('org.springframework.boot:spring-boot-starter-test')
	testCompile('org.springframework.security:spring-security-test')
	
	// Download HikariCP but, exclude hibernate-core to avoid version conflicts
	compile('com.zaxxer:HikariCP:2.5.1') {
		exclude group: 'org.hibernate', module: 'hibernate-core'
	}
	
  // Need this in order to get the HikariCPConnectionProvider
	compile('org.hibernate:hibernate-hikaricp:5.2.11.Final') {
		exclude group: 'com.zaxxer', module: 'HikariCP'
		exclude group: 'org.hibernate', module: 'hibernate-core'
	}
}

I cant figure out how to set the defaultRowFetchSize in the application.properties. Can you help with that? Thanks!

https://jdbc.postgresql.org/documentation/head/connect.html

defaultRowFetchSize = int
Determine the number of rows fetched in ResultSet by one fetch with trip to the database. Limiting the number of rows are fetch with each trip to the database allow avoids unnecessary memory consumption and as a consequence OutOfMemoryException.

The default is zero, meaning that in ResultSet will be fetch all rows at once. Negative number is not available.