JPA N+1 Query Disaster — 1,001 Queries for 100 Orders

import jakarta.persistence.*;
import jakarta.persistence.EntityManager;
import jakarta.persistence.EntityManagerFactory;
import jakarta.persistence.Persistence;

// A minimal runnable JPA example using a persistence.xml
// Works with Hibernate 6+ and H2 in-memory database
public class PersistenceContextDemo {

    public static void main(String[] args) {

        // Bootstrap JPA — in Spring Boot this happens automatically
        EntityManagerFactory emFactory =
                Persistence.createEntityManagerFactory("demo-unit");

        EntityManager em = emFactory.createEntityManager();

        em.getTransaction().begin();

        // --- STATE 1: TRANSIENT ---
        // 'newProduct' is just a regular Java object. JPA has no idea it exists.
        Product newProduct = new Product();
        newProduct.setName("Mechanical Keyboard");
        newProduct.setPrice(149.99);
        System.out.println("State: TRANSIENT — id is null: " + newProduct.getId());

        // --- STATE 2: MANAGED ---
        // persist() hands the object to the persistence context.
        // JPA now tracks every field change on 'newProduct'.
        em.persist(newProduct);
        System.out.println("State: MANAGED — id assigned: " + newProduct.getId());

        // Dirty checking in action: we change a field WITHOUT calling any save method.
        // JPA will detect this change and generate an UPDATE automatically on commit.
        newProduct.setPrice(129.99); // <-- no em.save(), no em.update() needed
        System.out.println("Price changed to 129.99 — JPA will auto-flush this on commit");

        em.getTransaction().commit(); // Flush happens here — INSERT then UPDATE sent to DB

        // --- STATE 3: DETACHED ---
        // After the transaction commits, the entity is still in memory
        // but JPA is no longer tracking it.
        em.close(); // closing the EntityManager detaches all entities
        System.out.println("State: DETACHED — object still in memory, but JPA ignores changes");

        // Changing a detached entity does NOT touch the database
        newProduct.setPrice(99.99); // silently ignored by JPA
        System.out.println("Price changed to 99.99 — but the DB still shows 129.99!");

        // To persist changes on a detached entity, you must merge() it
        // in a new EntityManager session:
        EntityManager em2 = emFactory.createEntityManager();
        em2.getTransaction().begin();
        Product reattached = em2.merge(newProduct); // now JPA tracks changes again
        em2.getTransaction().commit();
        System.out.println("After merge and commit — DB now shows: " + reattached.getPrice());

        em2.close();
        emFactory.close();
    }
}

import jakarta.persistence.*;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

// ─── Order.java ───────────────────────────────────────────────
@Entity
@Table(name = "orders") // 'order' is a reserved SQL keyword — always quote or rename
public class Order {

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

    @Column(nullable = false)
    private String customerEmail;

    @Column(nullable = false)
    private LocalDateTime placedAt;

    // mappedBy = "order" means: "the 'order' field on OrderItem owns this relationship."
    // cascade = PERSIST, MERGE: saving/updating an Order auto-saves its items.
    // orphanRemoval = true: removing an item from this list deletes it from the DB.
    @OneToMany(
        mappedBy = "order",
        cascade = {CascadeType.PERSIST, CascadeType.MERGE},
        orphanRemoval = true,
        fetch = FetchType.LAZY  // default — explicitly written here for clarity
    )
    private List<OrderItem> items = new ArrayList<>();

    // ── Helper method to keep BOTH sides of the relationship in sync ──
    // This is the pattern senior devs use. Never call items.add() directly.
    public void addItem(OrderItem item) {
        items.add(item);       // update the 'one' side (in-memory list)
        item.setOrder(this);   // update the 'many' side (the foreign key owner)
    }

    public void removeItem(OrderItem item) {
        items.remove(item);
        item.setOrder(null);   // orphanRemoval will delete it from the DB
    }

    // Read-only view — prevents callers from bypassing addItem()
    public List<OrderItem> getItems() {
        return Collections.unmodifiableList(items);
    }

    // getters / setters
    public Long getId() { return id; }
    public String getCustomerEmail() { return customerEmail; }
    public void setCustomerEmail(String customerEmail) { this.customerEmail = customerEmail; }
    public LocalDateTime getPlacedAt() { return placedAt; }
    public void setPlacedAt(LocalDateTime placedAt) { this.placedAt = placedAt; }
}

// ─── OrderItem.java ───────────────────────────────────────────
@Entity
@Table(name = "order_items")
public class OrderItem {

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

    // @ManyToOne is the OWNING side — it holds the foreign key column 'order_id'
    // EAGER is the default for @ManyToOne, shown explicitly here
    @ManyToOne(fetch = FetchType.LAZY) // Override to LAZY to avoid unnecessary joins
    @JoinColumn(name = "order_id", nullable = false) // defines the FK column name
    private Order order;

    @Column(nullable = false)
    private String productSku;

    @Column(nullable = false, precision = 10, scale = 2)
    private BigDecimal unitPrice;

    @Column(nullable = false)
    private int quantity;

    // getters / setters
    public Long getId() { return id; }
    public Order getOrder() { return order; }
    public void setOrder(Order order) { this.order = order; }
    public String getProductSku() { return productSku; }
    public void setProductSku(String productSku) { this.productSku = productSku; }
    public BigDecimal getUnitPrice() { return unitPrice; }
    public void setUnitPrice(BigDecimal unitPrice) { this.unitPrice = unitPrice; }
    public int getQuantity() { return quantity; }
    public void setQuantity(int quantity) { this.quantity = quantity; }
}

// ─── Usage example (inside a @Transactional service) ──────────
public class OrderService {

    private final EntityManager em;

    public OrderService(EntityManager em) {
        this.em = em;
    }

    public Order createOrder(String customerEmail) {
        Order order = new Order();
        order.setCustomerEmail(customerEmail);
        order.setPlacedAt(LocalDateTime.now());

        OrderItem keyboard = new OrderItem();
        keyboard.setProductSku("KB-MX-RED");
        keyboard.setUnitPrice(new BigDecimal("149.99"));
        keyboard.setQuantity(1);

        OrderItem mousepad = new OrderItem();
        mousepad.setProductSku("MP-XL-BLK");
        mousepad.setUnitPrice(new BigDecimal("29.99"));
        mousepad.setQuantity(2);

        // Using the helper method — both sides stay consistent
        order.addItem(keyboard);
        order.addItem(mousepad);

        // cascade PERSIST means JPA will also INSERT both OrderItems
        em.persist(order);

        System.out.println("Order created with id: " + order.getId());
        System.out.println("Items count: " + order.getItems().size());
        return order;
    }
}

import jakarta.persistence.*;
import java.util.List;

public class OrderRepository {

    private final EntityManager em;

    public OrderRepository(EntityManager em) {
        this.em = em;
    }

    // ─── PROBLEM: N+1 Query ───────────────────────────────────────
    // This loads all orders in 1 query.
    // But the moment we call order.getItems() in a loop, Hibernate fires
    // a separate SELECT for each order's items. 50 orders = 51 queries.
    public void demonstrateNPlusOne() {
        List<Order> orders = em.createQuery("SELECT o FROM Order o", Order.class)
                               .getResultList();

        // This loop is the trap — each getItems() call hits the database
        for (Order order : orders) {
            System.out.println(order.getCustomerEmail()
                + " — items: " + order.getItems().size()); // N queries fired here
        }
    }

    // ─── SOLUTION 1: JOIN FETCH ────────────────────────────────────
    // Fetches orders AND their items in a single SQL JOIN.
    // Use DISTINCT to prevent duplicate Order objects from the join result set.
    public List<Order> findAllOrdersWithItems() {
        return em.createQuery(
            "SELECT DISTINCT o FROM Order o JOIN FETCH o.items",
            Order.class
        ).getResultList();
        // Generated SQL: SELECT DISTINCT o.*, oi.* FROM orders o
        //                INNER JOIN order_items oi ON oi.order_id = o.id
    }

    // ─── SOLUTION 2: @EntityGraph (Spring Data JPA style) ─────────
    // Cleaner API — define the graph on the entity or inline.
    // Shown here as a named query for clarity.
    public List<Order> findOrdersWithItemsViaEntityGraph() {
        EntityGraph<Order> graph = em.createEntityGraph(Order.class);
        graph.addAttributeNodes("items"); // tell JPA to eagerly load 'items'

        return em.createQuery("SELECT o FROM Order o", Order.class)
                 .setHint("jakarta.persistence.fetchgraph", graph)
                 .getResultList();
    }

    // ─── JPQL Projection: fetch only what you need ─────────────────
    // For a summary dashboard, you don't need full Order objects.
    // A DTO projection is faster — no entity tracking overhead.
    public List<OrderSummary> findOrderSummaries() {
        return em.createQuery(
            // Constructor expression — JPA calls new OrderSummary(email, count, total)
            "SELECT new com.example.OrderSummary(o.customerEmail, COUNT(i), SUM(i.unitPrice * i.quantity)) "
          + "FROM Order o JOIN o.items i "
          + "GROUP BY o.customerEmail",
            OrderSummary.class
        ).getResultList();
    }

    // ─── Named Query (defined on the entity with @NamedQuery) ───────
    // Validated at startup — typos fail fast, not at runtime.
    // On Order entity: @NamedQuery(name="Order.findByEmail",
    //                              query="SELECT o FROM Order o WHERE o.customerEmail = :email")
    public List<Order> findByCustomerEmail(String email) {
        return em.createNamedQuery("Order.findByEmail", Order.class)
                 .setParameter("email", email) // always use named params — prevents SQL injection
                 .getResultList();
    }
}

// ─── DTO for projection queries ────────────────────────────────
class OrderSummary {
    private final String customerEmail;
    private final long itemCount;
    private final java.math.BigDecimal totalValue;

    // JPA calls this constructor via the JPQL constructor expression
    public OrderSummary(String customerEmail, long itemCount, java.math.BigDecimal totalValue) {
        this.customerEmail = customerEmail;
        this.itemCount = itemCount;
        this.totalValue = totalValue;
    }

    @Override
    public String toString() {
        return customerEmail + " | Items: " + itemCount + " | Total: $" + totalValue;
    }
}

// ─── Step 1: Add Hibernate caching dependencies (Maven) ───────
// <dependency>
//     <groupId>org.hibernate.orm</groupId>
//     <artifactId>hibernate-jcache</artifactId>
// </dependency>
// <dependency>
//     <groupId>org.ehcache</groupId>
//     <artifactId>ehcache</artifactId>
//     <classifier>jakarta</classifier>
// </dependency>

// ─── Step 2: Configure in application.properties ──────────────
// spring.jpa.properties.hibernate.cache.use_second_level_cache=true
// spring.jpa.properties.hibernate.cache.region.factory_class=org.hibernate.cache.jcache.internal.JCacheRegionFactory
// spring.jpa.properties.javax.cache.provider=org.ehcache.jsr107.EhcacheCachingProvider

// ─── Step 3: Enable caching on an entity ───────────────────────
import jakarta.persistence.*;
import org.hibernate.annotations.Cache;
import org.hibernate.annotations.CacheConcurrencyStrategy;

@Entity
@Cacheable
@Cache(usage = CacheConcurrencyStrategy.READ_ONLY) // for reference data
public class ProductCategory {

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

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

    @Column(nullable = false)
    private String displayName;

    // getters and setters
}

// ─── Step 4: Manual cache eviction ─────────────────────────────
@Service
public class CacheService {

    public void evictAllSecondLevelCache(EntityManagerFactory emf) {
        emf.getCache().evictAll();
    }

    public void evictRegion(String regionName) {
        // Region name is typically the fully qualified entity class name
        emf.getCache().evict(regionName);
    }
}

import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;

@Service
public class OrderService {

    // ─── Basic Read-Only Transaction ─────────────────────────────
    @Transactional(readOnly = true)
    public List<Order> findAllOrders() {
        // No dirty checking, no unnecessary UPDATEs
        return em.createQuery("SELECT o FROM Order o", Order.class).getResultList();
    }

    // ─── Transaction with Custom Isolation ────────────────────────
    @Transactional(isolation = Isolation.REPEATABLE_READ)
    public Order updateOrderStatus(Long orderId, String status) {
        Order order = em.find(Order.class, orderId);
        order.setStatus(status);
        // Flush and commit happen automatically on method exit
        return order;
    }

    // ─── REQUIRES_NEW for Independent Audit ───────────────────────
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void logAudit(String action, Long entityId) {
        // This runs in its own transaction; rolls back independently
        AuditLog log = new AuditLog();
        log.setAction(action);
        log.setEntityId(entityId);
        em.persist(log);
    }

    // ─── Pitfall: Self-Invocation ─────────────────────────────────
    public void selfInvocationProblem() {
        // This call does NOT apply @Transactional from updateOrderStatus
        // because it's called from within the same class.
        updateOrderStatus(1L, "SHIPPED");
    }
}

// io.thecodeforge — database tutorial

// Dynamic filtering with type-safe Criteria API — no string concatenation
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Invoice> query = cb.createQuery(Invoice.class);
Root<Invoice> root = query.from(Invoice.class);

// Build filters dynamically: 2019 production incident — forgot to handle null status
List<Predicate> predicates = new ArrayList<>();
if (statusFilter != null) {
    predicates.add(cb.equal(root.get("status"), statusFilter));
}
if (totalMin != null) {
    predicates.add(cb.greaterThanOrEqualTo(root.get("totalAmount"), totalMin));
}

query.where(cb.and(predicates.toArray(new Predicate[0])));
query.orderBy(cb.desc(root.get("createdAt")));

List<Invoice> results = entityManager.createQuery(query).getResultList();

// io.thecodeforge — database tutorial

// Optimistic locking with retry for product inventory decrement
@Version
private Integer version;  // JPA auto-increments on every write

// Pessimistic locking for critical stock deduction
@Transactional
public void deductStock(Long productId, int quantity) {
    Product product = entityManager.find(
        Product.class, productId,
        LockModeType.PESSIMISTIC_WRITE  // Locks the row — others wait
    );
    
    if (product.getStock() < quantity) {
        throw new InsufficientStockException("Only " + product.getStock() + " available");
    }
    
    product.setStock(product.getStock() - quantity);
    entityManager.flush();  // Forces SQL UPDATE with lock held
}

// io.thecodeforge — database tutorial

-- SINGLE_TABLE inheritance: one table, discriminator column
CREATE TABLE payment (
    id BIGINT PRIMARY KEY,
    payment_type VARCHAR(20) NOT NULL,
    amount DECIMAL(10,2),
    card_number VARCHAR(16) NULL,
    check_number VARCHAR(10) NULL
);

-- JOINED strategy: normalized tables, joins required
CREATE TABLE subscription (
    id BIGINT PRIMARY KEY,
    plan VARCHAR(50)
);
CREATE TABLE trial_subscription (
    id BIGINT PRIMARY KEY,
    trial_end DATE,
    FOREIGN KEY (id) REFERENCES subscription(id)
);

// io.thecodeforge — database tutorial

-- Persistent table with embedded value object
CREATE TABLE customer (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    street VARCHAR(200),
    city VARCHAR(100),
    zip VARCHAR(10),
    account_status VARCHAR(20)
);

-- @Embeddable Address maps to street, city, zip
-- @Enumerated(STRING) maps account_status

INSERT INTO customer VALUES (1, 'Alice', '123 Oak', 'Springfield', '01101', 'ACTIVE');

// io.thecodeforge — database tutorial

-- Bulk update without loading entities
UPDATE account SET status = 'ARCHIVED'
WHERE last_login < '2020-01-01';

-- Batch insert requires SEQUENCE generator
CREATE SEQUENCE user_seq START 1 INCREMENT 50;

CREATE TABLE "user" (
    id BIGINT DEFAULT nextval('user_seq') PRIMARY KEY,
    name VARCHAR(100)
);

-- jdbc batch enabled, identity disables it

// io.thecodeforge — database tutorial
// Core persistence unit definition in persistence.xml
<persistence-unit name="TheCodeForgePU" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
    <class>io.thecodeforge.model.Instructor</class>
    <class>io.thecodeforge.model.Course</class>
    <properties>
        <property name="jakarta.persistence.jdbc.url" value="jdbc:postgresql://localhost:5432/thecodeforge"/>
        <property name="jakarta.persistence.jdbc.user" value="admin"/>
        <property name="jakarta.persistence.jdbc.password" value="secret"/>
        <property name="hibernate.hbm2ddl.auto" value="validate"/>
    </properties>
</persistence-unit>

// io.thecodeforge — database tutorial
// AuditModel mapped superclass and Instructor entity
@MappedSuperclass
public abstract class AuditModel {
    @Column(name = "created_at", nullable = false, updatable = false)
    private LocalDateTime createdAt = LocalDateTime.now();

    @Column(name = "updated_at")
    private LocalDateTime updatedAt;

    @Column(name = "created_by")
    private String createdBy;

    @Column(name = "updated_by")
    private String updatedBy;

    @PreUpdate
    public void onUpdate() { this.updatedAt = LocalDateTime.now(); }
}

@Entity @Table(name = "instructors")
public class Instructor extends AuditModel {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String email;
}

// io.thecodeforge — database tutorial
// Instructor and Course repository definitions
package io.thecodeforge.repository;

import io.thecodeforge.model.Instructor;
import io.thecodeforge.model.Course;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import java.util.List;

public interface InstructorRepository extends JpaRepository<Instructor, Long> {
    Instructor findByEmailIgnoreCase(String email);
}

public interface CourseRepository extends JpaRepository<Course, Long> {
    @Query("SELECT c FROM Course c JOIN FETCH c.instructor WHERE c.credits > :minCredits")
    List<Course> findCoursesWithInstructorByMinCredits(int minCredits);
}

// io.thecodeforge — database tutorial
// REST controller for Instructor resource
@RestController @RequestMapping("/api/instructors")
public class InstructorResource {
    private final InstructorRepository repo;
    private final ModelMapper mapper;

    public InstructorResource(InstructorRepository repo, ModelMapper mapper) {
        this.repo = repo;
        this.mapper = mapper;
    }

    @GetMapping("/{id}")
    public ResponseEntity<InstructorDTO> get(@PathVariable Long id) {
        Instructor entity = repo.findById(id).orElseThrow(() -> new NotFoundException("Instructor not found"));
        return ResponseEntity.ok(mapper.map(entity, InstructorDTO.class));
    }

    @PostMapping
    @ResponseStatus(HttpStatus.CREATED)
    public InstructorDTO create(@Valid @RequestBody InstructorCreateRequest request) {
        Instructor entity = mapper.map(request, Instructor.class);
        return mapper.map(repo.save(entity), InstructorDTO.class);
    }
}