SSM综合案例 订单案例查询功能实现
一、课程目标
1. 【掌握】订单管理查询
2. 查询所有订单信息
3. 理解关系--mybatis；一对多 一对一
4. 理解 订单详情二、订单管理
2.1 查询所有订单
2.1 订单表关系结构图
2.2 创建相应的实体类
//会员类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Member {
private int id;
private String name;
private String nickName;
private String phoneNum;
private String email;
}
//旅客类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Traveller {
private int id;
private String name;
private String sex;
private String phoneNum;
private int credentialsType;//'证件类型 0身份证 1护照 2军官证',
private String credentialsNum;// '证件号码',
private int travellerType;//'旅客类型(人群) 0 成人 1 儿童',
}
//订单类
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Order{
private int id;
private String orderNum;
@DateTimeFormat(pattern="yyyy-MM-dd HH:mm")
private Date orderTime;
private int peopleCount;
private String orderDesc;
private int payType;//'支付方式（0 支付宝 1微信 2 其他'
private int orderStatus;// '订单状态（0未支付1已支付）',
private int productId;// '产品id外键',
private int memberId;// '会员（联系人）id外键',
private String orderTimeStr; //在需要修改的成员变量后加Str
private Product product;
private List<Traveller> travellers;
//1.获取日期格式化信息;
public String getOrderTimeStr() {
// 对日期格式化
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");
if (null != orderTime) {
orderTimeStr = dateFormat.format(orderTime);
}
return orderTimeStr;
}
public String getPayTypeStr() {
String pageTypeStr; //将这个变量返回一下
switch (payType){
case 0:pageTypeStr="支付宝";break;
case 1:pageTypeStr="微信";break;
default:pageTypeStr="其他";break;
}
return pageTypeStr;
}
public String getOrderStatusStr() {
return orderStatus==0? "未支付":"已支付";
}
}编写OrderMapper
public interface OrderMapper {
List<Order> findAll();编写OrderService
public interface OrderService {
List<Order> findAll();
}实现类
@Service
public class OrderServiceImpl implements OrderService {
@Autowired
private OrderMapper orderMapper;
@Override
public List<Order> findAll() {
return orderMapper.findAll();
}
}编写OrderController
@Controller
@RequestMapping("/order")
public class OrderController {
@Autowired
private OrderService orderService;
/**
* 分页查询所有订单
* @param page
* @param limit
* @return
*/
@RequestMapping("/findAll")
public ModelAndView findAll(@RequestParam(name = "page",defaultValue = "1")int pageNum,
@RequestParam(name = "limit",defaultValue = "5")int pageSize){
//利用分页，赋值
Page page= PageHelper.startPage(pageNum,pageSize);
ModelAndView mv=new ModelAndView();
// 查询数据
List<Order> orderList = orderService.findAll();
PageInfo pageInfo=new PageInfo(orderList);
//为mv设置对象
mv.addObject("pageInfo",pageInfo);
mv.setViewName("order-list");
return mv;
}
}编写OrderMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.yh.mapper.OrderMapper">
<!--查询订单，查询订单对应的产品-->
<resultMap id="findAll_product" type="com.yh.pojo.Orders" autoMapping="true">
<association property="product" column="productId" select="com.yh.mapper.ProductMapper.selectById">
</association>
</resultMap>
<select id="selectAll" resultMap="findAll_product">
select * from orders
</select>
</mapper>测试
总结：这里建议使用findAll,注意和之前的Product的定义保持一致，再次来理解restful风格。 好处：前后端分离开发，风格统一； /项目名/product/findAll /项目名/order/findAll /项目名/user/findAll
2.2 订单详情
查询订单详情流程
点击订单详情，查询当前订单关联信息在页面显示
2.1 5表关联查询
select *,o.id oid,p.id pid,t.id tid,t.`name` tname,t.phoneNum tnum
from orders o
left join product p on o.productId=p.id
left join member m on o.memberId=m.id
left join order_traveller ot on o.id=ot.orderId -- 这些继续和游客表建立关联;
left join traveller t on ot.travellerId=t.id
where o.id=12.2 Member实体类
//会员类 代码同上Traveller实体类
//旅客类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Traveller {
private int id;
private String name;
private String sex;
private String phoneNum;
private int credentialsType;//'证件类型 0身份证 1护照 2军官证',
private String credentialsNum;// '证件号码',
private int travellerType;//'旅客类型(人群) 0 成人 1 儿童',
private String credentialsTypeStr;
private String travellerTypeStr;
public String getCredentialsTypeStr() {
String typeStr="";
switch (credentialsType){
case 0:typeStr="身份证";break;
case 1:typeStr="护照";break;
case 2:typeStr="军官证";break;
}
return typeStr;
}
public String getTravellerTypeStr() {
return travellerType==0?"成人":"儿童";
}
}编写OrderMapper
public interface OrderMapper {
//根据id查询订单详细信息
public Order findById(int id);
}编写Orderervice
//2.根据id来查询订单;
public Order findById(Integer id);实现类
public Order findById(Integer id) {
return orderMapper.findById(id);
}编写OrderController
@RequestMapping("/findById")
public String findById(HttpServletRequest request,int id){
Order order = ordersService.findById(id);
request.setAttribute("order",orders);
return "order-show";
}编写OrderMapper.xml
方式一:
<!--*****************************resultMap:findByIdMap 定义********************-->
<resultMap id="findByIdMap" type="Order" autoMapping="true">
<!--订单表的id 主键-->
<id property="id" column="oid"/>
<!-- 映射到产品表 -->
<association property="product" autoMapping="true">
<id property="id" column="pid"/>
</association>
<!-- 映射到会员表 -->
<association property="member" autoMapping="true">
<id property="id" column="mid"/>
</association>
<!-- 映射到游客表,订单表和游客表的关系是啥？？? M:N 多对多的关系 -->
<collection property="travellers" ofType="Traveller" autoMapping="true">
<id property="id" column="tid"/>
<result property="name" column="tname"/>
<result property="phoneNum" column="tnum"/>
</collection>
</resultMap>
<!-- 3.findById这个方法的编写;由于是复杂数据类型，所以，返回类型是resultMap,不是resultType -->
<select id="findById" resultMap="findByIdMap">
select *,o.id oid,p.id pid,m.id mid,t.id tid,t.`name` tname,t.phoneNum tnum
from orders o
left join product p on o.productId=p.id
left join member m on o.memberId=m.id
left join order_traveller ot on o.id=ot.orderId -- 这些继续和游客表建立关联;
left join traveller t on ot.travellerId=t.id
where o.id=#{id}
</select>方式二：
<resultMap id="o_p_m_t" type="com.yh.pojo.Orders" autoMapping="true">
<association property="product" column="productId" select="com.yh.mapper.ProductMapper.selectById"> </association>
<association property="member" column="memberId" select="com.yh.mapper.MemberMapper.selectById"></association>
<collection property="travellers" column="id" select="com.yh.mapper.TravellerMapper.selectByOid"></collection>
</resultMap>
<select id="selectById" resultMap="o_p_m_t">
select * from orders where id=#{id}
</select>//根据产品id查询产品信息
@Select("select * from product where id =#{id}")
public Product selectById(int id);
//根据会员id查询会员信息
@Select("select * from member where id=#{id}")
public List<Member> selectById(int id);
//根据订单id查询旅客信息
@Select("select t.* from traveller t,order_traveller ot where t.id=ot.travellerId and ot.orderId=#{oid}")
public List<Traveller> selectByOid(int oid);测试
2.3 多条件模糊查询
就是在查询所有数据的基础上添加查询条件
编写OrderMapper
public interface OrderMapper {
//根据查询所有数据根据orderNum查询 productName模糊查询
public List<Order> selectAll(@Param("orderNum") String orderNum, @Param("productName") String productName);
}编写OrderService
public interface OrderService {
//根据查询所有数据根据orderNum查询 productName模糊查询
public List<Order> findAll(String orderNum, String productName);
}@Service
public class OrderServiceImpl implements OrderService {
@Autowired
private OrderMapper orderMapper;
/**
* 根据查询所有数据根据orderNum查询 productName模糊查询
* @param orderId
* @return
*/
@Override
public List<Order> findAll(String orderNum, String productName) {
return orderMapper.selectAll(orderNum, productName);
}
}编写OrdersController
@Controller
@RequestMapping("/order")
public class OrderController {
@Autowired
private OrderService orderService;
/**
* 根据查询所有数据根据orderNum查询 productName模糊查询
* @param page limit key value
* @return
*/
@RequestMapping("/findAll")
public String findAll(HttpServletRequest request, @RequestParam(value = "page",required = false,defaultValue = "1") int page, @RequestParam(value = "limit",required = false,defaultValue = "5")int limit,@RequestParam(value = "key",required = false,defaultValue = "")String key,@RequestParam(value = "value",required = false,defaultValue = "") String value){
PageHelper.startPage(page,limit);
List<Orders> all =null;
switch (key){
case "orderNum":
all= ordersService.findAll(value,null);
break;
case "productName":
all= ordersService.findAll(null,value);
break;
case "":
all= ordersService.findAll(null,null);
}
request.setAttribute("select",key);
request.setAttribute("selectValue",value);
PageInfo<Orders> pageInfo=new PageInfo<>(all);
request.setAttribute("pageInfo",pageInfo);
return "/orders/orders-list";
}
}编写OrdersMapper.xml
<resultMap id="o_p" type="com.yh.pojo.Order" autoMapping="true">
<association property="product" column="productId" autoMapping="true">
</association>
</resultMap>
<!-- 如果根据产品名称查询订单 需要两表关联查询 确定结果 -->
<select id="selectAll" resultMap="o_p">
select o.id,o.orderNum,o.orderTime,o.orderStatus,p.productName,p.productPrice from orders o,product p where o.productId=p.id
<if test="orderNum!='' and orderNum!=null">
and o.orderNum=#{orderNum}
</if>
<if test="productName!='' and productName!=null">
and p.productName like "%" #{productName} "%"
</if>
</select>maven下支持jdk1.8
<!-- 配置插件-->
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
</plugins>
</build>测试
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2022-10-20，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号