訂單模塊處理用戶下單、訂單查詢、詳情展示、狀態流轉 (未付款→已付款→已發貨→交易成功)、收貨確認等核心電商流程。涉及訂單主表、訂單項表、圖書表、用戶表多表關聯。
public class OrderItem {
private String iid; // 項 ID
private int count; // 購買數量
private double subtotal; // 小計金額
private Order order; // 所屬訂單
private Book book; // 圖書信息
// getter/setter
}
public class Order {
private String oid; // 訂單號
private Date ordertime; // 下單時間
private double total; // 總金額
private int state; // 狀態: 1未付款 2已付款未發貨 3已發貨未確認 4交易成功
private User owner; // 下單用戶
private String address; // 收貨地址
private List orderItemList; // 訂單項集合
// getter/setter
}
public class OrderDao {
private QueryRunner qr = new TxQueryRunner();
// 添加訂單主表
public void addOrder(Order order) {
try {
String sql = "insert into orders values(?,?,?,?,?,?)";
Timestamp time = new Timestamp(order.getOrdertime().getTime());
Object[] params = {order.getOid(), time, order.getTotal(), order.getState(), order.getOwner().getUid(), order.getAddress()};
qr.update(sql, params);
} catch (SQLException e) { throw new RuntimeException(e); }
}
// 批量添加訂單項 (核心：batch 方法)
public void addOrderItemList(List itemList) {
try {
String sql = "insert into orderitem values(?,?,?,?,?)";
Object[][] params = new Object[itemList.size()][];
for (int i = 0; i < itemList.size(); i++) {
OrderItem item = itemList.get(i);
params[i] = new Object[]{item.getIid(), item.getCount(), item.getSubtotal(), item.getOrder().getOid(), item.getBook().getBid()};
}
qr.batch(sql, params); // 一條 SQL 批量執行，性能極高
} catch (SQLException e) { throw new RuntimeException(e); }
}
// 按用戶查詢訂單列表 (含項明細)
public List findByUid(String uid) {
try {
// 1. 查訂單主表
List orderList = qr.query("select * from orders where uid=?", new BeanListHandler<>(Order.class), uid);
// 2. 為每個訂單加載項明細
for (Order order : orderList) loadOrderItems(order);
return orderList;
} catch (SQLException e) { throw new RuntimeException(e); }
}
// 加載指定訂單的所有項 (關聯查詢 orderitem + book)
private void loadOrderItems(Order order) throws SQLException {
String sql = "select i.*, b.* from orderitem i left join book b on i.bid=b.bid where i.oid=?";
List> mapList = qr.query(sql, new MapListHandler(), order.getOid());
List items = new ArrayList<>();
for (Map map : mapList) {
OrderItem item = CommonUtils.toBean(map, OrderItem.class);
Book book = CommonUtils.toBean(map, Book.class);
item.setBook(book);
item.setOrder(order);
items.add(item);
}
order.setOrderItemList(items);
}
// 按訂單號查詢單個訂單 (含項)
public Order findByOid(String oid) {
try {
Order order = qr.query("select * from orders where oid=?", new BeanHandler<>(Order.class), oid);
if (order != null) loadOrderItems(order);
return order;
} catch (SQLException e) { throw new RuntimeException(e); }
}
// 更新訂單狀態 (付款/發貨/確認收貨)
public void updateState(String oid, int state) {
try { qr.update("update orders set state=? where oid=?", state, oid); }
catch (SQLException e) { throw new RuntimeException(e); }
}
// 後台管理：查詢所有訂單 (分頁略)
public List findAll() {
try {
List list = qr.query("select * from orders", new BeanListHandler<>(Order.class));
for (Order o : list) loadOrderItems(o);
return list;
} catch (SQLException e) { throw new RuntimeException(e); }
}
}
public class OrderService {
private OrderDao orderDao = new OrderDao();
// 生成訂單 (從購物車)
public void createOrder(Cart cart, User user, String address) {
Order order = new Order();
order.setOid(CommonUtils.uuid());
order.setOrdertime(new Date());
order.setTotal(cart.getTotal());
order.setState(1); // 未付款
order.setOwner(user);
order.setAddress(address);
List items = new ArrayList<>();
for (CartItem cartItem : cart.getItems()) {
OrderItem item = new OrderItem();
item.setIid(CommonUtils.uuid());
item.setCount(cartItem.getCount());
item.setSubtotal(cartItem.getSubtotal());
item.setOrder(order);
item.setBook(cartItem.getBook());
items.add(item);
}
order.setOrderItemList(items);
// 事務保證主表+項表原子性 (TxQueryRunner 共享 Connection)
orderDao.addOrder(order);
orderDao.addOrderItemList(items);
}
public List myOrders(String uid) { return orderDao.findByUid(uid); }
public Order loadOrder(String oid) { return orderDao.findByOid(oid); }
public void payOrder(String oid) { orderDao.updateState(oid, 2); } // 模擬付款
public void deliverOrder(String oid) { orderDao.updateState(oid, 3); } // 發貨
public void confirmOrder(String oid) { orderDao.updateState(oid, 4); } // 確認收貨
}
public class OrderServlet extends BaseServlet {
private OrderService orderService = new OrderService();
// 提交訂單 (從購物車)
public String add(HttpServletRequest req, HttpServletResponse resp) {
User user = (User) req.getSession().getAttribute("session_user");
if (user == null) return "r:/jsps/user/login.jsp"; // 未登錄跳轉登錄
Cart cart = (Cart) req.getSession().getAttribute("cart");
String address = req.getParameter("address");
orderService.createOrder(cart, user, address);
req.getSession().removeAttribute("cart"); // 清空購物車
req.setAttribute("order", orderService.loadOrder(order.getOid()));
return "f:/jsps/order/confirm.jsp"; // 訂單確認頁
}
// 我的訂單列表
public String myOrders(HttpServletRequest req, HttpServletResponse resp) {
User user = (User) req.getSession().getAttribute("session_user");
req.setAttribute("orderList", orderService.myOrders(user.getUid()));
return "f:/jsps/order/list.jsp";
}
// 訂單詳情
public String load(HttpServletRequest req, HttpServletResponse resp) {
req.setAttribute("order", orderService.loadOrder(req.getParameter("oid")));
return "f:/jsps/order/desc.jsp";
}
// 確認收貨
public String confirm(HttpServletRequest req, HttpServletResponse resp) {
orderService.confirmOrder(req.getParameter("oid"));
return "r:OrderServlet?method=myOrders"; // 重定向刷新列表
}
// 後台：發貨
public String deliver(HttpServletRequest req, HttpServletResponse resp) {
orderService.deliverOrder(req.getParameter("oid"));
return "r:OrderServlet?method=adminList";
}
}
CREATE TABLE orders (
oid VARCHAR(32) PRIMARY KEY,
ordertime TIMESTAMP NOT NULL,
total DECIMAL(10,2) NOT NULL,
state INT NOT NULL DEFAULT 1,
uid VARCHAR(32) NOT NULL,
address VARCHAR(200),
FOREIGN KEY (uid) REFERENCES tb_user(uid)
);
CREATE TABLE orderitem (
iid VARCHAR(32) PRIMARY KEY,
count INT NOT NULL,
subtotal DECIMAL(10,2) NOT NULL,
oid VARCHAR(32) NOT NULL,
bid VARCHAR(32) NOT NULL,
FOREIGN KEY (oid) REFERENCES orders(oid),
FOREIGN KEY (bid) REFERENCES book(bid)
);
[用戶下單] → 1.未付款 → [支付] → 2.已付款未發貨 → [商家發貨] → 3.已發貨未確認 → [用戶確認收貨] → 4.交易成功
↑ |
|_______________________ 取消/退款 _______________________________|
QueryRunner.batch(sql, Object[][]) 將 N 條 INSERT 合併為一次網絡往返，訂單項寫入性能提升 10-50 倍。TxQueryRunner 通過 ThreadLocal 共享 Connection，保證訂單主表與項表要么全成功要么全回滾。MapListHandler + CommonUtils.toBean 靈活處理聯表結果，避免重複查詢 N+1 問題。訂單模塊是電商系統的「心臟」，數據準確性、狀態流轉正確性、高併發下單性能，直接決定業務成敗，需投入最大精力打磨。
