一、订单查询
JavaBean实体类
import nju.software.utils.DateUtils;
import java.util.Date;
import java.util.List;
public class Orders {
private String id;
private String orderNum;
private Date orderTime;
private String orderTimeStr;
private int orderStatus;
private String orderStatusStr;
private int peopleCount;
private Product product;
private List<Traveller> travellers;
private Member member;
private Integer payType;
private String payTypeStr;
private String orderDesc;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getOrderNum() {
return orderNum;
}
public void setOrderNum(String orderNum) {
this.orderNum = orderNum;
}
public Date getOrderTime() {
return orderTime;
}
public void setOrderTime(Date orderTime) {
this.orderTime = orderTime;
}
public String getOrderTimeStr() {
if (orderTime != null){
orderTimeStr = DateUtils.data2String(orderTime,"yyyy-MM-dd HH:mm");
}
return orderTimeStr;
}
public void setOrderTimeStr(String orderTimeStr) {
this.orderTimeStr = orderTimeStr;
}
public int getOrderStatus() {
return orderStatus;
}
public void setOrderStatus(int orderStatus) {
this.orderStatus = orderStatus;
}
public String getOrderStatusStr() {
if(orderStatus == 0){
orderStatusStr = "未支付";
}else if (orderStatus == 1){
orderStatusStr = "已支付";
}
return orderStatusStr;
}
public void setOrderStatusStr(String orderStatusStr) {
this.orderStatusStr = orderStatusStr;
}
public int getPeopleCount() {
return peopleCount;
}
public void setPeopleCount(int peopleCount) {
this.peopleCount = peopleCount;
}
public Product getProduct() {
return product;
}
public void setProduct(Product product) {
this.product = product;
}
public List<Traveller> getTravellers() {
return travellers;
}
public void setTravellers(List<Traveller> travellers) {
this.travellers = travellers;
}
public Member getMember() {
return member;
}
public void setMember(Member member) {
this.member = member;
}
public Integer getPayType() {
return payType;
}
public void setPayType(Integer payType) {
this.payType = payType;
}
public String getPayTypeStr() {
if(payType == 0){
payTypeStr = "支付宝";
}else if (payType == 1){
payTypeStr = "微信";
}else if (payType == 2){
payTypeStr = "其他";
}
return payTypeStr;
}
public void setPayTypeStr(String payTypeStr) {
this.payTypeStr = payTypeStr;
}
public String getOrderDesc() {
return orderDesc;
}
public void setOrderDesc(String orderDesc) {
this.orderDesc = orderDesc;
}
}
注意这三个成员变量,都是orders所关联的信息:
private Product product;
private List<Traveller> travellers;
private Member member;
持久层接口(dao)
public interface IOrderDao {
/*
*查询订单信息的时候,相应的产品信息也要查出来
*/
@Select("select * from orders")
@Results({
@Result(id = true,property = "id",column = "id"),
@Result(property = "orderNum",column = "orderNum"),
@Result(property = "orderTime",column = "orderTime"),
@Result(property = "orderStatus",column = "orderStatus"),
@Result(property = "peopleCount",column = "peopleCount"),
@Result(property = "payType",column = "payType"),
@Result(property = "orderDesc",column = "orderDesc"),
@Result(property = "product",column = "productId",javaType = Product.class,
one = @One(select = "nju.software.dao.IProductDao.findById"))
})
public List<Orders> findAll() throws Exception;
业务层service层
IOrdersService
import nju.software.domain.Orders;
import java.util.List;
public interface IOrdersService {
List<Orders> findAll(int page,int size) throws Exception;
Orders findById(String ordersId) throws Exception;
}
OrdersServiceImpl
import com.github.pagehelper.PageHelper;
import nju.software.dao.IOrderDao;
import nju.software.domain.Orders;
import nju.software.service.IOrdersService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
@Service
@Transactional
public class OrdersServiceImpl implements IOrdersService {
@Autowired
private IOrderDao iOrderDao;
@Override
public List<Orders> findAll(int page,int size) throws Exception {
//参数pageNum 是页码值 参数pageSize 代表是每页显示条数
//必须和真正执行的代码之前挨着
PageHelper.startPage(page,size);
return iOrderDao.findAll();
}
@Override
public Orders findById(String ordersId) throws Exception {
return iOrderDao.findById(ordersId);
}
}
Controller层
package nju.software.controller;
import com.github.pagehelper.PageInfo;
import nju.software.domain.Orders;
import nju.software.service.IOrdersService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;
import java.util.List;
@Controller
@RequestMapping("/orders")
public class OrdersController {
@Autowired
private IOrdersService iOrdersService;
//查询全部订单--未分页的
// @RequestMapping("/findAll.do")
// public ModelAndView findAll() throws Exception{
// ModelAndView mv = new ModelAndView();
// List<Orders> ordersList = iOrdersService.findAll();
// mv.addObject("ordersList",ordersList);
// mv.setViewName("orders-list");
// return mv;
// }
@RequestMapping("/findAll.do")
public ModelAndView findAll(@RequestParam(name = "page",required = true,defaultValue = "1")int page,@RequestParam(name = "size",required = true,defaultValue = "4")int size) throws Exception{
ModelAndView mv = new ModelAndView();
List<Orders> ordersList = iOrdersService.findAll(page, size);
//PageInfo就是一个分页的Bean
PageInfo pageInfo = new PageInfo(ordersList);
mv.addObject("pageInfo",pageInfo);
mv.setViewName("orders-page-list");
return mv;
}
@RequestMapping("/findById")
public ModelAndView findById(@RequestParam(name = "id",required = true)String ordersId) throws Exception {
ModelAndView mv = new ModelAndView();
Orders orders = iOrdersService.findById(ordersId);
mv.addObject("orders",orders);
mv.setViewName("orders-show");
return mv;
}
}
订单查询比商品查询要麻烦,因为订单中包含了其他管理的信息。查看数据表之间的来联系可以发现有以下的关系。
下面讲讲实现分页功能:
PageHelper的使用:是Mybatis的一个插件
在pom文件中添加依赖
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.2</version>
</dependency>
并且在applicationContext.xml中添加如下代码
<property name="plugins">
<array>
<!-- 传入插件的对象 -->
<bean class="com.github.pagehelper.PageInterceptor">
<property name="properties">
<props>
<prop key="helperDialect">mysql</prop>
<prop key="reasonable">true</prop>
</props>
</property>
</bean>
</array>
</property>
helperDialect :分页插件会自动检测当前的数据库链接,自动选择合适的分页方式。 你可以配置
helperDialect 属性来指定分页插件使用哪种方言。配置时,可以使用下面的缩写值:
oracle , mysql , mariadb , sqlite , hsqldb , postgresql , db2 , sqlserver , informix , h2 , sqlserver201
2 , derby
reasonable :分页合理化参数,默认值为 false 。当该参数设置为 true 时, pageNum<=0 时会查询第一
页, pageNum>pages (超过总数时),会查询最后一页。默认 false 时,直接根据参数进行查询。
在在OrdersServiceImpl中添加如下代码:
@Override
public List<Orders> findAll(int page,int size) throws Exception {
//参数pageNum 是页码值 参数pageSize 代表是每页显示条数
//必须和真正执行的分页语句代码之前挨着
PageHelper.startPage(page,size);
return iOrderDao.findAll();
}
于此同时在前端页面也要传递分页的信息:
<li id="system-setting">
<a href="${pageContext.request.contextPath}/orders/findAll.do?page=1&size=4">
<i class="fa fa-circle-o"></i> 订单管理
</a>
</li>
在controller中:
@RequestMapping("/findAll.do")
public ModelAndView findAll(@RequestParam(name = "page",required = true,defaultValue = "1")int page,@RequestParam(name = "size",required = true,defaultValue = "4")int size) throws Exception{
ModelAndView mv = new ModelAndView();
List<Orders> ordersList = iOrdersService.findAll(page, size);
//PageInfo就是一个分页的Bean
PageInfo pageInfo = new PageInfo(ordersList);
mv.addObject("pageInfo",pageInfo);
mv.setViewName("orders-page-list");
return mv;
}
其中PageInfo是一个JavaBean,用的构造函数。
前端的页码要写成pageInfo.list
<c:forEach items="${pageInfo.list}" var=“orders”>
因为pageInfo是一个bean,list属性才会把该页的数据传过来。
下面说前端页面的处理
<div class="box-footer">
<div class="pull-left">
<div class="form-group form-inline" >
总共2 页,共14 条数据。 每页
<select class="form-control" id="changePageSize" onchange="changePageSize()">
<option>1</option>
<option>2</option>
<option>3</option>
<option>4</option>
<option>5</option>
</select> 条
</div>
</div>
<div class="box-tools pull-right">
<ul class="pagination">
<li>
<a href="${pageContext.request.contextPath}/orders/findAll.do?page=1&size=${pageInfo.pageSize}" aria-label="Previous">首页</a>
</li>
<li><a href="${pageContext.request.contextPath}/orders/findAll.do?page=${pageInfo.pageNum-1}&size=${pageInfo.pageSize}">上一页</a></li>
<c:forEach begin="1" end="${pageInfo.pages}" var="pageNum">
<li><a href="${pageContext.request.contextPath}/orders/findAll.do?page=${pageNum}&size=${pageInfo.pageSize}">${pageNum}</a></li>
</c:forEach>
<li><a href="${pageContext.request.contextPath}/orders/findAll.do?page=${pageInfo.pageNum+1}&size=${pageInfo.pageSize}">下一页</a></li>
<li>
<a href="${pageContext.request.contextPath}/orders/findAll.do?page=${pageInfo.pages}&size=${pageInfo.pageSize}" aria-label="Next">尾页</a>
</li>
</ul>
</div>
</div>
//添加函数
<script>
function changePageSize() {
//获取下拉框的值
var pageSize = $("#changePageSize").val();
//向服务器发送请求,改变没页显示条数
location.href = "${pageContext.request.contextPath}/orders/findAll.do?page=1&size="
+ pageSize;
}
</script>
page=1&size=${pageInfo.pageSize}:为后续的每页显示条数来传值
page=${pageInfo.pages}:总页数
page=${pageInfo.pageNum-1}:当前页数的上一页
// pageNum保存了当前页码数,并重新发出跳转请求
<c:forEach begin="1" end="${pageInfo.pages}" var="pageNum">
<li><a href="${pageContext.request.contextPath}/orders/findAll.do?page=${pageNum}&size=${pageInfo.pageSize}">${pageNum}</a></li>
</c:forEach>
二、 订单详情
<button type="button" class="btn bg-olive btn-xs" onclick="location.href='${pageContext.request.contextPath}/orders/findById.do?id=${orders.id}'">详情</button>
把当前订单的id存在参数中,作为findById的参数。
在dao中编写代码
@Select("select * from orders where id = #{ordersId}")
@Results({
@Result(id = true,property = "id",column = "id"),
@Result(property = "orderNum",column = "orderNum"),
@Result(property = "orderTime",column = "orderTime"),
@Result(property = "orderStatus",column = "orderStatus"),
@Result(property = "peopleCount",column = "peopleCount"),
@Result(property = "payType",column = "payType"),
@Result(property = "orderDesc",column = "orderDesc"),
@Result(property = "product",column = "productId",javaType = Product.class,
one = @One(select = "nju.software.dao.IProductDao.findById")),
@Result(property = "member",column = "memberId",javaType = Member.class,
one = @One(select = "nju.software.dao.IMemberDao.findById")),
@Result(property = "travellers",column = "id",javaType =java.util.List.class,
many = @Many(select = "nju.software.dao.ITravellerDao.findByOrdersId"))
})
Orders findById(String ordersId) throws Exception;
}
此时设计到多表操作
对应的是
@Result(property = "product",column = "productId",javaType = Product.class,
one = @One(select = "nju.software.dao.IProductDao.findById")),
@Result(property = "member",column = "memberId",javaType = Member.class,
one = @One(select = "nju.software.dao.IMemberDao.findById")),
在相应的dao中
IMemberDao
import nju.software.domain.Member;
import org.apache.ibatis.annotations.Select;
public interface IMemberDao {
@Select("select * from member where id = #{id}")
public Member findById(String id) throws Exception;
}
IProductDao
import nju.software.domain.Product;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface IProductDao {
//省略之前写的方法
@Select("select * from product where id = #{id}")
public Product findById(String id) throws Exception;
}
但是orders和travellers是多对多所以需要中间表引入
@Result(property = "travellers",column = "id",javaType =java.util.List.class,
many = @Many(select = "nju.software.dao.ITravellerDao.findByOrdersId"))
在ITravellerDao中
import nju.software.domain.Traveller;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface ITravellerDao {
@Select("select * from traveller where id in (select travellerId from order_traveller where orderId = #{ordersId})")
public List<Traveller> findByOrdersId(String ordersId)throws Exception;
}
Controller中
@RequestMapping("/findById")
public ModelAndView findById(@RequestParam(name = "id",required = true)String ordersId) throws Exception {
ModelAndView mv = new ModelAndView();
Orders orders = iOrdersService.findById(ordersId);
mv.addObject("orders",orders);
mv.setViewName("orders-show");
return mv;
}
orders里面包含了product、menber、traveller的信息