Low-stock report у mini-shop

1. Low-stock report як сценарій читання

Якщо ви ніколи не робили звіти в проєктах, вітаю: попереду на вас чекає чимало цікавих відкриттів. Звітний сценарій відрізняється від звичайного «покажи мені сутність» тим, що йому майже завжди потрібна таблиця, а не доменна модель. Low-stock report — класичний приклад: бізнесу потрібен список товарів, у яких мало доступного залишку, щоб вчасно поповнити склад і не продавати «повітря».

У нашому домені mini-shop low-stock report звучить як просте людське запитання: «Які товари ось-ось закінчаться?». Він дивиться одночасно на stock_item, на product і зазвичай ще на category — хоча б для того, щоб сортувати й групувати. Це не схоже на «завантаж мені один StockItem за id»; це схоже на «дай мені набір даних для звіту».

До цього моменту в нас уже є всі деталі окремо: ми відокремили випадки, коли native query справді виправдана, перейшли з entity-моделі на таблиці та FK, а потім розібрали дисципліну alias-ів і чесний countQuery. Тут не будемо запускати ту саму теорію заново — просто зберемо все це в один робочий сценарій читання.

Отже, у фінальній версії low-stock report нам потрібні не Product і не StockItem, а нормальний рядок звіту: код категорії, SKU, імʼя товару й доступний залишок. Це вже достатньо схоже на те, що реально читатимуть склад або закупівлі.

2. Проєкція LowStockReportRow

Раніше для mapping нам вистачало скороченого рядка sku + availableQuantity. Для робочого low-stock report цього замало: потрібні ще категорія та імʼя товару. Тому тут одразу фіксуємо повну форму рядка. І так, це все ще внутрішній результат читання на рівні даних, а не web DTO.

package com.example.shopdatajpa.inventory.query;

public interface LowStockReportRow {

    // Код категорії, щоб можна було сортувати й групувати у звіті
    String getCategoryCode();

    // SKU — зазвичай головний «ідентифікатор для очей» для складу й закупівель
    String getSku();

    // Людинозрозуміле імʼя товару (для UI/CSV)
    String getName();

    // Доступний залишок (саме його порівнюють із порогом)
    int getAvailableQuantity();
}

Назви тут не випадкові: SQL нижче дасть alias-и з тими самими іменами. Цього вже достатньо, щоб Java-сторона не гадала, куди мапити кожну колонку.

Це той самий контракт alias-ів, який уже працював на мінімальному LowStockRow, тільки тепер для повного рядка звіту.

3. Native SQL у репозиторії

Форму результату вже зафіксовано, тож далі просто збираємо SQL за схемою БД: реальні таблиці, реальні join-и й alias-и, які збігаються з LowStockReportRow. Це той момент, коли розробник або дружить зі схемою БД, або починає ненавидіти все підряд — зазвичай невинну кішку, яка просто проходила повз.

Припустімо, що в проєкті ми вже явно назвали таблиці та колонки як у схемі: stock_item, product, category, і в нас є FK-колонки stock_item.product_id та product.category_id. Тоді SQL для low-stock report виглядає дуже «земно»:

select
  c.code as categoryCode,                   -- alias має збігатися з getCategoryCode()
  p.sku  as sku,                            -- alias має збігатися з getSku()
  p.name as name,                           -- alias має збігатися з getName()
  s.available_quantity as availableQuantity -- alias має збігатися з getAvailableQuantity()
from stock_item s
join product p  on p.id = s.product_id
join category c on c.id = p.category_id
where s.available_quantity <= :threshold
order by c.code asc, s.available_quantity asc, p.sku asc;

Додамо метод прямо в StockItemRepository: це все ще читання даних складу, і тут так простіше, ніж створювати окремий репозиторій заради одного сценарію.

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

import com.example.shopdatajpa.inventory.entity.StockItem;
import com.example.shopdatajpa.inventory.query.LowStockReportRow;

public interface StockItemRepository extends JpaRepository<StockItem, Long> {

    // Запит звіту: повертаємо проєкцію, а не entity, щоб не тягнути зайві зв’язки
    @Query(value = """
            select
                   c.code as categoryCode,
                   p.sku as sku,
                   p.name as name,
                   s.available_quantity as availableQuantity
            from stock_item s
            join product p on p.id = s.product_id
            join category c on c.id = p.category_id
            where s.available_quantity <= :threshold
            order by c.code asc, s.available_quantity asc, p.sku asc
            """, nativeQuery = true)
    List<LowStockReportRow> findLowStockReport(@Param("threshold") int threshold);
}

Тут важливі три речі: SQL оформлено text block-ом, alias-и збігаються з іменами властивостей проєкції, а метод повертає LowStockReportRow, а не entity. Цього вже достатньо, щоб сценарій читання не тягнув зайвий граф об’єктів.

Параметр threshold читається краще, ніж ?1, а імʼя findLowStockReport і далі виражає бізнес-запит, а не технічну деталь.

4. Сервіс звіту

Навіть якщо ми робимо проєкт у підході data-layer-first, сервісний шар усе одно важливий: він тримає use case як зрозумілу операцію і не дає репозиторіям перетворитися на «універсальний довідник усього на світі». У нашому випадку сервіс потрібен не для складної оркестрації, а щоб зафіксувати сценарій як окрему точку входу: «отримати звіт low-stock».

Зробимо невеликий InventoryReportService. Він буде дуже чесним: просто прийме threshold, викличе репозиторій і поверне список рядків. Без героїзму, без додаткових залежностей і без «давайте тут же оновимо залишки» — сьогодні ми читаємо, а не пишемо.

import org.springframework.stereotype.Service;

import java.util.List;

import com.example.shopdatajpa.inventory.query.LowStockReportRow;
import com.example.shopdatajpa.inventory.repository.StockItemRepository;

@Service
public class InventoryReportService {

    // Репозиторій — єдина залежність сервісу для цього сценарію
    private final StockItemRepository stockItemRepository;

    public InventoryReportService(StockItemRepository stockItemRepository) {
        this.stockItemRepository = stockItemRepository;
    }

    // Use case: отримати рядки звіту за порогом залишку
    public List<LowStockReportRow> lowStock(int threshold) {
        return stockItemRepository.findLowStockReport(threshold);
    }
}

Зверніть увагу: сервіс не знає нічого про SQL. Він знає лише про use case і про те, що є репозиторій, який уміє відповісти на це запитання. Це якраз та «межа відповідальності», яку ми вже обговорювали в блоці про репозиторії: репозиторій відповідає за читання й запис даних, а сервіс — за те, щоб use cases були оформлені людською мовою і в правильному місці.

Якщо вам захочеться додати «threshold за замовчуванням», це можна зробити тут же, щоб не розмазувати magic number по коду:

public List<LowStockReportRow> lowStock() {
    // Поріг за замовчуванням: зручно для dev-режиму та ручних перевірок
    return lowStock(5);
}

Але я б поки залишив метод із параметром — так ви краще бачите, що звіт залежить від бізнес-налаштування, а не від «так історично склалося».

5. Smoke-run через CommandLineRunner

Зараз у нас є репозиторій, є сервіс, і все виглядає красиво на папері. Залишилося зробити те, що в розробці цінується особливо сильно: переконатися, що воно справді працює. Оскільки ми не хочемо тягнути web-layer і контролери «заради однієї перевірки», скористаємося старим добрим CommandLineRunner у dev-профілі й просто надрукуємо звіт у консоль.

Невеликий компонент, який запускається під час старту застосунку в dev-профілі:

import org.springframework.boot.CommandLineRunner;
import org.springframework.context.annotation.Profile;
import org.springframework.stereotype.Component;

import com.example.shopdatajpa.inventory.query.LowStockReportRow;
import com.example.shopdatajpa.inventory.service.InventoryReportService;

@Profile("dev")
@Component
public class LowStockReportDevRunner implements CommandLineRunner {

    // Сервіс звіту — єдина точка входу в use case
    private final InventoryReportService reportService;

    public LowStockReportDevRunner(InventoryReportService reportService) {
        this.reportService = reportService;
    }

    @Override
    public void run(String... args) {
        // У dev-режимі просто перевіряємо, що запит справді повертає рядки
        for (LowStockReportRow row : reportService.lowStock(3)) {
            System.out.println(row.getSku() + " -> " + row.getAvailableQuantity());
            // наприклад: TEA-001 -> 2
        }
    }
}

Тут ми свідомо друкуємо лише SKU та кількість, щоб вивід був коротким. Якщо хочеться красивіше, можна друкувати і категорію, і імʼя:

System.out.println(
        // Формат виведення: «категорія | sku | імʼя | залишок» — зручно для очей
        row.getCategoryCode() + " | " + row.getSku() + " | " +
        row.getName() + " | " + row.getAvailableQuantity()
);
// наприклад: FOOD | TEA-001 | Green Tea | 2

Після запуску застосунку ви побачите, що запит справді виконується, а рядки приходять уже в зручній формі. Це приємний момент: ми не завантажували ні Product, ні Category як сутності, не чіпали ліниві зв’язки і не будували жодного графа об’єктів. Ми просто отримали потрібні поля, як і личить звіту.

Якщо у вас раптом нічого не виводиться, не поспішайте панікувати й видаляти Hibernate. Перша перевірка дуже проста: чи є в базі взагалі дані і чи є товари з available_quantity <= threshold. Часто звіт «не працює» рівно тому, що він працює надто чесно.

Невелика схема, щоб закріпити потік даних:

sequenceDiagram
    participant App as Сервіс звіту
    participant Repo as Репозиторій
    participant DB as PostgreSQL

    App->>Repo: "lowStock(threshold)"
    Repo->>DB: "native SQL (join + where)"
    DB-->>Repo: "рядки (categoryCode, sku, name, availableQuantity)"
    Repo-->>App: "List<LowStockReportRow>"

6. Пагінація: Page і countQuery

Якщо звіт невеликий, List-версії з попереднього методу вже достатньо. Page має сенс лише тоді, коли вам справді потрібні total і навігація сторінками. У native SQL це одразу означає дві обов’язкові речі: написати countQuery і зафіксувати порядок прямо у value-запиті.

Приклад методу з пагінацією: він може жити поруч із List-версією, якщо вам потрібні обидва варіанти.

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

public interface StockItemRepository extends JpaRepository<StockItem, Long> {

    @Query(
        value = """
                select
                       c.code as categoryCode,
                       p.sku as sku,
                       p.name as name,
                       s.available_quantity as availableQuantity
                from stock_item s
                join product p on p.id = s.product_id
                join category c on c.id = p.category_id
                where s.available_quantity <= :threshold
                order by c.code asc, s.available_quantity asc, p.sku asc
                """,
        countQuery = """
                select count(*)
                from stock_item s
                where s.available_quantity <= :threshold
                """,
        nativeQuery = true
    )
    Page<LowStockReportRow> findLowStockReportPage(
            @Param("threshold") int threshold,
            Pageable pageable
    );
}

Тут countQuery повторює той самий фільтр за threshold, а join-и прибрано, тому що сам фільтр живе у stock_item. Якщо додасте умову за product або category, її потрібно повторити і в countQuery, інакше total почне брехати.

І ще один практичний момент: для native pagination ми задаємо порядок явно. order by c.code asc, s.available_quantity asc, p.sku asc тут не випадковість, а захист від плаваючих сторінок, коли в кількох рядків однаковий залишок.

Зазвичай під конкретний use case обирають або List-, або Page-версію. Тримати обидві версії має сенс лише тоді, коли вони справді потрібні.

7. Типові помилки під час low-stock звіту

Помилка №1: збирати low-stock report через entity замість рядка звіту.
Коли ви повертаєте Product або StockItem, а потім вручну добираєте category/name, звіт починає тягнути зайві поля й зайву зв’язність. Для такого сценарію projection чесніша: вона одразу виражає форму рядка і не вдає із себе доменну модель.

Помилка №2: дати SQL і LowStockReportRow розʼїхатися за іменами.
available_quantity у БД і getAvailableQuantity() у Java самі по собі не зобов’язані збігатися. Якщо в SQL забули as availableQuantity, один стовпець уже може не замапитися так, як ви очікуєте.

Помилка №3: зробити page-версію без явного і стабільного order by.
Якщо сортувати «як вийде» або лише за одним неунікальним полем, рядки з однаковим залишком можуть стрибати між сторінками. Для native pagination порядок — частина контракту, а не декоративна деталь запиту.

Помилка №4: спростити countQuery сильніше, ніж можна.
Поки фільтр живе лише в stock_item, count можна рахувати без join-ів. Але якщо основний запит починає фільтрувати за product.status, category.code або ще чимось із приєднаних таблиць, ті самі умови мають потрапити і в countQuery. Інакше вміст сторінки та total почнуть жити в різних світах.

Помилка №5: тримати List- і Page-версію як дві різні логіки звіту.
Якщо одна версія сортує за категорією, а інша — лише за кількістю, якщо одна враховує threshold, а інша ще й статус товару, це вже не «два способи повернути те саме», а два різні звіти. Краще тримати один смисловий baseline і розширювати його обережно.