分析:
显示用户订单列表在order_list.jsp页面,要一个功能servlet,把数据传递给前台页面显示。同时要先判断用户是否已经登陆。关键在于数据需要怎么封装,这里涉及多表查询。
1.根据uid,查询用户的所有订单集合List<Order>。此时order对象里数据orderItem集合还没有封装好,需要遍历封装数据
2.判断List<Order>是否为空,如不为空,进行遍历,并进行多表查询。查询orderItem表的count,subtotal,查询product表的pimage,pname,shop_price。
select i.count,i.subtotal,p.pimage,p.pname,p.shop_price from orderItem i, product p where i.pid=p.pid and oid=?
因为查询过后的数据,不能用orderitem或product泛型直接封装。所以需要用MapListHandler来装查询之后的数据。意在于把count,subtotal,pimage,paneme,shop_price封到一起,然后下一步再查出来封装。
多表查询返回后的是List<Map<String,object>> mapList 。根据mapList里的count subtatol pimage pname shop_price 封装OrderItem对象与Product对象。
然后把Product对象封装到OrderItem对象里,再把OrderItem对象封装到Order对象的List<OrderItem>集合里。这样,一个所需要的Order对象封装完毕。
3把orderList传到request域,并转发到order_list.jsp
在order_list.jsp页面
导入jstl
1.遍历orderList,得到每个order对象。可以获取订单编号,订单付款状态,订单总计
2.遍历每个订单对象的订单项集合orderItems,得到每个订单下的每个订单项orderItem,获取count,subtotal,pimage,shop_price
步骤:
在head.jsp页面,修改“我的订单”的href。作为servlet入口
ProductServlet
0.判断user是否已经登陆。
1.查询该用户下的所有订单List<Order> orderList,此时数据还没有封装完毕,需要补充数据。
2.遍历orderList,多表查询orderitem product表
3.根据oid, 查询orderItems表,与有关联的product表
4.把mapList转换为List<OrderItem> OrderItems
从map中抽取piame paname shop_price封装到product中
从map中抽取count subtotal封装到orderItem里
将product封装到orderitem中
把orderItem对象封装到order对象的集合里
5.到目前为止,order对象封装完毕,把orderList转发到订单显示页面order_list.jsp
在order_list.jsp
导入jstl
1.遍历orderList,得到order,通过order获取oid total state
2.遍历order里面的orderItems.得到orderItem,通过orderItem获取count subtotal pimage pname shop_price.
head.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<!-- 登录 注册 购物车... -->
<div class="container-fluid">
<div class="col-md-4">
<img src="img/logo2.png" />
</div>
<div class="col-md-5">
<img src="img/header.png" />
</div>
<div class="col-md-3" style="padding-top:20px">
<ol class="list-inline">
<c:if test="${empty user }">
<li><a href="login.jsp">登录</a></li>
<li><a href="register.jsp">注册</a></li>
</c:if>
<c:if test="${!empty user }">
<span>欢迎您,${user.username } </span>
</c:if>
<li><a href="cart.jsp">购物车</a></li>
<li><a href="${pageContext.request.contextPath }/product?method=myOrder">我的订单</a></li>
</ol>
</div>
</div>
<!-- 导航条 -->
<div class="container-fluid">
<nav class="navbar navbar-inverse">
<div class="container-fluid">
<!-- Brand and toggle get grouped for better mobile display -->
<div class="navbar-header">
<button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#bs-example-navbar-collapse-1" aria-expanded="false">
<span class="sr-only">Toggle navigation</span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
<a class="navbar-brand" href="#">首页</a>
</div>
<!-- 动态获取商品分类ajax -->
<script type="text/javascript">
$(function(){
$.post(
"${pageContext.request.contextPath}/product?method=categoryList",
function(data){
//[{"cid":"xxx","cname":"xxx"},{},{},{},{}]
var content ="";
for (var i = 0; i < data.length; i++) {
//content+="<li class='active'><a href='#'>"+data[i].cname+"<span class='sr-only'>(current)</span></a></li>";
content+="<li><a href='${pageContext.request.contextPath}/product?method=productListByCid&cid="+data[i].cid+"'>"+data[i].cname+"<span class='sr-only'>(current)</span></a></li>";
}
$("#categoryUl").html(content);
},
"json"
);
});
</script>
<div class="collapse navbar-collapse" id="bs-example-navbar-collapse-1">
<ul class="nav navbar-nav" id="categoryUl">
<!-- <li class="active"><a href="product_list.htm">手机数码<span class="sr-only">(current)</span></a></li>
<li><a href="#">电脑办公</a></li> -->
</ul>
<form class="navbar-form navbar-right" role="search">
<div class="form-group">
<input type="text" class="form-control" placeholder="Search">
</div>
<button type="submit" class="btn btn-default">Submit</button>
</form>
</div>
</div>
</nav>
</div>
ProductServlet
package com.itheima.web.servlet;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.ResourceBundle;
import java.util.UUID;
import javax.servlet.ServletException;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.commons.beanutils.BeanUtils;
import com.google.gson.Gson;
import com.itheima.domain.Cart;
import com.itheima.domain.CartItem;
import com.itheima.domain.Category;
import com.itheima.domain.Order;
import com.itheima.domain.OrderItem;
import com.itheima.domain.PageBean;
import com.itheima.domain.Product;
import com.itheima.domain.User;
import com.itheima.service.ProductService;
import com.itheima.utils.JedisPoolUtils;
import com.itheima.utils.PaymentUtil;
import redis.clients.jedis.Jedis;
public class ProductServlet extends BaseServlet {
//显示用户的订单列表
public void myOrder(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {
//检查用户是否已经登陆
HttpSession session = request.getSession();
User user = (User) session.getAttribute("user");
if(user==null) {
//重定向到登陆页面
response.sendRedirect(request.getContextPath()+"/login.jsp");
return;
}
//1.查询该用户下的所有订单集合,此时order对象的数据还没封装好,没有orderItem集合
ProductService service = new ProductService();
List<Order> orderList = service.findAllOrder(user.getUid());
//2.遍历orders对象,多表查询orderItem,product表
if(orderList!=null) {
for(Order order:orderList) {
//3.根据oid, 查询orderItems表,与有关联的product表
//获取每个订单的oid
String oid = order.getOid();
List<Map<String,Object>> mapList = service.findOrderItemsByOid(oid);
//4.把mapList转换为List<OrderItem> OrderItems
for(Map<String,Object> map:mapList) {
try {
//从map中抽取piame paname shop_price封装到product中
Product product = new Product();
BeanUtils.populate(product, map);
//从map中抽取count subtotal封装到orderItem里
OrderItem item = new OrderItem();
//item.setCount(Integer.parseInt(map.get("count").toString()));
BeanUtils.populate(item, map);
//将product封装到orderitem中
item.setProduct(product);
//把orderItem对象封装到order对象的集合里
order.getOrderItems().add(item);
} catch (IllegalAccessException | InvocationTargetException e) {
e.printStackTrace();
}
}
}
}
//到目前为止,order对象封装完毕
//把orderList转发到订单显示页面order_list.jsp
request.setAttribute("orderList", orderList);
request.getRequestDispatcher("/order_list.jsp").forward(request, response);
}
// 确定订单---更新收货人的信息与在线支付功能
public void confirmOrder(HttpServletRequest request, HttpServletResponse response) throws IOException {
// 1.更新收货人信息
// 获取表单数据,用order封装数据()
Map<String, String[]> parameterMap = request.getParameterMap();
Order order = new Order();
try {
BeanUtils.populate(order, parameterMap);
} catch (IllegalAccessException | InvocationTargetException e) {
e.printStackTrace();
}
// 传递到service层修改数据库收货人信息
ProductService service = new ProductService();
service.updateOrderAdd(order);
// 2.在线支付
//第三方平台提供的代码,按照自己工程修改oid 与总计
// 获得 支付必须基本数据
String orderid = request.getParameter("oid");
//支付的金额,要从内存获取,不要在页面获取(不安全)
String money = order.getTotal()+"";
// 银行
String pd_FrpId = request.getParameter("pd_FrpId");
// 发给支付公司需要哪些数据
String p0_Cmd = "Buy";
String p1_MerId = ResourceBundle.getBundle("merchantInfo").getString("p1_MerId");
String p2_Order = orderid;
String p3_Amt = money;
String p4_Cur = "CNY";
String p5_Pid = "";
String p6_Pcat = "";
String p7_Pdesc = "";
// 支付成功回调地址 ---- 第三方支付公司会访问、用户访问
// 第三方支付可以访问网址
String p8_Url = ResourceBundle.getBundle("merchantInfo").getString("callback");
String p9_SAF = "";
String pa_MP = "";
String pr_NeedResponse = "1";
// 加密hmac 需要密钥
String keyValue = ResourceBundle.getBundle("merchantInfo").getString("keyValue");
String hmac = PaymentUtil.buildHmac(p0_Cmd, p1_MerId, p2_Order, p3_Amt, p4_Cur, p5_Pid, p6_Pcat, p7_Pdesc,
p8_Url, p9_SAF, pa_MP, pd_FrpId, pr_NeedResponse, keyValue);
String url = "https://www.yeepay.com/app-merchant-proxy/node?pd_FrpId=" + pd_FrpId + "&p0_Cmd=" + p0_Cmd
+ "&p1_MerId=" + p1_MerId + "&p2_Order=" + p2_Order + "&p3_Amt=" + p3_Amt + "&p4_Cur=" + p4_Cur
+ "&p5_Pid=" + p5_Pid + "&p6_Pcat=" + p6_Pcat + "&p7_Pdesc=" + p7_Pdesc + "&p8_Url=" + p8_Url
+ "&p9_SAF=" + p9_SAF + "&pa_MP=" + pa_MP + "&pr_NeedResponse=" + pr_NeedResponse + "&hmac=" + hmac;
// 重定向到第三方支付平台
response.sendRedirect(url);
}
// 提交订单
public void submitOrder(HttpServletRequest request, HttpServletResponse response) throws IOException {
// 获取session域
HttpSession session = request.getSession();
// 获取user,判断用户是已经登陆
User user = (User) session.getAttribute("user");
if (user == null) {
// 用户没有登陆跳转到登陆页面
response.sendRedirect(request.getContextPath() + "/login.jsp");
return;
}
// 封装Order对象
// 创建Order对象,封装order对象所维护的属性
Order order = new Order();
// 1.private String oid;//订单自己所属id
order.setOid(UUID.randomUUID().toString());
// 2.private Date ordertime;//订单创建时间
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String datestr = format.format(new Date());
order.setOrdertime(datestr);
// 3.private double total;//订单的总计
// 获取购车对象cart,cart内有order所需要的很多属性
Cart cart = (Cart) session.getAttribute("cart");
double total = cart.getTotal();
order.setTotal(total);
// 4.private int state;//付款状态,---1代表已经付款,0代表还没有付款
order.setState(0);
// 5.private String address;//订单地址
order.setAddress(null);
// 6.private String name;//收获人姓名
order.setName(null);
// 7.private String telephone;//电话
order.setTelephone(null);
// 8.private User user;//创建订单的用户
order.setUser(user);
// 9.封装订单项集合 private List<OrderItem> orderItems = new ArrayList<OrderItem>();
// 订单项集合的每个订单项起始就是购物车中的每个购物项
// 获取购物车项集合
Map<String, CartItem> cartItems = cart.getCartItems();
// 遍历购车项集合
for (Map.Entry<String, CartItem> entry : cartItems.entrySet()) {
// 获取购物车项
CartItem cartItem = entry.getValue();
// 封装每一个订单项
OrderItem orderItem = new OrderItem();
// 封装购买数量
orderItem.setCount(cartItem.getBuyNum());
// 封装订单项id
orderItem.setItemid(UUID.randomUUID().toString());
// 封装订单项商品
orderItem.setProduct(cartItem.getProduct());
// 封装订单项小计
orderItem.setSubtotal(cartItem.getSubtotal());
// 封装订单项所属的订单
orderItem.setOrder(order);
// 存储每一个订单项
order.getOrderItems().add(orderItem);
}
// 到此为止order对象总算封装完成
// 把order传到service层
ProductService service = new ProductService();
service.submitOrder(order);
// 把order存到session
session.setAttribute("order", order);
// 重定向到订单页面
response.sendRedirect(request.getContextPath() + "/order_info.jsp");
}
// 清空购物车
public void clearCart(HttpServletRequest request, HttpServletResponse response) throws IOException {
HttpSession session = request.getSession();
session.removeAttribute("cart");
// 跳转回cart.jsp
response.sendRedirect(request.getContextPath() + "/cart.jsp");
}
// 在购物车删除购物项
public void delProFromCart(HttpServletRequest request, HttpServletResponse response) throws IOException {
// 获取pid
String pid = request.getParameter("pid");
// 获取购物车项
HttpSession session = request.getSession();
Cart cart = (Cart) session.getAttribute("cart");
if (cart != null) {
Map<String, CartItem> cartItems = cart.getCartItems();
// 修改cart的总计
double total = cart.getTotal() - cartItems.get(pid).getSubtotal();
cart.setTotal(total);
// 删除购物项
cartItems.remove(pid);
}
// 跳转回cart.jsp
response.sendRedirect(request.getContextPath() + "/cart.jsp");
}
// 将商品添加到购物车
public void addProductToCart(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
HttpSession session = request.getSession();
ProductService service = new ProductService();
// 获得放在购物车商品的pid
String pid = request.getParameter("pid");
// 获得这次商品的购买数
int buyNum = Integer.parseInt(request.getParameter("buyNum"));
// 获得paoduct对象
Product product = service.findProductByPid(pid);
// 计算这次小计
double subtotal = product.getShop_price() * buyNum;
/*
* //封装cartItem CartItem carItem = new CartItem(); carItem.setBuyNum(buyNum);
* carItem.setProduct(product); carItem.setSubtotal(subtotal);
*/
// 获取购物车,判断session中是否已经有购物车 ,没有就创建
Cart cart = (Cart) session.getAttribute("cart");
if (cart == null) {
cart = new Cart();
}
// 将购物项放到车中---key是pid
// 先判断购物车中是否已将包含此购物项了 ----- 判断key是否已经存在
// 如果购物车中已经存在该商品----将现在买的数量与原有的数量进行相加操作、
Map<String, CartItem> cartItems = cart.getCartItems();
double newSubtotal = product.getShop_price() * buyNum;
if (cartItems.containsKey(pid)) {
// 购物车已经有该商品
// 获取之前的购物车项
CartItem oldCartItem = cartItems.get(pid);
// 之前和现在相加后的最后购买数量
buyNum = oldCartItem.getBuyNum() + buyNum;
// 之前和现在相加后的最后购买小计
newSubtotal = oldCartItem.getSubtotal() + subtotal;
}
// 封装最终的购物车项
CartItem carItem = new CartItem();
carItem.setBuyNum(buyNum);
carItem.setProduct(product);
carItem.setSubtotal(newSubtotal);
// 将购物项存到购物车中
cartItems.put(pid, carItem);
// 计算计算购物车总计
double total = cart.getTotal() + subtotal;
cart.setTotal(total);
// 车再次放回session
session.setAttribute("cart", cart);
// 直接跳转到购物车页面
response.sendRedirect(request.getContextPath() + "/cart.jsp");
}
// 1.获取商品分类列表
public void categoryList(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
ProductService service = new ProductService();
/*
* 有redis服务器的情况下// 先从缓存查看是否存在categoryList 如果没有从数据库查找,再存到redis缓存,如果有,直接调用 Jedis
* jedis = JedisPoolUtils.getJedis(); String categoryListJson =
* jedis.get("categoryListJson"); if (categoryListJson == null) {
* System.out.println("缓存没有数据,查找数据库"); // 获取商品分类 List<Category> categoryList =
* service.findAllCategory(); Gson gson = new Gson(); categoryListJson =
* gson.toJson(categoryList); jedis.set("categoryListJson", categoryListJson); }
*/
// 获取商品分类
List<Category> categoryList = service.findAllCategory();
Gson gson = new Gson();
String categoryListJson = gson.toJson(categoryList);
response.setContentType("text/html;charset=UTF-8");
response.getWriter().write(categoryListJson);
}
// 2.获取热门商品与最新商品集合
public void index(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
ProductService service = new ProductService();
// 获取热门商品
List<Product> hotProductList = service.findHotProduct();
// 获取最新商品
List<Product> newProductList = service.findNewProduct();
// 把集合传到域
request.setAttribute("hotProductList", hotProductList);
request.setAttribute("newProductList", newProductList);
// 转发
request.getRequestDispatcher("/index.jsp").forward(request, response);
}
// 3.商品详细信息页面
public void productInfo(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 获取pid
String pid = request.getParameter("pid");
String cid = request.getParameter("cid");
String currentPage = request.getParameter("currentPage");
ProductService service = new ProductService();
// 根据pid查询商品
Product product = service.findProductByPid(pid);
// 根据cid查询分类
Category category = service.findCategoryByPid(cid);
// 传到request域,转发
request.setAttribute("category", category);
request.setAttribute("product", product);
request.setAttribute("cid", cid);
request.setAttribute("currentPage", currentPage);
// 获得客户端携带的cookie 获得名字pids的cookie
// 转发前创建cookie,存储pid
String pids = pid;
Cookie[] cookies = request.getCookies();
if (cookies != null) {
for (int i = 0; i < cookies.length; i++) {
if ("pids".equals(cookies[i].getName())) {
pids = cookies[i].getValue();
String[] split = pids.split("-");
List<String> asList = Arrays.asList(split);
LinkedList<String> list = new LinkedList<String>(asList);
// 判断当前集合是否包含现在的pid
if (list.contains(pid)) {
// 包含当前商品的pid
list.remove(pid);
list.addFirst(pid);
} else {
// 不包含当前pid
list.addFirst(pid);
}
// 将集合转为字符串[3,1,2]转为3-1-2
StringBuffer sb = new StringBuffer();
for (int j = 0; j < list.size(); j++) {
sb.append(list.get(j));
sb.append("-");
}
// 去掉最后的-
pids = sb.substring(0, sb.length() - 1);
}
}
}
Cookie cookie_pids = new Cookie("pids", pids);
response.addCookie(cookie_pids);
request.getRequestDispatcher("/product_info.jsp").forward(request, response);
}
// 4.根据分类cid获取商品集合
public void productListByCid(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 获得cid
String cid = request.getParameter("cid");
// 获取当前页
String currentPageStr = request.getParameter("currentPage");
if (currentPageStr == null) {
currentPageStr = "1";
}
int currentPage = Integer.parseInt(currentPageStr);
int currentCount = 12;
// 根据cid找pageBean
ProductService service = new ProductService();
PageBean pageBean = service.getPageBeanByCid(cid, currentPage, currentCount);
// 定义一个手机历史商品的集合
ArrayList<Product> histroyProductList = new ArrayList<Product>();
// 获得客户端携带的名为pids的cookie
Cookie[] cookies = request.getCookies();
// 获取浏览过的商品
if (cookies != null) {
for (Cookie cookie : cookies) {
if ("pids".equals(cookie.getName())) {
String pids = cookie.getValue();
String[] split = pids.split("-");
for (int i = 0; i < split.length && i < 7; i++) {
Product product = service.findProductByPid(split[i]);
histroyProductList.add(product);
}
}
}
}
request.setAttribute("pageBean", pageBean);
request.setAttribute("cid", cid);
request.setAttribute("histroyProductList", histroyProductList);
request.getRequestDispatcher("product_list.jsp").forward(request, response);
}
}
ProductService
package com.itheima.service;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import com.itheima.dao.ProductDao;
import com.itheima.domain.Category;
import com.itheima.domain.Order;
import com.itheima.domain.PageBean;
import com.itheima.domain.Product;
import com.itheima.utils.DataSourceUtils;
public class ProductService {
// 获取热门商品
public List<Product> findHotProduct() {
ProductDao dao = new ProductDao();
List<Product> hotProductList = null;
try {
hotProductList = dao.findHotProduct();
} catch (SQLException e) {
e.printStackTrace();
}
return hotProductList;
}
// 获取最新商品
public List<Product> findNewProduct() {
ProductDao dao = new ProductDao();
List<Product> newProductList = null;
try {
newProductList = dao.findNewProduct();
} catch (SQLException e) {
e.printStackTrace();
}
return newProductList;
}
// 获得商品分类
public List<Category> findAllCategory() {
ProductDao dao = new ProductDao();
List<Category> categoryList = null;
try {
categoryList = dao.findAllCategory();
} catch (SQLException e) {
e.printStackTrace();
}
return categoryList;
}
// 根据cid获得商品列表,并封装pageBean
public PageBean getPageBeanByCid(String cid, int currentPage, int currentCount) {
ProductDao dao = new ProductDao();
PageBean<Product> pageBean = new PageBean<Product>();
// 当前页
pageBean.setCurrentPage(currentPage);
// 当前页显示条数
pageBean.setCurrentCount(currentCount);
// 总共条数
int totalCount = 0;
try {
totalCount = dao.findTotalCount(cid);
} catch (SQLException e) {
e.printStackTrace();
}
pageBean.setTotalCount(totalCount);
// 总共页数
int totalPage = (int) Math.ceil(1.0 * totalCount / currentCount);
pageBean.setTotalPage(totalPage);
// 商品list
int index = (currentPage - 1) * currentCount;
List<Product> list = null;
try {
list = dao.findProductByCid(cid, index, currentCount);
} catch (SQLException e) {
e.printStackTrace();
}
pageBean.setList(list);
return pageBean;
}
// 根据pid查询商品
public Product findProductByPid(String pid) {
ProductDao dao = new ProductDao();
Product product = null;
try {
product = dao.findProductByPid(pid);
} catch (SQLException e) {
e.printStackTrace();
}
return product;
}
// 根据cid查询分类
public Category findCategoryByPid(String cid) {
ProductDao dao = new ProductDao();
Category category = null;
try {
category = dao.findCategoryByPid(cid);
} catch (SQLException e) {
e.printStackTrace();
}
return category;
}
// 提交订单,把订单,订单项存到数据库
public void submitOrder(Order order) {
ProductDao dao = new ProductDao();
try {
// 1.开启事务
DataSourceUtils.startTransaction();
//2.存储order的方法
dao.addOrders(order);
//3.存储orderItem的方法
dao.addOrderItem(order);
} catch (SQLException e) {
try {
DataSourceUtils.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}finally {
try {
DataSourceUtils.commitAndRelease();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//更新收货人信息
public void updateOrderAdd(Order order) {
ProductDao dao = new ProductDao();
try {
dao.updateOrderAdd(order);
} catch (SQLException e) {
e.printStackTrace();
}
}
//付款成功,修改付款状态
public void updateState(String r6_Order) {
ProductDao dao =new ProductDao();
try {
dao.updateState(r6_Order);
} catch (SQLException e) {
e.printStackTrace();
}
}
//查询用户的所有订单
public List<Order> findAllOrder(String uid) {
ProductDao dao = new ProductDao();
List<Order> orders =null;
try {
orders = dao.findAllOrder(uid);
} catch (SQLException e) {
e.printStackTrace();
}
return orders;
}
//根据oid, 查询orderItems表,与有关联的product表
public List<Map<String, Object>> findOrderItemsByOid(String oid) {
ProductDao dao = new ProductDao();
List<Map<String, Object>> mapList = null;
try {
mapList = dao.findOrderItemsByOid(oid);
} catch (SQLException e) {
e.printStackTrace();
}
return mapList;
}
}
ProductDao
package com.itheima.dao;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import com.itheima.domain.Category;
import com.itheima.domain.Order;
import com.itheima.domain.OrderItem;
import com.itheima.domain.Product;
import com.itheima.utils.DataSourceUtils;
public class ProductDao {
//获取热门商品
public List<Product> findHotProduct() throws SQLException {
QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
String sql ="select * from product where is_hot=? limit ?,?";
return runner.query(sql, new BeanListHandler<Product>(Product.class), 1,0,9);
}
//获取最新商品
public List<Product> findNewProduct() throws SQLException {
QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
String sql ="select * from product order by pdate desc limit ?,?";
return runner.query(sql, new BeanListHandler<Product>(Product.class),0,9);
}
//获得商品分类
public List<Category> findAllCategory() throws SQLException {
QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
String sql ="select * from category";
return runner.query(sql, new BeanListHandler<Category>(Category.class));
}
//获取总共条数
public int findTotalCount(String cid) throws SQLException {
QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
String sql ="select count(*) from product where cid=?";
Long query = (Long) runner.query(sql, new ScalarHandler(), cid);
return query.intValue();
}
//根据cid获取商品
public List<Product> findProductByCid(String cid, int index, int currentCount) throws SQLException {
QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
String sql ="select * from product where cid=? limit ?,?";
List<Product> query = runner.query(sql, new BeanListHandler<Product>(Product.class), cid,index,currentCount);
return query;
}
//根据pid查询商品
public Product findProductByPid(String pid) throws SQLException {
QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
String sql ="select * from product where pid=?";
Product query = runner.query(sql, new BeanHandler<Product>(Product.class), pid);
return query;
}
//根据cid查询分类
public Category findCategoryByPid(String cid) throws SQLException {
QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
String sql ="select * from category where cid=?";
Category query = runner.query(sql, new BeanHandler<Category>(Category.class), cid);
return query;
}
//向orders表插入数据
public void addOrders(Order order) throws SQLException {
QueryRunner runner = new QueryRunner();
String sql = "insert into orders values(?,?,?,?,?,?,?,?)";
Connection conn = DataSourceUtils.getConnection();
runner.update(conn,sql, order.getOid(),order.getOrdertime(),order.getTotal(),order.getState(),
order.getAddress(),order.getName(),order.getTelephone(),order.getUser().getUid());
}
//添加订单项到数据库
public void addOrderItem(Order order) throws SQLException {
QueryRunner runner = new QueryRunner();
String sql = "insert into orderitem values(?,?,?,?,?)";
Connection connection = DataSourceUtils.getConnection();
//获取订单项集合,遍历,存到数据库中
List<OrderItem> orderItems = order.getOrderItems();
for(OrderItem orderItem:orderItems) {
runner.update(connection, sql, orderItem.getItemid(),orderItem.getCount(),orderItem.getSubtotal(),
orderItem.getProduct().getPid(),orderItem.getOrder().getOid());
}
}
//更新订单收货人信息
public void updateOrderAdd(Order order) throws SQLException {
QueryRunner runner =new QueryRunner(DataSourceUtils.getDataSource());
String sql = "update orders set address=?,name=?,telephone=? where oid=?";
runner.update(sql, order.getAddress(),order.getName(),order.getTelephone(),order.getOid());
}
//付款成功,修改付款状态
public void updateState(String r6_Order) throws SQLException {
QueryRunner runner =new QueryRunner(DataSourceUtils.getDataSource());
String sql = "update orders set state=? where oid=?";
runner.update(sql, 1,r6_Order);
}
//查询用户的所有订单
public List<Order> findAllOrder(String uid) throws SQLException {
QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
String sql ="select * from orders where uid=?";
List<Order> orders = runner.query(sql, new BeanListHandler<Order>(Order.class), uid);
return orders;
}
//根据oid, 查询orderItems表,与有关联的product表
public List<Map<String, Object>> findOrderItemsByOid(String oid) throws SQLException {
QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
String sql =
"select i.count,i.subtotal,p.pimage,p.pname,p.shop_price from orderitem i,product p where i.pid=p.pid and oid=? ";
List<Map<String, Object>> query = runner.query(sql, new MapListHandler(), oid);
return query;
}
}
order_list.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>会员登录</title>
<link rel="stylesheet" href="css/bootstrap.min.css" type="text/css" />
<script src="js/jquery-1.11.3.min.js" type="text/javascript"></script>
<script src="js/bootstrap.min.js" type="text/javascript"></script>
<!-- 引入自定义css文件 style.css -->
<link rel="stylesheet" href="css/style.css" type="text/css" />
<style>
body {
margin-top: 20px;
margin: 0 auto;
}
.carousel-inner .item img {
width: 100%;
height: 300px;
}
</style>
</head>
<body>
<!-- 引入header.jsp -->
<jsp:include page="/header.jsp"></jsp:include>
<div class="container">
<div class="row">
<div style="margin: 0 auto; margin-top: 10px; width: 950px;">
<strong>我的订单</strong>
<table class="table table-bordered">
<!-- 遍历orderList -->
<c:forEach items="${orderList }" var="order">
<tbody>
<tr class="success">
<th colspan="5">订单编号:${order.oid }
${order.state==0?"<a href='javascript:;'>未付款</a>":"已付款" }
${order.total }</th>
</tr>
<tr class="warning">
<th>图片</th>
<th>商品</th>
<th>价格</th>
<th>数量</th>
<th>小计</th>
</tr>
<!-- 遍历订单项 -->
<c:forEach items="${order.orderItems }" var="orderItem">
<tr class="active">
<td width="60" width="40%">
<input type="hidden" name="id" value="22">
<img src="${pageContext.request.contextPath}/${orderItem.product.pimage}" width="70" height="60">
</td>
<td width="30%"><a target="_blank">${orderItem.product.pname}</a></td>
<td width="20%">¥${orderItem.product.shop_price}</td>
<td width="10%">${orderItem.count }</td>
<td width="15%"><span class="subtotal">¥${orderItem.subtotal }</span></td>
</tr>
</c:forEach>
</tbody>
</c:forEach>
</table>
</div>
</div>
<div style="text-align: center;">
<ul class="pagination">
<li class="disabled"><a href="#" aria-label="Previous"><span
aria-hidden="true">«</span></a></li>
<li class="active"><a href="#">1</a></li>
<li><a href="#">2</a></li>
<li><a href="#">3</a></li>
<li><a href="#">4</a></li>
<li><a href="#">5</a></li>
<li><a href="#">6</a></li>
<li><a href="#">7</a></li>
<li><a href="#">8</a></li>
<li><a href="#">9</a></li>
<li><a href="#" aria-label="Next"> <span aria-hidden="true">»</span>
</a></li>
</ul>
</div>
</div>
<!-- 引入footer.jsp -->
<jsp:include page="/footer.jsp"></jsp:include>
</body>
</html>