电商订单系统设计（简单版）

下单 支付 主动取消订单 超时自动关单配套完整建表、实体、Mapper、XML、Service、Controller、事务、定时任务、异步、防超卖、状态流转基于SpringBoot2.5 MyBatis原生XML MySQL8.0。一、完整数据库表结构sqlCREATE DATABASE IF NOT EXISTS shop_full DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE shop_full; -- 商品表 CREATE TABLE product ( id BIGINT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(100) NOT NULL COMMENT 商品名称, price DECIMAL(10,2) NOT NULL COMMENT 单价, stock INT NOT NULL DEFAULT 0 COMMENT 库存, version INT NOT NULL DEFAULT 0 COMMENT 乐观锁版本号, create_time DATETIME DEFAULT CURRENT_TIMESTAMP, update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 订单主表 CREATE TABLE order_info ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL COMMENT 订单号, user_id BIGINT NOT NULL, total_amount DECIMAL(10,2) NOT NULL, pay_amount DECIMAL(10,2) DEFAULT 0, -- 订单状态0待支付 1已支付 2已取消 3已退款 order_status TINYINT NOT NULL DEFAULT 0, pay_time DATETIME NULL COMMENT 支付时间, cancel_time DATETIME NULL COMMENT 取消时间, expire_time DATETIME NULL COMMENT 超时过期时间, create_time DATETIME DEFAULT CURRENT_TIMESTAMP, update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id), KEY idx_order_status (order_status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 订单明细表 CREATE TABLE order_item ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, product_id BIGINT NOT NULL, product_name VARCHAR(100) NOT NULL, product_price DECIMAL(10,2) NOT NULL, quantity INT NOT NULL COMMENT 购买数量, create_time DATETIME DEFAULT CURRENT_TIMESTAMP, KEY idx_order_no (order_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 支付记录表 CREATE TABLE pay_log ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, pay_no VARCHAR(64) COMMENT 第三方支付流水号, pay_amount DECIMAL(10,2) NOT NULL, pay_status TINYINT NOT NULL DEFAULT 0 COMMENT 0待支付 1支付成功 2支付失败, pay_type TINYINT DEFAULT 1 COMMENT 1微信 2支付宝, create_time DATETIME DEFAULT CURRENT_TIMESTAMP, update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_order_no (order_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 操作日志表 CREATE TABLE operation_log ( id BIGINT PRIMARY KEY AUTO_INCREMENT, content VARCHAR(255) NOT NULL, create_time DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;二、核心依赖 pom.xmlxml?xml version1.0 encodingUTF-8? project xmlnshttp://maven.apache.org/POM/4.0.0 xmlns:xsihttp://www.w3.org/2001/XMLSchema-instance xsi:schemaLocationhttp://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd modelVersion4.0.0/modelVersion parent groupIdorg.springframework.boot/groupId artifactIdspring-boot-starter-parent/artifactId version2.5.15/version relativePath/ /parent groupIdcom.ecommerce/groupId artifactIdorder-full-system/artifactId version0.0.1-SNAPSHOT/version properties java.version1.8/java.version /properties dependencies dependency groupIdorg.springframework.boot/groupId artifactIdspring-boot-starter-web/artifactId /dependency dependency groupIdorg.springframework.boot/groupId artifactIdspring-boot-starter-jdbc/artifactId /dependency dependency groupIdorg.mybatis.spring.boot/groupId artifactIdmybatis-spring-boot-starter/artifactId version2.2.2/version /dependency dependency groupIdorg.springframework.boot/groupId artifactIdspring-boot-starter-task/artifactId /dependency dependency groupIdorg.springframework.boot/groupId artifactIdspring-boot-starter-aop/artifactId /dependency dependency groupIdmysql/groupId artifactIdmysql-connector-java/artifactId scoperuntime/scope /dependency dependency groupIdorg.projectlombok/groupId artifactIdlombok/artifactId optionaltrue/optional /dependency /dependencies build plugins plugin groupIdorg.springframework.boot/groupId artifactIdspring-boot-maven-plugin/artifactId configuration excludes exclude groupIdorg.projectlombok/groupId artifactIdlombok/artifactId /exclude /excludes /configuration /plugin /plugins /build /project三、配置文件 application.ymlyamlserver: port: 8080 spring: datasource: url: jdbc:mysql://localhost:3306/shop_full?useUnicodetruecharacterEncodingutf8serverTimezoneAsia/ShanghaiallowMultiQueriestrue username: root password: 123456 driver-class-name: com.mysql.cj.jdbc.Driver # 开启定时任务 task: scheduling: pool: core-size: 5 mybatis: mapper-locations: classpath:mapper/*.xml configuration: map-underscore-to-camel-case: true # 订单超时时间 30分钟 order: expire-minute: 30四、统一常量 实体类1. 订单状态常量java运行public class OrderConstant { // 订单状态 public static final int ORDER_WAIT_PAY 0; public static final int ORDER_PAY_SUCCESS 1; public static final int ORDER_CANCEL 2; public static final int ORDER_REFUND 3; // 支付状态 public static final int PAY_WAIT 0; public static final int PAY_SUCCESS 1; public static final int PAY_FAIL 2; }2. 核心实体简化展示全量 lombokjava运行import lombok.Data; import java.math.BigDecimal; import java.time.LocalDateTime; Data public class Product { private Long id; private String productName; private BigDecimal price; private Integer stock; private Integer version; private LocalDateTime createTime; private LocalDateTime updateTime; } Data public class OrderInfo { private Long id; private String orderNo; private Long userId; private BigDecimal totalAmount; private BigDecimal payAmount; private Integer orderStatus; private LocalDateTime payTime; private LocalDateTime cancelTime; private LocalDateTime expireTime; private LocalDateTime createTime; } Data public class OrderItem { private Long id; private String orderNo; private Long productId; private String productName; private BigDecimal productPrice; private Integer quantity; } Data public class PayLog { private Long id; private String orderNo; private String payNo; private BigDecimal payAmount; private Integer payStatus; private Integer payType; }五、Mapper 接口 XMLProductMapperjava运行Mapper public interface ProductMapper { Product selectById(Long id); // 乐观锁扣库存 int deductStock(Param(id) Long id, Param(num) Integer num, Param(version) Integer version); // 取消订单回补库存 int addStock(Param(id) Long id, Param(num) Integer num); }xml!-- ProductMapper.xml -- ?xml version1.0 encodingUTF-8? !DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtd mapper namespacecom.ecommerce.mapper.ProductMapper update iddeductStock UPDATE product SET stock stock - #{num}, version version 1 WHERE id #{id} AND version #{version} AND stock #{num} /update update idaddStock UPDATE product SET stock stock #{num} WHERE id #{id} /update /mapperOrderMapper、OrderItemMapper、PayLogMapper、LogMapper 省略只贴核心 SQLxml!-- 订单更新状态 -- update idupdateOrderStatus UPDATE order_info SET order_status #{status}, pay_time #{payTime}, update_time NOW() WHERE order_no #{orderNo} AND order_status #{oldStatus} /update !-- 查询超时未支付订单 -- select idlistExpireWaitPayOrder SELECT order_no FROM order_info WHERE order_status 0 AND expire_time NOW() LIMIT 100 /select六、核心 Service 层1. 日志服务独立事务 REQUIRES_NEWjava运行Service public class OperationLogService { Resource private OperationLogMapper operationLogMapper; Transactional(propagation Propagation.REQUIRES_NEW) public void recordLog(String content) { OperationLog log new OperationLog(); log.setContent(content); operationLogMapper.insert(log); } }2. 订单核心服务下单 支付 取消java运行Service Slf4j public class OrderService { Resource private ProductMapper productMapper; Resource private OrderMapper orderMapper; Resource private OrderItemMapper orderItemMapper; Resource private PayLogMapper payLogMapper; Resource private OperationLogService operationLogService; Value(${order.expire-minute}) private Integer expireMinute; // 1. 创建订单 Transactional(rollbackFor Exception.class, isolation Isolation.READ_COMMITTED) public String createOrder(Long userId, Long productId, Integer quantity) { // 1. 查询商品校验 Product product productMapper.selectById(productId); if (product null) throw new RuntimeException(商品不存在); if (product.getStock() quantity) throw new RuntimeException(库存不足); // 2. 乐观锁扣库存 int deduct productMapper.deductStock(productId, quantity, product.getVersion()); if (deduct 0) throw new RuntimeException(下单失败库存不足或并发冲突); // 3. 生成订单号 过期时间 String orderNo ORD System.currentTimeMillis() UUID.randomUUID().toString().substring(0,6); LocalDateTime expireTime LocalDateTime.now().plusMinutes(expireMinute); // 4. 保存订单 OrderInfo order new OrderInfo(); order.setOrderNo(orderNo); order.setUserId(userId); order.setTotalAmount(product.getPrice().multiply(new BigDecimal(quantity))); order.setOrderStatus(OrderConstant.ORDER_WAIT_PAY); order.setExpireTime(expireTime); orderMapper.insert(order); // 5. 订单明细 OrderItem item new OrderItem(); item.setOrderNo(orderNo); item.setProductId(productId); item.setProductName(product.getProductName()); item.setProductPrice(product.getPrice()); item.setQuantity(quantity); orderItemMapper.insert(item); // 6. 初始化支付记录 PayLog payLog new PayLog(); payLog.setOrderNo(orderNo); payLog.setPayAmount(order.getTotalAmount()); payLog.setPayStatus(OrderConstant.PAY_WAIT); payLogMapper.insert(payLog); // 异步日志 operationLogService.recordLog(用户[userId]创建订单orderNo); return orderNo; } // 2. 支付订单 Transactional(rollbackFor Exception.class) public void payOrder(String orderNo, String payNo, Integer payType) { // 1. 查询订单 OrderInfo order orderMapper.selectByOrderNo(orderNo); if (order null) throw new RuntimeException(订单不存在); if (!order.getOrderStatus().equals(OrderConstant.ORDER_WAIT_PAY)) { throw new RuntimeException(订单状态异常无法支付); } // 2. 更新订单状态为已支付 orderMapper.updateOrderStatus(orderNo, OrderConstant.ORDER_PAY_SUCCESS, OrderConstant.ORDER_WAIT_PAY, LocalDateTime.now()); // 3. 更新支付记录 payLogMapper.updatePaySuccess(orderNo, payNo, payType, order.getPayAmount()); operationLogService.recordLog(订单[orderNo]支付成功); } // 3. 主动取消订单 Transactional(rollbackFor Exception.class) public void cancelOrder(String orderNo) { // 1. 查询订单 OrderInfo order orderMapper.selectByOrderNo(orderNo); if (order null) throw new RuntimeException(订单不存在); if (!order.getOrderStatus().equals(OrderConstant.ORDER_WAIT_PAY)) { throw new RuntimeException(只有待支付订单可取消); } // 2. 查询订单商品 OrderItem item orderItemMapper.selectByOrderNo(orderNo); // 3. 回补库存 productMapper.addStock(item.getProductId(), item.getQuantity()); // 4. 修改订单为已取消 orderMapper.updateOrderCancel(orderNo, LocalDateTime.now()); operationLogService.recordLog(订单[orderNo]手动取消库存已回补); } // 4. 超时自动关闭订单 Transactional(rollbackFor Exception.class) public void autoCloseOrder(String orderNo) { OrderInfo order orderMapper.selectByOrderNo(orderNo); if (order null || !order.getOrderStatus().equals(OrderConstant.ORDER_WAIT_PAY)) { return; } // 回补库存 OrderItem item orderItemMapper.selectByOrderNo(orderNo); productMapper.addStock(item.getProductId(), item.getQuantity()); // 关闭订单 orderMapper.updateOrderCancel(orderNo, LocalDateTime.now()); operationLogService.recordLog(订单[orderNo]超时自动关闭); } }3. 定时任务定时扫描超时订单java运行Component EnableScheduling Slf4j public class OrderExpireTask { Resource private OrderMapper orderMapper; Resource private OrderService orderService; // 每1分钟执行一次 Scheduled(cron 0 */1 * * * ?) public void closeExpireOrder() { // 查询所有超时待支付订单 ListString expireOrderNos orderMapper.listExpireWaitPayOrder(); if (CollectionUtils.isEmpty(expireOrderNos)) { return; } for (String orderNo : expireOrderNos) { try { orderService.autoCloseOrder(orderNo); } catch (Exception e) { log.error(超时关闭订单{}失败{},orderNo,e.getMessage()); } } } }七、Controller 完整接口java运行RestController RequestMapping(/api/order) public class OrderController { Resource private OrderService orderService; // 1. 创建订单 PostMapping(/create) public Result create(Long userId, Long productId, Integer quantity){ try { String orderNo orderService.createOrder(userId,productId,quantity); return Result.success(orderNo,下单成功); }catch (Exception e){ return Result.fail(e.getMessage()); } } // 2. 支付订单 PostMapping(/pay) public Result pay(String orderNo,String payNo,Integer payType){ try { orderService.payOrder(orderNo,payNo,payType); return Result.success(支付成功); }catch (Exception e){ return Result.fail(e.getMessage()); } } // 3. 手动取消订单 PostMapping(/cancel) public Result cancel(String orderNo){ try { orderService.cancelOrder(orderNo); return Result.success(取消成功); }catch (Exception e){ return Result.fail(e.getMessage()); } } } // 统一返回工具 class Result{ private Integer code; private String msg; private Object data; public static Result success(Object data,String msg){ Result r new Result(); r.setCode(200); r.setMsg(msg); r.setData(data); return r; } public static Result fail(String msg){ Result r new Result(); r.setCode(500); r.setMsg(msg); return r; } // getter/setter }八、系统核心亮点 面试考点完整状态流转待支付 → 已支付 / 已取消手动 / 超时乐观锁防超卖库存扣减带 version 版本号解决并发超卖事务闭环下单 / 支付 / 取消 强事务保证日志独立事务REQUIRES_NEW业务失败日志不丢失订单超时自动关闭定时任务 cron 每分钟扫描自动回补库存、关闭订单库存双向操作下单扣库存、取消 / 超时 自动回补库存数据一致短事务设计只在 DB 写操作上加事务减少锁持有时间高性能九、接口测试地址http# 创建订单 POST http://localhost:8080/api/order/create?userId1productId1quantity2 # 模拟支付 POST http://localhost:8080/api/order/pay?orderNoxxxpayNoPAY123456payType2 # 手动取消 POST http://localhost:8080/api/order/cancel?orderNoxxx