Import supplier cost updates from a CSV file
Java · Spring Boot · beginner · modification
Adds an upload for the supplier's monthly cost file. `SupplierCostController` takes the multipart file and hands its stream to `SupplierCostImportService.importCosts`, which checks the header, splits each data row into its three fields, validates the cost against a strict decimal pattern, looks up all the file's SKUs in one `findBySkuIn` query instead of one query per row, and applies the costs inside a single transaction. Rows it cannot apply are reported by line number so purchasing can fix them in the supplier portal and upload again. The description column is informational and is not stored.
Shop backend on Spring Boot 3.3 (Java 21), Spring Data JPA (Hibernate 6) on PostgreSQL. Existing and unchanged: the `Product` entity (`id`, `sku` unique and non-null, `name`, `unitCost` a non-null `BigDecimal` in a `numeric(10,2)` column, with `setUnitCost`), the `SecurityConfig` that requires `ROLE_PURCHASING` on `/api/purchasing/**` and handles CSRF, and `spring.servlet.multipart.max-file-size=1MB`. A supplier file has at most 2,000 rows. There is one product catalog (no tenants). Spring Boot's default error handling renders a `ResponseStatusException` with its status and no stack trace.
Requirements
- `POST /api/purchasing/supplier-costs` takes the supplier's cost file as the multipart field `file` and sets each listed product's `unitCost` to the file's `unit_cost`, so purchasing stops typing new costs in by hand. It returns `{"updated": <number of rows applied>, "rejectedLines": [...]}`, the 1-based line numbers (the header is line 1) of the rows it did not apply, in ascending order.
- File format, as the supplier's export writes it: UTF-8 without a BOM, lines end in `\n` or `\r\n`, and the first line is exactly `sku,description,unit_cost`. A field is wrapped in double quotes when, and only when, it contains a comma or a double quote; a double quote inside a quoted field is doubled (`""`). Fields never contain line breaks and there is no whitespace around the commas. SKUs (`[A-Z0-9-]`) and costs are never quoted. Descriptions are free text, are not stored, and routinely contain commas (`"Bolts, M6, zinc"`).
- Every row in that format whose SKU matches a product and whose `unit_cost` is a non-negative decimal with at most 8 digits before the point and at most 2 after it is applied. A row is rejected (listed in `rejectedLines`, changes nothing) only when it does not have exactly three fields, its SKU matches no product, or its cost is not such a decimal. Blank lines are skipped and are not rejected. If a SKU appears on several applied rows, the last one wins.
- A file whose first line is not exactly the header (an empty file included) is answered with 400 and changes nothing.
Files touched
- src/main/java/com/acme/shop/catalog/ProductRepository.java
- src/main/java/com/acme/shop/purchasing/SupplierCostController.java
- src/main/java/com/acme/shop/purchasing/SupplierCostImportService.java
--- src/main/java/com/acme/shop/catalog/ProductRepository.java
import org.springframework.data.jpa.repository.JpaRepository;
+import java.util.Collection;
+import java.util.List;
import java.util.Optional;
Optional<Product> findBySku(String sku);
+
+ List<Product> findBySkuIn(Collection<String> skus);
}
--- src/main/java/com/acme/shop/purchasing/SupplierCostController.java
+package com.acme.shop.purchasing;
+
+import org.springframework.http.MediaType;
+import org.springframework.web.bind.annotation.PostMapping;
+import org.springframework.web.bind.annotation.RequestMapping;
+import org.springframework.web.bind.annotation.RequestParam;
+import org.springframework.web.bind.annotation.RestController;
+import org.springframework.web.multipart.MultipartFile;
+import java.io.IOException;
+import java.io.InputStream;
+
+@RestController
+@RequestMapping("/api/purchasing/supplier-costs")
+public class SupplierCostController {
+
+ private final SupplierCostImportService importService;