Spring Boot JPA — Why OSIV Killed Your Connection Pool

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
    );
}

# =========================================================
# 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

// === MANY-TO-ONE (owning side has the foreign key column) ===
@Entity
@Table(name = "forge_products")
public class ForgeProduct {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)  // LAZY is strongly recommended
    @JoinColumn(name = "category_id")
    private ForgeCategory category;
}

// === ONE-TO-MANY (inverse side, mappedBy points to owning field name) ===
@Entity
@Table(name = "forge_categories")
public class ForgeCategory {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @OneToMany(mappedBy = "category", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<ForgeProduct> products = new ArrayList<>();

    // Helper methods to keep both sides in sync
    public void addProduct(ForgeProduct product) {
        products.add(product);
        product.setCategory(this);
    }

    public void removeProduct(ForgeProduct product) {
        products.remove(product);
        product.setCategory(null);
    }
}

// === ONE-TO-ONE (owning side has the foreign key) ===
@Entity
@Table(name = "forge_users")
public class ForgeUser {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @OneToOne(mappedBy = "user", cascade = CascadeType.ALL)
    private ForgeUserProfile profile;
}

@Entity
@Table(name = "forge_user_profiles")
public class ForgeUserProfile {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id", unique = true)
    private ForgeUser user;
}

// === MANY-TO-MANY (requires join table) ===
@Entity
@Table(name = "forge_products")
public class ForgeProduct {
    @ManyToMany
    @JoinTable(
        name = "forge_product_tags",
        joinColumns = @JoinColumn(name = "product_id"),
        inverseJoinColumns = @JoinColumn(name = "tag_id")
    )
    private Set<ForgeTag> tags = new HashSet<>();
}

@Entity
@Table(name = "forge_tags")
public class ForgeTag {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToMany(mappedBy = "tags")
    private Set<ForgeProduct> products = new HashSet<>();
}

public interface ProductRepository extends JpaRepository<ForgeProduct, Long> {

    // === findBy — returns List (or Optional for single result) ===
    List<ForgeProduct> findByName(String name);
    List<ForgeProduct> findByNameIgnoreCase(String name);
    Optional<ForgeProduct> findFirstByNameOrderByPriceDesc(String name);
    List<ForgeProduct> findByPriceBetween(BigDecimal low, BigDecimal high);
    List<ForgeProduct> findByCategoryIdIn(List<Long> categoryIds);
    List<ForgeProduct> findByNameContainingIgnoreCase(String partialName);
    // === And / Or — combine conditions ===
    List<ForgeProduct> findByNameAndPrice(String name, BigDecimal price);
    List<ForgeProduct> findByNameOrCategoryId(String name, Long categoryId);
    // === OrderBy — sort on any field (ASC or DESC) ===
    List<ForgeProduct> findByCategoryIdOrderByPriceAsc(Long categoryId);
    List<ForgeProduct> findByCategoryIdOrderByPriceDescNameAsc(Long categoryId);
    // === countBy — returns long ===
    long countByCategoryId(Long categoryId);
    long countByPriceGreaterThan(BigDecimal minPrice);
    // === existsBy — returns boolean ===
    boolean existsByName(String name);
    boolean existsByCategoryIdAndPriceGreaterThan(Long categoryId, BigDecimal minPrice);
    // === deleteBy — returns void or int (must be @Modifying inside a transaction) ===
    // Note: deleteBy requires @Transactional and @Modifying from Spring Data 3.x+
    @Transactional
    void deleteByCategoryId(Long categoryId);
    @Transactional
    int deleteByNameIgnoreCase(String name);  // returns number of deleted rows
}

-- V1__create_forge_products.sql
-- Initial schema: create the forge_products table with proper column types and indexes.
-- This migration is idempotent (it runs only once per database).

CREATE TABLE forge_products (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(12,2) NOT NULL,
    category_id BIGINT,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    CONSTRAINT fk_product_category FOREIGN KEY (category_id) REFERENCES forge_categories(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX idx_product_name ON forge_products (name);
CREATE INDEX idx_product_category ON forge_products (category_id);

// Entity that is eligible for second-level caching
@Entity
@Table(name = "forge_categories")
@Cacheable  // This annotation makes Hibernate cache the entity in L2
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)  // READ_WRITE for mutable data, READ_ONLY for immutable
public class ForgeCategory {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, unique = true)
    private String name;

    // OneToMany collections are NOT cached by default; the L2 cache stores
    // only the entity itself. To cache the collection, add @Cache to the collection field.
    @OneToMany(mappedBy = "category")
    @Cache(usage = CacheConcurrencyStrategy.READ_WRITE)  // collection cache
    @BatchSize(size = 25)
    private List<ForgeProduct> products = new ArrayList<>();
}

// application.properties additions:
# Enable second-level cache
spring.jpa.properties.hibernate.cache.use_second_level_cache=true
# Specify the region factory class (Ehcache 3)
spring.jpa.properties.hibernate.cache.region.factory_class=org.hibernate.cache.jcache.JCacheRegionFactory
# Enable query cache (if you want to cache query results)
spring.jpa.properties.hibernate.cache.use_query_cache=true
# Specify the JCache provider
spring.cache.jcache.provider=org.ehcache.jsr107.EhcacheCachingProvider

# 5 Practice Projects for Spring Boot JPA

## Project 1: Inventory System (CRUD + basic query methods)
**Entities**: `Product` (id, name, quantity, price, createdAt), `Warehouse` (id, name, location)
**Relationships**: Many Products belong to one Warehouse (@ManyToOne)
**Tasks**:
- Implement full CRUD with JpaRepository
- Create a repository method: `findByPriceBetween` and `findByWarehouseLocationIgnoreCase`
- Test with an in-memory H2 database
- Write a Flyway migration for initial schema
- **Bonus**: Add a `@Query` that counts products by warehouse

## Project 2: Blog with Comments (one-to-many, pagination, N+1 fix)
**Entities**: `Post` (id, title, content), `Comment` (id, body, author, createdAt)
**Relationships**: One Post has many Comments (@OneToMany)
**Tasks**:
- Write a GET endpoint that returns paginated posts with comments
- Ensure queries do not cause N+1 (use JOIN FETCH or @EntityGraph)
- Add `@BatchSize` as an alternative fix
- Implement a `@Modifying` query to delete all comments by author
- **Bonus**: Enable second-level cache for `Post` metadata (title only) using Ehcache

## Project 3: Order Management with Relationships (many-to-many, cascade, orphanRemoval)
**Entities**: `Order` (id, orderDate, status, total), `Product` (id, name, price, stock), `OrderItem` (id, quantity, subtotal)
**Relationships**:
- Order has many OrderItems (@OneToMany, cascade = ALL, orphanRemoval = true)
- OrderItem has one Product (@ManyToOne)
- OrderItem is the join entity between Order and Product (many-to-many via composite pattern)
**Tasks**:
- Implement helper methods `addProduct(Product, int quantity)` that creates OrderItem and sets bidirectional relationships
- Ensure removing an OrderItem from the Order collection deletes it from DB (orphanRemoval)
- Add a repository method: `findByStatusAndOrderDateBetween`
- **Bonus**: Add a `@Query` that calculates total order value per customer using JPQL aggregation

## Project 4: Caching & Performance (L2 cache, connection pool monitoring)
**Entities**: Use any existing entities (e.g., `Product`, `Warehouse`)
**Tasks**:
- Integrate Ehcache second-level cache for `Warehouse` (read-heavy, rarely changes)
- Set up HikariCP metrics and expose via Actuator
- Create a load test with k6 or JMeter that simulates 100 concurrent users fetching products
- Measure connection pool metrics before and after disabling OSIV
- **Bonus**: Implement a `@QueryHints` with `org.hibernate.annotations.QueryHints.CACHEABLE` for a frequently executed query

## Project 5: Full E-Commerce Backend (all concepts combined)
**Entities**: `Customer` (id, name, email), `Order` (id, orderDate, status), `Product` (id, name, price, stock), `OrderItem` (id, quantity, unitPrice), `Category` (id, name), `Review` (id, rating, comment)
**Relationships**:
- Customer has many Orders
- Order has many OrderItems
- OrderItem belongs to one Product
- Product belongs to one Category
- Product has many Reviews
**Tasks**:
- Implement all repositories with custom query methods (findBy*, countBy*, existsBy*)
- Write Flyway migrations for initial schema and add indexes after load testing
- Disable OSIV and ensure all lazy loads are handled with JOIN FETCH or DTO projections
- Add second-level cache for Category (static) and review the impact
- Set up HikariCP pool monitoring with alerts on pending connections
- **Bonus**: Implement a bulk price update using `@Modifying` and batch clearing to avoid heap exhaustion