How do you handle database connection pooling in Spring Boot?

In Spring Boot, database connection pooling is handled automatically using a connection pool library, which improves application performance by reusing database connections instead of creating a new connection for every request. Here’s a detailed explanation of how it works and how to configure it:

1. What is Connection Pooling

Opening a database connection is expensive (network + authentication overhead).
Connection pooling keeps a pool of ready-to-use connections.
When the application needs a connection, it borrows one from the pool. After use, it returns it to the pool.
This reduces latency and improves throughput for high-concurrency applications.

2. Spring Boot Default Connection Pool

Spring Boot auto-configures a connection pool if a DataSource is available.
Default pool (Spring Boot 2.x/3.x): HikariCP (lightweight, fast, production-ready).
Other supported pools:
- Apache DBCP2
- Tomcat JDBC Pool

Spring Boot automatically picks HikariCP if it’s on the classpath.

3. Basic Configuration in `application.properties`

spring.datasource.url=jdbc:mysql://localhost:3306/mydb
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# HikariCP specific properties
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.idle-timeout=30000
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.pool-name=MyHikariCP

Explanation:

Property	Description
`maximum-pool-size`	Max number of connections in the pool
`minimum-idle`	Min idle connections maintained
`idle-timeout`	Max idle time before connection is removed
`max-lifetime`	Max lifetime of a connection before it’s retired
`connection-timeout`	Max time to wait for a connection from the pool
`pool-name`	Name for the connection pool (useful for monitoring)

4. Advanced Configuration (Optional)

Validation query: Ensures connections are alive before use.

spring.datasource.hikari.connection-test-query=SELECT 1
spring.datasource.hikari.validation-timeout=5000

Auto-commit mode: Usually left true, but can be configured:

spring.datasource.hikari.auto-commit=false

Leak detection: Detect connections not returned to the pool:

spring.datasource.hikari.leak-detection-threshold=2000

5. Programmatic Configuration (Optional)

You can configure HikariCP directly in Java:

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {

    @Bean
    public DataSource dataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
        config.setUsername("root");
        config.setPassword("root");
        config.setMaximumPoolSize(10);
        config.setMinimumIdle(5);
        config.setIdleTimeout(30000);
        config.setMaxLifetime(1800000);
        return new HikariDataSource(config);
    }
}

Useful if you need dynamic configuration or want to manage multiple data sources.

6. Monitoring Connection Pool

HikariCP exposes metrics via Micrometer, which can be integrated with Spring Boot Actuator:

management.endpoints.web.exposure.include=health,metrics
management.metrics.enable.hikari=true

Metrics like hikaricp.connections.active and hikaricp.connections.idle can be monitored.

7. Best Practices

Choose a production-ready pool: HikariCP is recommended.
Set reasonable max pool size based on DB capacity and app concurrency.
Enable leak detection in development to catch unreturned connections.
Use connection validation if the DB may close idle connections.
Monitor metrics in production via Spring Boot Actuator.