Connection Pooling

ismail talhaoui
3 min readMar 18, 2023

--

Connecting to a database, whether using an ORM (Object Relational Mapping) or JDBC directly, is often one of the most resource-intensive operations for applications because it requires significant amounts of time and memory.

The connection between an application and a database involves several steps between opening and closing. Therefore, opening and closing the connection after every operation can cause many problems.

To address this issue, we use connection pooling or the Data Access pattern, which allows us to reuse the same connection for multiple operations

We don’t need to reinvent the wheel every time we want to use this pattern, as there are many solutions available in the market depending on the programming language and database we use.

Most used solutions on the market are:

Hikaricp

C3PO

Apache Commons DBCP2

Tomcat

Vibur

by default Spring Boot 1 uses the Tomcat JDBC connection pool, and in Spring Boot 2 and 3, HikariCP is used by default. If HikariCP is excluded, Spring Boot will use Tomcat, and if Tomcat is excluded, Spring Boot will use Apache Commons DBCP2.

This article will focus on a detailed discussion of HikariCP, while future articles will cover the other connection pooling solutions.

Life cycle of connection Pooling Using HikariCP:

Connection Pooling Life cycle

Advantages of HikariCP

As we see Spring Boot 2 and 3 Uses HikariCP by Default because of many reasons:

I- it is very Fast because of the uses:

1. Bytecode level engineering (include Assembly level native)

2. Micro-optimization

3. Intelligent use of collections framework: ArrayList<statement> was replaced with custom class FastList that eliminates range checking and performs removed scan from head to tail

II- it has many Properties:

1. autocommit

2. connectionTimout: number of milliseconds that the client will Wait for a connection pool (default: 30s)

3. idleTimout: maximum amount of time that a connection is allowed to set idle in the pool this property apply only when minimumIdle is Defined to be less than the maximumPoolSize default value: 10 mins (600000 ms)

4. keepalivetime: how frequently Hikaricp will attempt to keep a connection alive in order to prevent it from being timeout by the database or network this value must be less than maxLifeTime default 0 (Disabled) min value: 30 seconds

5. maxlifetime: maximum lifetime of a connection in the pool it’s recommended setting this value and it should be several seconds shorter than any database. Default value: 30 min 0 mean no maxlifetime

6. connectionTestQuery: Query used to see if the connection still open

7. connectionInitSQL

8. validationTimeout

9. minimumIdle: minimum number of idle connections that hikaricp tries to maintain in the pool, it’s recommended not setting this value default some as maximumpoolSize

10. maximumPoolSize: the maximum size that the pool is allowed to reach including both idle and in use connections, default value 10 and the best value is determined by the execution environment.

11. PoolName

12. allowPoolSuspension

13. readOnly

14. transactionIsolation

15. leakDetectionThreshold

Thank you for your Attention and your time If you are interested in web and Mobile Development or Java or Scrum or making websites and Application or Decision System, make sure to check out my portfolio and to contact Me !

Thanks to All team Who contributed to this work

--

--

ismail talhaoui

Fullstack Java /Angular /Talend / BI Developer, Problems Solver