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

柔情只为你懂 2023-09-29 17:26 0阅读 0赞

## day02\_SSM综合案例 ##

## 一、课程目标 ##

1. 【掌握】订单管理查询
    2.  查询所有订单信息
    3.  理解关系--mybatis；一对多 一对一
    4.  理解 订单详情

## 二、订单管理 ##

### 2.1 查询所有订单 ###

#### 2.1 订单表关系结构图 ####

![在这里插入图片描述][fb80003c7e8e49d1a0d24ef52a03d551.png_pic_center]

![在这里插入图片描述][9f096b87667a4080bc33e89ddd4cb13f.png_pic_center]

#### 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>

#### 测试 ####

![在这里插入图片描述][f9e5541fe57e4c7e9a66a679c28445ec.png_pic_center]

> 总结：这里建议使用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 id="findByIdMap" type="Order" autoMapping="true">
            
            <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>
            
            <collection property="travellers" ofType="Traveller" autoMapping="true">
                <id property="id" column="tid"/>
                <result property="name" column="tname"/>
                <result property="phoneNum" column="tnum"/>
            </collection>
        </resultMap>
        
        <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);

#### 测试 ####

![在这里插入图片描述][22d2d9c14d9340c4aa10c5061b7b3b94.png_pic_center]

### 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 测试 ####

![在这里插入图片描述][beaa4f09686340b6ba4880be0eee9db3.png_pic_center]

![在这里插入图片描述][e524ed716cf04d8a9bc4d4cfd2ddcc33.png_pic_center]  
  
  
\`

maven下支持jdk1.8

#### 2.3.5 测试 ####

\[外链图片转存中…(img-b7IVkwJV-1669375533089)\]![在这里插入图片描述][5dd670b3615b4c81b31d6e4be97ba526.png_pic_center]

![在这里插入图片描述][0e54e2d8bd5b489aaafd54ad29f911d9.png_pic_center]

[fb80003c7e8e49d1a0d24ef52a03d551.png_pic_center]: https://img-blog.csdnimg.cn/fb80003c7e8e49d1a0d24ef52a03d551.png#pic_center
[9f096b87667a4080bc33e89ddd4cb13f.png_pic_center]: https://img-blog.csdnimg.cn/9f096b87667a4080bc33e89ddd4cb13f.png#pic_center
[f9e5541fe57e4c7e9a66a679c28445ec.png_pic_center]: https://img-blog.csdnimg.cn/f9e5541fe57e4c7e9a66a679c28445ec.png#pic_center
[22d2d9c14d9340c4aa10c5061b7b3b94.png_pic_center]: https://img-blog.csdnimg.cn/22d2d9c14d9340c4aa10c5061b7b3b94.png#pic_center
[beaa4f09686340b6ba4880be0eee9db3.png_pic_center]: https://img-blog.csdnimg.cn/beaa4f09686340b6ba4880be0eee9db3.png#pic_center
[e524ed716cf04d8a9bc4d4cfd2ddcc33.png_pic_center]: https://img-blog.csdnimg.cn/e524ed716cf04d8a9bc4d4cfd2ddcc33.png#pic_center
[5dd670b3615b4c81b31d6e4be97ba526.png_pic_center]: https://img-blog.csdnimg.cn/5dd670b3615b4c81b31d6e4be97ba526.png#pic_center
[0e54e2d8bd5b489aaafd54ad29f911d9.png_pic_center]: https://img-blog.csdnimg.cn/0e54e2d8bd5b489aaafd54ad29f911d9.png#pic_center