实战②：数据建模与 API 契约设计

CREATE TABLE `user` (  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户 ID，自增主键',  `phone`       VARCHAR(20)     NOT NULL                COMMENT '手机号，登录账号',  `password`    CHAR(60)        NOT NULL                COMMENT 'BCrypt 哈希，定长 60，永不存明文',  `nickname`    VARCHAR(32)     NOT NULL DEFAULT ''     COMMENT '昵称',  `status`      TINYINT         NOT NULL DEFAULT 1      COMMENT '1 正常 0 禁用',  `deleted`     TINYINT         NOT NULL DEFAULT 0      COMMENT '逻辑删除 0 否 1 是',  `create_time` DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',  `update_time` DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3)                                ON UPDATE CURRENT_TIMESTAMP(3)        COMMENT '更新时间',  PRIMARY KEY (`id`),  UNIQUE KEY `uk_user_phone` (`phone`)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4  COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户表';

CREATE TABLE `product` (  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '商品 ID',  `name`        VARCHAR(128)    NOT NULL                COMMENT '商品名',  `subtitle`    VARCHAR(255)    NOT NULL DEFAULT ''     COMMENT '副标题',  `price`       DECIMAL(12,2)   NOT NULL                COMMENT '单价（元），精确金额',  `status`      TINYINT         NOT NULL DEFAULT 1      COMMENT '1 上架 0 下架',  `extra_info`  JSON            NULL                    COMMENT '扩展信息（标签/图片），结构不定',  `deleted`     TINYINT         NOT NULL DEFAULT 0      COMMENT '逻辑删除',  `create_time` DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',  `update_time` DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3)                                ON UPDATE CURRENT_TIMESTAMP(3)        COMMENT '更新时间',  PRIMARY KEY (`id`),  KEY `idx_product_status` (`status`)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '商品表';

CREATE TABLE `inventory` (  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '库存 ID',  `product_id`  BIGINT UNSIGNED NOT NULL                COMMENT '商品 ID',  `available`   INT UNSIGNED    NOT NULL DEFAULT 0      COMMENT '可售数量，无符号天然拒绝负值',  `locked`      INT UNSIGNED    NOT NULL DEFAULT 0      COMMENT '锁定数量（下单未支付）',  `version`     INT UNSIGNED    NOT NULL DEFAULT 0      COMMENT '乐观锁版本号',  `update_time` DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3)                                ON UPDATE CURRENT_TIMESTAMP(3)        COMMENT '更新时间',  PRIMARY KEY (`id`),  UNIQUE KEY `uk_inventory_product` (`product_id`)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '库存表';

CREATE TABLE `inventory_log` (  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '流水 ID',  `product_id`  BIGINT UNSIGNED NOT NULL                COMMENT '商品 ID',  `change_qty`  INT             NOT NULL                COMMENT '变动数量，扣减为负、回补为正',  `type`        VARCHAR(16)     NOT NULL                COMMENT 'DEDUCT 扣减 / RELEASE 释放 / RESTORE 回补',  `ref_id`      BIGINT UNSIGNED NOT NULL                COMMENT '关联单号（订单 ID 等）',  `create_time` DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',  PRIMARY KEY (`id`),  KEY `idx_invlog_product_time` (`product_id`, `create_time`),  UNIQUE KEY `uk_invlog_ref_type` (`ref_id`, `type`)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '库存流水表';

CREATE TABLE `order` (  `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单 ID',  `order_no`       VARCHAR(32)     NOT NULL                COMMENT '对外业务订单号',  `user_id`        BIGINT UNSIGNED NOT NULL                COMMENT '下单用户 ID',  `total_amount`   DECIMAL(12,2)   NOT NULL                COMMENT '订单总额（元）',  `status`         VARCHAR(16)     NOT NULL                COMMENT 'CREATED/PAID/SHIPPED/COMPLETED/CLOSED/REFUNDED',  `idempotent_key` VARCHAR(64)     NOT NULL                COMMENT '客户端幂等键',  `receiver_name`  VARCHAR(32)     NOT NULL DEFAULT ''     COMMENT '收货人',  `receiver_phone` VARCHAR(20)     NOT NULL DEFAULT ''     COMMENT '收货电话',  `receiver_addr`  VARCHAR(255)    NOT NULL DEFAULT ''     COMMENT '收货地址',  `pay_time`       DATETIME(3)     NULL                    COMMENT '支付时间',  `ship_time`      DATETIME(3)     NULL                    COMMENT '发货时间',  `close_time`     DATETIME(3)     NULL                    COMMENT '关闭时间',  `expire_time`    DATETIME(3)     NOT NULL                COMMENT '超时关单时间 = 创建 + 30 分钟',  `deleted`        TINYINT         NOT NULL DEFAULT 0      COMMENT '逻辑删除',  `create_time`    DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',  `update_time`    DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3)                                   ON UPDATE CURRENT_TIMESTAMP(3)        COMMENT '更新时间',  PRIMARY KEY (`id`),  UNIQUE KEY `uk_order_no` (`order_no`),  UNIQUE KEY `uk_order_idem` (`user_id`, `idempotent_key`),  KEY `idx_order_user_status_time` (`user_id`, `status`, `create_time`),  KEY `idx_order_status_expire` (`status`, `expire_time`)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '订单表';

CREATE TABLE `order_item` (  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单项 ID',  `order_id`      BIGINT UNSIGNED NOT NULL                COMMENT '订单 ID',  `product_id`    BIGINT UNSIGNED NOT NULL                COMMENT '商品 ID',  `product_name`  VARCHAR(128)    NOT NULL                COMMENT '下单时商品名快照',  `product_price` DECIMAL(12,2)   NOT NULL                COMMENT '下单时单价快照',  `quantity`      INT             NOT NULL                COMMENT '购买数量',  `amount`        DECIMAL(12,2)   NOT NULL                COMMENT '小计 = 单价 × 数量',  `create_time`   DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',  PRIMARY KEY (`id`),  KEY `idx_item_order` (`order_id`)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '订单项表';

CREATE TABLE `payment` (  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '支付记录 ID',  `order_id`      BIGINT UNSIGNED NOT NULL                COMMENT '订单 ID',  `trade_no`      VARCHAR(64)     NOT NULL                COMMENT '支付网关流水号，回调幂等键',  `amount`        DECIMAL(12,2)   NOT NULL                COMMENT '支付金额',  `channel`       VARCHAR(16)     NOT NULL DEFAULT 'MOCK' COMMENT '支付渠道',  `status`        VARCHAR(16)     NOT NULL                COMMENT 'INIT/SUCCESS/FAILED',  `callback_time` DATETIME(3)     NULL                    COMMENT '回调到达时间',  `create_time`   DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',  `update_time`   DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3)                                  ON UPDATE CURRENT_TIMESTAMP(3)        COMMENT '更新时间',  PRIMARY KEY (`id`),  UNIQUE KEY `uk_payment_trade_no` (`trade_no`),  KEY `idx_payment_order` (`order_id`)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '支付记录表';

-- 我的订单列表：按用户 + 状态 + 时间倒序ALTER TABLE `order`  ADD KEY `idx_order_user_status_time` (`user_id`, `status`, `create_time`);-- 定时关单扫描：先按状态筛，再按过期时间取最早的若干条ALTER TABLE `order`  ADD KEY `idx_order_status_expire` (`status`, `expire_time`);

POST /api/v1/ordersAuthorization: Bearer eyJhbGciOiJIUzI1NiJ9...Content-Type: application/json{  "receiverName": "张三",  "receiverPhone": "13800138000",  "receiverAddr": "深圳市南山区科技园 1 号楼 802",  "idempotentKey": "9f2c1e0a-3b7d-4c11-8e2a-create-order",  "items": [    { "productId": 1001, "quantity": 2 }  ]}

{  "code": 0,  "message": "ok",  "data": {    "orderNo": "202610071530001234",    "status": "CREATED",    "totalAmount": 11998.00,    "expireTime": "2026-10-07T07:30:00Z",    "items": [      { "productId": 1001, "productName": "蜂巢智能音箱",        "productPrice": 5999.00, "quantity": 2, "amount": 11998.00 }    ]  },  "traceId": "a1b2c3d4",  "timestamp": 1759827000000}

{  "code": 1001,  "message": "库存不足",  "data": { "productId": 1001, "available": 1, "requested": 2 },  "traceId": "e5f6a7b8",  "timestamp": 1759827004321}

package com.beeorder.order.dto;import jakarta.validation.Valid;import jakarta.validation.constraints.*;import java.util.List;public class CreateOrderRequest {    @NotBlank(message = "收货人不能为空")    @Size(max = 32)    private String receiverName;    @NotBlank(message = "收货电话不能为空")    @Pattern(regexp = "^1[3-9]\\d{9}$", message = "手机号格式不正确")    private String receiverPhone;    @NotBlank(message = "收货地址不能为空")    @Size(max = 255)    private String receiverAddr;    @NotEmpty(message = "订单项不能为空")    @Size(max = 50, message = "单个订单最多 50 个商品")    @Valid    private List<Item> items;    @NotBlank(message = "幂等键不能为空")    @Size(max = 64)    private String idempotentKey;    public static class Item {        @NotNull(message = "商品 ID 不能为空")        private Long productId;        @NotNull @Min(1) @Max(999)        private Integer quantity;        // getters / setters 省略    }    // getters / setters 省略}

package com.beeorder.order.vo;import java.math.BigDecimal;import java.time.LocalDateTime;import java.util.List;public class OrderVO {    private String orderNo;          // 只给业务单号，不给自增 id    private String status;           // 状态机枚举名    private BigDecimal totalAmount;    private List<OrderItemVO> items;    private String receiverName;    private String receiverPhone;    // 输出时脱敏为 138****8000    private LocalDateTime createTime;    private LocalDateTime payTime;    // 注意：不含 userId（前端无需知道）、不含 deleted、不含 version}

package com.beeorder.order.converter;public final class OrderConverter {    private OrderConverter() {}    public static OrderVO toVO(Order order, List<OrderItem> items) {        OrderVO vo = new OrderVO();        vo.setOrderNo(order.getOrderNo());        vo.setStatus(order.getStatus());        vo.setTotalAmount(order.getTotalAmount());        vo.setReceiverName(order.getReceiverName());        vo.setReceiverPhone(MaskUtil.phone(order.getReceiverPhone()));  // 脱敏        vo.setCreateTime(order.getCreateTime());        vo.setPayTime(order.getPayTime());        vo.setItems(items.stream().map(OrderConverter::toItemVO).toList());        return vo;    }}

package com.beeorder.common.response;public class Result<T> {    private int code;          // 0 表示成功    private String message;    private T data;    private String traceId;    // 链路追踪 ID，排障用    private long timestamp;    public static <T> Result<T> ok(T data) {        Result<T> r = new Result<>();        r.code = 0;        r.message = "ok";        r.data = data;        r.timestamp = System.currentTimeMillis();        return r;    }    public static <T> Result<T> fail(ErrorCode ec) {        Result<T> r = new Result<>();        r.code = ec.getCode();        r.message = ec.getMessage();        r.timestamp = System.currentTimeMillis();        return r;    }    // getters / setters 省略}

package com.beeorder.common.response;public enum ErrorCode {    OK(0, "ok", 200),    STOCK_NOT_ENOUGH(1001, "库存不足", 409),    ORDER_NOT_FOUND(1002, "订单不存在", 404),    ORDER_STATUS_ILLEGAL(1003, "订单状态不允许该操作", 409),    DUP_PAY_CALLBACK(2001, "重复支付回调，已忽略", 200),    PAY_AMOUNT_MISMATCH(2002, "支付金额与订单不符", 400),    UNAUTHORIZED(3001, "未认证或令牌已过期", 401),    FORBIDDEN(3002, "无权访问该资源", 403),    PARAM_INVALID(4001, "参数校验失败", 400),    INTERNAL_ERROR(5000, "系统繁忙，请稍后重试", 500);    private final int code;    private final String message;    private final int httpStatus;    ErrorCode(int code, String message, int httpStatus) {        this.code = code;        this.message = message;        this.httpStatus = httpStatus;    }    // getters 省略}

@Transactionalpublic OrderVO createOrder(Long userId, CreateOrderRequest req) {    try {        // 组装并插入订单；(user_id, idempotent_key) 上有唯一索引 uk_order_idem        Order order = assemble(userId, req);        orderMapper.insert(order);        return doCheckout(order, req);          // 扣库存、写订单项    } catch (DuplicateKeyException e) {        // 唯一键冲突 = 这是一次重复提交：查出首次结果直接返回        Order exist = orderMapper.selectByUserIdAndIdemKey(userId, req.getIdempotentKey());        log.info("重复下单，返回首次结果 orderNo={}", exist.getOrderNo());        return OrderConverter.toVO(exist, orderItemMapper.selectByOrderId(exist.getId()));    }}

@Transactionalpublic void handleCallback(PayCallbackDTO dto) {    Payment pay = paymentMapper.selectByTradeNo(dto.getTradeNo());    if (pay != null && PayStatus.SUCCESS.name().equals(pay.getStatus())) {        log.info("重复支付回调，已忽略 tradeNo={}", dto.getTradeNo());        return;                                   // 第一道：已成功，直接返回    }    // 第二道：条件更新，只有 CREATED 才能转 PAID    int rows = orderMapper.markPaid(dto.getOrderId(), OrderStatus.CREATED, OrderStatus.PAID);    if (rows == 0) {        log.warn("订单状态不允许支付，忽略 orderId={}", dto.getOrderId());        return;    }    paymentMapper.updateStatus(dto.getTradeNo(), PayStatus.SUCCESS);    inventoryService.commitLocked(dto.getOrderId());   // 锁定库存转实扣}

package com.beeorder.common.response;import java.util.List;public class PageResult<T> {    private List<T> list;    private long total;        // 总条数    private int page;          // 当前页（从 1 开始）    private int size;          // 每页条数    private int pages;         // 总页数    private boolean hasNext;   // 是否还有下一页    public static <T> PageResult<T> of(List<T> list, long total, int page, int size) {        PageResult<T> r = new PageResult<>();        r.list = list;        r.total = total;        r.page = page;        r.size = size;        r.pages = (int) ((total + size - 1) / size);        r.hasNext = (long) page * size < total;        return r;    }    // getters / setters 省略}

package com.beeorder.order.dto;import java.time.LocalDateTime;public class OrderQuery {    private String status;             // 可选：按状态筛选    private LocalDateTime beginTime;   // 可选：创建时间起点    private LocalDateTime endTime;     // 可选：创建时间终点    private Integer page = 1;    private Integer size = 20;         // 服务端上限 100    private Long userId;               // 由 JWT 注入，禁止前端传入！    public int getOffset() {        return (Math.max(page, 1) - 1) * Math.min(size, 100);    }    // getters / setters 省略}

-- 错误：MySQL 解析器会把 order 当成 ORDER BY 的语法部分，直接报语法错误SELECT * FROM order WHERE id = 1;      -- ERROR 1064-- 正确：用反引号包起来，声明它是一个标识符SELECT * FROM `order` WHERE id = 1;

CREATE DATABASE IF NOT EXISTS beeorder DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;USE beeorder;CREATE TABLE `user` (  `id`       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  `phone`    VARCHAR(20)     NOT NULL,  `password` CHAR(60)        NOT NULL,  `nickname` VARCHAR(32)     NOT NULL DEFAULT '',  PRIMARY KEY (`id`),  UNIQUE KEY `uk_user_phone` (`phone`)) ENGINE = InnoDB;CREATE TABLE `product` (  `id`    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  `name`  VARCHAR(128)    NOT NULL,  `price` DECIMAL(12,2)   NOT NULL,  PRIMARY KEY (`id`)) ENGINE = InnoDB;CREATE TABLE `order` (  `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  `order_no`       VARCHAR(32)     NOT NULL,  `user_id`        BIGINT UNSIGNED NOT NULL,  `total_amount`   DECIMAL(12,2)   NOT NULL,  `status`         VARCHAR(16)     NOT NULL,  `idempotent_key` VARCHAR(64)     NOT NULL,  `receiver_name`  VARCHAR(32)     NOT NULL,  `create_time`    DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3),  PRIMARY KEY (`id`),  UNIQUE KEY `uk_order_no` (`order_no`),  UNIQUE KEY `uk_order_idem` (`user_id`, `idempotent_key`)) ENGINE = InnoDB;CREATE TABLE `order_item` (  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  `order_id`      BIGINT UNSIGNED NOT NULL,  `product_id`    BIGINT UNSIGNED NOT NULL,  `product_name`  VARCHAR(128)    NOT NULL,  `product_price` DECIMAL(12,2)   NOT NULL,  `quantity`      INT             NOT NULL,  `amount`        DECIMAL(12,2)   NOT NULL,  PRIMARY KEY (`id`),  KEY `idx_item_order` (`order_id`)) ENGINE = InnoDB;INSERT INTO `user` (`phone`, `password`, `nickname`) VALUES ('13800138000', '$2a$10$placeholder', '小明');INSERT INTO `product` (`id`, `name`, `price`) VALUES (1001, '蜂巢智能音箱', 5999.00);

package com.beeorder.order.domain;import java.math.BigDecimal;public class OrderItem {    private Long id;    private Long orderId;    private Long productId;    private String productName;    private BigDecimal productPrice;    private Integer quantity;    private BigDecimal amount;    // getters / setters 省略}

package com.beeorder.order.mapper;import com.beeorder.order.domain.OrderItem;import org.apache.ibatis.annotations.Insert;import org.apache.ibatis.annotations.Mapper;import org.apache.ibatis.annotations.Options;import org.apache.ibatis.annotations.Param;import org.apache.ibatis.annotations.Select;import java.util.List;@Mapperpublic interface OrderItemMapper {    @Insert("INSERT INTO order_item (order_id, product_id, product_name, product_price, quantity, amount) "          + "VALUES (#{orderId}, #{productId}, #{productName}, #{productPrice}, #{quantity}, #{amount})")    @Options(useGeneratedKeys = true, keyProperty = "id")    int insert(OrderItem item);    @Select("SELECT id, order_id AS orderId, product_id AS productId, "          + "product_name AS productName, product_price AS productPrice, quantity, amount "          + "FROM order_item WHERE order_id = #{orderId}")    List<OrderItem> selectByOrderId(@Param("orderId") Long orderId);}

package com.beeorder.order.controller;import com.beeorder.common.response.Result;import com.beeorder.order.dto.CreateOrderRequest;import jakarta.validation.Valid;import org.springframework.web.bind.annotation.PostMapping;import org.springframework.web.bind.annotation.RequestBody;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;@RestController@RequestMapping("/api/v1/orders")public class OrderController {    private final OrderService orderService;    public OrderController(OrderService orderService) {        this.orderService = orderService;    }    @PostMapping    public Result<OrderVO> create(@Valid @RequestBody CreateOrderRequest req) {        // userId 必须来自 JWT，绝不能由前端传入        return Result.ok(orderService.createOrder(10015L, req));    }}

package com.beeorder.order.service;import com.beeorder.order.dto.CreateOrderRequest;import com.beeorder.order.mapper.OrderItemMapper;import com.beeorder.order.mapper.OrderMapper;import com.beeorder.order.vo.OrderVO;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.dao.DuplicateKeyException;import org.springframework.stereotype.Service;import org.springframework.transaction.annotation.Transactional;@Servicepublic class OrderService {    private static final Logger log = LoggerFactory.getLogger(OrderService.class);    private final OrderMapper orderMapper;    private final OrderItemMapper orderItemMapper;    public OrderService(OrderMapper orderMapper, OrderItemMapper orderItemMapper) {        this.orderMapper = orderMapper;        this.orderItemMapper = orderItemMapper;    }    @Transactional    public OrderVO createOrder(Long userId, CreateOrderRequest req) {        try {            var order = assemble(userId, req);          // 生成 orderNo、算 totalAmount、置 CREATED            orderMapper.insert(order);            req.getItems().forEach(item ->                    orderItemMapper.insert(toSnapshot(order.getId(), item)));  // 名称与单价在此冻结            return OrderConverter.toVO(order, orderItemMapper.selectByOrderId(order.getId()));        } catch (DuplicateKeyException e) {            // 撞在 uk_order_idem 上 = 重复提交，查出首次结果原样返回            var exist = orderMapper.selectByUserIdAndIdemKey(userId, req.getIdempotentKey());            log.info("重复下单，返回首次结果 orderNo={}", exist.getOrderNo());            return OrderConverter.toVO(exist, orderItemMapper.selectByOrderId(exist.getId()));        }    }    // assemble / toSnapshot 留给练习第二档实现}

curl -s -X POST http://localhost:8080/api/v1/orders \  -H "Content-Type: application/json" \  -d '{"receiverName":"张三","receiverPhone":"13800138000","receiverAddr":"深圳市南山区科技园 1 号楼 802","idempotentKey":"demo-key-001","items":[{"productId":1001,"quantity":2}]}'

{ "code": 0, "message": "ok", "data": { "orderNo": "202610071530001234", "status": "CREATED", "totalAmount": 11998.00 }, "traceId": null, "timestamp": 1759827000000 }