Pagination: Pageable and PageRequest

1. Pagination as a data-layer responsibility

When you first write a repository method and get back a List<Product>, everything seems wonderful: here is the list — take it and enjoy. But after a couple of days, the project starts behaving like a nice person at a buffet: "I'll take everything." And then you accidentally read a thousand products from the database, then ten thousand, then "well, it is just slow on the local machine." Pagination is needed not because someone likes "Next page" buttons, but because data reading must always be limited and predictable.

The most important shift in mindset here is this: limiting the result is part of the repository contract. It is not decoration on top and not "we'll trim it later in the controller." If you tell the repository in advance, "give me only 20 items," you save JVM memory, network time, database resources, and, which is especially nice, the nerves of the person who will debug it.

To avoid talking at the level of abstractions, let us recall SQL from the first days of the course. Pagination in the relational world is very down-to-earth LIMIT and OFFSET (plus ORDER BY, otherwise it is a lottery). Spring Data simply provides a convenient Java form for passing these intentions into the query.

Pageable: how to read a list

If you have not worked with Pageable yet, it may seem like "another thing to memorize." But in reality, this is one of the friendliest Spring Data API objects: it tells the repository three things — which page, what size, in what order. That is all. No SQL strings, no manual LIMIT/OFFSET, no dance of "we'll trim the list after reading."

It is important that Pageable is exactly a description of the request for data, not a "result container." In other words, Pageable answers the question "how to read," not "what we read." That is why it appears in the parameters of a repository method.

It is useful to keep in mind a simple table connecting Pageable with its SQL equivalent:

Pageable

That is just three points, but they turn "give me everything" into "give me a neat piece, please." And yes, if you are familiar with arrays in Java, you will be pleased (or not): page numbering in Pageable starts from zero. The first page is 0. As usual in programming: zero is not an error, but a way of life.

2. PageRequest: creating Pageable

You usually do not work with the Pageable interface directly: you just create its implementation. The most common implementation for "classic" pagination (through offset) is PageRequest. It is created through the static method of(...), and there is something pleasantly honest about that from an engineering perspective: you explicitly set page and size, and if you want, you add sorting.

Below is the simplest example, without sorting. It is useful for the first steps, but in real lists, order is usually needed anyway.

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

// Pageable is "how to read", not "what was read"
Pageable pageable = PageRequest.of(0, 20); // 0 = first page, 20 = page size (number of entities)

Pay attention to two things. First, 0 means "the first page." Second, 20 means "20 result rows," not "20 kilobytes" and not "20 seconds of patience." Spring Data interprets size as the number of entities in the response.

A slightly more realistic example is with sorting. Here we carefully use what we discussed in the previous lecture: a stable order, where the second field (id) helps lock in the result if the primary field is not unique.

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

// Important: with pagination, sorting is almost always needed, otherwise pages will "wander"
Pageable pageable = PageRequest.of(
        0, 12,
        // Stabilize the order: first by createdAt, then by id (in case of identical dates)
        Sort.by("createdAt").descending().and(Sort.by("id").descending())
);

You can now pass this object to the repository, and the repository will read data in "chunks" rather than as the full set. And yes: you are again using entity field names (createdAt, id), not created_at and not product_id — we are still living in the world of Spring Data JPA, which looks at the Java model.

3. Pageable in the repository

For pagination to actually work, Pageable must appear in the repository method signature. It looks boring, but boring things are often what save projects: the method signature becomes a contract that cannot be "forgotten" if you do not want to read everything again.

Suppose our mini-shop has a typical catalog scenario: "get products in a category, only active ones, paginated." At the derived query level, this can be expressed very naturally: a filter by category.code + status, and then add Pageable.

import com.example.shopdatajpa.catalog.entity.Product;
import com.example.shopdatajpa.catalog.entity.ProductStatus;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;

public interface ProductRepository extends JpaRepository<Product, Long> {

    // Return Page<Product> to get not only the list, but also page metadata
    Page<Product> findByCategoryCodeAndStatus(
            String categoryCode,
            ProductStatus status,
            // Pageable passes page size/number and sorting into the query
            Pageable pageable
    );
}

There are several practical details here that beginners usually skip.

First, it is usually more logical to put Pageable as the last parameter: that way the signature reads as "conditions → reading control." Spring Data understands this either way, but it is easier for people to read in this order.

Second, notice that we return Page<Product>. This means we want not only the list, but also a "result wrapper" that can store page information. For this lecture, what matters more is the fact that the result type changes when we talk about paginated reading. We are not turning the details of the possible result wrappers into a separate philosophy yet — we just need to learn how to pass Pageable correctly.

Third, the method remains readable. This is genuinely good news: pagination itself does not make a derived query a "monster." What turns it into a monster is the attempt to cram 7 filters and 3 sorts into one method name — not Pageable.

4. Pageable in SQL: ORDER BY, LIMIT, OFFSET

When you call a repository method with Pageable, what happens under the hood is what you already know from SQL, just wrapped in a framework layer. Hibernate and Spring Data build a query that will include ORDER BY, LIMIT, and OFFSET. Even if you do not write SQL by hand, it is useful to mentally "see" it so you do not treat pagination like magic.

Imagine we requested page 0, size 12, sorting createdAt desc, id desc, category "TEA", status ACTIVE. In your head, you can think of it like this:

select ...
from product p
join category c on p.category_id = c.id
where c.code = 'TEA'
  and p.status = 'ACTIVE'
-- ORDER BY is required for stable pages (otherwise the order is not guaranteed)
order by p.created_at desc, p.id desc
-- LIMIT = page size, OFFSET = pageNumber * pageSize
limit 12 offset 0;

And if we requested the second page (pageNumber = 1), the offset would become 12:

select ...
from product p
join category c on p.category_id = c.id
where c.code = 'TEA'
  and p.status = 'ACTIVE'
order by p.created_at desc, p.id desc
limit 12 offset 12;

And here is the point that is very important to feel: pagination without sorting is "give me 12 random rows, then 12 other random rows". The database is not required to return rows in the same order if you did not specify ORDER BY. Sometimes it seems like "it is already by id," but that is just a lucky coincidence of your environment and data. As soon as the table gets larger, updates appear, the query plan changes — the order will drift, and pages will start overlapping or skipping elements.

So the practical conclusion is simple: if you do pagination, do sorting. In a real project, this rule usually lives as an unwritten team law, roughly at the level of "do not push code that crashes on startup to main."

5. Pagination in CatalogService

It is very easy to make the repository "smart" and leave the service as "just a proxy." But in practice, the opposite is more convenient: the repository provides the ability to read, and the service forms the use case. This means the service decides what page size is reasonable by default, what order is considered sensible, and how to handle the page number that came from the outside world (or even just from your CommandLineRunner).

Let us take our CatalogService (which we have already started building earlier) and add a method that reads a catalog page. Here we do not touch the web layer and do not discuss DTOs: we just show how the service turns "give me a page" into a PageRequest.

import com.example.shopdatajpa.catalog.entity.Product;
import com.example.shopdatajpa.catalog.entity.ProductStatus;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;

public Page<Product> getActiveCatalogPage(String categoryCode, int page, int size) {
    // Fix the "canonical" catalog order: newest first
    Sort sort = Sort.by("createdAt").descending().and(Sort.by("id").descending());

    // Build Pageable directly in the service: it is part of the use case, not "somewhere later"
    PageRequest pageable = PageRequest.of(page, size, sort);

    // The repository receives both filters and reading rules (page/size/sorting)
    return productRepository.findByCategoryCodeAndStatus(categoryCode, ProductStatus.ACTIVE, pageable);
}

Several things are happening "correctly" here, and they are very useful at the level of habit.

The service sets the sorting itself and does not push that decision onto the calling code. This is convenient because the "catalog" use case usually implies a fixed order: for example, "newest first," "cheapest first," "by name first." If the order truly needs to be dynamic, then yes, you would move Sort outside. But by default, it is better to fix the order as part of the scenario.

The second point is that PageRequest is created in the service, not "somewhere later." This is methodologically important: pagination is part of the use case, not a random add-on that lives sometimes in the repository, sometimes in the controller, and sometimes in a utility class.

And the third point is the place where page numbering mistakes usually surface. People like "page 1," while PageRequest likes "page 0." To avoid endless confusion, you can agree on this: inside the application, always zero-based; and if you ever need to accept a page number "like a human would," you will convert it explicitly in one place.

An example of such a conversion (just as a safe arithmetic snippet):

int requestedPage = 1;                      // as the person said: "the first page"
int page = Math.max(0, requestedPage - 1);  // as PageRequest understands it: 0 (and protection from negative values)

Yes, it looks trivial. But it is exactly these trivial two lines that save hours of debugging when someone starts complaining: "why is the first page empty?"

6. Mini demo: reading a page

When we study the persistence layer, we really want to "touch" the result. Since we do not have a web layer yet (and that is fine), we can make a small training demo through CommandLineRunner: application startup, one service call, printing a few lines. It is not architecture of the century, but for learning it works great — like a flashlight in a basement where you have not turned the lights on yet.

For example, print the SKU and names of products from the first page:

import org.springframework.data.domain.Page;

// Request the 0th page (that is, the "first"), with 3 items per page
Page<Product> page = catalogService.getActiveCatalogPage("TEA", 0, 3);

// getSize() is the page size you requested (not the actual number of elements in the table)
System.out.println("Page size = " + page.getSize()); // Page size = 3

// getContent() returns the elements of the current page
page.getContent().forEach(p ->
        System.out.println(p.getSku() + " -> " + p.getName())
);

// Example of expected output (will depend on your data and sorting):
// TEA-001 -> Green Tea
// TEA-002 -> Black Tea
// TEA-003 -> Oolong Tea

What matters here is the effect: you see that the service reads three items, even though there may be 300 in the table. And this feeling of "we control reading" is one of the main things in working with repositories. It is not the database telling you "take everything"; you ask for exactly as much as the scenario needs.

7. Typical mistakes when working with Pageable

Mistake #1: assuming the first page is 1 and getting strange "empty" results.
PageRequest lives in a zero-based world: PageRequest.of(0, 20) is the first page. If you pass 1, you are honestly asking for the second one. This mistake is especially sneaky because the code compiles, runs, and only later does someone notice that "we never show the first page at all."

Mistake #2: trying to pass null instead of Pageable so that "it returns everything."
This style quickly turns the repository into an unpredictable thing: sometimes we read 20, sometimes we read "everything," and then we are surprised by memory usage and response time. If you need unrestricted reading, it is better to create a separate repository method without Pageable so the contract is visible at a glance. But in a catalog and admin lists, it is usually better not to do "everything" at all.

Mistake #3: changing the sorting between neighboring pages of the same list.
Paged reading assumes that you are moving through one and the same ordered sequence. If page one is sorted by createdAt desc, and page two suddenly by name asc, you are effectively reading a different list. The result will be ragged: duplicates, gaps, and the feeling that the database is trolling you.

Mistake #4: doing pagination without ORDER BY, hoping it will be "by id anyway."
The database is not required to return rows in a stable order without ORDER BY. Sometimes it seems that everything is stable because the table is small, the query plan is simple, and life has not yet taught you a lesson. As soon as there is more data or updates appear, pages will start wandering. If you do pagination, the order must be explicit.

Mistake #5: taking an excessively large page size "just in case."
The phrase "let us use a size of 10_000 so it is definitely enough" usually means "let us cancel the point of pagination, but call it pagination." Page size is part of the design decision. For a catalog, 10–50 is often enough, sometimes 100, but huge sizes almost always lead to extra load, even if the query is formally "still a Page."

1

Task

Spring Data JPA, level 10, lesson 3

Locked

First Page of Available Books

First Page of Available Books

Open

1

Task

Spring Data JPA, level 10, lesson 3

Locked

Second Page of Employees by Department Code

Second Page of Employees by Department Code

Open