Hikari Connection Pooling —

Connection pooling is a technique for efficiently using and managing the connections of any application. First let’s see the importance of connection pooling —

a) Resource efficiency — Opening and closing a connection is an expensive operation, hence it helps us using the existing connections from the pool whenever a connection is required.

b) Performance improvement — Pooled connections are readily available, hence eliminating the need to establish a new connection for every request.

c) Connection limit — Connection pooling also helps us limiting the number of open connections, this also prevents resource exhaustion.

In this blog I will focus on database connection pooling using Hikari for a spring boot application. We need to add the Hikari dependency in our pom.xml.

<dependency>
      <groupId>com.zaxxer</groupId>
      <artifactId>HikariCP</artifactId>
</dependency>

Now we need to add few properties —

#default is 10
spring.datasource.hikari.maximum-pool-size=10
#default is same as max pool size
spring.datasource.hikari.minimum-idle=10
#default is 30 seconds
spring.datasource.hikari.connection-timeout=30000
#default is 600000 i.e 10 minutes
spring.datasource.hikari.idle-timeout=600000
#default is 1800000 i.e 30 minutes
spring.datasource.hikari.max-lifetime=1800000 
spring.datasource.hikari.pool-name=HikariConnPool

Let’s try to understand the above properties first —

a) maximum-pool-size → sets the maximum number of connections that can be held in the connection pool.

b) connection-timeout → defines the maximum time application is willing to wait for a connection from the pool. If pool is exhausted and this time is reached then an exception is thrown with a message “connection acquisition timed out”.

Note — If the maximum number of connections exhausted from the pool and a new request comes in, it will behave depending on the connection-timeout. The new request will wait until the connection is available or it is timed out.

c) minimum-idle → defines the minimum number of idle connections that the pool should try to maintain. An idle connection is one that is not in use but is kept ready and in open state. Maintaining idle connections can improve the performance of the applications as it will reduce the time taken to acquire a new connection.

d) idle-timeout → specifies the maximum amount of time a connection should remain idle before it closes. It helps in releasing resources and prevents connection leaks.

e) max-lifetime → sets the maximum lifetime for a connection in the pool, after this the connection is closed and is replaced with the new connection in the pool. This helps in preventing issues with the long-lived connections.

@Configuration
@EnableTransactionManagement
public class DbConfig {

     @Value("${spring.datasource.url}")
     private String url;
     @Value("${spring.datasource.username}")
     private String username;
     @Value("${spring.datasource.password}")
     private String password;
     @Value("${spring.datasource.driver-class-name}")
     private String driverClassName;
   
     @Value("${spring.jpa.properties.hibernate.dialect}")
     private String dialect;
     @Value("${spring.jpa.show-sql}")
     private String showSql;
     @Value("${spring.jpa.hibernate.ddl-auto}")
     private String ddlAuto;
     
     @Value("${spring.datasource.hikari.maximum-pool-size}")
     private int hikariMaximumPoolSize;
     
     @Value("${spring.datasource.hikari.minimum-idle}")
     private int hikariMinimumIdle;
     
     @Value("${spring.datasource.hikari.connection-timeout}")
     private long hikariConnectionTimeout;
     
     @Value("${spring.datasource.hikari.idle-timeout}")
     private long hikariIdleTimeout;
     
     @Value("${spring.datasource.hikari.max-lifetime}")
     private long hikariMaxLifeTime;
   
     @Value("${spring.datasource.hikari.pool-name}")
     private String hikariPoolName;
     
     @Value("${spring.datasource.hikari.connection-test-query}")
     private String hikariConnectionTestQuery;
 
     @Bean
     @Autowired
     public DataSource datasource() {
        HikariConfig config = new HikariConfig();
        config.setUsername(username);
        config.setPassword(password);
        config.setDriverClassName(driverClassName);
        config.setJdbcUrl(url);
        
        config.setMaximumPoolSize(hikariMaximumPoolSize); // Adjust based on your needs
        config.setMinimumIdle(hikariMinimumIdle);
        config.setConnectionTimeout(hikariConnectionTimeout);
        config.setMaxLifetime(hikariMaxLifeTime);
        config.setIdleTimeout(hikariIdleTimeout);
        config.setConnectionTestQuery(hikariConnectionTestQuery);
        config.setPoolName(hikariPoolName);
        log.info("Data source created");
      
        HikariDataSource dataSource = new HikariDataSource(config);
        return dataSource;
     }
 
 
    @Bean
    public LocalSessionFactoryBean sessionFactory() {
        LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();
        sessionFactory.setDataSource(datasource());
        sessionFactory.setPackagesToScan("com.spring.boot.learning"); // Replace with your package name
        sessionFactory.setHibernateProperties(hibernateProperties());
        log.info("session factory created");
        return sessionFactory;
    }
 
    @Bean
    public PlatformTransactionManager hibernateTransactionManager(SessionFactory sessionFactory) {
        HibernateTransactionManager transactionManager = new HibernateTransactionManager();
        transactionManager.setSessionFactory(sessionFactory);
        return transactionManager;
    }
 
    private Properties hibernateProperties() {
        Properties properties = new Properties();
        properties.setProperty("hibernate.dialect", dialect);
        properties.setProperty("hibernate.show_sql", showSql);
        properties.setProperty("hibernate.hbm2ddl.auto", ddlAuto);
        log.info("Hibernate properties created");
        return properties;
    }
}