Insert Many at Once

A supplier sends the shop a list of new products. One POST /products per product costs one request, one query and one commit each, and the commit is the slow part: it waits until the disk has really stored the data. A batch sends the whole list in one request, and the shop saves it with one commit.

batchUpdate: a JdbcTemplate can run one SQL statement once for each item of a list, all in a single call. Each item is an Object[], with its values in the order of the ? marks. Here it adds two lines to order 4 at once, 2 mugs and 1 lamp:

List<Object[]> items = List.of(new Object[] {4, 1, 2}, new Object[] {4, 3, 1});
db.batchUpdate("INSERT INTO order_items (order_id, product_id, quantity) VALUES (?, ?, ?)", items);

All or nothing: if one row is refused, like a product with no price in a NOT NULL column, Spring throws a DataAccessException when the database reaches that row. Without a transaction, every row before it is already committed. With @Transactional on the route, the rows wait in one transaction that Spring commits when the method ends. Spring rolls back by itself only if an exception leaves the method; when you catch it, call TransactionAspectSupport.currentTransactionStatus().setRollbackOnly() and the whole batch is thrown away. The client can fix its list and send it again without making doubles.

GET /products  →  200  [{"id": 1, "name": "Mug", "price": 12}, …, {"id": 8, "name": "Gift card", "price": 50}]
POST /products/batch  [{"name": "Cup", "price": 8}, {"name": "Bowl", "price": 11}, {"name": "Vase", "price": 18}]
    →  201  {"added": 3}
GET /debug/queries  →  200  {"queries": 2}   (1 for the first GET /products, 1 for the whole batch)
GET /products  →  200  [… the 8 products …, {"id": 9, "name": "Cup", "price": 8}, {"id": 10, "name": "Bowl", …}, {"id": 11, "name": "Vase", …}]
POST /products/batch  [{"name": "Tray", "price": 6}, {"name": "Clock"}]
    →  400  {"error": "Each product needs a name and a price"}
GET /products/12  →  404  {"error": "Product not found"}

Your task: in runMany(sql, rows), which already counts one query, return db.batchUpdate(sql, rows). Then finish POST /products/batch: insert all the rows with one runMany call inside a try; catch DataAccessException, mark the transaction rollback-only and answer 400; put @Transactional on the route so the batch is committed once; and clear the cache before the 201 answer {"added": …}.

Assignment
Follow the Coding Tutorial to finish runMany and the POST /products/batch route.

Hint
The route reads each value with item.get("price"): a missing price becomes null, and the database refuses it for you. Because your catch handles the exception, Spring never sees it: setRollbackOnly() is what tells it to roll back instead of committing.