Project: Customers in Java

Time to put the module together. The shop wants a small API for its customers. GET /customers lists them, and GET /customers/{customerId} shows one of them, with how many orders they placed and how much they spent. Your query and queryOne helpers do the talking to the database.

Numbers worked out from tables: the order count and the money spent are not stored anywhere. Every request works them out from orders joined with order_items, with COUNT and SUM, so they are always up to date. Watch for two traps. A JOIN gives one row per item, so count orders with COUNT(DISTINCT ...). SUM of no rows is NULL, so wrap it in COALESCE, which gives the first of its values that isn't NULL. For example, here is a route for the stock of one category:

@GetMapping("/categories/{name}/stock")
Object getCategoryStock(@PathVariable String name) {
    String sql = "SELECT COUNT(*) AS products, COALESCE(SUM(stock), 0) AS in_stock FROM products WHERE category = ?";
    Map<String, Object> totals = queryOne(sql, name);
    return totals;
}

A category with no products gets "in_stock": 0, not null. COUNT and SUM always give exactly one row, so totals is never null. A customer is different: queryOne gives null for an id no customer has, and a route that returns null answers 200 with an empty body. Your two routes must answer:

GET /customers     →  200  [{"id": 1, "name": "Ana Pop", "city": "Cluj"}, {"id": 2, "name": "Ben Ortiz", "city": "Madrid"}, …, {"id": 5, "name": "Eva Rossi", "city": "Rome"}]
GET /customers/1   →  200  {"id": 1, "name": "Ana Pop", "email": "ana@example.com", "city": "Cluj", "joined": "2026-01-10", "orders": 2, "spent": 75}
GET /customers/2   →  200  {"id": 2, "name": "Ben Ortiz", …, "orders": 1, "spent": 29}
GET /customers/3   →  200  {"id": 3, "name": "Chloe Ng", …, "orders": 2, "spent": 89}
GET /customers/5   →  200  {"id": 5, "name": "Eva Rossi", …, "orders": 0, "spent": 0}
GET /customers/99  →  404  {"error": "Customer not found"}
POST /orders {"customer_id": 5, "items": [{"product_id": 1, "quantity": 2}, {"product_id": 4, "quantity": 3}]}  →  201  {"id": 8}
GET /customers/5   →  200  {"id": 5, "name": "Eva Rossi", …, "orders": 1, "spent": 30}

Your task: the two routes and their return lines are already at the end of the file. getCustomers answers the id, name and city of every customer. getCustomer answers the whole customer plus orders and spent (cancelled orders don't count), or a 404 when no customer has that id.

Assignment
Follow the Coding Tutorial to build the two customer routes on top of your query and queryOne helpers.

Hint
Read the customer with queryOne and check for null first. The totals also come back as one row, because COUNT and SUM turn all the rows into one. put its "orders" and "spent" into the customer's map before you return it.