Сквозной SQL-сценарий заказа

1. Таблицы сценария заказа

Прежде чем написать хотя бы один INSERT, полезно остановиться и посмотреть на картину сверху: какие таблицы участвуют и как они связаны. Это как готовить на кухне: если вы не знаете, где лежит соль, то в какой-то момент начинаете солить сахаром — формально белое, но результат… запоминается надолго.

В сценарии оформления заказа mini-shop мы будем трогать четыре таблицы: product (чтобы взять цену), stock_item (чтобы проверить и списать остаток), customer_order (корень заказа) и order_item (позиции заказа). Важно помнить: заказ — это не одна строка, а связка строк. Именно поэтому мы постоянно возвращаемся к теме внешних ключей и транзакции.

Ниже — упрощённая схема связей (без категорий, чтобы не отвлекаться):

erDiagram
  PRODUCT ||--|| STOCK_ITEM : "1 to 1"
  CUSTOMER_ORDER ||--o{ ORDER_ITEM : "1 to many"
  PRODUCT ||--o{ ORDER_ITEM : "1 to many"

  PRODUCT {
    bigint id PK
    numeric price
  }

  STOCK_ITEM {
    bigint id PK
    bigint product_id FK
    int available_quantity
  }

  CUSTOMER_ORDER {
    bigint id PK
    varchar order_number UK
    varchar status
    varchar customer_email
    numeric total_amount
  }

  ORDER_ITEM {
    bigint id PK
    bigint order_id FK
    bigint product_id FK
    int quantity
    numeric unit_price
    numeric line_total
  }

Если хочется короткую «шпаргалку», вот зачем каждая таблица нужна именно в этом сценарии:

2. Скелет операции placeOrder

Теперь сделаем то, что в начале обучения делают редко, а в реальной разработке — почти всегда: сначала нарисуем скелет операции, а уже потом будем прикручивать к нему мышцы SQL. Такой подход спасает от хаоса, потому что вы заранее понимаете границу действия: где операция начинается и где она обязана закончиться либо commit, либо rollback.

Оформление заказа можно представить как небольшой алгоритм с проверками. Мы сначала читаем данные, затем создаём корень заказа, потом создаём позиции и на каждом товаре пытаемся списать остаток. Если хотя бы одно списание не получилось, откатываем всё. Сумму заказа либо считаем заранее, либо финализируем в конце, когда уже уверены, что всё прошло успешно.

Вот простая блок-схема:

flowchart TD
    A[Начать транзакцию] --> B[Прочитать цену и остаток]
    B --> C[INSERT customer_order]
    C --> D[Для каждой позиции: INSERT order_item]
    D --> E[UPDATE stock_item с проверкой available_quantity]
    E -->|0 строк обновлено| X[Ошибка: товара не хватает] --> R[ROLLBACK]
    E -->|1 строка обновлена| F[Суммируем total_amount]
    F --> G[UPDATE customer_order.total_amount]
    G --> K[COMMIT]

А теперь тот же смысл в Java-скелете. Детали PreparedStatement пока не важны; нам нужна граница транзакции и порядок шагов:

import java.math.BigDecimal;
import java.sql.Connection;

// Важно: выключаем автокоммит, чтобы управлять границей операции вручную
conn.setAutoCommit(false);

try {
    // 1) Создаём «корень» заказа и получаем его идентификатор
    long orderId = insertOrder(conn);

    // 2) Создаём позиции и параллельно пытаемся списывать остатки
    BigDecimal total = insertItemsAndUpdateStock(conn, orderId);

    // 3) Финализируем сумму заказа только когда все шаги прошли успешно
    updateOrderTotal(conn, orderId, total);

    // 4) Один commit в самом конце: либо всё, либо ничего
    conn.commit();
} catch (Exception ex) {
    // Обязательный rollback на любой ошибке, иначе останутся «полуданные»
    conn.rollback();
    throw ex;
}

Обратите внимание на дисциплину: commit строго в конце, а rollback — обязательная часть ветки ошибки. Это не опция, а половина смысла транзакции.

3. Какие данные читаем в начале сценария

В начале сценария нам нужны две вещи: цена товара и доступный остаток. В живом выполнении этот SELECT идёт уже внутри той же транзакции оформления заказа. Он важен сам по себе: без него непонятно, какую цену мы потом фиксируем в order_item и что именно проверяем в stock_item.

Психологически хочется сразу «что-нибудь вставить» — руки тянутся к INSERT. Но в сценарии заказа лучше сначала собрать исходные данные: цену товара и доступный остаток. Иначе вы рискуете записать позицию заказа с непонятной ценой или попытаться списать то, чего нет. Да, в конечном итоге база всё равно должна защититься, но вы хотите, чтобы сценарий был осознанным, а не в режиме «база ругалась — я чинил по факту».

Для одного товара нам нужно вытащить цену из product и остаток из stock_item. Так как stock_item связан с product через product_id, мы делаем обычный JOIN:

String readProductAndStock = """
    select p.id, p.price, s.available_quantity
    from product p
    join stock_item s on s.product_id = p.id
    where p.id = ?
    """;

Если показать минимальный JDBC-фрагмент без лишних деталей, это выглядит примерно так:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

try (PreparedStatement ps = conn.prepareStatement(readProductAndStock)) {
    // Передаём идентификатор товара в параметр запроса
    ps.setLong(1, productId);

    // Выполняем запрос и получаем результат
    ResultSet rs = ps.executeQuery();

    // Для простоты считаем, что товар существует.
    // В реальном коде здесь нужно обработать ситуацию, когда rs.next() == false.
    rs.next();
}

Здесь есть две важные тонкости. Во‑первых, цену нужно читать не потому, что вы не доверяете каталогу, а потому, что в заказе цена должна быть зафиксирована. Поэтому в order_item мы храним unit_price, а не полагаемся на то, что цена всегда берётся из product.

Во‑вторых, даже если вы прочитали available_quantity, это ещё не гарантия успеха. Настоящая проверка происходит в момент UPDATE stock_item ... where available_quantity >= ..., потому что именно он атомарно решает вопрос «хватает или нет». Поэтому чтение — это подготовка и расчёты, а окончательная проверка — в команде изменения данных.

4. Где проходит граница транзакции

После чтения исходных данных важно зафиксировать одну вещь, которую нельзя «добавить потом без последствий»: все дальнейшие шаги живут внутри одной транзакции оформления заказа. Именно поэтому setAutoCommit(false) стоит до создания customer_order, вставки order_item и списания остатков.

Почему важно проговорить это до всех красивых деталей? Потому что транзакция — это контейнер смысла. Внутри неё любые INSERT и UPDATE — это шаги одной операции, а снаружи — просто отдельные изменения. Вы хотите, чтобы база знала: «это всё одно действие».

И ещё один практический момент: транзакция — не про сложность кода, а про целостность данных. Даже если вы написали 20 строк Java, но там три SQL-команды, которые должны либо пройти вместе, либо не пройти вообще, — это уже кандидат на транзакцию.

5. Создаём customer_order

Теперь мы можем вставить заказ в customer_order. Делать это нужно до позиций, потому что order_item.order_id обязан ссылаться на уже существующий заказ. Это как сначала завести папку, а потом складывать в неё документы: без папки вы не сможете нормально сказать, к чему относится каждая бумажка.

Для простоты — чтобы не уплыть в генерацию идентификаторов — будем считать, что id и order_number мы уже получили на уровне приложения. Вставка может выглядеть так:

String insertOrder = """
    insert into customer_order (id, order_number, status, customer_email, total_amount)
    values (?, ?, 'NEW', ?, 0)
    """;

Почему total_amount пока 0? Потому что мы ещё не уверены, что все позиции успешно создадутся и остаток спишется. Мы можем накапливать сумму в коде по мере вставки позиций и обновить заказ в конце. Это чуть больше SQL-шагов, зато логика получается аккуратной: сумма заказа — результат позиций, а не «число, придуманное заранее и забытое при ошибке».

Мини-вставка через executeUpdate() выглядит так:

import java.sql.PreparedStatement;

try (PreparedStatement ps = conn.prepareStatement(insertOrder)) {
    // 1) id заказа (задаём сами в рамках примера)
    ps.setLong(1, orderId);

    // 2) номер заказа (должен быть уникальным)
    ps.setString(2, orderNumber);

    // 3) email клиента
    ps.setString(3, customerEmail);

    // INSERT должен затронуть ровно 1 строку — это наш «сигнал успеха»
    ps.executeUpdate();
}

Здесь мы не проверяем результат, но в голове держим важное правило: INSERT тоже может не вставить строку — например, из-за нарушения UNIQUE на order_number. В реальном коде вы бы либо ловили исключение, либо проверяли количество затронутых строк. В рамках транзакции это отлично: если вставка заказа не прошла, мы сразу оказываемся в ветке rollback.

6. order_item и сумма заказа

Когда корневая строка заказа уже есть, можно добавлять позиции. Именно в order_item мы фиксируем «снимок покупки»: какой товар, сколько штук и по какой цене. Это важно, потому что каталог может жить своей жизнью — цены и названия меняются, — а заказ должен оставаться исторически корректным.

SQL для вставки позиции выглядит вполне прямолинейно:

String insertOrderItem = """
    insert into order_item (id, order_id, product_id, quantity, unit_price, line_total)
    values (?, ?, ?, ?, ?, ?)
    """;

С точки зрения арифметики сумма заказа обычно строится из line_total, а line_total — из quantity * unit_price. В Java почти всегда используют BigDecimal, потому что деньги любят точность, а double любит сюрпризы (и да, это те самые сюрпризы, когда 0.1 + 0.2 внезапно не 0.3). Мини-фрагмент расчёта:

import java.math.BigDecimal;

// Цена товара, которую мы прочитали из product (или получили из DTO)
BigDecimal unitPrice = productPrice;

// Количество, которое хочет купить клиент
int quantity = 2;

// Стоимость строки заказа: количество * цена за единицу
BigDecimal lineTotal = unitPrice.multiply(BigDecimal.valueOf(quantity));

// Накапливаем итоговую сумму заказа по всем позициям
totalAmount = totalAmount.add(lineTotal);

И вставка позиции — сильно упрощённо — может выглядеть так:

import java.sql.PreparedStatement;

try (PreparedStatement ps = conn.prepareStatement(insertOrderItem)) {
    // id позиции (в примере «приходит сверху»)
    ps.setLong(1, orderItemId);

    // FK на заказ
    ps.setLong(2, orderId);

    // FK на товар
    ps.setLong(3, productId);

    // Сколько штук купили
    ps.setInt(4, quantity);

    // Фиксируем цену на момент покупки (историческая корректность)
    ps.setBigDecimal(5, unitPrice);

    // Фиксируем сумму строки, чтобы её можно было проверять и суммировать
    ps.setBigDecimal(6, lineTotal);

    ps.executeUpdate(); // ожидаем 1 строку
}

Обратите внимание: unit_price мы кладём в order_item, даже если цена уже есть в product. Это не дублирование ради дублирования, а фиксация исторического факта: «покупатель купил по такой цене». Без этого завтра вы поменяете цену в каталоге — и внезапно старые заказы «переоценятся», как будто прошлогодний телефон вы продали по сегодняшней цене.

7. Списание остатка через UPDATE

Вот мы и дошли до шага, на котором чаще всего ломается наивная реализация заказа: списание остатка. Главная идея здесь — не просто сделать available_quantity = available_quantity - 2, а сделать это так, чтобы база сама гарантировала: мы не уйдём в минус. Иначе вы очень легко получаете состояние «продали 2, хотя было 1», а потом героически объясняете клиенту, почему доставка отменяется.

Самый практический приём — списывать остаток условным UPDATE:

String updateStock = """
    update stock_item
    set available_quantity = available_quantity - ?
    where product_id = ?
      and available_quantity >= ?
    """;

Смысл здесь в том, что база либо изменит одну строку — значит, товара хватило, — либо изменит ноль строк — значит, товара не хватило. Это невероятно важный сигнал, и его нельзя игнорировать.

Мини-проверка выглядит так:

import java.sql.PreparedStatement;

try (PreparedStatement ps = conn.prepareStatement(updateStock)) {
    // 1) На сколько уменьшаем остаток
    ps.setInt(1, quantity);

    // 2) Какой товар списываем
    ps.setLong(2, productId);

    // 3) Проверка в WHERE: остаток должен быть не меньше требуемого количества
    ps.setInt(3, quantity);

    int updatedRows = ps.executeUpdate();

    // Если обновили 0 строк — условие WHERE не прошло: товара не хватило
    if (updatedRows == 0) throw new IllegalStateException("Недостаточно остатка");
}

Здесь есть маленькая «взрослая» мысль, которую стоит забрать с собой: нулевое количество обновлённых строк — это нормальный результат SQL. Это не «ошибка драйвера», не «база зависла» и не «магия». Это просто означает, что условие WHERE не совпало ни с одной строкой. В нашем сценарии это превращается в понятный бизнес-смысл: товара не хватает, заказ нельзя оформить.

И вот тут транзакция начинает играть главную роль. Мы уже могли успеть вставить customer_order и несколько order_item. Но если один UPDATE stock_item не прошёл, мы бросаем исключение, ловим его снаружи и вызываем rollback. В результате база откатывает всё: и заказ, и позиции, и любые изменения остатков по предыдущим товарам.

8. Пишем сумму и commit

Когда все позиции вставлены и остатки списались успешно, у нас в коде накопился totalAmount. Теперь можно привести заказ в завершённый вид: записать итоговую сумму в customer_order и подтвердить транзакцию. В этот момент мы уже знаем, что операция не оборвалась посередине, а значит сумма действительно соответствует позициям.

Финализирующий UPDATE:

String updateOrderTotal = """
    update customer_order
    set total_amount = ?
    where id = ?
    """;

И минимальный фрагмент выполнения:

import java.sql.PreparedStatement;

try (PreparedStatement ps = conn.prepareStatement(updateOrderTotal)) {
    // Итоговая сумма заказа (посчитана по позициям)
    ps.setBigDecimal(1, totalAmount);

    // Обновляем конкретный заказ
    ps.setLong(2, orderId);

    ps.executeUpdate(); // ожидаем 1 строку
}

После этого мы делаем commit. И именно в этот момент операция становится «видимой» как завершённая: заказ существует, позиции существуют, остаток списан, сумма совпадает. До commit всё это было как черновик, который можно выбросить через rollback. После commit — это уже часть истории данных.

Если хочется просто проверить глазами, что всё действительно сложилось, обычно делают простой запрос вида «покажи заказ и его позиции» или хотя бы смотрят количество строк в order_item по order_id. Это не отдельная тема, но как мыслительный приём очень полезно: всегда представляйте, какое состояние таблиц вы ожидаете увидеть после commit.

9. Rollback-сценарий и откат изменений

Про rollback легко говорить абстрактно, но важно почувствовать его механику. Представьте, что мы оформляем заказ из двух позиций: по первому товару остатка хватило, по второму — нет. Если транзакции нет, вы получите «полузаказ»: первая позиция записалась и остаток списался, а дальше всё упало. С транзакцией мир получается честнее: один из UPDATE stock_item возвращает 0, мы бросаем исключение, и снаружи срабатывает rollback всей операции.

Важная мысль: rollback откатывает не последнюю команду, а все изменения внутри транзакции, которые ещё не были подтверждены. Именно поэтому база отменит и customer_order, и уже вставленные order_item, и успешное списание по предыдущей позиции. И именно поэтому мы так упорно держим commit в конце: пока сценарий не дошёл до конца, всё считается черновиком.

И ещё одна дисциплина, которую стоит держать в голове: после ошибки и rollback соединение должно вернуться в нормальное состояние. В учебных примерах это часто игнорируют, а в реальной жизни «сломанное соединение» может поехать дальше и начать ломать другие операции. Даже если вы не пишете сложное приложение, привычка аккуратно закрывать ресурсы и контролировать autocommit — это инвестиция в спокойную жизнь.

10. Типичные ошибки при оформлении заказа

Ошибка №1: подтверждать транзакцию «по дороге», а не в конце.
Иногда новичок делает commit() после вставки customer_order, потому что «ну заказ же уже создали». А потом при вставке позиции или при списании остатков что-то падает — и rollback уже не способен вернуть базу в исходное состояние. В результате остаётся «заказ-призрак»: он есть, а позиций нет. Если операция логически одна, commit должен быть один — в конце.

Ошибка №2: не проверять количество затронутых строк в критическом UPDATE.
Команда вида update stock_item set available_quantity = available_quantity - 2 where product_id = 10 and available_quantity >= 2 — это не просто обновление, это одновременно и обновление, и проверка. Если обновлено 0 строк, это означает «товара не хватило». Если вы игнорируете этот факт и продолжаете сценарий, вы оформляете заказ без реального резервирования или списания товара — то есть создаёте красивую запись в базе, которая не соответствует реальности.

Ошибка №3: считать total_amount «самостоятельным» числом.
Часто делают так: сумму заказа посчитали где-то отдельно, позиции записали где-то отдельно, и эти две реальности постепенно расходятся. На SQL-уровне простая дисциплина такая: total_amount должен вытекать из позиций. Даже если вы считаете сумму в коде, логика должна оставаться связанной: позиция → line_total → сумма заказа, а не «я где-то помню, что сумма была 399.98».

Ошибка №4: вставлять order_item до customer_order и удивляться FK-ошибке.
Это та самая ошибка порядка, за которую база ругает абсолютно справедливо. Если позиция заказа ссылается на заказ, а заказа ещё нет, ссылка не может быть корректной. Исправление почти всегда одно: сначала создаём корневую запись, затем зависимые.

Ошибка №5: забывать сделать rollback в обработке исключения.
Иногда в catch логируют ошибку, пробрасывают исключение дальше, но rollback не вызывают. В итоге соединение остаётся внутри незавершённой транзакции, а изменения могут зависнуть до закрытия соединения или дать неожиданный эффект. Если вы отключили autocommit, то при ошибке обязаны явно выбрать: либо commit, либо rollback. Третьего не дано — это не философия, это физика транзакций.