案例1-查询我的订单(将所属的订单项也要查询出来) 需求: 点击 页面上的 "我的订单",分页展示我所有的订单(将所属的订单项也要查询出来) 技术分析: 多表的查询: 内连接 格式1: select * from a <inner> join b on 连接条件; 格式2: select * from a,b where 连接条件; 外连接(全部显示一张表的数据，另一张表的数据没有的为null) 左外连接 select * from a left <outer> join b on 连接条件; 子查询 步骤分析: 1.修改head.jsp的连接 我的订单，通过分页展示 /store/order?method=findAllByPage&currPage=? 2.在orderservlet中编写findAllByPage方法 获取用户(session)，要判断有没有用户 获取当前页 固定pagesize 调用orderservice根据用户查询所有订单 返回值:Pagebean 将pagebean放入request域中,请求转发到/jsp/order_list.jsp 3.在orderService中findAllByPage方法的操作:返回一个pagebean 封装一个pagebean new pagebean(currPage,pageSize,list,totalCount) //前台传来，固定，查询，查询 调用dao查询list和totalcount 将user.uid传递过去 4.在orderDao中查询所有的订单 select * from orders where uid=? limit m,n 可以查询出所有的订单 结果可以List<Order> list 使用beanListHandler 遍历所有的订单,根据订单id 查询订单项表和商品表 select * from orderitem oi,product p where oi.pid = p.pid and oi.oid = ? 用mapListhandler封装结果集,然后使用BeanUtils封装成指定的bean对象 ,添加到order的items中即可
/store/WebContent/jsp/head.jsp
<a href="${pageContext.request.contextPath }/order?method=findAllByPage&currPage=1">我的订单</a></li>
com.louis.web.servlet.OrderServlet
public String findAllByPage(HttpServletRequest request, HttpServletResponse response) throws Exception { //1、获取当前页 int currPage = Integer.parseInt(request.getParameter("currPage")); int pageSize = 3; //2、获取用户 User user = (User) request.getSession().getAttribute("user"); if (user == null) { request.setAttribute("msg", "您还没有登陆，请登录"); return "/jsp/msg.jsp"; } //3、调用service分页查询 参数：currpage pageSize user 返回值：PageBean OrderService orderService = (OrderService) BeanFactory.getBean("OrderService"); PageBean<Order> bean = orderService.findAllByPage(currPage,pageSize,user); //4、将pageBean放入到request域中 request.setAttribute("pb", bean); return "/jsp/order_list.jsp"; }
com.louis.service.impl.OrderServiceImpl
/** * 分页查询订单 */ @Override public PageBean<Order> findAllByPage(int currPage, int pageSize, User user) throws Exception { OrderDao od=(OrderDao) BeanFactory.getBean("OrderDao"); // 查询当前页数据 List<Order> list=od.findAllByPage(currPage,pageSize,user.getUid()); // 查询总条数 int totalCount=od.getTotalCount(user.getUid()); return new PageBean<>(list, currPage, pageSize, totalCount); }
com.louis.dao.impl.OrderDaoImpl
/** * 查询我的订单 分页 */ @Override public List<Order> findAllByPage(int currPage, int pageSize, String uid) throws Exception { QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource()); String sql="select * from orders where uid = ? order by ordertime desc limit ? , ?"; List<Order> list = qr.query(sql, new BeanListHandler<>(Order.class), uid,(currPage-1)*pageSize,pageSize); //遍历订单集合 封装每个订单的订单项列表 sql="select * from orderitem oi,product p where oi.pid=p.pid and oi.oid = ?"; for (Order order : list) { //当前订单包含的所有内容 List<Map<String, Object>> mList = qr.query(sql, new MapListHandler(), order.getOid()); //map的key:字段名 value:字段值 for (Map<String, Object> map : mList) { //封装product Product p=new Product(); BeanUtils.populate(p, map); //封装orderItem OrderItem oi = new OrderItem(); BeanUtils.populate(oi, map); oi.setProduct(p); //将orderItem对象添加到对应的order对象的list集合中 order.getItems().add(oi); } } return list; } /** * 获取我的订单的总条数 */ @Override public int getTotalCount(String uid) throws Exception { QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource()); String sql="select count(*) from orders where uid = ?"; return ((Long)qr.query(sql, new ScalarHandler(), uid)).intValue(); }
/store/WebContent/jsp/order_list.jsp
问
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <!doctype html> <html> <head> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1"> <title>会员登录</title> <link rel="stylesheet" href="${pageContext.request.contextPath}/css/bootstrap.min.css" type="text/css" /> <script src="${pageContext.request.contextPath}/js/jquery-1.11.3.min.js" type="text/javascript"></script> <script src="${pageContext.request.contextPath}/js/bootstrap.min.js" type="text/javascript"></script> <!-- 引入自定义css文件 style.css --> <link rel="stylesheet" href="${pageContext.request.contextPath}/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> <!-- 动态包含 --> <jsp:include page="/jsp/head.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"> <c:forEach items="${pb.list }" var="o"> <tbody> <tr class="success"> <th colspan="5">订单编号:${o.oid } 订单金额:${o.total } <c:if test="${o.state==0 }"> <a href="${pageContext.request.contextPath }/order?method=getById&oid=${o.oid}">付款</a> </c:if> <c:if test="${o.state==1 }"> 已付款 </c:if> <c:if test="${o.state==2 }"> <a href="#">确认收货</a> </c:if> <c:if test="${o.state==3 }"> 已完成 </c:if> </th> </tr> <tr class="warning"> <th>图片</th> <th>商品</th> <th>价格</th> <th>数量</th> <th>小计</th> </tr> <c:forEach items="${o.items }" var="oi"> <tr class="active"> <td width="60" width="40%"> <input type="hidden" name="id" value="22"> <img src="${pageContext.request.contextPath}/${oi.product.pimage}" width="70" height="60"> </td> <td width="30%"> <a target="_blank">${oi.product.pname }</a> </td> <td width="20%"> ￥${oi.product.shop_price } </td> <td width="10%"> ${oi.count } </td> <td width="15%"> <span class="subtotal">￥${oi.subtotal }</span> </td> </tr> </c:forEach> </tbody> </c:forEach> </table> </div> </div> <div style="text-align: center;"> <ul class="pagination"> <c:if test="${1==pb.currPage }"> <li class="disabled"><a href="javascript:void(0)" aria-label="Previous"><span aria-hidden="true">«</span></a></li> </c:if> <c:if test="${1!=pb.currPage }"> <li><a href="${pageContext.request.contextPath }/order?method=findAllByPage&currPage=${pb.currPage-1}" aria-label="Previous"><span aria-hidden="true">«</span></a></li> </c:if> <c:forEach begin="1" end="${pb.totalPage }" var="n"> <c:if test="${n==pb.currPage }"> <li class="active"><a href="javascript:void(0)">${n }</a></li> </c:if> <c:if test="${n!=pb.currPage }"> <li><a href="${pageContext.request.contextPath }/order?method=findAllByPage&currPage=${n}">${n }</a></li> </c:if> </c:forEach> <c:if test="${pb.currPage== pb.totalPage }"> <li class="disabled"> <a href="javascript:void(0)" aria-label="Next"> <span aria-hidden="true">»</span> </a> </li> </c:if> <c:if test="${pb.currPage!= pb.totalPage }"> <li> <a href="${pageContext.request.contextPath }/order?method=findAllByPage&currPage=${pb.currPage+1}" aria-label="Next"> <span aria-hidden="true">»</span> </a> </li> </c:if> </ul> </div> </div> <div style="margin-top:50px;"> <img src="${pageContext.request.contextPath}/image/footer.jpg" width="100%" height="78" alt="我们的优势" title="我们的优势" /> </div> <div style="text-align: center;margin-top: 5px;"> <ul class="list-inline"> <li><a>关于我们</a></li> <li><a>联系我们</a></li> <li><a>招贤纳士</a></li> <li><a>法律声明</a></li> <li><a>友情链接</a></li> <li><a target="_blank">支付方式</a></li> <li><a target="_blank">配送方式</a></li> <li><a>服务声明</a></li> <li><a>广告声明</a></li> </ul> </div> <div style="text-align: center;margin-top: 5px;margin-bottom:20px;"> Copyright © 2005-2016 传智商城 版权所有 </div> </body> </html>
问题
1、内连接、外连接
2、beanHandler
BeanListHandler
MapListHandler
scalarHandler
3、BeanUtils.populate