Filters in SQL in Java

Searching by name was one optional filter. A shop needs more: ?max_price=10, ?category=office, or several at once, joined with &. A request can send any mix of them, so no single fixed query fits: you build the query piece by piece.

Keep two lists side by side. conditions holds the pieces of the WHERE, each with a ?, and params holds the values for those ?, in the same order. The visitor's text only ever goes into params, so the query stays safe. At the end, String.join(" AND ", ...) glues the pieces with AND between them, and params.toArray() hands the values to queryForList:

List<String> pieces = List.of("year >= ?", "author = ?");
System.out.println(String.join(" AND ", pieces));        // year >= ? AND author = ?
System.out.println(String.join(" AND ", List.of()));     // an empty string

Declare max_price as an Integer, not an int: Spring turns "10" into the number 10, and gives null when the URL doesn't send it, which an int can't hold. Test every filter with != null: a maximum price of 0 is a real filter.

GET /products?max_price=10                  →  200  [{"id": 2, "name": "Notebook", …}, {"id": 4, "name": "Pen", …}, {"id": 7, "name": "Stapler", …}]
GET /products?category=home                 →  200  [{"id": 3, "name": "Lamp", …}, {"id": 5, "name": "Plant", …}]
GET /products?category=office&max_price=8   →  200  [{"id": 2, "name": "Notebook", …}, {"id": 4, "name": "Pen", …}]
GET /products?name=Teapot&category=kitchen  →  200  [{"id": 6, "name": "Teapot", "price": 22, "category": "kitchen", "stock": 4}]
GET /products?name=Lamp&max_price=20        →  200  []
GET /products?max_price=0                   →  200  []
GET /products?category=x' OR '1'='1         →  200  []
GET /products                               →  200  [… all 8 products …]

Your task: next to name, getProducts now takes the optional filters max_price (products costing at most that much) and category. Build the WHERE from the filters the request really sends, with a ? placeholder for each value.

Assignment
Follow the Coding Tutorial to check each filter, collect the conditions and their values, and run the query you built.

Hint
Add a condition and its value in the same if, so the two lists stay in step. Add " WHERE " only when conditions.size() is more than 0.