Spring Data JPA in practice: pagination, projections, and fixing N+1 queries

Beyond findAll()

Spring Data JPA makes the first steps easy: extend JpaRepository and you get save, findById, and findAll for free. Real applications quickly need more: listing thousands of rows page by page, returning lightweight summaries instead of full entities, and keeping the number of SQL queries under control.

This article covers three techniques that make a noticeable difference in performance and API design. The examples target Spring Boot 3.x or later.

We will use a simple blog model:

@Entity
public class Article {

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

    private String title;
    private String slug;
    private Instant publishedAt;

    @ManyToOne(fetch = FetchType.LAZY)
    private Author author;

    @OneToMany(mappedBy = "article")
    private List<Comment> comments = new ArrayList<>();

    // getters omitted
}

See what SQL is executed

Before optimizing, make queries visible during development:

spring:
  jpa:
    open-in-view: false
logging:
  level:
    org.hibernate.SQL: debug
    org.hibernate.orm.jdbc.bind: trace

org.hibernate.SQL prints every statement and org.hibernate.orm.jdbc.bind prints the bound parameter values. Use this in development only, never in production.

open-in-view: false deserves a special mention. By default, Spring Boot keeps the persistence context open until the HTTP response is written, and logs a warning about it. That makes lazy loading "just work" in controllers and JSON serialization, but it also hides extra queries and holds a database connection for the entire request. Turning it off forces you to load what you need inside the service layer, which is exactly what the rest of this article is about.

Pagination and sorting

Never return an unbounded list from an API. Spring Data supports pagination through Pageable:

public interface ArticleRepository extends JpaRepository<Article, Long> {

    Page<Article> findByPublishedAtBefore(Instant now, Pageable pageable);
}

In a controller, Spring MVC can build the Pageable from query parameters:

@GetMapping("/api/articles")
Page<ArticleSummary> list(@PageableDefault(size = 20, sort = "publishedAt", direction = Sort.Direction.DESC) Pageable pageable) {
    return articleService.listPublished(pageable);
}

Clients can now call /api/articles?page=2&size=10&sort=title,asc. Pages are zero-based by default.

Protect the database from huge pages with a global limit:

spring:
  data:
    web:
      pageable:
        max-page-size: 100

Page or Slice?

A Page includes the total number of elements, which requires an extra count query. For "load more" or infinite scroll interfaces where you only need to know whether there is a next page, return a Slice instead. It fetches one extra row to detect the next page and skips the count entirely, which matters on large tables.

A stable JSON format for pages

Serializing PageImpl directly exposes an internal structure that may change between versions, and recent Spring Data versions log a warning about it. Either map to your own response type or enable the stable DTO format:

@SpringBootApplication
@EnableSpringDataWebSupport(pageSerializationMode = EnableSpringDataWebSupport.PageSerializationMode.VIA_DTO)
public class BlogApplication { }

This produces a predictable shape with content and a page object holding size, number, totalElements, and totalPages.

Projections: fetch only what you need

A list page usually shows a title, a slug, a date, and an author name. Loading full entities, including large text columns, is wasteful. Projections let you select just those columns.

Interface projections

Declare an interface with getters matching entity properties:

public interface ArticleListItem {
    String getTitle();
    String getSlug();
    Instant getPublishedAt();
    String getAuthorName(); // traverses article.author.name
}

Use it as the return type of a query method:

Page<ArticleListItem> findByPublishedAtBefore(Instant now, Pageable pageable);

Spring Data generates a query that selects only the needed columns, joining author for getAuthorName().

Record projections with JPQL

For full control, use a constructor expression with a Java record:

public record ArticleSummary(Long id, String title, String slug, Instant publishedAt, String authorName) {}

@Query("""
        select new com.example.blog.ArticleSummary(a.id, a.title, a.slug, a.publishedAt, au.name)
        from Article a
        join a.author au
        where a.publishedAt < :now
        """)
Page<ArticleSummary> findSummaries(@Param("now") Instant now, Pageable pageable);

Projections are read-only, never trigger lazy loading, and are an ideal fit for API responses. Many teams use entities for writes and projections for reads.

The N+1 query problem

This innocent-looking code is one of the most common performance bugs in JPA applications:

@Transactional(readOnly = true)
public List<ArticleDto> latest() {
    return articleRepository.findTop20ByOrderByPublishedAtDesc().stream()
            .map(a -> new ArticleDto(a.getTitle(), a.getAuthor().getName()))
            .toList();
}

The SQL log reveals the problem:

select ... from article order by published_at desc fetch first 20 rows only
select ... from author where id=?
select ... from author where id=?
-- ... 18 more

One query loads the articles, then one additional query per article loads its author: 1 + N queries. With 20 rows it is merely slow; with 500 it can take down a page.

Fix 1: fetch join

Load the association in the same query with join fetch:

@Query("select a from Article a join fetch a.author order by a.publishedAt desc")
List<Article> findLatestWithAuthor(Pageable pageable);

Fix 2: entity graphs

An @EntityGraph achieves the same thing declaratively, and works nicely with derived query methods:

@EntityGraph(attributePaths = "author")
List<Article> findTop20ByOrderByPublishedAtDesc();

Fix 3: batch fetching

Sometimes you cannot change every query. Hibernate can load lazy associations in batches instead of one by one:

spring:
  jpa:
    properties:
      hibernate:
        default_batch_fetch_size: 50

Now accessing authors for 20 articles triggers a single where id in (...) query instead of 20 separate ones. It is a great safety net to enable globally.

A warning about collections and pagination

Fetch joins work well for @ManyToOne and @OneToOne. Fetch-joining a collection such as comments while paginating is a trap: the database cannot apply the limit correctly because each article now appears once per comment, so Hibernate loads all rows and paginates in memory, logging a warning like HHH90003004. For collections, paginate the parent entities first, then load the children with batch fetching or a second query using where article.id in (:ids).

Keep transactions read-only where possible

Mark query methods in services with @Transactional(readOnly = true). It documents intent, lets Hibernate skip dirty checking for loaded entities, and some connection pools and databases can route read-only transactions to replicas.

Checklist

• Turn on SQL logging in development and actually read it.
• Set spring.jpa.open-in-view=false.
• Paginate every list endpoint and cap the page size.
• Use projections for read-only views.
• Make @ManyToOne associations LAZY and fetch them explicitly when needed.
• Enable default_batch_fetch_size as a safety net.

Exercise

Build an endpoint GET /api/authors/{id}/articles that returns a paginated list of record projections containing the article title, publication date, and comment count. Write it as a single JPQL query with count(c) and group by, then verify in the SQL log that exactly two queries run: one for the page and one for the total count.