Too Many Queries

Every query is a trip to the database. A route that runs a query inside a loop makes one trip per element: one query to list the orders, then one more for each order. With 7 orders that is 1 + 7 = 8 queries; with 10,000 orders, 10,001. This is the N+1 problem: the answer is right, but the route gets slower as the shop grows.

It hides in any loop that runs a query. Here it is on another route, a list of customers with their orders:

List<Map<String, Object>> customers = db.queryForList("SELECT * FROM customers");
for (Map<String, Object> customer : customers) {
    List<Map<String, Object>> orders = db.queryForList("SELECT * FROM orders WHERE customer_id = ?", customer.get("id"));
}

The fix is one query with a JOIN. It sends back one row per item, with the columns of its order next to it, and ORDER BY keeps the rows of each order together. Your Java code then groups the rows into orders, with no more queries:

GET /orders  →  200  [{"id": 1, "status": "paid", "items": [{"product_id": 1, "quantity": 2}, {"product_id": 4, "quantity": 5}]}, {"id": 2, "status": "paid", "items": [{"product_id": 3, "quantity": 1}]}, … 7 orders]
POST /orders  {"customer_id": 5, "items": [{"product_id": 8, "quantity": 1}, {"product_id": 5, "quantity": 2}]}  →  201  {"id": 8}
GET /orders  →  200  [… the same 7 orders, then {"id": 8, "status": "new", "items": [{"product_id": 8, "quantity": 1}, {"product_id": 5, "quantity": 2}]}]

Your task: at the end of the controller, getOrders answers GET /orders. It already runs a query and returns result, which stays empty. Finish it so it answers every order with its id, its status and its items, each with its product_id and quantity, and asks the database only once: one db.queryForList call.

Assignment
Follow the Coding Tutorial to build GET /orders with one JOIN query, and group its rows into orders in Java.

Hint
A row starts a new order when result is still empty, or when the last order in it, result.getLast(), has another "id": compare the ids with equals. Check result.isEmpty() first: getLast() on an empty list throws an exception. Every row, new order or not, adds its item to the last order.