Pagination and Filtering

Return large collections in bounded pages with a stable sort, safe filter and sort parameters, and next links clients can follow.

SyntaxGET /products?category=books&sort=-price&limit=20&offset=40 GET /events?limit=50&after=8812 Link: </products?limit=20&offset=60>; rel="next"

A list endpoint that returns everything works in development and fails in production: ten rows become a million, responses take seconds, and the heap fills up building one JSON string. Every collection endpoint needs a page size with a maximum from day one; adding it later breaks clients that expect the full list.

Offset pagination

?limit=20&offset=40 maps directly to SQL LIMIT 20 OFFSET 40 and lets users jump to page 3. Two problems: the database still reads and discards the skipped rows, so deep pages get slow, and when rows are inserted or deleted between requests, items shift and appear twice or not at all.

Cursor (keyset) pagination

?limit=20&after=8812 asks for rows after the last one the client saw, which becomes WHERE id < ? ORDER BY id DESC LIMIT 20 for a newest-first list. It uses an index, stays fast at any depth and does not skip or repeat items when data changes. You lose random access to page N. Feeds, logs and exports should use cursors; admin tables with page numbers can use offsets.

Rules that apply to both

  • Stable sort: always end the sort with a unique column such as id. Sorting by price alone lets rows with equal prices swap between requests.
  • Clamp or reject the limit: give it a default (20) and a maximum (100). Never let a client ask for a million rows.
  • Whitelist sort and filter fields: map sort=-price to a known comparator or SQL fragment. Column names cannot be bound as SQL parameters, so concatenating a client-supplied sort field is an injection hole.
  • Tell the client how to continue: a next link in the body or a Link header (rel="next"), so clients do not build URLs themselves.

Filtering

Filters are query parameters on the collection: /products?category=books&maxPrice=40. Decode them with URLDecoder, validate them like any other input, and return 400 for unknown parameters and values rather than ignoring them, or a typo such as categry=books silently returns everything. A total count is useful for page numbers but costs a second query on large tables; make it optional if it gets expensive.

Example

Try it yourself
Loading editor…
Press Run to execute the code.

When to use it

  • Page an admin product table with limit and offset and show the total for page numbers.
  • Stream an activity feed with after cursors so new events never cause repeats while scrolling.
  • Let clients sort orders by date or amount through a whitelist that cannot be abused for SQL injection.

More examples

Why offsets skip and repeat, and cursors do not

The new row pushed everything one place down, so offset 3 shows event 50 again. The cursor version asks for ids below the last one seen, which is the WHERE id < ? query a keyset page runs against an index.

Example · java
List<Integer> page(List<Integer> newestFirst, int offset, int limit) {
    return newestFirst.stream().skip(offset).limit(limit).toList();
}

List<Integer> pageAfter(List<Integer> newestFirst, Integer afterId, int limit) {
    return newestFirst.stream().filter(id -> afterId == null || id < afterId).limit(limit).toList();
}

void main() {
    List<Integer> events = new ArrayList<>(List.of(70, 60, 50, 40, 30, 20, 10));

    List<Integer> first = page(events, 0, 3);
    List<Integer> firstByCursor = pageAfter(events, null, 3);
    IO.println("page 1:              " + first);

    events.addFirst(80);   // a new event arrives while the user reads page 1

    IO.println("page 2 by offset=3:  " + page(events, 3, 3));
    IO.println("page 2 by after=" + firstByCursor.getLast() + ": " + pageAfter(events, firstByCursor.getLast(), 3));
}

// Output:
// page 1:              [70, 60, 50]
// page 2 by offset=3:  [50, 40, 30]
// page 2 by after=50: [40, 30, 20]

Parsing repeated and encoded query parameters

Use getRawQuery, not getQuery: getQuery has already decoded the string, so an encoded & inside a value would split it in the wrong place. URLDecoder also turns + into a space, which is how HTML forms encode spaces.

Example · java
Map<String, List<String>> parse(String rawQuery) {
    Map<String, List<String>> params = new LinkedHashMap<>();
    if (rawQuery == null || rawQuery.isEmpty()) return params;
    for (String pair : rawQuery.split("&")) {
        if (pair.isEmpty()) continue;
        int eq = pair.indexOf('=');
        String key = URLDecoder.decode(eq < 0 ? pair : pair.substring(0, eq), StandardCharsets.UTF_8);
        String value = eq < 0 ? "" : URLDecoder.decode(pair.substring(eq + 1), StandardCharsets.UTF_8);
        params.computeIfAbsent(key, k -> new ArrayList<>()).add(value);
    }
    return params;
}

void main() {
    URI uri = URI.create("http://localhost/articles?tag=java&tag=web&q=rest+api%20design&author=&page");
    // getRawQuery keeps %20 and + encoded, so decoding happens once, here.
    IO.println(parse(uri.getRawQuery()));
}

// Output:
// {tag=[java, web], q=[rest api design], author=[], page=[]}

Turning filters into a safe SQL query

Filter values go in as ? parameters for a PreparedStatement, while the sort is chosen from a fixed map because identifiers cannot be parameters. LIMIT ? OFFSET ? is PostgreSQL, MySQL and SQLite syntax; the SQL standard spelling is OFFSET ... ROWS FETCH NEXT ... ROWS ONLY.

Example · java
record SqlQuery(String sql, List<Object> params) {}

// Client sort keys map to fixed SQL fragments; client text never reaches ORDER BY.
static final Map<String, String> ORDER_BY = Map.of(
    "price", "price ASC, id ASC",
    "-price", "price DESC, id DESC",
    "name", "name ASC, id ASC");

SqlQuery build(String category, BigDecimal maxPrice, String sort, int limit, int offset) {
    String order = ORDER_BY.get(sort);
    if (order == null) throw new IllegalArgumentException("unknown sort: " + sort);
    List<String> where = new ArrayList<>();
    List<Object> params = new ArrayList<>();
    if (category != null) { where.add("category = ?"); params.add(category); }
    if (maxPrice != null) { where.add("price <= ?"); params.add(maxPrice); }
    String sql = "SELECT id, name, price FROM products"
        + (where.isEmpty() ? "" : " WHERE " + String.join(" AND ", where))
        + " ORDER BY " + order + " LIMIT ? OFFSET ?";
    params.add(limit);
    params.add(offset);
    return new SqlQuery(sql, params);
}

void main() {
    SqlQuery q = build("books", new BigDecimal("40.00"), "-price", 20, 40);
    IO.println(q.sql());
    IO.println(q.params());
    try {
        build(null, null, "price; DROP TABLE products", 20, 0);
    } catch (IllegalArgumentException e) {
        IO.println("400 " + e.getMessage());
    }
}

// Output:
// SELECT id, name, price FROM products WHERE category = ? AND price <= ? ORDER BY price DESC, id DESC LIMIT ? OFFSET ?
// [books, 40.00, 20, 40]
// 400 unknown sort: price; DROP TABLE products

Related

java reading request bodies and query stringsjava preventing sql injectionjava sorting with comparatorjava sorting distinct and limitjava designing resources and endpoints

Discussion

  • Be the first to comment on this lesson.