Entity Graph vs Lazy Loading: N+1 Query Problem Demonstration

A comprehensive Spring Boot application demonstrating the N+1 query problem in JPA/Hibernate and how to solve it using EntityGraph optimization. This project provides a practical, hands-on comparison between lazy loading and EntityGraph approaches with real-time query counting and detailed logging.

🎯 Purpose

This project serves as a proof of concept to demonstrate:

• The N+1 query problem that occurs with lazy loading
• How EntityGraph can solve this performance issue
• Real-time SQL query counting and monitoring
• Best practices for handling many-to-many relationships in JPA

📋 Table of Contents

• Features
• Architecture
• Quick Start
• API Endpoints
• Database Schema
• Query Comparison
• Technologies Used
• Project Structure
• Configuration
• Testing
• Performance Analysis
• Contributing

🚀 Features

Core Functionality

• N+1 Query Problem Demonstration: Shows how lazy loading can cause performance issues
• EntityGraph Optimization: Demonstrates the solution using JPA EntityGraph
• Real-time Query Counting: Custom StatementInspector tracks every SQL query
• Automated Testing: Comprehensive test suite validating query performance
• REST API: Endpoints to test both approaches via HTTP requests

Technical Features

• Many-to-Many Relationships: Book ↔ Author entities with proper JPA mapping
• DTO Pattern: Clean data transfer objects for API responses
• SQL Logging: Detailed SQL query logging with formatting
• H2 Console: In-memory database with web console for debugging
• YAML Configuration: Clean, hierarchical configuration structure

🏗️ Architecture

Entity Relationship Diagram

┌─────────────┐         ┌─────────────────┐         ┌─────────────┐
│   Author    │         │   book_authors  │         │    Book     │
├─────────────┤         │  (join table)   │         ├─────────────┤
│ id (PK)     │◄────────┤ author_id (FK)  │         │ id (PK)     │
│ name        │         │ book_id (FK)    │────────►│ title       │
│ email       │         └─────────────────┘         │ isbn        │
└─────────────┘                                     └─────────────┘

Application Layers

┌─────────────────────────────────────────────────────────┐
│                    REST Layer                           │
│  BookController - Exposes /lazy and /entity-graph APIs │
└─────────────────────────────────────────────────────────┘
                              │
┌─────────────────────────────────────────────────────────┐
│                  Service Layer                          │
│   BookService - Business logic and query strategies    │
└─────────────────────────────────────────────────────────┘
                              │
┌─────────────────────────────────────────────────────────┐
│                Repository Layer                         │
│  BookRepository - Data access with EntityGraph support │
└─────────────────────────────────────────────────────────┘
                              │
┌─────────────────────────────────────────────────────────┐
│                 Persistence Layer                       │
│     JPA/Hibernate with H2 In-Memory Database           │
└─────────────────────────────────────────────────────────┘

🚀 Quick Start

Prerequisites

• Java 21 or higher
• Maven 3.6+
• IDE (IntelliJ IDEA, Eclipse, or VS Code)

Installation & Running

1. Clone the repository

   git clone <repository-url>
   cd entity-graph

2. Run the application

   mvn spring-boot:run

3. Access the application

   • Application: http://localhost:8080
   • H2 Console: http://localhost:8080/h2-console
     • JDBC URL: jdbc:h2:mem:testdb
     • Username: sa
     • Password: (empty)

What Happens on Startup

1. Database Creation: H2 creates tables automatically
2. Data Population: SQL script inserts 20 authors and 10 books
3. Demo Execution: N+1 query demonstration runs automatically
4. Query Logging: All SQL queries are logged with counts

🌐 API Endpoints

Books API

Method	Endpoint	Description
GET	/api/books/lazy	Fetch books using lazy loading (N+1 problem)
GET	/api/books/entity-graph	Fetch books using EntityGraph (optimized)

Example Usage

Test Lazy Loading (N+1 Problem):

curl -X GET http://localhost:8080/api/books/lazy

Test EntityGraph (Optimized):

curl -X GET http://localhost:8080/api/books/entity-graph

Response Format

[
  {
    "id": 1,
    "title": "The Enchanted Forest",
    "isbn": "ISBN-978-0-100000-X",
    "authors": [
      {
        "id": 1,
        "name": "J.K. Rowling",
        "email": "jkrowling@literature.com"
      }
    ]
  }
]

🗄️ Database Schema

Sample Data

The application automatically populates the database with:

Authors (20 total)

• J.K. Rowling, Stephen King, George R.R. Martin
• Agatha Christie, J.R.R. Tolkien, Isaac Asimov
• And 14 more famous authors...

Books (10 total)

Book Title	Authors Count	Sample Authors
The Enchanted Forest	1	J.K. Rowling
Mystery of the Lost Key	2	Stephen King, George R.R. Martin
Dragons and Legends	3	Agatha Christie, J.R.R. Tolkien, Isaac Asimov
The Secret Garden Chronicles	4	Arthur Conan Doyle, Jane Austen, Mark Twain, Charles Dickens

⚡ Query Comparison

Lazy Loading (N+1 Problem)

-- Initial query to fetch all books
SELECT b.id, b.title, b.isbn FROM books b

-- Then N additional queries (one per book to fetch authors)
SELECT a.id, a.name, a.email FROM authors a 
JOIN book_authors ba ON a.id = ba.author_id 
WHERE ba.book_id = 1

SELECT a.id, a.name, a.email FROM authors a 
JOIN book_authors ba ON a.id = ba.author_id 
WHERE ba.book_id = 2

-- ... 8 more similar queries

Total Queries: 11 (1 + 10)

EntityGraph (Optimized)

-- Single query with JOIN to fetch everything
SELECT b.id, ba.book_id, a.id, a.email, a.name, b.isbn, b.title 
FROM books b 
LEFT JOIN book_authors ba ON b.id = ba.book_id 
LEFT JOIN authors a ON a.id = ba.author_id

Total Queries: 1

Performance Impact

• Query Reduction: 90% fewer queries (11 → 1)
• Network Round-trips: Significant reduction
• Database Load: Dramatically improved
• Response Time: Faster for larger datasets

🛠️ Technologies Used

Core Framework

• Spring Boot 3.5.6: Main application framework
• Spring Data JPA: Data persistence and repository layer
• Hibernate 6.6+: ORM implementation

Database

• H2 Database: In-memory database for development and testing
• Flyway/SQL Scripts: Database initialization

Development Tools

• Lombok: Reduces boilerplate code
• Maven: Build and dependency management
• SLF4J + Logback: Logging framework

Testing

• JUnit 5: Unit and integration testing
• Spring Boot Test: Testing Spring components

📁 Project Structure

src/
├── main/
│   ├── java/com/example/entitygraph/
│   │   ├── controller/
│   │   │   └── BookController.java              # REST endpoints
│   │   ├── dto/
│   │   │   ├── AuthorDto.java                   # Author data transfer object
│   │   │   └── BookDto.java                     # Book data transfer object
│   │   ├── entity/
│   │   │   ├── Author.java                      # Author JPA entity
│   │   │   └── Book.java                        # Book JPA entity with EntityGraph
│   │   ├── repository/
│   │   │   ├── AuthorRepository.java            # Author data access
│   │   │   └── BookRepository.java              # Book data access with EntityGraph
│   │   ├── service/
│   │   │   └── BookService.java                 # Business logic interface
│   │   ├── util/
│   │   │   ├── QueryCounter.java                # SQL query counter
│   │   │   └── CountingStatementInspector.java  # Hibernate query interceptor
│   │   ├── demo/
│   │   │   └── N1QueryDemoRunner.java           # Automated demonstration
│   │   └── EntityGraphApplication.java          # Main application class
│   └── resources/
│       ├── application.yaml                     # Application configuration
│       └── data.sql                             # Database initialization script
└── test/
    └── java/com/example/entitygraph/
        └── service/
            └── BookServiceTest.java             # Service layer tests

⚙️ Configuration

Application Configuration (application.yaml)

spring:
  application:
    name: entity-graph
  
  # Database Configuration
  datasource:
    url: jdbc:h2:mem:testdb
    driver-class-name: org.h2.Driver
    username: sa
    password:
  
  # JPA Configuration
  jpa:
    hibernate:
      ddl-auto: create-drop
    show-sql: true
    properties:
      hibernate:
        format_sql: true
        use_sql_comments: true
        session_factory:
          statement_inspector: com.example.entitygraph.util.CountingStatementInspector
  
  # SQL Initialization
  sql:
    init:
      mode: always

# Logging Configuration
logging:
  level:
    org.hibernate.SQL: DEBUG
    com.example.entitygraph: DEBUG

Key Configuration Features

• Query Logging: All SQL queries are logged with formatting
• Statement Inspector: Custom interceptor counts every query
• Auto-initialization: Database populated on startup
• H2 Console: Enabled for development debugging

🧪 Testing

Running Tests

# Run all tests
mvn test

# Run specific test class
mvn test -Dtest=BookServiceTest

# Run with detailed logging
mvn test -Dspring.profiles.active=test

Test Coverage

• N+1 Problem Validation: Verifies lazy loading causes multiple queries
• EntityGraph Optimization: Confirms single query execution
• Query Count Assertions: Validates exact number of SQL queries
• Data Integrity: Ensures correct data mapping and relationships

Sample Test Output

========================================
TESTING LAZY LOADING (N+1 PROBLEM)
========================================
Books fetched: 10
Total queries executed: 11
Expected: 11 queries (1 for books + 10 for authors)

========================================
TESTING ENTITY GRAPH (OPTIMIZED)
========================================
Books fetched: 10
Total queries executed: 1
Expected: 1 query (books with authors in single join)

📊 Performance Analysis

Query Execution Patterns

Scenario	Books Count	Queries (Lazy)	Queries (EntityGraph)	Improvement
10 books	10	11	1	90.9%
50 books	50	51	1	98.0%
100 books	100	101	1	99.0%

When to Use Each Approach

Use Lazy Loading When:

• You rarely need related entities
• Working with single entities
• Memory constraints are critical

Use EntityGraph When:

• You always need related entities
• Working with collections
• Performance is critical
• Reducing database round-trips

🔍 Monitoring & Debugging

Query Monitoring

The application includes a custom CountingStatementInspector that:

• Intercepts every SQL query
• Assigns sequential numbers to queries
• Logs queries with detailed formatting
• Provides real-time query counting

H2 Console

Access the H2 database console at http://localhost:8080/h2-console:

• Browse tables and data
• Execute custom SQL queries
• Verify data relationships
• Debug query execution

Log Analysis

Monitor application logs to see:

SQL Query #1: SELECT b.id, b.title, b.isbn FROM books b
SQL Query #2: SELECT a.id, a.name FROM authors a WHERE ...
SQL Query #3: SELECT a.id, a.name FROM authors a WHERE ...

📚 Additional Resources

• Spring Data JPA Documentation
• Hibernate EntityGraph Documentation
• N+1 Query Problem Explained
• Spring Boot Testing Documentation