Pages of Results in Java

A real shop can have thousands of products, far too many for one answer. So clients ask for one page at a time: /products?page=2&per_page=3 means pages of 3 products, and the second one. The answer also gives the total, how many products match in all, and next_page, the page to ask for next, or null when there is none.

SQL cuts out one page with LIMIT and OFFSET: OFFSET skips rows, then LIMIT gives at most that many of the rows after them. To count the rows a query gives, wrap it in SELECT COUNT(*) FROM (...). On the orders table, with 5 orders per page:

-- page 3 of the new orders: skip 10, give the next 5
SELECT * FROM orders WHERE status = 'new' LIMIT 5 OFFSET 10
-- how many new orders there are in all
SELECT COUNT(*) FROM (SELECT * FROM orders WHERE status = 'new')

In Spring, @RequestParam takes a default: @RequestParam(defaultValue = "5") int limit gives 5 when the request has no limit. The opener of getProducts already reads page (1 by default) and per_page (10 by default) this way.

Your GET /products must give these answers:

GET /products?page=2&per_page=3
    →  200  {"products": [Pen, Plant, Teapot], "total": 8, "next_page": 3}
GET /products?page=3&per_page=3
    →  200  {"products": [Stapler, Gift card], "total": 8, "next_page": null}
GET /products?page=4&per_page=3
    →  200  {"products": [], "total": 8, "next_page": null}
GET /products?category=office&per_page=2
    →  200  {"products": [Notebook, Pen], "total": 3, "next_page": 2}
GET /products?category=office&per_page=2&page=2
    →  200  {"products": [Stapler], "total": 3, "next_page": null}
GET /products?max_price=10&per_page=3
    →  200  {"products": [Notebook, Pen, Stapler], "total": 3, "next_page": null}
GET /products
    →  200  {"products": [all 8 products], "total": 8, "next_page": null}
GET /products?page=0&per_page=3
    →  400  {"error": "page and per_page must be 1 or more"}

Each product in "products" is the full object, like {"id": 4, "name": "Pen", "price": 2, "category": "office", "stock": 120}.

Your task: make GET /products answer one page at a time, with the filters still working. Answer a map with the page's "products", the "total" and the "next_page". A page or per_page below 1 gets a 400.

Assignment
Follow the Coding Tutorial to count the matching products, give the answer its new shape, and cut one page out with LIMIT and OFFSET.

Hint
Page page starts after (page - 1) * per_page rows. Count the total before you add LIMIT to the query, or it will count only one page. nextPage is an Integer, since an int can't be null, and Map.of refuses null values, so next_page goes into a HashMap with put.