Pages That Stay Fast

A real shop may sell 100,000 products, and no answer should carry them all, so the list comes in pages. With LIMIT and OFFSET, a deep page like OFFSET 90000 makes the database walk past 90,000 rows only to throw them away: the deeper the page, the slower it gets.

A keyset page remembers where the last page ended instead: "the rows after id 6". The id is the primary key, which has its own index, so the database jumps straight to the first id after it, and page 1 and page 30,000 cost the same. Here is the idea for a list of orders, 5 per page:

@GetMapping("/orders")
Object getOrders(@RequestParam(defaultValue = "0") int last) {
    return db.queryForList("SELECT id, status FROM orders WHERE id > ? ORDER BY id LIMIT 5", last);
}

Each answer also tells the client where the next page starts: "next_after" is the id of the page's last row when the page is full, and null on a shorter, last page. Map.of doesn't accept null, so use Optional.empty(), which Spring writes as null. Because a page starts after an id, not after a count of rows, a product removed between two pages doesn't make the next page skip one.

GET /products          →  200  {"next_after": 3, "products": [{"id": 1, "name": "Mug", "price": 12}, {"id": 2, …}, {"id": 3, …}]}
GET /products?after=3  →  200  {"next_after": 6, "products": [{"id": 4, "name": "Pen", "price": 2}, {"id": 5, …}, {"id": 6, …}]}
GET /products?after=6  →  200  {"next_after": null, "products": [{"id": 7, "name": "Stapler", "price": 9}, {"id": 8, …}]}
GET /products?after=8  →  200  {"next_after": null, "products": []}
(then the Notebook, id 2, is removed)
GET /products?after=3  →  200  {"next_after": 6, "products": [{"id": 4, …}, {"id": 5, …}, {"id": 6, …}]}
GET /products          →  200  {"next_after": 4, "products": [{"id": 1, …}, {"id": 3, …}, {"id": 4, …}]}

Your task: add a constant PAGE_SIZE set to 3, and make getProducts take after from the query string with @RequestParam (0 when it is missing), fetch the id, name and price of the products whose id is bigger, in id order, at most PAGE_SIZE of them, and answer a Map.of with "products" and "next_after".

Assignment
Follow the Coding Tutorial to turn GET /products into keyset pages.

Hint
Read the parameter with @RequestParam(defaultValue = "0") int after. Start with Object nextAfter = Optional.empty();, and only when the page is full, rows.size() == PAGE_SIZE, set it to the id of the last row, rows.get(rows.size() - 1).get("id").