Shop项目--12. 显示用户订单列表order_list.jsp

分析:

显示用户订单列表在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>








  • 3
    点赞
  • 15
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
请帮我看看这个sql语句是否正确,并改正:select a.ebeln, a.ebelp, a.art, a.kdauf, a.zbw, a.size1, a.txz01, a.menge, b.quantity, (a.menge - b.quantity) as OWE_MATERIAL, ( select sum(THIS_PLAN_QTY) as HAVE_PLAN , a.ebeln , a.ebelp , a.kdauf from SCM_OUTSOURCE_PRODUCTION_PLAN_LIST c , SCM_PROCESS_PURCHASE_ORDER_D a group by c.order_no , c.order_seq , c.sales_order , a.ebeln , a.ebelp , a.kdauf having c.order_no = a.ebeln And c.order_seq = a.ebelp And c.sales_order = a.kdauf ) , ( select a.menge - t.HAVE_PLAN as OWN_PLAY from (select sum(THIS_PLAN_QTY) as HAVE_PLAN , c.order_no , c.order_seq , c.sales_order from SCM_OUTSOURCE_PRODUCTION_PLAN_LIST c , SCM_PROCESS_PURCHASE_ORDER_D a group by c.order_no , c.order_seq , c.sales_order , a.ebeln , a.ebelp , a.kdauf having c.order_no = a.ebeln And c.order_seq = a.ebelp And c.sales_order = a.kdauf ) t , SCM_PROCESS_PURCHASE_ORDER_D a where a.ebeln = t.order_no and a.ebelp = t.order_seq and a.menge = t.sales_order ) , d.scan_qty, (d.quantity - d.scan_qty) as OWN_PRODUCE, e.scan_qty, (e.quantity - e.scan_qty) as OWE_SHIP from SCM_PROCESS_PURCHASE_ORDER_D a left join MMS_INOUT_BILL_M b On a.ebeln = b.purchase_order_number And a.ebelp = b.purchase_order_seq And a.kdauf = b.sales_order left join SCM_OUTSOURCE_PRODUCTION_PLAN_LIST c on a.ebeln = c.order_no And a.ebelp = c.order_seq And a.kdauf = c.sales_order left join SCM_PROCESS_OUTPUT d on a.ebeln = d.order_no And a.ebelp = d.order_seq And a.kdauf = d.sales_order left join SCM_OUTSOURCE_SHIP e on a.ebeln = e.qr_code and a.ebelp = e.order_no and a.kdauf = e.order_seq where a.art = 'IE7181' Order by a.ebeln desc, a.ebelp;
05-24
这个 SQL 语句比较复杂,但是有一些问题需要修改: - 在子查询中,需要将 select 子句中的 a.ebeln, a.ebelp, a.kdauf 与 group by 子句中的字段保持一致,否则会出现错误。 - 子查询中的第一个 SELECT 子句需要加上 WHERE 子句,限制查询条件。 - 子查询中的第二个 SELECT 子句中的 a.menge 应该改为 t.sales_order。 - 在最后的 SELECT 子句中,d.scan_qty 与 e.scan_qty 都没有明确指定表名,可能会出现错误。 以下是修改后的 SQL 语句: ``` SELECT a.ebeln, a.ebelp, a.art, a.kdauf, a.zbw, a.size1, a.txz01, a.menge, b.quantity, (a.menge - b.quantity) AS OWE_MATERIAL, ( SELECT SUM(c.THIS_PLAN_QTY) AS HAVE_PLAN, a.ebeln, a.ebelp, a.kdauf FROM SCM_OUTSOURCE_PRODUCTION_PLAN_LIST c, SCM_PROCESS_PURCHASE_ORDER_D a WHERE c.order_no = a.ebeln AND c.order_seq = a.ebelp AND c.sales_order = a.kdauf AND a.art = 'IE7181' GROUP BY c.order_no, c.order_seq, c.sales_order, a.ebeln, a.ebelp, a.kdauf ), ( SELECT a.menge - t.sales_order AS OWN_PLAY FROM ( SELECT SUM(c.THIS_PLAN_QTY) AS HAVE_PLAN, c.order_no, c.order_seq, c.sales_order FROM SCM_OUTSOURCE_PRODUCTION_PLAN_LIST c, SCM_PROCESS_PURCHASE_ORDER_D a WHERE c.order_no = a.ebeln AND c.order_seq = a.ebelp AND c.sales_order = a.kdauf AND a.art = 'IE7181' GROUP BY c.order_no, c.order_seq, c.sales_order, a.ebeln, a.ebelp, a.kdauf ) t, SCM_PROCESS_PURCHASE_ORDER_D a WHERE a.ebeln = t.order_no AND a.ebelp = t.order_seq AND a.kdauf = t.sales_order AND a.art = 'IE7181' ), d.scan_qty, (d.quantity - d.scan_qty) AS OWN_PRODUCE, e.scan_qty, (e.quantity - e.scan_qty) AS OWE_SHIP FROM SCM_PROCESS_PURCHASE_ORDER_D a LEFT JOIN MMS_INOUT_BILL_M b ON a.ebeln = b.purchase_order_number AND a.ebelp = b.purchase_order_seq AND a.kdauf = b.sales_order LEFT JOIN SCM_OUTSOURCE_PRODUCTION_PLAN_LIST c ON a.ebeln = c.order_no AND a.ebelp = c.order_seq AND a.kdauf = c.sales_order LEFT JOIN SCM_PROCESS_OUTPUT d ON a.ebeln = d.order_no AND a.ebelp = d.order_seq AND a.kdauf = d.sales_order LEFT JOIN SCM_OUTSOURCE_SHIP e ON a.ebeln = e.qr_code AND a.ebelp = e.order_no AND a.kdauf = e.order_seq WHERE a.art = 'IE7181' ORDER BY a.ebeln DESC, a.ebelp; ```

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值