JDBC DataSource and Connection Pool Basics in Spring Boot 3

Introduction

Most backend systems are data-driven, and that starts with a reliable database connection layer. In this chapter, you will learn how Spring Boot 3 auto-configures JDBC, how DataSource works, why connection pools are essential, and how to tune basic HikariCP settings for stable local and production behavior.

Prerequisites

• Completed web chapters up to:
  • 09_content_negotiation_and_http_message_converters.md
• Basic SQL knowledge
• A local MySQL/PostgreSQL instance (or Docker database)

Why DataSource Matters

In Java apps, DataSource is the standard entry point for obtaining DB connections.

Without a pool:

• each request may create/close physical DB connections
• latency increases
• DB and app resource usage becomes unstable

With a pool:

• app reuses existing connections
• throughput improves
• resource usage is controlled by pool limits

Spring Boot handles this setup automatically when dependencies and config are in place.

Add JDBC Dependencies

For MySQL example:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
 
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
</dependency>

For PostgreSQL, replace driver:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
</dependency>

Configure Database Connection

application.yml example (MySQL):

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/demo?useSSL=false&serverTimezone=Asia/Shanghai
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver

Equivalent properties style:

spring.datasource.url=jdbc:mysql://localhost:3306/demo?useSSL=false&serverTimezone=Asia/Shanghai
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

When Boot sees JDBC starter + driver + datasource properties, it auto-creates a pooled DataSource.

Default Connection Pool: HikariCP

Spring Boot 3 uses HikariCP by default (if on classpath).

Why HikariCP is common:

• high performance
• low overhead
• good defaults
• mature ecosystem support

You usually do not need manual bean config for basic use.

Key Hikari Settings

spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000

What they mean:

• maximum-pool-size: max active connections in pool
• minimum-idle: minimum idle connections kept ready
• connection-timeout: max wait time to borrow a connection
• idle-timeout: idle connection recycle timeout
• max-lifetime: max lifetime before connection refresh

Verify DataSource at Startup

Add a quick check component:

If startup succeeds and prints DB URL, base connectivity is working.

Manual SQL Query with DataSource

In some situations (admin endpoints, quick scripts, special JDBC features), you may want to use the DataSource directly and run JDBC code.

This example proves:

• Spring Boot can inject the pooled DataSource
• JDBC can successfully execute SQL and read results

Execute SQL with JdbcTemplate

JdbcTemplate is the simplest Spring JDBC abstraction.

This proves:

• datasource is wired
• pool can provide connections
• SQL execution round-trip is functional

Common Connection Problems

Communications link failure

Possible causes:

• DB not running
• host/port incorrect
• firewall or Docker network issue

Access denied for user

Possible causes:

• wrong username/password
• user host permissions not granted

Cannot load driver class

Possible causes:

• DB driver dependency missing
• wrong driver-class-name

HikariPool-1 - Connection is not available

Possible causes:

• pool exhausted (queries too slow or connections leaked)
• maximum-pool-size too small for concurrent load

Enable SQL and Pool Logging (Dev)

Useful in local debugging:

logging:
  level:
    com.zaxxer.hikari: INFO
    org.springframework.jdbc.core: DEBUG

This helps observe:

• connection pool startup
• SQL execution traces
• timeout or borrow issues

Practical Pool Tuning Guidelines

• Set pool size with DB capacity in mind (not just app thread count)
• Keep max-lifetime slightly lower than DB-side connection timeout
• Monitor slow SQL before increasing pool size blindly
• Use indexes and query optimization first; pool tuning is not a SQL fix

Production Safety Notes

• Keep credentials out of Git-tracked config files
• Use environment variables or secret managers
• Add health checks to detect DB failures early
• Configure proper DB time zone/encoding explicitly

Quick Recap

In this chapter, you learned:

1. How Boot auto-configures JDBC DataSource
2. Why connection pooling is required in real services
3. Core HikariCP parameters and when to tune them
4. How to verify connectivity with JdbcTemplate
5. How to troubleshoot common DB connection failures

Next chapter will build on this foundation with MyBatis integration and mapper-based data access.

FAQ

Do I need to create DataSource bean manually?

Usually no. Spring Boot auto-configures it if dependencies and spring.datasource.* settings are present.

Is JdbcTemplate obsolete if I use MyBatis/JPA later?

No. JdbcTemplate is still useful for simple queries, admin scripts, and edge cases where full ORM mapping is unnecessary.

Should I set maximum-pool-size to a very high value?

No. Pool size must match DB capacity and workload characteristics. Bigger is not always better.

Why does app start but first query fail?

Startup may succeed before hitting real SQL paths. Verify credentials, schema existence, and network reachability with an explicit test endpoint or startup check.

Can I use H2 for learning instead of MySQL/PostgreSQL?

Yes. H2 is great for quick demos, but production-like behavior is better learned with the same database family you plan to use in real deployments.