Mybatis框架中Mapper.xml文件嵌套查询

忘是亡心i 2022-10-31 04:16 213阅读 0赞

# 前言 #

充分运用mybatis标签，嵌套查询

--------------------

# 代码实现： #

# 具体sql语句实现： #

<select id="getSubItems" parameterType="String" resultType="com.zcw.pojo.vo.MySubOrderItemVO">
    
          select
            oi.item_id as itemId,
            oi.item_name as itemName,
            oi.item_img as itemImg,
            oi.item_spec_name as itemSpecName,
            oi.buy_counts as buyCounts,
            oi.price as price
          from
            order_items oi
          where
            oi.order_id = #{ orderId}
    
        </select>

<select id="queryMyOrders" resultMap="myOrdersVO" parameterType="Map">
        SELECT
            od.id as orderId,
            od.created_time as createdTime,
            od.pay_method as payMethod,
            od.real_pay_amount as realPayAmount,
            od.post_amount as postAmount,
            os.order_status as orderStatus,
            od.is_comment as isComment
        FROM
            orders od
        LEFT JOIN
            order_status os
        on od.id = os.order_id
        WHERE
            od.user_id = #{ paramsMap.userId}
        AND
            od.is_delete = 0
            <if test="paramsMap.orderStatus != null">
              and os.order_status = #{ paramsMap.orderStatus}
            </if>
        ORDER BY
            od.updated_time ASC
      </select>

# 实体类： #

/** * 用户中心，我的订单列表VO */
    public class MyOrdersVO { 
    
        private String orderId;
        private Date createdTime;
        private Integer payMethod;
        private Integer realPayAmount;
        private Integer postAmount;
        private Integer isComment;
        private Integer orderStatus;
    
        private List<MySubOrderItemVO> subOrderItemList;
    
        public String getOrderId() { 
            return orderId;
        }
    
        public void setOrderId(String orderId) { 
            this.orderId = orderId;
        }
    
        public Date getCreatedTime() { 
            return createdTime;
        }
    
        public void setCreatedTime(Date createdTime) { 
            this.createdTime = createdTime;
        }
    
        public Integer getPayMethod() { 
            return payMethod;
        }
    
        public void setPayMethod(Integer payMethod) { 
            this.payMethod = payMethod;
        }
    
        public Integer getRealPayAmount() { 
            return realPayAmount;
        }
    
        public void setRealPayAmount(Integer realPayAmount) { 
            this.realPayAmount = realPayAmount;
        }
    
        public Integer getPostAmount() { 
            return postAmount;
        }
    
        public void setPostAmount(Integer postAmount) { 
            this.postAmount = postAmount;
        }
    
        public Integer getOrderStatus() { 
            return orderStatus;
        }
    
        public void setOrderStatus(Integer orderStatus) { 
            this.orderStatus = orderStatus;
        }
    
        public List<MySubOrderItemVO> getSubOrderItemList() { 
            return subOrderItemList;
        }
    
        public void setSubOrderItemList(List<MySubOrderItemVO> subOrderItemList) { 
            this.subOrderItemList = subOrderItemList;
        }
    
        public Integer getIsComment() { 
            return isComment;
        }
    
        public void setIsComment(Integer isComment) { 
            this.isComment = isComment;
        }
    }

/** * 用户中心，我的订单列表嵌套商品VO */
    public class MySubOrderItemVO { 
    
        private String itemId;
        private String itemImg;
        private String itemName;
        private String itemSpecName;
        private Integer buyCounts;
        private Integer price;
    
        public String getItemId() { 
            return itemId;
        }
    
        public void setItemId(String itemId) { 
            this.itemId = itemId;
        }
    
        public String getItemImg() { 
            return itemImg;
        }
    
        public void setItemImg(String itemImg) { 
            this.itemImg = itemImg;
        }
    
        public String getItemName() { 
            return itemName;
        }
    
        public void setItemName(String itemName) { 
            this.itemName = itemName;
        }
    
        public String getItemSpecName() { 
            return itemSpecName;
        }
    
        public void setItemSpecName(String itemSpecName) { 
            this.itemSpecName = itemSpecName;
        }
    
        public Integer getBuyCounts() { 
            return buyCounts;
        }
    
        public void setBuyCounts(Integer buyCounts) { 
            this.buyCounts = buyCounts;
        }
    
        public Integer getPrice() { 
            return price;
        }
    
        public void setPrice(Integer price) { 
            this.price = price;
        }
    }