Skip to main content

Chapter 4: Pagination and Performance

Introduction​

Problem: You have 100,000 products in the database. Do you display them all?

List<Product> products = productRepo.findAll();  // ❌ 100,000 objects in memory!

Result:

  • 💥 OutOfMemoryError
  • 🐢 Very slow application
  • 😡 Unhappy users

This chapter teaches you how to optimize!

What You Will Learn​

  1. Pagination with Pageable and Page<T>
  2. Sorting with Sort
  3. The N+1 problem and how to solve it
  4. Projections for performance

Duration: 1h

Pagination with Pageable​

The Concept​

Instead of loading everything:

Page 1: [Products 1-20]
Page 2: [Products 21-40]
Page 3: [Products 41-60]
...

The user sees 20 results at a time, not 100,000!

Code: Repository with Pagination​

public interface ProductRepository extends JpaRepository<Product, Long> {
    
    // Inherited method
    Page<Product> findAll(Pageable pageable);
    
    // Your method with pagination
    Page<Product> findByCategory(String category, Pageable pageable);
    
    // With @Query
    @Query("SELECT p FROM Product p WHERE p.price < :maxPrice")
    Page<Product> findCheap(@Param("maxPrice") Double maxPrice, Pageable pageable);
}

Key Points:

  • Pageable parameter last
  • Return Page<T> (not List<T>)

Usage: Create a Pageable​

import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;

// Page 0 (first page), 20 items per page
Pageable pageable = PageRequest.of(0, 20);
Page<Product> page = productRepo.findAll(pageable);

// Page information
int totalPages = page.getTotalPages();           // Total number of pages
long totalElements = page.getTotalElements();    // Total number of elements
List<Product> products = page.getContent();       // The 20 products of the page
boolean hasNext = page.hasNext();                // Is there a next page?

Complete Example: Paginated REST API​

Controller:

@RestController
@RequestMapping("/api/products")
public class ProductController {
    
    private final ProductRepository productRepo;
    
    public ProductController(ProductRepository productRepo) {
        this.productRepo = productRepo;
    }
    
    @GetMapping
    public Page<Product> getProducts(
        @RequestParam(defaultValue = "0") int page,
        @RequestParam(defaultValue = "20") int size
    ) {
        Pageable pageable = PageRequest.of(page, size);
        return productRepo.findAll(pageable);
    }
}

Request:

GET /api/products?page=0&size=20

JSON Response:

{
  "content": [
    { "id": 1, "name": "Product 1" },
    { "id": 2, "name": "Product 2" },
    ...
  ],
  "pageable": {
    "pageNumber": 0,
    "pageSize": 20
  },
  "totalPages": 500,
  "totalElements": 10000,
  "last": false,
  "first": true
}

Sorting with Sort​

Simple Sort​

import org.springframework.data.domain.Sort;

// Sort by ascending price
Sort sort = Sort.by("price").ascending();
Page<Product> page = productRepo.findAll(PageRequest.of(0, 20, sort));

// Sort by descending price
Sort sort = Sort.by("price").descending();

Multiple Sort​

// Sort by category ASC, then price DESC
Sort sort = Sort.by("category").ascending()
                .and(Sort.by("price").descending());

Pageable pageable = PageRequest.of(0, 20, sort);
Page<Product> page = productRepo.findAll(pageable);

Sorting in the Controller​

@GetMapping
public Page<Product> getProducts(
    @RequestParam(defaultValue = "0") int page,
    @RequestParam(defaultValue = "20") int size,
    @RequestParam(defaultValue = "name") String sortBy,
    @RequestParam(defaultValue = "ASC") String direction
) {
    Sort sort = direction.equalsIgnoreCase("DESC") 
        ? Sort.by(sortBy).descending() 
        : Sort.by(sortBy).ascending();
    
    Pageable pageable = PageRequest.of(page, size, sort);
    return productRepo.findAll(pageable);
}

Request:

GET /api/products?page=0&size=20&sortBy=price&direction=DESC

The N+1 Problem​

What is it?​

The MOST COMMON performance problem in JPA!

Problem Example​

Entities:

@Entity
public class Department {
    @Id
    private Long id;
    private String name;
    
    @OneToMany(mappedBy = "department", fetch = FetchType.LAZY)
    private List<Employee> employees;
}

@Entity
public class Employee {
    @Id
    private Long id;
    private String name;
    
    @ManyToOne(fetch = FetchType.LAZY)
    private Department department;
}

Problematic Code:

List<Department> departments = deptRepo.findAll();  // 1 query

for (Department dept : departments) {
    List<Employee> employees = dept.getEmployees();  // N queries!
    System.out.println(dept.getName() + ": " + employees.size());
}

Executed SQL:

-- 1 query
SELECT * FROM department;  

-- N queries (one per department!)
SELECT * FROM employee WHERE department_id = 1;
SELECT * FROM employee WHERE department_id = 2;
SELECT * FROM employee WHERE department_id = 3;
...
SELECT * FROM employee WHERE department_id = N;

If you have 100 departments → 1 + 100 = 101 queries! 💥

Solution 1: JOIN FETCH​

@Query("SELECT d FROM Department d JOIN FETCH d.employees")
List<Department> findAllWithEmployees();

Executed SQL:

-- 1 SINGLE query!
SELECT d.*, e.* 
FROM department d 
LEFT JOIN employee e ON d.id = e.department_id;

✅ 1 query instead of 101!

Solution 2: @EntityGraph​

@EntityGraph(attributePaths = {"employees"})
List<Department> findAll();

Equivalent to JOIN FETCH but simpler!

Solution 3: Batch Size​

If JOIN FETCH is not possible:

@Entity
public class Department {
    
    @OneToMany(mappedBy = "department")
    @BatchSize(size = 10)  // Load 10 departments at a time
    private List<Employee> employees;
}

Executed SQL:

SELECT * FROM department;

-- Instead of N queries, only N/10!
SELECT * FROM employee WHERE department_id IN (1, 2, 3, ..., 10);
SELECT * FROM employee WHERE department_id IN (11, 12, 13, ..., 20);
...

Projections for Performance​

Problem​

You load the ENTIRE entity when you only need 2 fields:

List<Product> products = productRepo.findAll();
for (Product p : products) {
    System.out.println(p.getName() + ": " + p.getPrice());
    // You only use name and price!
}

❌ Waste of memory and bandwidth!

Solution 1: Interface Projection​

Create an interface:

public interface ProductSummary {
    String getName();
    Double getPrice();
}

Repository:

List<ProductSummary> findAllBy();

Usage:

List<ProductSummary> summaries = productRepo.findAllBy();
for (ProductSummary summary : summaries) {
    System.out.println(summary.getName() + ": " + summary.getPrice());
}

✅ Loads only name and price!

Solution 2: DTO with @Query​

public class ProductDTO {
    private String name;
    private Double price;
    
    public ProductDTO(String name, Double price) {
        this.name = name;
        this.price = price;
    }
}

Repository:

@Query("SELECT new com.example.dto.ProductDTO(p.name, p.price) FROM Product p")
List<ProductDTO> findAllDTOs();

Fetch Strategies​

@ManyToOne(fetch = FetchType.LAZY)
private Department department;

Advantages:

  • ✅ Loads only if necessary
  • ✅ Saves memory

Disadvantage:

  • ⚠️ Can cause N+1

Solution: Use JOIN FETCH when necessary

FetchType.EAGER (To Avoid)​

@ManyToOne(fetch = FetchType.EAGER)
private Department department;

Advantages:

  • ✅ Data always available

Disadvantages:

  • ❌ Loads even if not used
  • ❌ Can load too much data

Rule: Use LAZY by default!

Pagination + Sorting + Performance: Complete Example​

public interface ProductRepository extends JpaRepository<Product, Long> {
    
    // Pagination + Sorting + Projection
    @Query("SELECT new com.example.dto.ProductDTO(p.name, p.price) " +
           "FROM Product p WHERE p.category = :category")
    Page<ProductDTO> findByCategoryOptimized(
        @Param("category") String category,
        Pageable pageable
    );
    
    // Pagination + Sorting + JOIN FETCH
    @Query("SELECT p FROM Product p JOIN FETCH p.category")
    Page<Product> findAllWithCategory(Pageable pageable);
}

Usage:

// Page 0, 20 items, sorted by price DESC
Pageable pageable = PageRequest.of(0, 20, Sort.by("price").descending());

// Optimized loading
Page<ProductDTO> page = productRepo.findByCategoryOptimized("Electronics", pageable);

System.out.println("Total: " + page.getTotalElements());
System.out.println("Pages: " + page.getTotalPages());

for (ProductDTO dto : page.getContent()) {
    System.out.println(dto.getName() + ": " + dto.getPrice());
}

Performance Tips​

1. Always Paginate Large Lists​

// ❌ BAD
List<Product> products = productRepo.findAll();  // All products!

// ✅ GOOD
Page<Product> page = productRepo.findAll(PageRequest.of(0, 20));

2. Use Projections​

// ❌ Loads entire entity
List<Product> products = productRepo.findAll();

// ✅ Loads only necessary fields
List<ProductSummary> summaries = productRepo.findAllBy();

3. Avoid N+1​

// ❌ N+1 Problem
List<Employee> employees = empRepo.findAll();
employees.forEach(e -> System.out.println(e.getDepartment().getName()));

// ✅ JOIN FETCH
@Query("SELECT e FROM Employee e JOIN FETCH e.department")
List<Employee> findAllWithDepartment();

4. Use LAZY by Default​

// ✅ GOOD
@ManyToOne(fetch = FetchType.LAZY)
private Department department;

// ❌ AVOID
@ManyToOne(fetch = FetchType.EAGER)
private Department department;

5. Limit Join Depth​

// ❌ Too many JOINs
@Query("SELECT e FROM Employee e " +
       "JOIN FETCH e.department d " +
       "JOIN FETCH d.company c " +
       "JOIN FETCH c.industry i")

// ✅ Only what's necessary
@Query("SELECT e FROM Employee e JOIN FETCH e.department")

Query Monitoring​

Enable SQL Logs​

application.properties:

# Show SQL queries
spring.jpa.show-sql=true

# Format SQL (readable)
spring.jpa.properties.hibernate.format_sql=true

# Show parameters
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

# Hibernate statistics
spring.jpa.properties.hibernate.generate_statistics=true

Console:

Hibernate: 
    select
        product0_.id as id1_0_,
        product0_.name as name2_0_,
        product0_.price as price3_0_ 
    from
        product product0_ limit ?

Chapter Summary​

Pagination​

Pageable pageable = PageRequest.of(page, size, sort);
Page<Entity> page = repo.findAll(pageable);

Sorting​

Sort sort = Sort.by("field").ascending();
Sort multi = Sort.by("field1").and(Sort.by("field2").descending());

N+1 Problem​

// ❌ Problem
List<Entity> list = repo.findAll();
list.forEach(e -> e.getRelation());

// ✅ Solution
@Query("SELECT e FROM Entity e JOIN FETCH e.relation")
List<Entity> findAllWithRelation();

Projections​

// Interface
public interface Summary {
    String getName();
}

// DTO
@Query("SELECT new DTO(e.name) FROM Entity e")
List<DTO> findDTOs();

Self-Assessment​

  1. How to create a page with 20 items sorted by price?
  2. What is the N+1 problem?
  3. How to solve it?
  4. Why use projections?

Answers:

  1. PageRequest.of(0, 20, Sort.by("price").ascending())
  2. 1 main query + N queries for relations
  3. JOIN FETCH or @EntityGraph
  4. To load only the necessary fields

Congratulations! 🎉​

You have completed all the theory of Module 6!

You now master:

  • ✅ 15+ convention keywords
  • ✅ The 3 types of relationships
  • ✅ @Query (JPQL and SQL)
  • ✅ Pagination and performance

Next step: Practice with the labs!

➡️ Start TP6.1: Advanced Conventions

⬅️ Previous Chapter: @Query

⬅️ Back to Module 6