1.有三个表（Product上，Orders，OrderItem）
分别创建对应的三个实体类
OrderItem中有外键Order_id 参考Orders中的id ；Product_id参考Product中的id；Orders中有外键User_id参考User中的id
sql 代码：
CREATE DATABASE day2017_04_05;
USE day2017_04_05;
CREATE TABLE `user` (
`id` INT(11) AUTO_INCREMENT,
`username` VARCHAR(20) ,
`PASSWORD` VARCHAR(20) ,
`gender` VARCHAR(10) ,
`email` VARCHAR(50) ,
`telephone` VARCHAR(20) ,
`introduce` VARCHAR(100),
`activeCode` VARCHAR(50) ,
`state` INT(11) ,
`role` VARCHAR(10) DEFAULT '普通用户',
`registTime` TIMESTAMP ,
PRIMARY KEY (`id`)
)
商品表
CREATE TABLE `products` (
`id` VARCHAR(100) ,
`name` VARCHAR(40) ,
`price` DOUBLE ,
`category` VARCHAR(40) ,
`pnum` INT(11) ,
`imgurl` VARCHAR(100) ,
`description` VARCHAR(255) ,
PRIMARY KEY (`id`)
)
订单表
CREATE TABLE `orders` (
`id` VARCHAR(100) ,
`money` DOUBLE ,
`receiverAddress` VARCHAR(255) ,
`receiverName` VARCHAR(20) ,
`receiverPhone` VARCHAR(20) ,
`paystate` INT(11) ,
`ordertime` TIMESTAMP ,
`user_id` INT(11) ,
PRIMARY KEY (`id`),
FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
)
订单项表
CREATE TABLE `orderitem` (
`order_id` VARCHAR(100) ,
`product_id` VARCHAR(100),
`buynum` INT(11) ,
PRIMARY KEY (`order_id`,`product_id`),
FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`),
FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
)
实体类
public class Order {
private String id; // 订单编号
private double money; // 订单总价
private String receiverAddress; // 送货地址
private String receiverName; // 收货人姓名
private String receiverPhone; // 收货人电话
private int paystate; // 订单状态
private Date ordertime; // 下单时间
private int userid;
private List<OrderItem> orderItems;
}
public class OrderItem {
private Order order;//订单
private Product product; //商品
private int buynum; //购物数量
}
public class Product {
private String id; // 商品编号
private String name; // 名称
private double price; // 价格
private String category; // 分类
private int pnum; // 数量
private String imgurl; // 图片路径
private String description; // 描述
}
servlet 操作
package com.learning.web.servlet; import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.UUID; import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; import org.apache.commons.beanutils.BeanUtils; import com.learning.domain.Order;
import com.learning.domain.OrderItem;
import com.learning.domain.Product;
import com.learning.domain.User;
import com.learning.service.OrderService; @WebServlet("/createOrder")
public class CreateOrderServlet extends HttpServlet {
private static final long serialVersionUID = 1L; protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { Order order=new Order();
User user = (User) request.getSession().getAttribute("user");
try {
BeanUtils.populate(order, request.getParameterMap());
order.setId(UUID.randomUUID().toString());
order.setUserid(user.getId());
} catch (Exception e) {
e.printStackTrace();
} List<OrderItem> orderItems=new ArrayList<OrderItem>();
//从session中获取cart，添加到订单项中
Map<Product, String> cart = (Map<Product, String>) request.getSession().getAttribute("cart");
for (Map.Entry<Product, String> map : cart.entrySet()) {
OrderItem orderItem=new OrderItem();
orderItem.setBuynum(Integer.parseInt(map.getValue()));
orderItem.setProduct(map.getKey());
orderItem.setOrder(order);
orderItems.add(orderItem);
} order.setOrderItems(orderItems);
OrderService orderService=new OrderService();
orderService.createOrder(order);
//生成订单，删除cart中的所有product
cart.clear(); request.getRequestDispatcher("/pay.jsp").forward(request, response); }
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
} }
public void createOrder(Order order) {
try {
ManageTheadLocal.startTransaction();
orderDao.createOrder(order);
orderItemDao.createOrderItem(order);
productDao.updateProductNumber(order);
ManageTheadLocal.commit();
} catch (SQLException e) {
ManageTheadLocal.rollback();
e.printStackTrace();
}
}
public void createOrder(Order order) throws SQLException {
QueryRunner queryRunner=new QueryRunner(C3P0Util.getDataSource());
queryRunner.update(ManageTheadLocal.getConnection()," insert into orders values(?,?,?,?,?,?,?,?)",order.getId(),order.getMoney(),order.getReceiverAddress(),order.getReceiverName(),order.getReceiverPhone(),order.getPaystate(),order.getOrdertime(),order.getUserid());
}
public void createOrderItem(Order order) throws SQLException {
List<OrderItem> orderItems = order.getOrderItems();
QueryRunner queryRunner=new QueryRunner(C3P0Util.getDataSource());
Object[][] params =new Object[orderItems.size()][];
for (int i = 0; i < params.length; i++) {
params[i]=new Object[]{order.getId(),orderItems.get(i).getProduct().getId(),orderItems.get(i).getBuynum()};
}
queryRunner.batch(ManageTheadLocal.getConnection()," insert into orderitem values (?,?,?) ",params );
}
public void updateProductNumber(Order order) throws SQLException {
List<OrderItem> orderItems = order.getOrderItems();
QueryRunner queryRunner=new QueryRunner(C3P0Util.getDataSource());
Object[][] params=new Object[orderItems.size()][];
for (int i = 0; i < params.length; i++) {
params[i]=new Object[]{orderItems.get(i).getBuynum()};
}
queryRunner.batch(ManageTheadLocal.getConnection()," update products set pnum=pnum-?", params);
}