Show stock availability on the cart page
Java · Spring Boot · intermediate · modification
Availability badges for the cart page ("In stock" / "Only 3 left" / "Out of stock"). The service sums `onHand - reserved` across warehouses for the whole cart in one grouped query, then maps the totals back onto the request lines, so the response keeps the cart's order. The request is validated at the controller (line count, SKU length, quantity range). Tested on a local cart with a stocked mug and a sold-out T-shirt: 7 available and in stock, 0 available and out of stock.
Shop backend on Spring Boot 3.3 (Java 21, Gradle with the Spring Boot plugin), Spring Data JPA with Hibernate 6.5 on PostgreSQL. Existing `StockLevel` entity (table `stock_levels`): `Long id`, `String sku`, `Long warehouseId`, `int onHand`, `int reserved`, all non-null, with a check constraint `0 <= reserved <= on_hand` and one row per SKU and warehouse. A SKU gets its first row at its first goods receipt in a warehouse, so new and pre-order catalogue items have no rows at all; a sold-out SKU keeps its rows at 0. SKUs are exact, case-sensitive codes. `SecurityConfig` permits `/api/cart/**` and `/error` to guests (guest checkout) and handles CSRF for the SPA. Showing exact counts ("Only 3 left") is a product decision.
Requirements
- `POST /api/cart/availability` takes `{"lines": [{"sku", "quantity"}]}`: 1 to 100 non-null lines, `sku` non-blank and at most 64 characters, `quantity` an integer from 1 to 999 (Spring Boot's default Jackson coercion applies). The cart merges duplicate SKUs before it calls, so the endpoint does not check for them. Any other invalid body gets Spring's default 400.
- It answers `{"lines": [{"sku", "requested", "available", "inStock"}]}` with one entry per request line, in request order. `available` is the SKU's `onHand - reserved` summed over all warehouses; `inStock` is `available >= requested`.
- The cart page calls this for every cart it shows, and carts often hold new and pre-order items. A SKU with no `stock_levels` rows yet has `available` 0 and `inStock` false, and the other lines are still answered.
- Availability for the whole cart comes from one query, not one per line.
Files touched
- src/main/java/com/acme/shop/inventory/StockLevelRepository.java
- src/main/java/com/acme/shop/cart/AvailabilityRequest.java
- src/main/java/com/acme/shop/cart/AvailabilityResponse.java
- src/main/java/com/acme/shop/cart/AvailabilityService.java
- src/main/java/com/acme/shop/cart/AvailabilityController.java
--- src/main/java/com/acme/shop/inventory/StockLevelRepository.java
package com.acme.shop.inventory;
+import java.util.Collection;
+import java.util.List;
import java.util.Optional;
import org.springframework.data.jpa.repository.JpaRepository;
+import org.springframework.data.jpa.repository.Query;
+import org.springframework.data.repository.query.Param;
public interface StockLevelRepository extends JpaRepository<StockLevel, Long> {
Optional<StockLevel> findBySkuAndWarehouseId(String sku, Long warehouseId);
+
+ @Query("""
+ select s.sku as sku, sum(s.onHand - s.reserved) as available
+ from StockLevel s
+ where s.sku in :skus
+ group by s.sku""")
+ List<SkuAvailability> sumAvailableBySku(@Param("skus") Collection<String> skus);
+
+ interface SkuAvailability {
+
+ String getSku();
+
+ Long getAvailable();
+ }
}