收官实战：电商订单持久层，从三张表到上线验收

@Service
public class OrderService {

    private final ProductMapper productMapper;
    private final OrderMapper orderMapper;
    private final OrderItemMapper orderItemMapper;

    public OrderService(ProductMapper productMapper, OrderMapper orderMapper,
                        OrderItemMapper orderItemMapper) {
        this.productMapper = productMapper;
        this.orderMapper = orderMapper;
        this.orderItemMapper = orderItemMapper;
    }

    @Transactional(rollbackFor = Exception.class)
    public String placeOrder(Long userId, Long productId, int quantity, String requestId) {
        // 1. 幂等：requestId 唯一索引兜底（3.2）
        // 2. 防超卖扣库存，失败抛业务异常（本节）
        // 3. 落 orders 与 order_items，返回订单号
    }
}

for (int i = 0; i < 3; i++) {
    Product p = productMapper.selectById(productId);
    if (p.getStock() < quantity) {
        throw new BizException("库存不足");
    }
    p.setStock(p.getStock() - quantity);
    if (productMapper.updateById(p) > 0) {
        break;                      // 版本比对成功，扣减生效
    }
    // 影响行数 0：被人抢先，重读重试
}

@Update("UPDATE products SET stock = stock - #{n} WHERE id = #{id} AND stock >= #{n}")
int deductStock(@Param("id") Long id, @Param("n") int n);

if (productMapper.deductStock(productId, quantity) == 0) {
    throw new BizException("库存不足");
}

try {
    orderMapper.insert(order);
} catch (DuplicateKeyException e) {
    return orderMapper.selectByRequestId(requestId).getOrderNo();   // 幂等返回原单
}

Page<OrderListVo> selectOrderPage(Page<OrderListVo> page, @Param("q") OrderQuery q);

<select id="selectOrderPage" resultType="com.example.shop.vo.OrderListVo">
    SELECT id, order_no, status, total_amount, created_at
    FROM orders
    <where>
        <if test="q.userId != null">AND user_id = #{q.userId}</if>
        <if test="q.status != null">AND status = #{q.status}</if>
    </where>
    ORDER BY created_at DESC
</select>

<resultMap id="orderDetailMap" type="com.example.shop.vo.OrderDetailVo">
    <id property="id" column="id"/>
    <result property="orderNo" column="order_no"/>
    <collection property="items" ofType="com.example.shop.vo.OrderItemVo"
                select="selectItemsByOrderId" column="id"/>
</resultMap>

@Scheduled(fixedDelay = 60_000)
public void cancelTimeoutOrders() {
    List<Long> ids = orderMapper.selectTimeoutIds(30);   // PENDING 且 created_at 超过 30 分钟
    for (Long id : ids) {
        orderMapper.cancelIfPending(id);                 // 影响行数不问，天然幂等
    }
}

@Update("UPDATE orders SET status = 'CANCELLED', cancel_reason = '超时未支付' " +
        "WHERE id = #{id} AND status = 'PENDING'")
int cancelIfPending(@Param("id") Long id);

@SpringBootTest
@Testcontainers
class OrderPersistenceTest {

    @Container
    @ServiceConnection
    static MySQLContainer<?> mysql = new MySQLContainer<>("mysql:8.0");

    @Autowired OrderService orderService;
    @Autowired ProductMapper productMapper;

    @Test
    @DisplayName("下单成功：库存扣减且两表落库")
    void placeOrderDeductsStockAndPersists() {
        String orderNo = orderService.placeOrder(1L, 1L, 2, "req-1");
        assertThat(orderNo).isNotBlank();
        assertThat(productMapper.selectById(1L).getStock()).isEqualTo(8);   // 10 - 2
        // orders 与 order_items 各一行，断言按同思路补全
    }

    @Test
    @DisplayName("库存不足：整单回滚，零残留")
    void insufficientStockRollsBackEverything() {
        long before = productMapper.selectById(1L).getStock();
        assertThatThrownBy(() -> orderService.placeOrder(1L, 1L, 999, "req-2"))
                .isInstanceOf(BizException.class);
        assertThat(productMapper.selectById(1L).getStock()).isEqualTo(before);
    }

    @Test
    @DisplayName("并发扣减：20 人抢 5 份库存，恰好成功 5 单")
    void concurrentDeductionNeverOversells() throws Exception {
        // CountDownLatch 起跑 20 线程并发下单（quantity=1）
        // 断言：成功数 == 5；selectById(1L).getStock() == 0
    }
}