Place an Order

An order lives in two tables. orders gets one row for the order itself: the customer, the date and a status. order_items gets one row for each product in it: which order, which product, how many and at what price. Placing an order means writing all of these rows.

SQLite picks the id of a new row. End an INSERT with RETURNING id and it answers like a SELECT: run it with db.queryForList and you get back one row that holds the new id, so you can use it in the rows that belong to it. Here it is for a reviews table, if the shop had one:

List<Map<String, Object>> rows = db.queryForList("INSERT INTO reviews (product_id, stars) VALUES (?, ?) RETURNING id", 4, 5);
Object reviewId = rows.get(0).get("id");

Each item's unit_price comes from the products table, not from the request: the client only sends product ids and quantities. JdbcTemplate saves each statement in shop.db as soon as it runs, so there is no commit to write. The answer is 201 with the new id. An order with no items is a bad request: answer 400 before you insert anything.

POST /orders {"customer_id": 5, "items": [{"product_id": 6, "quantity": 1}, {"product_id": 4, "quantity": 3}]}
    →  201  {"id": 8}
GET /orders/8/items  →  200  {"order_id": 8, "items": [{"name": "Teapot", "quantity": 1, "unit_price": 22}, {"name": "Pen", "quantity": 3, "unit_price": 2}], "total": 28}
GET /orders/8  →  200  {"id": 8, "ordered": "<today's date>", "status": "new", "customer": "Eva Rossi"}
POST /orders {"customer_id": 1, "items": [{"product_id": 8, "quantity": 2}]}
    →  201  {"id": 9}
GET /orders/9/items  →  200  {"order_id": 9, "items": [{"name": "Gift card", "quantity": 2, "unit_price": 50}], "total": 100}
POST /orders {"customer_id": 2, "items": []}
    →  400  {"error": "An order needs at least one item"}

Your task: the route POST /orders, the placeOrder method and its items line are given. Insert the order with today's date (date('now')) and the status 'new', keep its id, add one order_items row per item with the product's current price, and answer {"id": …} with 201. When items is empty, answer 400 with the error "An order needs at least one item".

Assignment
Follow the Coding Tutorial to fill in placeOrder: the order row, its item rows, the 201 answer and the check for an empty order.

Hint
The order's INSERT ends with RETURNING id, so run it with db.queryForList; the item rows only need db.update. The empty check goes right below items, before the first INSERT.