Spring Boot JPA (Part 8) | JPQL, Derived Queries, N+1 Problem, Joins and Pagination

The Universal Translator Analogy

Imagine traveling across five European countries where different local dialects are spoken: German, French, Italian, Spanish, and Dutch. If you are an author publishing a novel, you do not rewrite your original manuscript five times from scratch in five distinct grammatical systems. Instead, you write the novel in a single universal international language. A local publishing house in each nation translates that universal script into the specific regional dialect of that country.

In database development, JPQL (Java Persistence Query Language) is that universal language. SQL is tied to specific database dialects: PostgreSQL has its own syntax, Oracle has another, MySQL has another. If you write raw SQL directly, your application is locked into that database vendor. JPQL queries your Java entity classes and fields, completely unaware of physical table names or database dialects. Hibernate translates your universal JPQL queries into optimized native SQL for whatever database dialect is configured at runtime.

This lecture covers derived query methods, JPQL fundamentals, the infamous N+1 query problem and its solutions (JOIN FETCH and @EntityGraph), and high performance pagination and sorting.

---

1. Derived Query Methods in Spring Data JPA

Spring Data JPA provides query derivation: by following naming conventions on repository interface methods, Spring automatically parses the method name and generates the underlying SQL query at startup with zero code:

package com.example.orderservice.repository;

import com.example.orderservice.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.util.List;
import java.util.Optional;

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    // 1. Exact match lookup
    Optional<User> findByEmail(String email);

    // 2. Combining multiple conditions (AND / OR)
    List<User> findByLastNameAndActive(String lastName, boolean active);

    // 3. Comparison operators
    List<User> findByAgeGreaterThanEqual(int minAge);

    // 4. Pattern matching (LIKE)
    List<User> findByUsernameContainingIgnoreCase(String keyword);

    // 5. Null checks
    List<User> findByPhoneNumberIsNull();

    // 6. Date ranges
    List<User> findByCreatedDateBetween(LocalDate startDate, LocalDate endDate);

    // 7. Limiting results
    List<User> findTop5ByOrderByAgeDesc();
}

Supported Keywords Overview:

• Equality: Is, Equals, Not
• Comparison: GreaterThan, GreaterThanEqual, LessThan, Between
• String matching: StartingWith, EndingWith, Containing, Like, IgnoreCase
• Boolean: True, False
• Collection membership: In, NotIn

---

2. JPQL (Java Persistence Query Language)

When queries involve complex joins, aggregates, or calculations, derived method names become unwieldy (e.g. findByCustomerAgeGreaterThanAndStatusEqualsAndOrderTotalGreaterThan...).

Use the @Query annotation to write JPQL:

@Query("SELECT u FROM User u WHERE u.status = 'ACTIVE' AND u.email LIKE %:domain")
List<User> findActiveUsersByDomain(@Param("domain") String domain);

The Cardinal Rule of JPQL:

1. Query Entity Class Names, NOT database table names (User, not tbl_users).
2. Query Java Field Names, NOT database column names (u.emailAddress, not email_addr).

Parameter Binding Styles:

// 1. Named parameters (Recommended for clarity)
@Query("SELECT u FROM User u WHERE u.firstName = :first AND u.lastName = :last")
List<User> findByName(@Param("first") String first, @Param("last") String last);

// 2. Positional parameters (?1, ?2)
@Query("SELECT u FROM User u WHERE u.firstName = ?1 AND u.lastName = ?2")
List<User> findByNamePositional(String first, String last);

---

3. The Infamous N+1 Query Problem

The N+1 Problem is the number one cause of catastrophic performance degradation in production JPA applications.

How the N+1 Problem Happens:

Suppose an Order entity has a @ManyToOne association to Customer:

// Controller or Service queries all orders
List<Order> orders = orderRepository.findAll();

for (Order order : orders) {
    System.out.println(order.getCustomer().getName());
}

What SQL queries does Hibernate execute?

1. The "1" Query: Hibernate executes one query to fetch all 100 orders:

   SELECT * FROM orders; -- returns 100 orders

2. The "N" Queries: For each of the 100 orders, when order.getCustomer().getName() is called, Hibernate executes an individual query to fetch that order's customer:

   SELECT * FROM customers WHERE id = 1;
   SELECT * FROM customers WHERE id = 2;
   ... (executed 100 separate times!) ...
   SELECT * FROM customers WHERE id = 100;

Instead of executing a single SQL join, Hibernate executes 101 separate database queries, flooding the network connection pool and causing massive latency spikes!

---

Solving the N+1 Problem

There are two primary industry solutions:

Solution 1: JOIN FETCH in JPQL

JOIN FETCH instructs Hibernate to write a SQL INNER JOIN or LEFT JOIN and populate the associated entity eagerly in a single database query:

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {

    // Resolves the N+1 problem in ONE query
    @Query("SELECT o FROM Order o JOIN FETCH o.customer")
    List<Order> findAllOrdersWithCustomers();
}

Generated SQL (Single Query!):

SELECT o.*, c.*
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

Solution 2: @EntityGraph

@EntityGraph allows you to override default lazy fetching for specific queries declaratively without rewriting JPQL strings:

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {

    // Tells Hibernate to fetch the 'customer' association in the same query
    @EntityGraph(attributePaths = {"customer", "items"})
    List<Order> findAll();
}

---

4. Pagination and Sorting

Fetching one million rows into JVM heap memory will crash your server with an OutOfMemoryError. Real world APIs paginate results:

package com.example.orderservice.service;

import com.example.orderservice.entity.Order;
import com.example.orderservice.repository.OrderRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;

@Service
public class OrderPaginationService {

    @Autowired
    private OrderRepository orderRepository;

    public Page<Order> getOrdersPaged(int pageIndex, int pageSize) {
        // Page 0, 20 items per page, sorted by orderDate descending
        Pageable pageable = PageRequest.of(pageIndex, pageSize, Sort.by("orderDate").descending());

        return orderRepository.findAll(pageable);
    }
}

Page<T> vs Slice<T>:

• Page<T>: Executes two SQL queries: one to fetch the paginated records (LIMIT 20 OFFSET 0), and a second query to calculate the total row count (SELECT COUNT(*)). Use Page when the frontend displays total pages (e.g. "Page 1 of 45").
• Slice<T>: Executes one SQL query, fetching pageSize + 1 rows to know whether a next page exists without running an expensive COUNT(*) query across millions of rows. Use Slice for infinite scroll mobile feeds.

---

Interview Questions & Pitfalls

Q1: What is the fundamental difference between JPQL and native SQL?

JPQL queries Java entity classes and their property attributes (e.g. SELECT u FROM User u WHERE u.emailAddress = ...), allowing Hibernate to translate the query into any database dialect dynamically. Native SQL queries physical database tables and column names directly (e.g. SELECT * FROM tbl_users WHERE email_addr = ...), binding the code to a specific database engine.

Q2: What is the N+1 problem in JPA, and how do you resolve it?

The N+1 problem occurs when fetching N parent records causes Hibernate to execute N additional independent queries to fetch associated child entities. It is resolved using JOIN FETCH in JPQL or using Spring Data JPA's @EntityGraph(attributePaths = {...}) annotation, both of which force Hibernate to retrieve parent and child entities together in a single SQL join query.

Q3: What is the difference between Page<T> and Slice<T> in Spring Data JPA?

Page<T> executes an extra COUNT(*) database query to determine total element count and total pages, which can be computationally expensive on large tables. Slice<T> only queries pageSize + 1 elements to determine whether subsequent records exist without calculating total counts, making it significantly faster for infinite scrolling user interfaces.

Q4: Can pagination be combined with custom @Query definitions in Spring Data JPA?

Yes. Add a Pageable parameter as the final argument in your repository method: @Query("SELECT u FROM User u WHERE u.active = true") Page<User> findActiveUsers(Pageable pageable). Spring Data JPA automatically appends pagination clauses to the translated SQL and constructs a count query for you.

Q5: Why does writing JOIN without FETCH in JPQL fail to resolve the N+1 problem?

A standard JPQL JOIN filters the result set based on joined table conditions, but it does not populate the child object references into memory. The child associations remain uninitialized lazy proxies. When your code later accesses the child entities, Hibernate is still forced to fire N individual queries. Adding FETCH (JOIN FETCH) explicitly instructs Hibernate to populate the child objects during the initial query.