需求:
点击 页面上的 "我的订单",分页展示我所有的订单(将所属的订单项也要查询出来)
技术分析:
多表的查询:
内连接
格式1:
select * from a join b on 连接条件;
格式2:
select * from a,b where 连接条件;
外连接
左外连接
select * from a left 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中即可
---------------------------------------------------------------------------------------------------------------------------
前台
href="${pageContext.request.contextPath }/order?method=findAllByPage&currPage=1">我的订单</a></li>
---------------------------------------------------------------------------------------------------------------------------
OrderServlet
public String findAllByPage(HttpServletRequest request, HttpServletResponse response) throws Exception {
//获取当前页
int currPage=Integer.parseInt(request.getParameter("currPage")) ;
int pageSize=3;
//获取用户
User user = (User) request.getSession().getAttribute("user");
//判断用户是否登录
if(user==null) {
//提示没有登录
request.setAttribute("msg", "请先登录");
return "/jsp/msg.jsp";
}
//调用Orderservice 分页查询 参数：currPage pagesize user 返回值：PageBean
OrderService os=(OrderService) BeanFactory.getBean("OrderService");
PageBean<Order> bean=os.findAllByPage(currPage,pageSize,user);
//将PageBean放入request域中
request.setAttribute("pb", bean);
return "/jsp/order_list.jsp";
}
---------------------------------------------------------------------------------------------------------------------------
package com.feizhu.service;
import com.feizhu.domain.Order;
import com.feizhu.domain.PageBean;
import com.feizhu.domain.User;
public interface OrderService {
void add(Order order) throws Exception;
PageBean<Order> findAllByPage(int currPage, int pageSize, User user)throws Exception;
Order getById(String oid) throws Exception;
void updateOrder(Order order)throws Exception;
}
---------------------------------------------------------------------------------------------------------------------------
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);
}
---------------------------------------------------------------------------------------------------------------------------
package com.feizhu.dao;
import java.util.List;
import com.feizhu.domain.Order;
import com.feizhu.domain.OrderItem;
public interface OrderDao {
void add(Order order) throws Exception;
void addItem(OrderItem oi)throws Exception;
List<Order> findAllByPage(int currPage, int pageSize, String uid)throws Exception;
int getTotalCount(String uid)throws Exception;
Order getById(String oid) throws Exception;
void update(Order order)throws Exception;
}
---------------------------------------------------------------------------------------------------------------------------
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());
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();
}
---------------------------------------------------------------------------------------------------------------------------
<!-- 动态包含 -->
<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">订单编号:${bean.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 }">
<a>已付款</a>
</c:if> <c:if test="${o.state==2 }">
<a>确认收获</a>
</c:if> <c:if test="${o.state==3 }">
<a>已完成</a>
</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>
---------------------------------------------------------------------------------------------------------------------------
备注：由于个人原因，本博客暂停更新。如有问题可联系本人，本人提供技术指导、学习方向、学习路线。本人微信wlp1156107728(添加注明来意) QQ1156107728(添加注明来意)
1798
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
