Skip to content
notes.vinhnq.com

Search

SpringMid16 min read

Pagination and dynamic filters in Spring: Pageable, Page, Slice and JPA Specifications

Review 13 cards
On this page

Spring MVC end to end series: 1. Maven and project layout · 2. DispatcherServlet and views · 3. Forms and validation · 4. Form login with Spring Security · 5. Sessions and remember-me · 6. File upload · 7. Cart and checkout · 8. Pagination and dynamic filters

A shop’s product list has two jobs: fetch only one page of data, and filter by whatever combination of criteria the user ticks. This post does both with Spring Data JPA: Pageable for pagination, Specification for a dynamic WHERE clause. Afterwards you can build /products?factory=APPLE,DELL&price=10-15-trieu&page=2&sort=price,desc without 400 errors, without user-controlled sort columns, and without loading the whole table into memory.

At a glance:

flowchart LR
  A["URL: page, size, sort, filters"] --> B["Controller: Pageable + filter"]
  B --> C["Whitelist sort, price enum"]
  C --> D["Specification.allOf"]
  D --> E["(price1 OR price2) AND brand"]
  E --> F{"Need total count?"}
  F -->|"Yes"| G["Page: extra count(*)"]
  F -->|"Load more"| H["Slice: fetch size + 1"]

1. Why paginate, and what OFFSET/LIMIT do

Key idea: loading a whole table to show 12 products wastes memory, bandwidth and time, and gets worse as data grows. Pagination fetches exactly one page with LIMIT (how many rows) and OFFSET (how many to skip).

For page n (1-based) with size rows: OFFSET = (n - 1) × size. Page 1 skips 0 rows; page 3 with size 10 skips 20. Hibernate 6 on H2 generates offset ? rows fetch first ? rows only; on MySQL/PostgreSQL it’s limit ? offset ?.

The page lives in the query string (the part of the URL after ?, key=value pairs joined by &). So each page has its own link: bookmarks, sharing and the Back button all work.

2. Pageable, Page and Slice in Spring Data

Key idea: let the repository extend JpaRepository (which already includes PagingAndSortingRepository), pass a Pageable to findAll, get a Page<T>. The controller doesn’t read page with @RequestParam: declare a Pageable parameter and Spring builds it.

@GetMapping({"/", "/products"})
String list(@ModelAttribute("filter") ProductFilter filter,
            @PageableDefault(size = 12, sort = "id") Pageable pageable, Model model) {
    model.addAttribute("page", service.search(filter, safeSort(pageable)));
    return "products/list";
}
Type SQL it runs Use it when
Page<T> the page query + select count(*) you need total pages, a 1 2 3 … page bar
Slice<T> one query for size + 1 rows “load more” or infinite scroll: you only need to know if there’s more
List<T> + Pageable just the page query you handle navigation yourself

Page gives the view getContent() (the rows), getNumber() (current page, 0-based), getTotalPages(), hasNext(), hasPrevious(). From those the view renders previous/next buttons and marks the current page active.

One-indexed pages, max-page-size, and how Spring skips the count query
spring.data.web.pageable.one-indexed-parameters=true   # ?page=1 is the first page in URLs
spring.data.web.pageable.max-page-size=50              # ?size=100000 is lowered to 50
spring.data.web.pageable.default-page-size=20          # when there's no @PageableDefault

one-indexed-parameters changes only the URL. Internally, Pageable.getPageNumber() and Page.getNumber() still count from 0, so the view adds 1 when printing page numbers (${i == page.number + 1}). Lab tests: nothing passed → number = 0, size = 12; ?page=2 → number = 1; ?size=100000 → size = 50.

Without a size cap, ?size=1000000 is a “dump the table” command anyone can send.

Spring Data doesn’t always run the count. When the first page already holds fewer rows than size, it knows the total without counting (PageableExecutionUtils). With spring.jpa.show-sql=true on the lab’s filter tests, 10 page queries ran but only 4 select count(p1_0.id) from product p1_0, because the other filters returned fewer than 12 rows.

A UI bug on empty tables: the loop that prints page numbers runs from 1 to totalPages, and with totalPages = 0 JSP throws 'end' < 0. In Thymeleaf, #numbers.sequence(1, 0) doesn’t fail but counts down to 1, 0. Wrap the page bar in th:if="${page.totalPages > 1}" and show “No data” when page.empty.

Key idea: Spring turns ?sort=price,desc into a Sort for you, which means the user picks the ORDER BY column. Filter the column through an allowlist before it reaches the repository.

private static final Set<String> SORTABLE = Set.of("price", "name", "id");

static Pageable safeSort(Pageable p) {
    Sort sort = Sort.by(p.getSort().stream().filter(o -> SORTABLE.contains(o.getProperty())).toList());
    return PageRequest.of(p.getPageNumber(), p.getPageSize(), sort.isUnsorted() ? Sort.by("id") : sort);
}

Spring Data doesn’t paste the raw sort string into SQL, so this isn’t classic SQL injection. But an unknown column (?sort=passwordHash) either throws PropertyReferenceException as a 500, or lets outsiders sort by a sensitive field to probe data. The allowlist stops both, and a unique column (id) at the end keeps the order stable across pages.

Links to other pages must keep the filters. A link with only ?page=3 loses every ticked box. In Thymeleaf, @{/products(page=${i}, factory=${filter.factory}, price=${filter.price}, sort=${param.sort})} rebuilds the query string; null values are dropped. Changing a filter goes back to page 1.

URLs showing %2C instead of a comma are normal: the browser encodes special characters (percent-encoding), and Spring decodes them before binding.

4. Ways to write filter queries

Key idea: query methods and JPQL suit fixed conditions; when the number of conditions depends on what the user ticks, you build the WHERE at runtime: Criteria API, Specifications (Criteria in small pieces), or QueryDSL.

Approach Example Fits when Where it hurts
Query method findByFactoryInAndPriceBetween 1-2 fixed conditions 3 optional criteria = 2³ = 8 methods
@Query JPQL where (:f is null or p.factory in :f) fixed conditions, joins the is null or trick hurts plans, hard to read
Criteria API cb.and(...) with EntityManager fully dynamic long and repetitive
Specification allOf(factoryIn(f), priceAtLeast(min)) dynamic filters on one entity awkward for complex joins and projections
QueryDSL product.factory.in(f).and(...) many type-safe dynamic queries extra library and code generation step

JPA part 2 already has a short Specification example and compares JPQL with native queries. The rest of this post goes deep into the product filter.

5. Specifications and the JPA metamodel

Key idea: a Specification<T> is a function (root, query, cb) -> Predicate. root is the entity being queried, cb (CriteriaBuilder) builds conditions, query is for changing the whole statement (distinct, group by). The repository also extends JpaSpecificationExecutor<T> to get findAll(spec, pageable).

The Predicate here is jakarta.persistence.criteria.Predicate (a WHERE expression), not java.util.function.Predicate (a Java function returning boolean). Same name, entirely different things: the first runs in the database.

public static Specification<Product> factoryIn(Collection<String> factories) {
    if (factories == null || factories.isEmpty()) return null;      // no filter
    return (root, query, cb) -> root.get(Product_.factory).in(factories);
}

public static Specification<Product> priceAtLeast(BigDecimal min) {
    if (min == null) return null;
    return (root, query, cb) -> cb.greaterThanOrEqualTo(root.get(Product_.price), min);
}

// ProductService
Specification<Product> spec = Specification.allOf(
        nameContains(f.q()), factoryIn(f.factory()), targetIn(f.target()),
        priceAtLeast(f.minPrice()), priceAtMost(f.maxPrice()), priceInAnyRange(f.priceRanges()));
return products.findAll(spec, pageable);

Product_ is the metamodel: a class generated by an annotation processor at compile time, with a type-safe field per attribute (Product_.price is a SingularAttribute<Product, BigDecimal>). Renaming the price field breaks the filter code at compile time, instead of root.get("price") failing at runtime.

pom.xml for the metamodel and the lab’s full Specification set
<plugin>
  <groupId>org.apache.maven.plugins</groupId>
  <artifactId>maven-compiler-plugin</artifactId>
  <configuration>
    <annotationProcessorPaths>
      <path>
        <groupId>org.hibernate.orm</groupId>
        <artifactId>hibernate-jpamodelgen</artifactId>
        <version>${hibernate.version}</version>   <!-- 6.6.x with Spring Boot 3.5 -->
      </path>
    </annotationProcessorPaths>
  </configuration>
</plugin>

The generated class lands in target/generated-sources/annotations/.../Product_.java.

public final class ProductSpecs {
    public static Specification<Product> nameContains(String q) {
        if (q == null || q.isBlank()) return null;
        String pattern = "%" + q.trim().toLowerCase() + "%";
        return (root, query, cb) -> cb.like(cb.lower(root.get(Product_.name)), pattern);
    }
    public static Specification<Product> targetIn(Collection<String> targets) { /* like factoryIn */ }
    public static Specification<Product> priceAtMost(BigDecimal max) { /* lessThanOrEqualTo */ }

    /** (r1 OR r2 OR ...), then AND-ed with the other criteria. */
    public static Specification<Product> priceInAnyRange(List<PriceRange> ranges) {
        if (ranges == null || ranges.isEmpty()) return null;
        return (root, query, cb) -> {
            var price = root.get(Product_.price);
            Predicate[] ors = ranges.stream()
                    .map(r -> r.max() == null ? cb.greaterThanOrEqualTo(price, r.min()) : cb.between(price, r.min(), r.max()))
                    .toArray(Predicate[]::new);
            return cb.or(ors);
        };
    }
}

Specification.allOf (Spring Data 3.x) skips null elements, so each function returning null for an empty parameter is enough; the service needs no ifs. The older Specification.where(a).and(b) also accepts nulls.

6. Lab: filtering products by brand, purpose and price range

Key idea: collect the query string into a ProductFilter record, map price-range slugs to an allowlisted enum, make each criterion one Specification, combine with allOf. The price ranges are OR-ed inside one Specification, so the parentheses are always right.

The task (paraphrased): /products supports

  1. minPrice, maxPrice: minimum and maximum price.
  2. factory=APPLE or factory=APPLE,DELL: one or more brands (an IN condition).
  3. target=GAMING,VAN-PHONG: intended use, several values.
  4. price=10-15-trieu,tren-20-trieu: one or more fixed price ranges.
  5. q=tuf: search by name; everything combines with page and sort.
/** Spring splits "APPLE,DELL" into a List<String> when binding. */
public record ProductFilter(String q, List<String> factory, List<String> target,
                            BigDecimal minPrice, BigDecimal maxPrice, List<String> price) {
    public List<PriceRange> priceRanges() {
        return price == null ? List.of() : price.stream().map(PriceRange::fromSlug).flatMap(Optional::stream).toList();
    }
}

public enum PriceRange {
    UNDER_10M("duoi-10-trieu", 0, 10_000_000L),
    FROM_10_TO_15M("10-15-trieu", 10_000_000L, 15_000_000L),
    FROM_15_TO_20M("15-20-trieu", 15_000_000L, 20_000_000L),
    OVER_20M("tren-20-trieu", 20_000_000L, null);
    // slug, min, max, fromSlug(...)
}

The SQL Hibernate generates for ?price=10-15-trieu,tren-20-trieu&factory=APPLE,ASUS (taken from the log during the test run):

select ... from product p1_0
where p1_0.factory in (?,?) and (p1_0.price between ? and ? or p1_0.price>=?)
order by p1_0.id offset ? rows fetch first ? rows only

The parentheses around the two ranges are the whole point. If each range were its own Specification chained with .or() into a series of .and()s, the chaining order decides the result and easily produces factory IN (...) AND r1 OR r2, which matches every product over 20 million from any brand.

The test filterByFactoryListAndPriceRanges (all green): factory=APPLE,DELL returns the MacBook and the Dell; the two ranges return the MacBook (25M) and the Dell (12M); adding factory=APPLE,ASUS leaves only the MacBook; minPrice=16000000&sort=price,desc returns the MacBook then the Asus; the unknown slug khong-ton-tai is ignored, leaving only the q=tuf filter.

Count queries, fetch joins and DISTINCT when a Specification joins

When filtering by a child table (say, products with at least one 5-star review), the Specification must join:

static Specification<Product> hasRatingAtLeast(int stars) {
    return (root, query, cb) -> {
        query.distinct(true);                        // a 1-n join repeats a product per matching row
        Join<Product, Review> r = root.join(Product_.reviews);
        return cb.greaterThanOrEqualTo(r.get(Review_.stars), stars);
    };
}

(This snippet was not run: the lab has no Review entity.)

Three things to know:

  • A 1-n join multiplies rows: without distinct, a product with 3 matching reviews shows up 3 times on the page and the total is wrong.
  • Fetch join in a Specification + pagination: root.fetch(...) makes Hibernate paginate in memory (warning HHH90003004, as in part 7), and the count query Spring Data derives from the same Specification fails because a count can’t fetch. If you must fetch, check query.getResultType() and fetch only when it isn’t Long (the count query).
  • Projections for list pages: the lab’s Product entity has a @Lob detailDesc column, and the page query selects it. A list page needs only name, price and image; use an interface projection or DTO (Spring Data 3.x has findBy(spec, q -> q.as(ProductCard.class).page(pageable)), not run in the lab) to avoid shipping long text over the wire.

Remember

  • Explain why taking a Pageable beats @RequestParam int page, and where pages count from 0 or 1.
  • Know when to use Page, when Slice, and when to move to keyset pagination.
  • Write Specifications for IN, OR-ed price ranges and comparisons, and combine them with allOf and null pieces.
  • Name the three URL inputs you must bound: size, sort, and filter values (enum or allowlist).
  • Spot the leading-wildcard LIKE trap and the fetch-join-with-pagination trap.

Interview questions

Junior

What are OFFSET and LIMIT? For page 3 with 10 rows per page, what’s the OFFSET?

LIMIT is how many rows to return, OFFSET is how many to skip first. Page 3 (1-based), size 10: OFFSET = (3 - 1) × 10 = 20.

In Spring, PageRequest.of(2, 10) (0-based) gives the same result. Add an ORDER BY on a unique column, or the order across pages is undefined.

A controller takes @RequestParam int page and a user opens the URL without page. What happens, and how do you fix it?

Spring returns 400 because a required parameter is missing. Fix it by taking a Pageable with @PageableDefault, or at least a defaultValue.

Pageable also deals with non-numeric values, sort and size, and honours one-indexed-parameters and max-page-size.

Mid

How do Page and Slice differ? When do you pick Slice?

Page runs an extra count(*) to know the total rows and pages; Slice fetches size + 1 rows just to know whether there’s a next page.

Pick Slice for “load more” or infinite scroll, especially when counting a large table under complex conditions is expensive. Pick Page when the UI needs a page-number bar.

Why must the sort parameter from the URL be filtered?

Because it picks the ORDER BY column: an unknown column causes a 500, and sorting by a sensitive field helps outsiders probe data.

Spring Data doesn’t paste raw sort strings into SQL, but it accepts any entity property. Use an allowlist, map display names (price-asc) to a Sort on the server, and always add a unique column last for a stable order.

What is a Specification and how does it differ from a query method?

A Specification is a piece of WHERE built at runtime with the Criteria API; a query method fixes its conditions in the method name at compile time.

With three optional criteria, query methods need eight methods, while Specifications are three small functions combined with allOf, skipping any that are null. Use the metamodel (Product_) so attribute names are checked at compile time.

A user picks two price ranges and two brands. What’s the correct SQL condition, and where does it usually go wrong?

factory IN (a, b) AND (price BETWEEN x1 AND y1 OR price >= x2): the price ranges must be parenthesised before being AND-ed with the brands.

Chaining each range with .or() into a series of .and()s easily becomes ... AND r1 OR r2, matching every r2 product regardless of brand. Put the ranges in one Specification returning cb.or(...), then read the SQL in the log to check.

Senior

The list page searches names with LIKE and gets slow as the table grows. What do you do?

LIKE '%x%' can’t use a B-tree, so move to a tool built for substring search: a trigram index, the database’s full-text index, or a search engine; and review the count query and selected columns at the same time.

Measure with EXPLAIN first. Prefix-only search (LIKE 'x%') can use an index. On PostgreSQL, pg_trgm + GIN suits contains-search; ranked, typo-tolerant search calls for Elasticsearch. Also: use Slice if you don’t need totals, a projection to skip the @Lob column, and escape % and _ in the keyword.

A Specification joins a child table and the page returns duplicates and a wrong total. Why, and how do you fix it?

A 1-n join turns each product into several rows; add query.distinct(true) or rewrite the join as an exists subquery, and avoid fetch joins when paginating.

A collection fetch join plus pagination makes Hibernate page in memory, and the count query derived from the same Specification fails. An exists subquery often plans better than distinct on large tables. Verify with the SQL log and a test with many child rows.

Useful phrases for interviews

  • “I take a Pageable instead of a raw page parameter, so missing or invalid values fall back to defaults.”
  • “Pageable is zero-based internally; one-indexed parameters only change the URL.”
  • “Page costs an extra count query; Slice just fetches one more row.”
  • “Deep offsets get slower, so for infinite scroll I’d switch to keyset pagination.”
  • “Sort fields go through an allowlist before they reach the repository.”
  • “Each filter is a small specification, and null pieces are simply skipped by allOf.”
  • “The price ranges are OR-ed inside one specification, so the parentheses are always right.”
  • “The generated metamodel makes attribute names type-safe.”
  • “A leading wildcard rules out a B-tree index, so large catalogs need trigram or full-text search.”

Notes based on Hỏi Dân IT course material, rewritten and extended in my own words.