JAVAEE框架技术之15SSM综合案例 订单管理查询

day02_SSM综合案例

一、课程目标

1. 【掌握】订单管理查询
2.  查询所有订单信息
3.  理解关系--mybatis；一对多 一对一
4.  理解 订单详情
1
2
3
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? "未支付":"已支付";
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

编写OrderMapper

public interface OrderMapper {   
    List<Order> findAll();
1
2

编写OrderService

public interface OrderService {
    List<Order> findAll();
}
1
2
3

实现类

@Service
public class OrderServiceImpl implements OrderService {    
    @Autowired
    private OrderMapper orderMapper;
   
    @Override
    public List<Order> findAll() {
        return orderMapper.findAll();
    }
}
1
2
3
4
5
6
7
8
9
10

编写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;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

编写OrderMapper.xml

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>
1
2
3
4
5
6
7
8
9
10
11
12
13

测试

总结：这里建议使用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
1
2
3
4
5
6
7

2.2 Member实体类

//会员类  代码同上
1

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?"成人":"儿童";
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

编写OrderMapper

public interface OrderMapper {
    //根据id查询订单详细信息
    public Order findById(int id);
}
1
2
3
4

编写Orderervice

//2.根据id来查询订单;
    public Order findById(Integer id);
1
2

实现类

public Order findById(Integer id) {
        return orderMapper.findById(id);
}
1
2
3

编写OrderController

    @RequestMapping("/findById")
    public String findById(HttpServletRequest request,int id){
        Order order = ordersService.findById(id);
        request.setAttribute("order",orders);
        return "order-show";
    }
1
2
3
4
5
6

编写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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

方式二：

    <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>
1
2
3
4
5
6
7
8
9

    // 注意下面是三个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);
1
2
3
4
5
6
7
8
9
10
11
12

测试

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);
}
1
2
3
4

2.3.2 OrderService

public interface OrderService {
	//根据查询所有数据根据orderNum查询  productName模糊查询
   public List<Order> findAll(String orderNum, String productName);
}
1
2
3
4

@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);
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

旧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";
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

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;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

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 '上%'
1
2
3
4
5
6
7
8

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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

maven下支持jdk1.8

  <build>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.pluginsgroupId>
        <artifactId>maven-compiler-pluginartifactId>
        <configuration>
          <source>1.8source>
          <target>1.8target>
          <encoding>UTF-8encoding>
        configuration>
      plugin>
    plugins>
  build>
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2.3.5 测试

`

maven下支持jdk1.8

  <build>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.pluginsgroupId>
        <artifactId>maven-compiler-pluginartifactId>
        <configuration>
          <source>1.8source>
          <target>1.8target>
          <encoding>UTF-8encoding>
        configuration>
      plugin>
    plugins>
  build>
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2.3.5 测试

[外链图片转存中…(img-b7IVkwJV-1669375533089)]