day02_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=1
2.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>
// 注意下面是三个ProductMapper MemberMapper TravellerMapper //根据产品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 多条件模糊查询
就是在查询所有数据的基础上添加查询条件
2.3.1 OrderMapper
@Param用于dao层，是mybatis中的注解
使得mapper.xml中的参数与后台的参数对应上，也增强了可读性
如果两者参数名一致得话，spring会自动进行封装，不一致的时候就需要手动去使其对应上。
即：用注解来简化xml配置的时候,@Param注解的作用是给参数命名,参数命名后就能根据名字得到参数值,正确的将参数传入sql语句中 。
复习mybatis的@Param技术点,向mapper.xml传值
public interface OrderMapper { //根据查询所有数据根据orderNum查询 productName模糊查询 public List<Order> selectAll(@Param("orderNum") String orderNum, @Param("productName") String productName); }
2.3.2 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); } }
旧OrderController（不写）
@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){ //key:匹配orderNum 或productName;再做查询; 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-list"; } }
2.3.3 优化的OrderController
@RequestMapping("/findAll") public ModelAndView findAll(@RequestParam(name = "page",defaultValue = "1")int pageNum, @RequestParam(name = "limit",defaultValue = "5")int pageSize, @RequestParam(name = "key",defaultValue = "")String key, @RequestParam(name = "value",defaultValue = "")String value){ PageHelper.startPage(pageNum,pageSize); // ModelAndView mv=new ModelAndView(); List<Order>orderList=null; //因为我们key值做判断；调用模糊查; findAll():第一个参数：是订单号；第二个参数：产品名; //在之前的jdk中，之允许int char整形判断；7 之后加上了String switch (key){ case "orderNum": orderList=orderService.findAll(value,null);break; case "productName": orderList=orderService.findAll(null,value);break; case "": orderList=orderService.findAll(null,null);break; //就是我们之前写的查询所有信息的代码; } PageInfo pageInfo=new PageInfo(orderList); System.out.println("******"+orderList); //控制器向视图页面传值; mv.addObject("select",key); //这里要注意，不是key :key,是回传回去的select mv.addObject("selectValue",value); mv.addObject("pageInfo",pageInfo); //设置页面； mv.setViewName("order-list"); return mv; }
2.3.4 OrderMapper.xml
先在navicat来测试一下，sql关联查询的代码；
-- select * from orders where orderNum='001' 只有订单号 select * from orders where productName where '游' -- 原因在于orders表么有name -- 怎么办？联合查询 select o.id,o.orderNum,p.productName,p.productPrice,o.orderTime,o.orderStatus from orders o INNER JOIN product p on o.productId=p.id where p.productName like '上%'
mapper.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>
2.3.5 测试
`
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>
2.3.5 测试
[外链图片转存中…(img-b7IVkwJV-1669375533089)]
