Java Intermediate

Spring Boot with MySQL and JPA: The Definitive Persistence Guide

📅 March 09, 2026 ⏱ 4 min read 🎯 Intermediate

Where developers are forged. · Structured learning · Free forever.

📍 Part of: Spring Boot → Topic 6 of 15

Master Spring Boot with MySQL and JPA 3.

⚙️ Intermediate — basic Java knowledge assumed

In this tutorial, you'll learn

Master Spring Boot with MySQL and JPA 3.

Spring Boot with MySQL and JPA maps Java objects to MySQL tables via annotations — Hibernate generates SQL, Spring Data generates CRUD repository implementations, and Spring Boot auto-configures HikariCP. What took 300 lines of JDBC boilerplate now takes 30 lines of entity class and a repository interface.
Spring Boot 3.2.x uses the jakarta.persistence namespace — if you are migrating from 2.x, update every @Entity, @Column, @Transactional, and @Id import from javax to jakarta. This is a breaking change with no behavioral difference.
Spring Data JPA repositories eliminate DAO boilerplate — method-name-derived queries (findByNameContainingIgnoreCase) are parsed and validated at startup, not at runtime. A startup failure means your method name is wrong — a runtime failure means your logic is wrong. This distinction saves significant debugging time.

✦ Plain-English analogy ✦ Real code with output ✦ Interview questions

⚡Quick Answer

JPA maps Java objects (Entities) to MySQL tables — Hibernate generates SQL so you don't write raw JDBC
Spring Data JPA Repositories provide CRUD methods from an interface — no implementation class needed
HikariCP is the default connection pool — misconfigured pool size is the #1 cause of database-related outages
The N+1 problem is the most common performance killer — one query for parents, N queries for children; fix it with JOIN FETCH or @EntityGraph
spring.jpa.open-in-view=false is non-negotiable in production — OSIV holds connections open until view rendering completes, not until your SQL finishes
Use BigDecimal for money, never Double — floating-point precision errors corrupt financial data silently and accumulate across thousands of transactions
ddl-auto=validate in production, Flyway for migrations — never let Hibernate touch your production schema autonomously

🚨 START HERE

JPA/MySQL Performance Debugging Cheat Sheet

Quick-reference commands for diagnosing JPA and MySQL performance issues in production. Each card maps a symptom to the exact commands you need — copy, paste, diagnose.

🟡Connection pool exhaustion — 'HikariPool-1 - Connection is not available, request timed out' errors

Immediate ActionCheck HikariCP pool state and MySQL process list to determine whether connections are truly busy or just held open by OSIV

Commands

curl -s http://localhost:8080/actuator/metrics/hikaricp.connections.pending | jq '.measurements[] | select(.statistic=="VALUE") | .value'

mysql -u forge_user -p -e "SELECT command, state, time, info FROM information_schema.processlist WHERE command != 'Sleep' ORDER BY time DESC LIMIT 20;"

Fix NowIf pending > 0: set spring.jpa.open-in-view=false and increase spring.datasource.hikari.maximum-pool-size to (core_count * 2) + 1. If all connections show Sleep state with long time values, OSIV is the definitive culprit.

🟠N+1 query problem — hundreds of SQL queries for a single API request, response time 10x slower than expected

Immediate ActionEnable SQL logging temporarily and count SELECT statements per request to confirm N+1

Commands

grep -c 'Hibernate: select' /var/log/app/application.log

grep 'Hibernate: select' /var/log/app/application.log | sort | uniq -c | sort -rn | head -10

Fix NowIdentify the repeated select pattern (same table, different ID). Add JOIN FETCH to the JPQL query or add @EntityGraph(attributePaths = {"<collection-name>"}) to the repository method. Re-run and confirm query count drops to 1–2.

🟠Slow queries — API p99 latency spiked from 50ms to 5 seconds, database CPU elevated

Immediate ActionIdentify which queries are slow in MySQL and check if they are using indexes

Commands

mysql -u forge_user -p -e "SELECT query_time, sql_text FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;"

mysql -u forge_user -p -e "EXPLAIN SELECT * FROM forge_products WHERE category_id = 42 AND status = 'ACTIVE';"

Fix NowIf EXPLAIN shows 'type: ALL' (full table scan), add an index: ALTER TABLE forge_products ADD INDEX idx_category_status (category_id, status). Enable MySQL slow query log if not already on: SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1;

🔴OutOfMemoryError during batch processing — heap fills up during bulk inserts or exports

Immediate ActionCapture heap histogram to confirm Persistence Context entity accumulation is the source

Commands

jcmd $(pgrep -f spring-boot) GC.heap_dump /tmp/heapdump.hprof

jcmd $(pgrep -f spring-boot) GC.class_histogram | grep -E 'ForgeProduct|EntityEntry|StatefulPersistence' | head -10

Fix NowAdd entityManager.flush() and entityManager.clear() every 500 entities in the batch loop. Add spring.jpa.properties.hibernate.jdbc.batch_size=500 and spring.jpa.properties.hibernate.order_inserts=true to enable true JDBC batching.

Production IncidentThe Connection Pool Exhaustion That Killed Black Friday — OSIV and HikariCP MisconfigurationAn e-commerce platform with Open-in-View enabled and a 10-connection HikariCP pool exhausted all connections during peak traffic. Every request hung for 30 seconds waiting for a connection, then timed out. The platform was down for 3 hours on their highest-revenue day of the year.

SymptomEvery API endpoint returned a timeout error after 30 seconds. Application logs filled with 'HikariPool-1 - Connection is not available, request timed out after 30000ms' errors. MySQL's SHOW PROCESSLIST showed only 10 active connections — the pool maximum — all in a 'Sleep' state, not actually executing SQL. The application had 500 requests queued waiting for a connection that would never be released. The load balancer health checks started failing, and the deployment team began emergency scaling — which made things worse by adding more application instances that each immediately saturated their own 10-connection pools against the same MySQL instance.

AssumptionThe team assumed MySQL was the bottleneck — CPU graphs showed a spike and they immediately scaled the database instance from db.r5.large to db.r5.4xlarge and added two read replicas. The database was never the problem: CPU was at 5%, query execution time was under 10ms, and MySQL max_connections was 500 with only 10 used. The actual bottleneck was invisible in every dashboard they checked because nobody was watching HikariCP's pending thread count — the metric that would have told them 490 threads were waiting for a connection that 10 sleeping connections were holding indefinitely.

Root causespring.jpa.open-in-view was left at its Spring Boot default value of true. With OSIV enabled, each HTTP request acquires a database connection at the beginning of the request lifecycle — before any controller code runs — and holds that connection open until the HTTP response is fully written back to the client. This includes time spent in the controller, time spent serializing the response to JSON, time spent in any Spring filter or interceptor that runs after the controller returns, and time waiting for the client's TCP stack to acknowledge the response. A typical checkout API call on this platform executed SQL in 8ms but spent 2,200ms serializing a complex order object with nested product data to JSON — because lazy-loaded product associations were being triggered during Jackson serialization outside any explicit transaction boundary, and OSIV was keeping the connection open to service those lazy loads. With a pool of 10 connections and requests holding each connection for 2,200ms rather than 8ms, the effective throughput ceiling was 10 / 2.2 = 4.5 requests per second. At Black Friday traffic volumes the queue depth hit 500 in under two minutes.

FixSet spring.jpa.open-in-view=false immediately as an emergency configuration change deployed without a full release. This alone reduced average connection hold time from 2,200ms to 12ms and cleared the queue within 90 seconds of the change propagating. Followed up with: increased HikariCP maximum-pool-size from 10 to 20 using the formula (core_count * 2) + effective_spindle_count for the application server. Fixed the lazy-loading root cause by adding JOIN FETCH to the order query so all required associations were loaded within the transaction boundary — no lazy loads during serialization. Added HikariCP metrics to the existing Prometheus/Grafana stack: hikaricp.connections.active, hikaricp.connections.pending, and hikaricp.connections.timeout. Added a Grafana alert on hikaricp.connections.pending > 2 sustained for 60 seconds — this would have fired 8 minutes before the outage escalated.

Key Lesson

OSIV holds database connections for the entire HTTP request lifecycle including JSON serialization — disable it in production with spring.jpa.open-in-view=false from day one, not after your first connection pool outageHikariCP pool size must be tuned to your actual workload — the default of 10 is appropriate for local development and nothing else; calculate it as (core_count * 2) + effective_spindle_countMonitor hikaricp.connections.pending, not just hikaricp.connections.active — pending threads are the leading indicator of pool exhaustion and fire minutes before requests start timing outConnection pool exhaustion cascades — when all connections are held, every new request queues and eventually times out; the platform appears down even though MySQL is healthy and barely utilizedEmergency scaling made this incident worse — adding application instances multiplied the connection demand against the same MySQL instance without increasing pool capacity proportionally

Production Debug GuideWhen Spring Boot with MySQL and JPA behaves unexpectedly in production, here is how to go from observable symptom to root cause to resolution. These are the paths I have walked through actual incidents — not hypothetical scenarios.

Application hangs on every database query — requests timeout after 30 seconds→Check HikariCP pool state first: GET /actuator/metrics/hikaricp.connections.active and /actuator/metrics/hikaricp.connections.pending. If active equals maximum-pool-size and pending is greater than zero, the pool is exhausted — not MySQL. Run SHOW PROCESSLIST on MySQL to confirm: if you see connections in 'Sleep' state for longer than your average request duration, OSIV is the culprit. Check spring.jpa.open-in-view — if it is true or not set (defaults to true), set it to false and redeploy. Check for slow queries holding connections: SELECT * FROM information_schema.processlist WHERE command != 'Sleep' ORDER BY time DESC. Do not scale the database until you have confirmed the database is actually the bottleneck.

Queries that are fast in MySQL Workbench are slow in the application — 50ms in isolation, 2 seconds from the API→Enable spring.jpa.show-sql=true temporarily and count the SELECT statements for a single API request. If you see more than 3–4 selects for what should be a single query, you have an N+1 problem. Check if LAZY-loaded collections are being initialized outside a @Transactional context — each initialization triggers an additional roundtrip. Run EXPLAIN on the generated SQL in MySQL Workbench to verify the query plan is using indexes. Check if Hibernate is selecting all columns when only a subset are needed — consider DTO projections for read-heavy endpoints.

Duplicate key exceptions on insert — even though the data looks unique from the application's perspective→Check the @GeneratedValue strategy — GenerationType.IDENTITY relies on MySQL auto_increment and must match the column definition exactly. GenerationType.SEQUENCE requires a database sequence object that MySQL 8.0 supports but requires explicit setup. Check if the same entity is being passed to save() twice in the same @Transactional block — in some cases the Persistence Context detects it as a new entity both times. Check for concurrent requests hitting the same insert path: add a UNIQUE constraint at the database level and handle DataIntegrityViolationException at the service layer rather than assuming the application prevents duplicates.

LazyInitializationException — failed to lazily initialize a collection of role→The collection or association was accessed after the @Transactional boundary closed and the Persistence Context was released. Three fixes in order of preference: (1) add JOIN FETCH to the repository query so the association is loaded within the transaction, (2) add @EntityGraph(attributePaths = {"roles"}) to the repository method to load the association eagerly without modifying the JPQL, (3) ensure the service method that accesses the collection is annotated @Transactional so the Persistence Context stays open. Never enable OSIV as a workaround for LazyInitializationException in production.

Schema validation fails on startup — 'Schema-validation: wrong column type encountered in column' or 'Schema-validation: missing column'→The entity definition does not match the MySQL table schema. Run a manual schema diff: compare the column type, nullable constraint, length, precision, and scale in your @Column annotation against the actual column in information_schema.columns. BigDecimal precision/scale mismatches are particularly common — @Column(precision=10, scale=2) must match DECIMAL(10,2) exactly. Check that Flyway migrations are running before Hibernate schema validation — if Flyway has not applied the latest migration, validation will fail against the old schema. Set spring.jpa.hibernate.ddl-auto=none temporarily and run Flyway migrations manually to diagnose migration ordering issues.

OutOfMemoryError during batch processing — heap fills up during bulk inserts or bulk updates→JPA's Persistence Context caches every entity loaded or persisted during a transaction as a managed object. Inserting 100,000 entities in a single transaction without clearing the Persistence Context means all 100,000 entity objects plus their snapshot copies for dirty checking live in heap simultaneously. Fix: call entityManager.flush() followed by entityManager.clear() every N entities (500 is a reasonable starting point). Enable Hibernate batch inserts with hibernate.jdbc.batch_size=500 and hibernate.order_inserts=true to reduce roundtrips. If memory pressure persists, capture a heap dump with jcmd $(pgrep -f spring-boot) GC.heap_dump /tmp/heap.hprof and analyze with Eclipse MAT to confirm the Persistence Context is the source.

Connecting a Java application to a database used to be a nightmare of boilerplate JDBC code, manual connection pooling, and error-prone SQL strings embedded in Java classes. Spring Boot with MySQL and JPA eliminates that friction — it is one of the most frequently used combinations in the Java ecosystem for a reason.

By leveraging the Repository pattern and Object-Relational Mapping (ORM), Spring Boot allows you to interact with your MySQL database using plain Java objects. Hibernate, the default JPA provider, handles SQL generation, connection management, transaction boundaries, and schema validation. You focus on the business problem; the framework handles the plumbing.

In this guide we break down exactly what Spring Boot with MySQL and JPA does, why it was designed this way, and how to configure it correctly for production. That means going beyond the getting-started tutorial — we cover HikariCP tuning, the N+1 problem and its fixes, OSIV and why it will eventually cause a connection pool outage if you leave it enabled, and the right way to manage schema changes without letting Hibernate near your production tables.

We target Spring Boot 3.2.x with Jakarta Persistence 3.1 (the post-javax namespace migration) and MySQL 8.0+, ensuring the configuration and code in this guide works on the stack you are actually running in 2026, not the stack from a five-year-old tutorial.

By the end you will have both the conceptual understanding and practical code to build a production-ready persistence layer — one that performs well under load, fails safely when things go wrong, and does not surprise you with silent data corruption six months after launch.

What Is Spring Boot with MySQL and JPA and Why Does It Exist?

Spring Boot with MySQL and JPA exists to solve the 'Impedance Mismatch' — the fundamental structural difference between how Java models data (objects with fields and relationships) and how MySQL stores data (tables with rows and foreign keys). Bridging these two models manually through JDBC is possible but expensive: you write SQL strings, manage ResultSet iteration, map columns to fields, handle null values explicitly, manage connection lifecycle, and implement transactions by hand. Every CRUD operation is dozens of lines of infrastructure code that contributes nothing to the business problem.

JPA — the Java Persistence API, implemented by Hibernate — makes this mapping declarative. You annotate a Java class with @Entity and its fields with @Column, and Hibernate generates the SQL at startup. Spring Data JPA wraps Hibernate with a Repository abstraction that generates CRUD operations from an interface definition with zero implementation code. Spring Boot auto-configures HikariCP (the fastest connection pool in the JVM ecosystem), a Hibernate SessionFactory, and the transaction manager — all from the properties you provide in application.properties.

The result: what took 300 lines of JDBC boilerplate takes 30 lines of annotated entity class and a one-line repository interface. That is the reason this stack is the default choice for Java database persistence in 2026.

With Spring Boot 3.2.x, the namespace has migrated from javax.persistence to jakarta.persistence — all imports in this guide use the jakarta package. If you are migrating from Spring Boot 2.x, this is a breaking change that requires updating every @Entity, @Column, @Transactional, and @Id import in your codebase. The behavior is identical; only the package name changed.

io/thecodeforge/persistence/model/ForgeProduct.java · JAVA

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192

package io.thecodeforge.persistence.model;

import jakarta.persistence.*;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.AllArgsConstructor;
import org.hibernate.annotations.CreationTimestamp;
import org.hibernate.annotations.UpdateTimestamp;
import java.math.BigDecimal;
import java.time.LocalDateTime;

@Entity
@Table(
    name = "forge_products",
    indexes = {
        // Declare indexes here so Hibernate schema validation knows about them.
        // For production, create indexes in Flyway migrations, not via ddl-auto.
        @Index(name = "idx_product_name", columnList = "name"),
        @Index(name = "idx_product_category", columnList = "category_id")
    }
)
@Data
@NoArgsConstructor
@AllArgsConstructor
public class ForgeProduct {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    // nullable = false maps to NOT NULL in the schema — Hibernate validates this
    @Column(nullable = false, length = 100)
    private String name;

    // ALWAYS BigDecimal for money — never Double or Float.
    // precision = total digits, scale = digits after decimal point.
    // This maps to DECIMAL(12,2) in MySQL.
    @Column(nullable = false, precision = 12, scale = 2)
    private BigDecimal price;

    @Column(name = "category_id")
    private Long categoryId;

    // @CreationTimestamp — Hibernate sets this on INSERT, never on UPDATE.
    // updatable = false enforces this at the JPA column level too.
    @CreationTimestamp
    @Column(name = "created_at", nullable = false, updatable = false)
    private LocalDateTime createdAt;

    // @UpdateTimestamp — Hibernate updates this on every UPDATE automatically.
    @UpdateTimestamp
    @Column(name = "updated_at", nullable = false)
    private LocalDateTime updatedAt;
}

// --- Repository Interface ---
// No implementation class. Spring Data generates the implementation at startup
// via JDK dynamic proxies. The method names are parsed into JPQL at boot time.

package io.thecodeforge.persistence.repository;

import io.thecodeforge.persistence.model.ForgeProduct;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.math.BigDecimal;
import java.util.List;

@Repository
public interface ProductRepository extends JpaRepository<ForgeProduct, Long> {

    // Method-name-derived query — Spring Data parses this at startup.
    // Generates: SELECT * FROM forge_products WHERE LOWER(name) LIKE LOWER(?)
    List<ForgeProduct> findByNameContainingIgnoreCase(String name);

    // Custom JPQL for anything method names cannot express cleanly.
    // JPQL uses entity class names and field names, not table/column names.
    @Query("SELECT p FROM ForgeProduct p WHERE p.price > :minPrice ORDER BY p.price DESC")
    List<ForgeProduct> findExpensiveProducts(@Param("minPrice") BigDecimal minPrice);

    // Use @Modifying for UPDATE and DELETE — without it, Hibernate treats the
    // query as a SELECT and throws an exception.
    // clearAutomatically = true evicts affected entities from the Persistence
    // Context so subsequent reads reflect the updated values.
    @Modifying(clearAutomatically = true)
    @Query("UPDATE ForgeProduct p SET p.price = p.price * :multiplier WHERE p.categoryId = :categoryId")
    int applyPriceMultiplierByCategory(
        @Param("multiplier") BigDecimal multiplier,
        @Param("categoryId") Long categoryId
    );
}

▶ Output

INFO HikariPool-1 - Starting...
INFO HikariPool-1 - Start completed. Pool size: 20, Idle connections: 10
INFO Hibernate: create table forge_products (
id bigint not null auto_increment,
name varchar(100) not null,
price decimal(12,2) not null,
category_id bigint,
created_at datetime(6) not null,
updated_at datetime(6) not null,
primary key (id)
) engine=InnoDB
INFO Hibernate: create index idx_product_name on forge_products (name)
INFO Hibernate: create index idx_product_category on forge_products (category_id)

Mental Model

JPA Is a Leaky Abstraction — Know What Happens Under the Hood

JPA eliminates JDBC boilerplate but does not eliminate SQL. Every JPA operation translates to SQL eventually. The engineers who get burned by JPA are the ones who stopped thinking about SQL the moment they stopped writing it. Monitor what Hibernate generates, understand when it generates it, and design your entities and queries with the resulting SQL in mind.

@Entity maps a Java class to a MySQL table — field names become column names by default, @Column overrides naming and adds constraints
JpaRepository gives you save(), findById(), findAll(), deleteById() and pagination for free — Spring Data generates the implementation at startup via reflection
Method-name-derived queries (findByNameContainingIgnoreCase) are parsed and validated at startup — if the field name does not exist on the entity, the application will not start
@Query with JPQL operates on entity class names and field names, not table and column names — it is database-agnostic but Hibernate still validates it at startup
The Persistence Context (first-level cache) tracks managed entities — every entity you load or save within a @Transactional method is tracked, and changes are flushed automatically at commit via dirty checking
@Modifying is required for UPDATE and DELETE queries — without it, Hibernate refuses to execute the mutation and throws an exception at runtime

📊 Production Insight

A team inherited a codebase where a senior developer had used Double for the price field on a product entity — the reasoning at the time was 'it is just a display value.' Six months later, financial reconciliation reports began showing discrepancies. The gap between what the application calculated and what accounting expected totaled $4,300 across 18,000 transactions. No single transaction was wrong by more than a few cents, which is why it was invisible in testing.

Double uses IEEE 754 floating-point representation. The decimal value 0.1 cannot be represented exactly in binary floating-point — it is a repeating fraction. Add, multiply, and round enough of these inexact values and the errors accumulate. BigDecimal stores the exact decimal value without binary approximation, which is why every financial system in the world uses it.

Rule: any field that represents money, price, quantity, or any value that will be added or multiplied across many records uses BigDecimal with explicit precision and scale. No exceptions. The extra verbosity is worth exactly $4,300 per six months of production.

🎯 Key Takeaway

JPA bridges the object-relational impedance mismatch — @Entity annotations define the mapping, Hibernate generates the SQL, and Spring Data generates the CRUD implementations from an interface with no code.

Spring Boot 3.2.x uses the jakarta.persistence namespace — update all imports from javax.persistence when migrating from Spring Boot 2.x.

JPA is a leaky abstraction. Monitor the generated SQL with spring.jpa.show-sql=true during development. If you cannot describe the SQL a repository method generates, you cannot predict its performance under production load.

Choosing Between JPQL, Native SQL, and Method-Name Queries

IfSimple filtering on one or two fields with standard comparisons (equals, contains, in, between)

→

UseUse method-name-derived queries — findByNameContainingIgnoreCase, findByStatusIn, findByCategoryIdAndPriceBetween. Zero JPQL to write, validated at startup, readable without context.

IfJoins across entities, aggregations (COUNT, SUM), subqueries, or ORDER BY with complex logic

→

UseUse @Query with JPQL — SELECT p FROM ForgeProduct p JOIN FETCH p.category WHERE p.price > :min ORDER BY p.name. JPQL is database-agnostic and validated by Hibernate at startup.

IfMySQL-specific features: FULLTEXT search, JSON_EXTRACT, window functions (ROW_NUMBER, RANK), or performance-critical queries where you need full control over the execution plan

→

UseUse @Query(nativeQuery = true) with a SQL string — SELECT * FROM forge_products WHERE MATCH(name, description) AGAINST(:term IN BOOLEAN MODE). Test the query directly in MySQL Workbench first and verify the EXPLAIN plan before wiring it into the repository.

IfBulk UPDATE or DELETE that should not load entities into the Persistence Context

→

UseUse @Modifying + @Query — UPDATE ForgeProduct p SET p.price = p.price * :multiplier WHERE p.categoryId = :cat. Add clearAutomatically = true to evict affected entities from the first-level cache. Without @Modifying, Hibernate will throw an exception at runtime.

Common Mistakes and How to Avoid Them

The two most damaging mistakes with Spring Boot JPA are both invisible during development and only reveal themselves under production load. The first is the N+1 Select Problem — the application makes one query to fetch a list of parent entities and then N additional queries to fetch the associated child entities for each parent. With 10 records in your development database, this executes 11 queries and is unnoticeable. With 500 records in production, it executes 501 queries and the endpoint takes 8 seconds instead of 50 milliseconds.

The second is OSIV (Open Session in View) left at its default value of true. During development, this feels like a feature — you can access lazy-loaded associations anywhere in your code without worrying about transaction boundaries. In production, it means every HTTP request holds a database connection from the moment it enters the DispatcherServlet until the moment the HTTP response bytes are flushed to the client. With a pool of 20 connections and serialization taking 500ms per request, your effective throughput ceiling is 20 / 0.5 = 40 requests per second regardless of how fast your SQL executes.

Below is the full production application.properties configuration. Every setting is here for a reason — the comments explain the non-obvious ones.

src/main/resources/application.properties · PROPERTIES

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475

# =========================================================
# MySQL Connection — all sensitive values from environment
# variables, never hardcoded in application.properties
# =========================================================
spring.datasource.url=jdbc:mysql://${DB_HOST:localhost}:3306/${DB_NAME:forge_db}?useSSL=true&requireSSL=true&serverTimezone=UTC&allowPublicKeyRetrieval=false&rewriteBatchedStatements=true
spring.datasource.username=${DB_USER:forge_user}
spring.datasource.password=${DB_PASS:}
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# =========================================================
# HikariCP Connection Pool
# Formula: maximum-pool-size = (core_count * 2) + effective_spindle_count
# For a 4-core server with SSD storage: (4 * 2) + 1 = 9, round up to 10.
# Going above 20-30 on a single MySQL instance rarely helps and can cause
# mutex contention on MySQL's internal lock manager.
# =========================================================
spring.datasource.hikari.pool-name=ForgeHikariPool
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=10

# How long (ms) a caller waits for a connection before failing with an exception.
# 20 seconds is generous for most workloads — consider 5-10s for latency-sensitive APIs.
spring.datasource.hikari.connection-timeout=20000

# How long (ms) an idle connection stays in the pool before being closed.
# 5 minutes is a reasonable default — shorter for serverless/bursty workloads.
spring.datasource.hikari.idle-timeout=300000

# Maximum lifetime of a connection in the pool regardless of idle state.
# Must be shorter than MySQL's wait_timeout (default 8 hours, commonly set to
# 10-30 minutes on managed cloud instances). Set to 25 minutes to be safe.
spring.datasource.hikari.max-lifetime=1500000

# Validates that the connection is alive before handing it to the caller.
# MySQL 8.0+ supports SELECT 1 as the keepalive query.
spring.datasource.hikari.connection-test-query=SELECT 1
spring.datasource.hikari.keepalive-time=60000

# =========================================================
# JPA / Hibernate
# =========================================================

# validate: check entities match schema at startup; fail fast if they do not.
# NEVER use create, create-drop, or update in production.
# Schema changes belong in Flyway migrations, not in Hibernate's DDL generation.
spring.jpa.hibernate.ddl-auto=validate

# false in production — show-sql floods your log with SQL and has measurable
# throughput impact under load. Enable temporarily for N+1 debugging.
spring.jpa.show-sql=false
spring.jpa.properties.hibernate.format_sql=false

# MySQL8Dialect is correct for MySQL 8.0+.
# Without this, Hibernate may generate SQL using syntax not optimized for MySQL 8.
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect

# =========================================================
# OSIV — Open Session in View
# Default is true. This is wrong for production.
# Set to false: database connections are released when @Transactional ends,
# not when the HTTP response is written. This is the single highest-impact
# JPA configuration change for connection pool efficiency.
# =========================================================
spring.jpa.open-in-view=false

# =========================================================
# Hibernate Batch Processing
# Enables JDBC-level batching: multiple INSERT/UPDATE statements are sent
# to MySQL in a single network round-trip instead of one per statement.
# Requires rewriteBatchedStatements=true in the JDBC URL (already set above).
# =========================================================
spring.jpa.properties.hibernate.jdbc.batch_size=500
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true
spring.jpa.properties.hibernate.generate_statistics=false

▶ Output

INFO ForgeHikariPool - Starting...
INFO ForgeHikariPool - Start completed. Pool size: 20, Idle connections: 10
INFO HibernateJpaVendorAdapter - Hibernate validation mode: validate
INFO SchemaValidator - Schema validation successful — all entities match the database schema
WARN spring.jpa.open-in-view is disabled — LazyInitializationException will be thrown if lazy associations are accessed outside @Transactional

⚠ Two Settings That Will Eventually Cause a Production Incident If You Leave Them at Defaults

spring.jpa.open-in-view defaults to true and spring.jpa.hibernate.ddl-auto defaults to create-drop in some contexts. The first will cause connection pool exhaustion under moderate load — it is not a matter of if, it is a matter of when your traffic crosses the threshold. The second will drop and recreate your tables on every application restart if triggered in the wrong environment. These are not edge cases. They are the two most common causes of Spring Boot JPA production incidents I have seen across teams. Set open-in-view=false and ddl-auto=validate before you ship anything.

📊 Production Insight

A team I worked with inherited a codebase with spring.jpa.open-in-view=true and a 10-connection pool. The application had been running fine for two years with 150 daily active users. They ran a Black Friday promotion and daily active users hit 1,200 for 6 hours. The connection pool exhausted within 4 minutes of the traffic spike. The on-call engineer spent 45 minutes diagnosing before finding the OSIV setting — one line in application.properties that had never been questioned.

After setting open-in-view=false and deploying, the pending connection count dropped from 340 to 0 within 2 minutes. The pool of 10 connections was sufficient for the load — connections were just being held 200x longer than they needed to be.

Rule: always set spring.jpa.open-in-view=false. The LazyInitializationException it occasionally causes during development is a feature, not a bug — it forces you to design your transaction boundaries correctly instead of relying on OSIV as a crutch.

🎯 Key Takeaway

The N+1 problem is invisible in development (small datasets) and catastrophic in production (real datasets). Enable spring.jpa.show-sql=true during development and count SELECT statements per request. If the count grows linearly with the result set size, you have an N+1 problem. Fix it with JOIN FETCH or @EntityGraph before it reaches production.

OSIV holds database connections for the entire HTTP request lifecycle — JSON serialization, view rendering, Spring filters, and client TCP acknowledgment all happen while your connection is held. Set spring.jpa.open-in-view=false unconditionally in production.

HikariCP pool size is not a 'set it and forget it' configuration — monitor hikaricp.connections.pending in production and alert on any non-zero value sustained for more than 60 seconds. A pending count above zero means requests are queuing for connections, which means your pool is undersized for your current load.

HikariCP Pool Sizing Decisions

IfLocal development or small application with fewer than 50 concurrent users

→

UseUse maximum-pool-size=10 with minimum-idle=5. The default is appropriate here — do not over-provision for development workloads.

IfMedium application with 50–500 concurrent users and mixed read/write workload

→

UseUse maximum-pool-size = (core_count * 2) + 1. For a 4-core server: 9, rounded up to 10. For an 8-core server: 17. Set minimum-idle equal to maximum-pool-size for steady-state workloads where you want connections pre-warmed.

IfHigh-traffic application where the pool is frequently saturated and queries are short (under 50ms)

→

UseUse maximum-pool-size=20–30 and add ProxySQL or PgBouncer as an external connection pooler in front of MySQL. A single MySQL instance handles connection overhead poorly above 200 simultaneous connections — pooling at the proxy level multiplexes connections more efficiently.

IfMicroservice with very short-lived queries (under 10ms) and spiky traffic patterns (serverless, event-driven)

→

UseUse maximum-pool-size=5 with minimum-idle=2 and connection-timeout=5000. Short queries release connections quickly so a smaller pool serves more throughput. Aggressive timeout (5s) fails fast during pool exhaustion spikes rather than queuing requests for 30 seconds.

🗂 Traditional JDBC vs. Spring Boot JPA

JPA eliminates the JDBC boilerplate and provides database portability through Hibernate dialects. The trade-off is less direct control over generated SQL and N+1 query risks that do not exist when you write every SQL statement by hand. Choose the right tool per use case — some teams use both in the same application.

Aspect	Traditional JDBC	Spring Boot JPA
Boilerplate	High — manual Connection acquisition from pool, PreparedStatement creation, ResultSet iteration, and field-by-field mapping for every query. A simple findById is 20+ lines.	Zero — @Entity annotation and JpaRepository interface. The `save()`, findById(), findAll(), deleteById() implementations are generated at startup. A complete CRUD repository is 5 lines.
SQL Writing	Required for every operation including the simplest CRUD. You write, maintain, and test every SQL string — including handling dialect differences between MySQL versions.	Optional for standard CRUD — methods like `save()` and findById() are dynamically generated. Custom queries use JPQL (database-agnostic) or native SQL for MySQL-specific features.
Database Portability	Low — SQL strings are often written for a specific MySQL version or syntax. Migrating to PostgreSQL or another engine requires rewriting queries.	High — Hibernate dialects translate JPQL to the correct SQL for each database engine. Switching from MySQL to PostgreSQL in tests is a configuration change, not a code change.
Transaction Management	Manual — conn.setAutoCommit(false), `conn.commit()`, and `conn.rollback()` on every method that requires transactional semantics. Easy to forget, easy to leak.	Declarative — add @Transactional to the service method and Spring handles commit on success and rollback on any RuntimeException. Transaction boundaries are visible and auditable.
Performance Visibility	High — you write every SQL statement, so you know exactly what runs, when it runs, and what parameters it uses. No hidden queries, no surprise N+1 patterns.	Lower by default — Hibernate generates SQL behind the scenes. N+1 queries, unnecessary full-column SELECTs, and redundant dirty-check flushes are invisible without explicit SQL logging.
Connection Pooling	Manual setup required — you choose HikariCP, DBCP2, or C3P0, add the dependency, and configure it explicitly. Nothing is auto-configured.	Auto-configured — HikariCP is on the classpath via spring-boot-starter-data-jpa and configured from spring.datasource.hikari.* properties. Zero code required for basic pooling.
Batch Operations	Full control — `PreparedStatement.addBatch()` and executeBatch() give you explicit JDBC-level batching. Best raw throughput for bulk inserts and updates.	Requires explicit configuration — hibernate.jdbc.batch_size, hibernate.order_inserts, and rewriteBatchedStatements=true in the JDBC URL. Also requires entityManager.flush()/clear() to prevent Persistence Context heap exhaustion.
Best for	High-throughput bulk operations, reporting queries returning millions of rows, MySQL-specific features (window functions, JSON operations), and teams that need deterministic SQL output.	Transactional CRUD operations, REST API backends, applications where development velocity matters more than maximum query throughput, and teams that want schema portability.

🎯 Key Takeaways

Spring Boot with MySQL and JPA maps Java objects to MySQL tables via annotations — Hibernate generates SQL, Spring Data generates CRUD repository implementations, and Spring Boot auto-configures HikariCP. What took 300 lines of JDBC boilerplate now takes 30 lines of entity class and a repository interface.
Spring Boot 3.2.x uses the jakarta.persistence namespace — if you are migrating from 2.x, update every @Entity, @Column, @Transactional, and @Id import from javax to jakarta. This is a breaking change with no behavioral difference.
Spring Data JPA repositories eliminate DAO boilerplate — method-name-derived queries (findByNameContainingIgnoreCase) are parsed and validated at startup, not at runtime. A startup failure means your method name is wrong — a runtime failure means your logic is wrong. This distinction saves significant debugging time.
Always use BigDecimal for monetary and financial fields, mapped to MySQL DECIMAL(precision, scale). Float and Double use IEEE 754 binary floating-point which cannot represent decimal fractions exactly — the resulting rounding errors accumulate silently across thousands of transactions.
The N+1 problem is invisible in development and catastrophic in production. Enable spring.jpa.show-sql=true during development, count SELECT statements per request, and fix any linear growth in query count with JOIN FETCH or @EntityGraph before the code ships.
Set spring.jpa.open-in-view=false unconditionally in production. OSIV holds database connections for the entire HTTP request lifecycle — including serialization time — which reduces your effective connection pool throughput by orders of magnitude under real load.
Set spring.jpa.hibernate.ddl-auto=validate in production and use Flyway for schema migrations. Hibernate should never autonomously modify your production schema — validation gives you the startup-time schema mismatch detection without the risk of data loss.
Monitor HikariCP metrics in production: hikaricp.connections.pending is the leading indicator of pool exhaustion and fires minutes before requests start timing out. Alert on any non-zero value sustained for 60 seconds.

⚠ Common Mistakes to Avoid

✕Overusing JPA for high-performance read-only queries — reporting and analytics endpoints

Symptom

Reporting endpoints that return 50,000+ rows take 30–60 seconds with JPA. Memory usage spikes to several gigabytes, GC pause frequency increases, and the application becomes unresponsive during large report generation. The SQL itself runs in 200ms in MySQL Workbench — the bottleneck is Hibernate materializing every row as a managed entity in the Persistence Context.

Fix

Use JdbcTemplate or @Query(nativeQuery = true) with a DTO projection for bulk read-only queries. JPA is optimized for transactional CRUD, not analytical reporting. For JPQL-based projections, use a constructor expression to avoid entity materialization: SELECT new io.thecodeforge.dto.ProductSummary(p.id, p.name, p.price) FROM ForgeProduct p WHERE p.categoryId = :catId. This returns DTOs instead of managed entities — no Persistence Context overhead, no dirty checking, no snapshot copies.

✕Calling repository.save() on a managed entity within a @Transactional method

Symptom

Developers call repository.save(entity) after modifying a managed entity that was loaded within the same @Transactional method. This triggers an additional SELECT (Hibernate checking whether to INSERT or UPDATE via SimpleJpaRepository.save()) followed by a redundant UPDATE — even though Hibernate's dirty checking would have generated the same UPDATE automatically at transaction commit. In methods that process hundreds of entities, this doubles the SQL statement count.

Fix

Understand Hibernate dirty checking: entities loaded within a @Transactional method are managed. Modifying any field on a managed entity is automatically detected and flushed as an UPDATE at commit — no save() call required. Only call save() when: (1) the entity is new and has never been persisted, or (2) the entity was loaded outside a transaction (detached state) and you need to merge it back. Annotate query-only service methods with @Transactional(readOnly = true) — this tells Hibernate to skip dirty checking entirely, reducing flush overhead for pure read workloads.

✕Ignoring the N+1 Select Problem — accessing lazy collections without JOIN FETCH

Symptom

An API endpoint that returns 200 categories with their associated products executes 1 query for categories and 200 queries for products — one per category. In development with 10 categories this is 11 queries and takes 15ms. In production with 200 categories this is 201 queries and takes 3 seconds. The SQL log shows hundreds of identical SELECT statements against the forge_products table with different category_id values — the unmistakable signature of N+1.

Fix

Three approaches in order of preference: (1) JOIN FETCH in JPQL — SELECT c FROM Category c JOIN FETCH c.products WHERE c.active = true. One SQL JOIN query instead of N+1 separate selects. (2) @EntityGraph on the repository method — @EntityGraph(attributePaths = {"products"}) List<Category> findByActiveTrue(). Equivalent result, better separation between the query strategy and the JPQL. (3) @BatchSize(size = 25) on the collection field — Hibernate loads products in batches of 25 instead of one-by-one, reducing N+1 to ceil(N/25)+1 queries. Use @BatchSize when you cannot modify the query but need to reduce roundtrip count.

✕Keeping Open Session in View (OSIV) enabled in production

Symptom

Database connections are held for the entire HTTP request lifecycle — from the first line of DispatcherServlet.doDispatch() to the last byte of the HTTP response. With a 20-connection pool and serialization taking 500ms per request, the effective throughput ceiling is 40 requests per second, regardless of how fast the SQL runs. Under moderate traffic spikes, hikaricp.connections.pending climbs above zero and requests start queuing. Under sustained load, the queue grows unbounded and requests timeout.

Fix

Set spring.jpa.open-in-view=false. Connections are released immediately when the @Transactional method returns — the pool is available for the next request while serialization continues on the current request. The consequence: any lazy-loaded association accessed outside a @Transactional method will throw LazyInitializationException. Treat this as correct behavior — it forces you to explicitly define what data is loaded in the transaction boundary using JOIN FETCH or @EntityGraph. This produces better-designed code, not just better performance.

✕Using Double or Float for financial or monetary fields

Symptom

Financial calculations show rounding discrepancies — $10.01 stored as Double may retrieve as 10.009999999999999. Across thousands of transactions, these sub-cent errors accumulate into reportable discrepancies. Auditors flag the system. Financial reconciliation fails. The errors are silent — no exception is thrown, no warning is logged — because Double arithmetic is technically correct for binary floating-point, just not for decimal arithmetic.

Fix

Use BigDecimal for every monetary field. Map to MySQL DECIMAL with explicit precision and scale: @Column(precision = 12, scale = 2) maps to DECIMAL(12,2) in MySQL. Never use Float or Double for any value that will be added, multiplied, or compared across transactions. For arithmetic operations, always use BigDecimal's own methods: bigDecimal.add(), bigDecimal.multiply(), bigDecimal.setScale(2, RoundingMode.HALF_UP). Do not convert to Double for calculation and back to BigDecimal — the precision loss survives the roundtrip.

✕Not setting ddl-auto=validate in production and not using Flyway for schema migrations

Symptom

A developer accidentally left ddl-auto=create-drop on a production profile during a deployment. The application started, Hibernate dropped all tables, recreated them empty, and the application then ran against a database with no data. In a less catastrophic scenario, ddl-auto=update silently modified column precision from DECIMAL(12,2) to DECIMAL(10,2) because a developer changed the @Column annotation precision — truncating all price values with more than 10 digits.

Fix

Set spring.jpa.hibernate.ddl-auto=validate in every environment except local development. Validation checks that entity annotations match the actual schema at startup and fails the application if they do not — it never modifies the schema. Manage schema changes with Flyway: SQL migration files named V1__initial_schema.sql, V2__add_product_index.sql are stored in src/main/resources/db/migration. Spring Boot auto-configures Flyway when flyway-core is on the classpath — migrations run automatically on startup before Hibernate schema validation. Schema changes are versioned, reviewed in pull requests, and auditable in Git history.

Interview Questions on This Topic

QWhat is the internal difference between FetchType.LAZY and FetchType.EAGER? In what scenario would LAZY still cause an exception?Mid-levelReveal
FetchType.EAGER loads the associated entity or collection in the same SQL query as the parent — typically via an outer JOIN. The association is available immediately after the parent is loaded, regardless of whether you are inside or outside a transaction. FetchType.LAZY defers loading until the association is accessed — Hibernate creates a proxy object (a subclass of the target entity generated by ByteBuddy at runtime) that intercepts the first getter call and executes a SELECT at that point. LAZY is the default for @OneToMany and @ManyToMany collections. EAGER is the default for @ManyToOne and @OneToOne in older JPA spec versions — this default causes many accidental N+1 patterns because every @ManyToOne join is fetched on every query, even when you do not need it. LAZY throws LazyInitializationException when the proxy's getter is called after the Persistence Context has been closed. This happens when: (1) the parent entity was loaded in a @Transactional service method and returned to a controller or test that accesses the lazy collection after the transaction committed, (2) OSIV is disabled (spring.jpa.open-in-view=false) and a lazy collection is accessed during JSON serialization, or (3) the entity was serialized to a session or cache and deserialized — the proxy loses its EntityManager reference. Fixes in order of preference: JOIN FETCH in the query to load the association within the transaction, @EntityGraph on the repository method, or ensuring the collection access happens within the @Transactional boundary.
QDescribe three different ways to solve the N+1 Select Problem in Spring Data JPA. When would you choose each?Mid-levelReveal
(1) JOIN FETCH in JPQL — SELECT c FROM Category c JOIN FETCH c.products WHERE c.active = true. Hibernate generates a single SQL JOIN query. Choose this when you always need the association loaded and you want the query in the repository to self-document what it fetches. Trade-off: if the collection is large, the JOIN produces a wide result set with data duplication that Hibernate must deduplicate in memory. (2) @EntityGraph on the repository method — @EntityGraph(attributePaths = {"products"}) List<Category> findByActiveTrue(). Hibernate generates a JOIN equivalent to JOIN FETCH. Choose this when you want different fetch strategies for different callers of the same entity type — one method fetches with products, another fetches without. The entity definition stays clean without fetch=EAGER on the field. (3) @BatchSize(size = 25) on the collection field — @BatchSize(size = 25) @OneToMany private List<Product> products. When a lazy collection is initialized, Hibernate loads it in batches of 25 using SQL IN clauses instead of one query per parent. Choose this when you cannot modify the query (e.g., a third-party library owns the query) or when JOIN FETCH produces excessive data duplication for very large collections. Reduces N+1 to ceil(N/25)+1 queries — not a complete solution but a significant improvement. In practice, JOIN FETCH and @EntityGraph are the preferred solutions. @BatchSize is a mitigation when the other two are not applicable.
QHow does Hibernate's First Level Cache work, and how does it differ from a Second Level Cache like Hazelcast or Ehcache?SeniorReveal
The First Level Cache (Persistence Context) is scoped to a single EntityManager instance, which in Spring Boot corresponds to a single @Transactional method invocation. Every entity loaded or persisted within that transaction is stored in the Persistence Context keyed by entity type and primary key. A second findById(id) call within the same transaction returns the cached instance immediately — no SQL executed. The cache is automatically discarded when the transaction commits or rolls back. The Second Level Cache (L2) is scoped to the SessionFactory — effectively the application lifetime. It is shared across all transactions and all threads. When a transaction loads an entity, Hibernate checks L2 first. If the entity is in L2, no SQL is executed. When a transaction commits, it updates L2 with the new entity state. External providers (Hazelcast, Ehcache, Redis via hibernate-redis) implement the L2 SPI. Key differences: the L1 cache is always active and requires no configuration. The L2 cache must be explicitly enabled (hibernate.cache.use_second_level_cache=true) and configured with a provider. L2 requires careful invalidation strategy — if another application or a direct SQL UPDATE modifies the database without going through JPA, L2 becomes stale and returns incorrect data. L2 is appropriate for reference data that changes rarely and is read frequently (category lists, configuration tables, product metadata). It is inappropriate for data that changes frequently or is user-specific.
QWhat is LazyInitializationException and why does it occur after a transaction has closed?Mid-levelReveal
LazyInitializationException is thrown when application code tries to access a LAZY-loaded collection or association after the Persistence Context that loaded the parent entity has been closed. Inside a @Transactional method, the EntityManager is open and Hibernate can execute a SQL SELECT to initialize the lazy proxy when its getter is called. After the @Transactional method returns, Spring closes the EntityManager, returns the database connection to the pool, and releases the Persistence Context. Any lazy proxy on entities returned from that method now has no EntityManager to execute the initialization SQL — Hibernate throws LazyInitializationException. The most common scenario in Spring Boot: a service method annotated @Transactional loads a Category entity and returns it to the controller. The controller passes it to Jackson for JSON serialization. Jackson calls getProducts() on the category to serialize the products list. At this point, the transaction has committed, the EntityManager is closed, and the products collection is an uninitialized proxy. LazyInitializationException fires inside Jackson, producing a 500 response with no useful error message to the client. Four fixes: (1) add JOIN FETCH or @EntityGraph to the repository query so products are loaded before the transaction closes, (2) access the collection within the @Transactional service method and map to a DTO before returning, (3) use spring.jpa.open-in-view=true as a workaround — explicitly not recommended for production due to connection pool implications, (4) annotate the controller method @Transactional — technically works but couples your web layer to your persistence layer, which is an architectural violation.
QExplain Hibernate's Dirty Checking mechanism. How does Hibernate know which fields to include in an UPDATE statement?SeniorReveal
Dirty Checking is Hibernate's mechanism for automatically detecting changes to managed entities and generating UPDATE statements without explicit save() calls. When an entity is loaded within a @Transactional method, Hibernate stores a snapshot of the entity's field values in the Persistence Context alongside the entity reference — this is called the 'original state'. The snapshot is a shallow copy of every persistent field at the moment the entity was loaded or last flushed. At flush time (which occurs at transaction commit, before a query that might return stale data, or when explicitly called), Hibernate compares the current field values of every managed entity against its snapshot. If any field value differs, Hibernate generates an UPDATE statement. By default, Hibernate includes all columns in the UPDATE regardless of how many changed — this is the default behavior for UPDATE generation. You can change this to only include modified columns with @DynamicUpdate on the entity class, which tells Hibernate to compare field by field and generate a minimal UPDATE. @DynamicUpdate is useful for wide tables with many columns where full-row UPDATE statements create excessive I/O and lock contention. Performance implications: dirty checking compares every field of every managed entity at every flush. For transactions that load thousands of entities — bulk processing, report generation — this comparison overhead is significant. Use entityManager.clear() periodically to evict entities you no longer need from the Persistence Context, eliminating them from the dirty check cycle. Use @Transactional(readOnly = true) for query-only methods — Hibernate skips dirty checking entirely for read-only transactions.
QHow do you configure HikariCP for optimal performance, and what metrics should you monitor in production?SeniorReveal
HikariCP configuration starts with the pool size formula: maximum-pool-size = (core_count * 2) + effective_spindle_count. For a 4-core server with SSD storage (spindle count = 1): 9, rounded to 10. For an 8-core server: 17. This formula is derived from the HikariCP maintainer's benchmarks showing diminishing returns beyond this ceiling for OLTP workloads. Going above 30 connections on a single MySQL instance typically causes internal lock contention rather than improved throughput. minimum-idle should equal maximum-pool-size for consistent steady-state workloads — pre-warming connections avoids the latency spike of establishing new connections during traffic surges. For bursty workloads (serverless, event-driven), set minimum-idle lower (2–5) to reduce idle connection cost. max-lifetime (default 1800000ms, 30 minutes) must be less than MySQL's wait_timeout setting. If MySQL closes an idle connection before HikariCP's max-lifetime expires, HikariCP hands a dead connection to the application. Default MySQL wait_timeout is 8 hours, but cloud-managed MySQL instances (RDS, Cloud SQL) often set it to 10–30 minutes. Set max-lifetime to 25 minutes as a safe default. connection-timeout (default 30000ms) is how long a caller waits for a connection before receiving an exception. 20–30 seconds is appropriate for most applications. For latency-sensitive APIs where a 30-second timeout is unacceptable, set it to 5 seconds and let the circuit breaker handle the failure gracefully. Metrics to monitor in production with Prometheus/Grafana: hikaricp.connections.active (current connections in use — alert if consistently above 80% of maximum), hikaricp.connections.pending (threads waiting for a connection — alert on any non-zero value sustained for 60 seconds; this is the leading indicator of pool exhaustion), hikaricp.connections.timeout (total connections that timed out waiting — any non-zero value indicates the pool is undersized), hikaricp.connections.acquire (latency histogram for connection acquisition — p99 above 100ms indicates pool pressure).

Frequently Asked Questions

When should I use JPA vs JdbcTemplate?

Use JPA for transactional CRUD operations where you benefit from: dirty checking (no explicit save() for managed entities), relationship management (@OneToMany, @ManyToOne with cascading), schema validation at startup, and database portability via Hibernate dialects. JPA is the right tool for the majority of Spring Boot application persistence — REST API backends, user data management, order processing, and any domain where you model relationships between entities.

Use JdbcTemplate or NamedParameterJdbcTemplate for: high-throughput bulk read operations returning millions of rows (reporting, analytics, data exports), bulk inserts where raw JDBC batching outperforms Hibernate batch configuration, MySQL-specific features where native SQL is required and JPQL cannot express the query, and cases where you need deterministic SQL output without the Persistence Context overhead.

In practice, many production applications use both. JPA handles the transactional write path and standard read queries. JdbcTemplate handles reporting endpoints and bulk processing. Spring Boot auto-configures a JdbcTemplate bean using the same HikariCP datasource — there is no additional configuration required to use both in the same application.

What is the difference between JPQL and native SQL in Spring Data JPA?

JPQL (Java Persistence Query Language) operates on entity class names and field names — not table and column names. SELECT p FROM ForgeProduct p WHERE p.price > :min is valid JPQL. Hibernate translates JPQL to the correct SQL dialect for the configured database at startup — the query is database-agnostic. Hibernate also validates JPQL at startup: if you reference a field that does not exist on the entity, the application fails to start. This is a significant advantage during development.

Native SQL in Spring Data JPA uses @Query(nativeQuery = true) and operates on table names and column names as they exist in MySQL. SELECT * FROM forge_products WHERE price > :min is native SQL. It is MySQL-specific and will not work on PostgreSQL without modification. It is not validated by Hibernate at startup — a typo in a column name produces a runtime exception, not a startup failure.

When to use native SQL: FULLTEXT search (MATCH(name) AGAINST(:term IN BOOLEAN MODE)), MySQL JSON functions (JSON_EXTRACT, JSON_ARRAYAGG), window functions (ROW_NUMBER() OVER, RANK() OVER), and performance-critical queries where you need to control the execution plan and JPQL generates suboptimal SQL. Always run the native query directly in MySQL Workbench first and verify the EXPLAIN plan before wiring it into the repository.

How do I handle database schema migrations in production?

Use Flyway for versioned, auditable, SQL-based schema migrations. Add the flyway-core dependency and place SQL migration files in src/main/resources/db/migration following the naming convention V{version}__{description}.sql — for example, V1__create_forge_products.sql, V2__add_category_index.sql, V3__add_updated_at_column.sql.

Spring Boot auto-configures Flyway when flyway-core is on the classpath. On startup, Flyway checks the flyway_schema_history table in MySQL to determine which migrations have already run, then executes any pending migrations in version order — before Hibernate schema validation runs. This ordering matters: Flyway applies the schema change, then Hibernate validates that the entity annotations match the updated schema.

Key practices: (1) migration files are append-only and immutable once merged to the main branch — never modify a migration that has run in any environment, (2) every schema change goes through a migration file, never through ddl-auto=update, (3) test migrations locally against a copy of production schema before deploying, (4) for large table alterations on production MySQL, use pt-online-schema-change (Percona Toolkit) or gh-ost to apply the change without table locks.

What is the difference between @Transactional(readOnly=true) and @Transactional()?

@Transactional(readOnly = true) tells Spring and Hibernate that the method performs only read operations. Three performance benefits: (1) Hibernate skips dirty checking entirely at flush time — no snapshot comparison, no UPDATE generation, measurably faster for methods that load many entities, (2) the database connection may be set to read-only mode (MySQL: SET SESSION TRANSACTION READ ONLY), which allows the MySQL optimizer to skip undo log overhead for read-only transactions, (3) if you have configured read-write splitting (a primary MySQL for writes, read replicas for reads), Spring's @Transactional(readOnly = true) is the signal that routes the query to a read replica.

@Transactional() (default) allows both reads and writes — Hibernate performs dirty checking at flush time, the transaction can contain INSERT/UPDATE/DELETE statements, and any RuntimeException triggers a rollback.

Annotate every service method @Transactional(readOnly = true) by default and override with @Transactional() (or no annotation) on methods that perform writes. This is a low-cost, high-value optimization that requires no architectural changes.

How do I batch insert 100,000 entities without running out of memory?

JPA's Persistence Context caches every entity loaded or persisted in memory as a managed object plus a snapshot copy for dirty checking. Inserting 100,000 entities in a single transaction without clearing the Persistence Context means 200,000 object references (100,000 entities + 100,000 snapshots) live in heap simultaneously — OutOfMemoryError is the predictable outcome.

The solution requires four steps working together: (1) set spring.jpa.properties.hibernate.jdbc.batch_size=500 to group INSERT statements into batches of 500 before sending to MySQL, (2) set spring.jpa.properties.hibernate.order_inserts=true to ensure inserts of the same entity type are grouped together — enabling the batch, (3) add rewriteBatchedStatements=true to the JDBC URL — this is the MySQL JDBC driver-level setting that rewrites individual INSERT statements into a multi-row INSERT, (4) call entityManager.flush() followed by entityManager.clear() every 500 entities in your batch loop. flush() sends the current batch to MySQL, clear() evicts all managed entities from the Persistence Context, making them eligible for garbage collection.

If memory pressure persists after these changes, or if maximum throughput is required, consider bypassing JPA entirely for the bulk insert and using JdbcTemplate's batchUpdate() method — it has zero Persistence Context overhead and is typically 3–5x faster than Hibernate batch inserts for large volumes.

How do I monitor JPA and MySQL performance in production?

Four layers of monitoring, each catching different classes of problems:

(1) HikariCP metrics via Actuator and Prometheus — add spring-boot-starter-actuator and micrometer-registry-prometheus. Key metrics: hikaricp.connections.active (current usage), hikaricp.connections.pending (leading indicator of pool exhaustion — alert at > 0 for 60 seconds), hikaricp.connections.timeout (count of timed-out requests — alert at any non-zero value), hikaricp.connections.acquire (p99 acquisition latency — alert above 100ms).

(2) Hibernate statistics — enable spring.jpa.properties.hibernate.generate_statistics=true in non-production environments only (statistics collection has CPU overhead). This logs query counts, entity load counts, cache hit ratios, and flush counts per session — invaluable for identifying N+1 patterns and unnecessary dirty-check flushes during load testing.

(3) Datasource proxy or P6Spy — add datasource-proxy-spring-boot-starter to log every SQL statement with execution time, parameter values, and the calling method. More precise than spring.jpa.show-sql=true because it shows actual bound parameter values and millisecond execution times. Use this during performance testing, not in production (log volume is significant).

(4) MySQL slow query log — SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; Queries taking longer than 1 second are logged to the slow query log with execution plan details. Combine with pt-query-digest (Percona Toolkit) to aggregate slow queries and identify the most expensive patterns across millions of log entries.

🔥

Naren Founder & Author

Developer and founder of TheCodeForge. I built this site because I was tired of tutorials that explain what to type without explaining why it works. Every article here is written to make concepts actually click.

About Naren Get in touch

Forged with 🔥 at TheCodeForge.io — Where Developers Are Forged