“Hikari CP: Efficient Database Connection Pooling”

Efficient and reliable database connectivity is crucial for the performance and scalability of applications. Database connection pooling minimizes the overhead of establishing new connections for each transaction, optimizing database interactions. In this article, we will explore the benefits of using Hikari CP (Connection Pool) and the DAO (Data Access Object) interface to achieve efficient and robust database connectivity. We will delve into the concept with practical examples.

Let’s understand more about connection pooling

What is Database Connection Pooling and why do we need this?

Database connection pooling is a technique used to enhance the efficiency and performance of applications that interact with databases. It involves creating and managing a pool of pre-established database connections that can be reused by multiple application processes or threads.

Instead of creating a new database connection every time an application needs to interact with the database, connection pooling maintains a pool of idle connections that are ready for use. When an application requires a database connection, it borrows one from the pool. After the transaction or operation is completed, the connection is returned to the pool instead of being closed. This eliminates the overhead of establishing a new connection for each interaction, as establishing connections can be a resource-intensive process.

Advantages of using connection pooling

1. Improved Performance: Connection pooling reduces the time and resources required to establish a new database connection every time, leading to faster response times and improved application performance.
2. Resource Optimization: Since connections are reused from the pool, the overall number of connections needed to serve multiple requests is reduced. This optimizes resource utilization and avoids exhausting the maximum allowed connections set by the database.
3. Connection Management: Connection pooling libraries often provide mechanisms for managing the lifecycle and health of connections. This includes features like connection validation, timeout handling, and ensuring reliable and stable database connections.

What is Hikari CP

Hikari CP (Connection Pool) is a high-performance, lightweight, and widely adopted connection pooling library for Java applications. It is designed to provide fast and efficient database connectivity while optimizing resource usage. It is known for its exceptional performance, low latency, and small memory footprint. It is commonly used with various Java frameworks and technologies, including Spring, Hibernate.

To use Hikari CP in a Java application, you need to add the Hikari CP dependency to your project configuration, configure the connection pool settings, and obtain connections from the pool for performing database operations.

Let’s explore how to use Hikari CP in conjunction with the DAO interface.

Step 1: Adding Hikari CP to Your Project To use Hikari CP, you need to add its dependency to your project. For Maven, include the following in your pom.xml file:

If you are using Java 11+ Maven artifact:

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

If you are using Java 8 Maven artifact:

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

Step 2: Now, configure Hikari CP to establish the connection pool. Here’s an example of configuring Hikari CP in a Java application:

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

//...

   private static HikariDataSource createHikariDataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://" +
                System.getProperty("db_host") +
                ":" +
                System.getProperty("db_port") +
                "/" +
                System.getProperty("db_name") +
                "?user=" +
                System.getProperty("db_user") +
                "&password=" +
                System.getProperty("db_password") +
                "&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC");
        config.setMaximumPoolSize(10);
        return new HikariDataSource(config);
    }

Above values, you can adjust based on your database configuration.

Step 3: Now, we have to create the DAO Interface The DAO interface acts as an abstraction layer between the application and the database. It defines the operations that can be performed on the data. Here’s an example of a simple UserDAO interface:

public interface UsersDAO {

    @RegisterRowMapper(UserMapper.class)
    @SqlQuery("select id, name, caption from user_groups")
    List<User> getUserGroup();

}

Step 4: Next, implement the DAO interface using Hikari CP for database connectivity. Here’s an example implementation for the UserDAO interface:

import com.google.inject.Provides;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.jdbi.v3.sqlobject.SqlObjectPlugin;


public class MySQLDBPool{
    private static Jdbi jdbi;
    public UsersDAO usersDAO;


    @Override
    public void configure() {
       this.jdbi = Jdbi.create(createHikariDataSource()).installPlugin(new SqlObjectPlugin());
       usersDAO = jdbi.onDemand(UsersDAO.class);
      
    }

Here we are calling the method created in Step 2.

Conclusion

With Hikari CP, We can benefit from improved application performance, resource optimization, and seamless scalability. The library’s connection management features, such as connection validation and timeout handling, contribute to the stability and reliability of database connections.

Please follow me on Medium, I will post useful information like above.

Thanks for reading this article ❤

If this article helps you, please clap 👏 this article.

If I got something wrong? Let me in the comments. I would love to improve.