Spring Boot JPA — Why OSIV Killed Your Connection Pool

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

// io.thecodeforge — java tutorial
// Shows the N+1 explosion and the fix

@Entity
public class Author {
    @Id @GeneratedValue
    private Long id;
    private String name;

    // THIS IS BROKEN IN PRODUCTION:
    @OneToMany(fetch = FetchType.EAGER, mappedBy = "author")
    private List<Book> books; // 1 query for author, N for each book
}

// Fix: LAZY + explicit JOIN FETCH
@Repository
public interface AuthorRepository extends JpaRepository<Author, Long> {
    @Query("SELECT a FROM Author a JOIN FETCH a.books WHERE a.id = :id")
    Optional<Author> findByIdWithBooks(@Param("id") Long id);
}

// Or use @EntityGraph
@EntityGraph(attributePaths = {"books"})
Optional<Author> findByName(String name);

// io.thecodeforge — java tutorial
// Demonstrating proper transaction boundaries

@Service
public class OrderService {
    private final OrderRepository repo;
    private final InventoryClient client;

    // WITHOUT @Transactional — dangerous
    public Order createAndNotify(Order order) {
        Order saved = repo.save(order);           // tx 1: commits immediately
        client.notifyWarehouse(saved.getId());     // tx 2: separate connection
        return saved;                              // what if client throws? No rollback
    }

    // WITH @Transactional — safe
    @Transactional
    public Order createAndNotifySafe(Order order) {
        Order saved = repo.save(order);
        client.notifyWarehouse(saved.getId());
        return saved;
    }
}

// io.thecodeforge — java tutorial
// Refactor @ManyToMany with extra columns

@Entity
public class EmployeeProject {
    @Id @GeneratedValue
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "employee_id")
    private Employee employee;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "project_id")
    private Project project;

    private LocalDate assignedDate;
    @Enumerated(EnumType.STRING)
    private Role role; // e.g., LEAD, CONTRIBUTOR
}

@Entity
public class Employee {
    @Id @GeneratedValue
    private Long id;

    @OneToMany(mappedBy = "employee", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<EmployeeProject> assignments;
}

// io.thecodeforge — java tutorial

// Generated by Spring Initializr — no manual dependency hell
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class ProductService {

    private final ProductRepository repo;

    public ProductService(ProductRepository repo) {
        this.repo = repo;
    }

    @Transactional
    public Product createProduct(String name, double price) {
        Product p = new Product();
        p.setName(name);
        p.setPrice(price);
        return repo.save(p);
    }
}

// io.thecodeforge — java tutorial

spring:
  profiles: dev
  datasource:
    url: jdbc:h2:mem:testdb;MODE=MySQL;DB_CLOSE_DELAY=-1
    driver-class-name: org.h2.Driver
  jpa:
    database-platform: org.hibernate.dialect.H2Dialect
    hibernate:
      ddl-auto: validate  # Catch schema drift early
---
spring:
  profiles: prod
  datasource:
    url: jdbc:mysql://prod-rds:3306/mydb?useSSL=false&allowPublicKeyRetrieval=true
    driver-class-name: com.mysql.cj.jdbc.Driver
  jpa:
    database-platform: org.hibernate.dialect.MySQLDialect

// io.thecodeforge — java tutorial

// Bad: layer-first structure (avoid)
// com.example.entity.User
// com.example.repository.UserRepository
// com.example.service.UserService

// Good: feature-first structure
package com.example.user;

import jakarta.persistence.*;

@Entity
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String email;
}

// UserRepository stays in same package
// UserService stays in same package
// UserController stays in same package

// io.thecodeforge — java tutorial

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRepository extends JpaRepository<User, Long> {
    Page<User> findByActiveTrue(Pageable pageable);
}

// Usage in service:
// Page<User> users = userRepository.findByActiveTrue(
//     PageRequest.of(0, 20, Sort.by("createdAt").descending())
// );