Home/Learn/MySQL/MySQL with Spring Boot / JDBC

MySQL with Spring Boot / JDBC

Beginner

Administration

Configure spring.datasource.url with the JDBC URL, driver-class-name=com.mysql.cj.jdbc.Driver, and HikariCP pool settings; use Flyway or Liquibase for schema migrations.

Overview

Connecting MySQL to Spring Boot requires three things: the MySQL JDBC driver, a DataSource configuration (HikariCP by default), and a JPA or JDBC template for data access. Spring Boot auto-configures HikariCP when spring.datasource.* properties are present. Flyway or Liquibase handle schema versioning — running migration scripts automatically at startup. Tuning the HikariCP pool size directly impacts throughput and database connection exhaustion under load.

DataSource & HikariCP Configuration

Add the mysql-connector-j dependency. Configure JDBC URL with required parameters (serverTimezone, charset, SSL). HikariCP pool settings control how many connections are kept open.

XML + Properties — MySQL datasource and HikariCP

<!-- pom.xml -->
<dependency>
  <groupId>com.mysql</groupId>
  <artifactId>mysql-connector-j</artifactId>
  <!-- version managed by Spring Boot BOM -->
</dependency>
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

# application.properties
spring.datasource.url=jdbc:mysql://localhost:3306/shopdb\
  ?serverTimezone=UTC\
  &characterEncoding=utf8mb4\
  &useSSL=false\
  &allowPublicKeyRetrieval=true\
  &rewriteBatchedStatements=true

spring.datasource.username=shop_app
spring.datasource.password=${DB_PASSWORD}
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# HikariCP pool tuning
spring.datasource.hikari.pool-name=ShopHikariPool
spring.datasource.hikari.maximum-pool-size=10     # max DB connections
spring.datasource.hikari.minimum-idle=5           # min idle connections
spring.datasource.hikari.connection-timeout=30000 # ms before throwing if no conn
spring.datasource.hikari.idle-timeout=600000      # ms before idle conn released
spring.datasource.hikari.max-lifetime=1800000     # ms max conn lifetime

Flyway Schema Migrations

Flyway runs versioned SQL migration scripts from src/main/resources/db/migration at startup. Scripts are named V{version}__{description}.sql. Spring Boot auto-configures Flyway when it is on the classpath.

XML + Properties + SQL — Flyway migrations

<!-- pom.xml -->
<dependency>
  <groupId>org.flywaydb</groupId>
  <artifactId>flyway-mysql</artifactId>
</dependency>

# application.properties
spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration
spring.flyway.baseline-on-migrate=true   # needed when migrating existing DB

# Migrations in src/main/resources/db/migration/
# V1__Create_orders_table.sql
CREATE TABLE orders (
    id BIGINT NOT NULL AUTO_INCREMENT,
    customer_id BIGINT NOT NULL,
    status ENUM('DRAFT','PLACED','SHIPPED') NOT NULL DEFAULT 'DRAFT',
    total DECIMAL(10,2) NOT NULL,
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (id),
    INDEX idx_customer_status (customer_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# V2__Add_notes_column.sql
ALTER TABLE orders ADD COLUMN notes VARCHAR(500) NULL, ALGORITHM=INSTANT;

JPA & Query Configuration

Configure Hibernate dialect, DDL generation, and SQL logging. In production, never use ddl-auto=create or update — use Flyway migrations instead. Enable spring.jpa.open-in-view=false to avoid the open-session-in-view anti-pattern.

Properties — Hibernate/JPA configuration

# application.properties — JPA / Hibernate
spring.jpa.database-platform=org.hibernate.dialect.MySQL8Dialect
spring.jpa.hibernate.ddl-auto=validate   # validate schema against entities (safe)
# Options: none, validate, update, create, create-drop
# Production: none or validate (use Flyway for schema changes)

spring.jpa.show-sql=true                 # log SQL (dev only — verbose in prod)
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true

# Disable OSIV (Open Session In View) — prevents lazy loading in presentation layer
spring.jpa.open-in-view=false

# Batch inserts — avoid N individual INSERTs
spring.jpa.properties.hibernate.jdbc.batch_size=50
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true

# Second-level cache (optional)
spring.jpa.properties.hibernate.cache.use_second_level_cache=true
spring.jpa.properties.hibernate.cache.region.factory_class=\
  org.hibernate.cache.jcache.JCacheRegionFactory

Key Points to Remember

1mysql-connector-j is the modern driver; use jdbc:mysql://host/db?serverTimezone=UTC.
2HikariCP is Spring Boot's default pool — tune maximum-pool-size based on DB max_connections.
3Pool size formula: (2 × CPU cores) + effective_spindle_count for I/O-bound workloads.
4Use Flyway or Liquibase for schema migrations — never rely on ddl-auto=update in production.
5spring.jpa.open-in-view=false prevents the OSIV anti-pattern and lazy loading in web layer.
6rewriteBatchedStatements=true in the JDBC URL enables true MySQL multi-row INSERT batching.

Interview Questions

What is HikariCP and why does Spring Boot use it by default?

EasyInfosys

What is the recommended ddl-auto setting for a production Spring Boot app?

EasyTCS

How does Flyway know which migration scripts have already been applied?

MediumAmazon

What is the Open Session In View anti-pattern and why should you disable it?

HardNetflix

How would you tune HikariCP pool size for a MySQL database on a 4-core server?

HardFlipkart

Ask Aria about MySQL with Spring Boot / JDBC

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

MySQL Configuration Tuning