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