springboot项目，自定义xml，查询多表封装返回

一、返回一对多

需求：根据userId和orderStatus查询不同状态下的订单（）

分析：一个订单里面有一种或多种商品，返回结果是一个一对多的关系。故可在订单vo实体类中封装一个商品详情vo的list。

以下根据实际开发步骤进行

1、mapper层接口和先写部分xml

mapper层接口

public interface OrdersMapperCustom {

    /**
     * 根据userId和orderStatus查询不同状态下的订单
     * @param map
     * @return
     */
    List<MyOrdersVO> queryMyOrders(@Param("paramsMap") Map<String, Object> map);
}

部分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="com.xpf.mapper.OrdersMapperCustom" >
  
  <select id="queryMyOrders" parameterType="map" resultMap="myOrdersVO">
    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,
      od.is_comment as isComment,
      os.order_status as orderStatus,

      oi.item_id as itemId,
      oi.item_name as itemName,
      oi.item_img as itemImg,
      oi.item_spec_id as itemSpecId,
      oi.item_spec_name as itemSpecName,
      oi.buy_counts as buyCounts,
      oi.price as price
    from
      orders od
        left join
      order_status os
      on od.id = os.order_id
        left join
      order_items oi
      on
        od.id = oi.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>

</mapper>

这里的返回结果resultMap="myOrdersVO"，正是需要封装的。

（这里先写部分xml是可以根据select 【】from的【】中的查询内容封装）标记①

2、

2.1 封装子商品详情vo

根据 标记① 中查询结果后部分内容（语句中换行了）

oi.item_id as itemId,
oi.item_name as itemName,
oi.item_img as itemImg,
oi.item_spec_id as itemSpecId,
oi.item_spec_name as itemSpecName,
oi.buy_counts as buyCounts,
oi.price as price

封装子商品详情vo

/**
 * 用于封装订单vo立的每个子商品详情vo
 */
public class MySubOrderItemVO {

    private String itemId;
    private String itemName;
    private String itemImg;
    private String itemSpecId;
    private String itemSpecName;
    private Integer buyCounts;
    private Integer price;
    
    //getXxx()和setXxx()此处省略
}

2.2 同理根据标记①查询结果前部分内容

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,
od.is_comment as isComment,
os.order_status as orderStatus,

封装订单vo

/**
 * 用于封装订单vo
 */
public class MyOrdersVO {

    private String orderId;
    private Date createdTime;
    private Integer payMethod;
    private Integer realPayAmount;
    private Integer orderStatus;
    private Integer postAmount;
    private Integer isComment;

    private List<MySubOrderItemVO> subOrderItemList;

    //getXxx()和setXxx()此处省略
    
}

3、继续完成xml中封装返回结果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="com.xpf.mapper.OrdersMapperCustom" >
  <resultMap id="myOrdersVO" type="com.xpf.pojo.vo.MyOrdersVO" >
    <id column="orderId" property="orderId"  />
    <result column="createdTime" property="createdTime"  />
    <result column="payMethod" property="payMethod"  jdbcType="INTEGER"/>
    <result column="realPayAmount" property="realPayAmount"  />
    <result column="orderStatus" property="orderStatus"  />
    <result column="postAmount" property="postAmount"  />
    <result column="isComment" property="isComment"  />

    <collection property="subOrderItemList" ofType="com.xpf.pojo.vo.MySubOrderItemVO">
      <result column="itemId" property="itemId" />
      <result column="itemName" property="itemName"  />
      <result column="itemImg" property="itemImg"  />
      <result column="itemSpecId" property="itemSpecId"  />
      <result column="itemSpecName" property="itemSpecName"  />
      <result column="buyCounts" property="buyCounts" jdbcType="INTEGER" />
      <result column="price" property="price" jdbcType="INTEGER" />
    </collection>
  </resultMap>

  <select id="queryMyOrders" parameterType="map" resultMap="myOrdersVO">
    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,
      od.is_comment as isComment,
      os.order_status as orderStatus,
      oi.item_id as itemId,
      oi.item_name as itemName,
      oi.item_img as itemImg,
      oi.item_spec_id as itemSpecId,
      oi.item_spec_name as itemSpecName,
      oi.buy_counts as buyCounts,
      oi.price as price
    from
      orders od
        left join
      order_status os
      on od.id = os.order_id
        left join
      order_items oi
      on
        od.id = oi.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>

</mapper>

二、返回一对一

返回一对一与以上步骤类似，且比较简单，请看代码注释

1、mapper层接口。

public interface ItemsMapperCustom{
    /**
     * 根据规格idList(例如：1001,1002,1003),查询出购物车详情VO信息
     * @param specIdsList
     * @return
     */
    List<ShopcartVO> queryItemsBySpecIds(@Param("paramsList") List specIdsList);
}

2、写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="com.xpf.mapper.ItemsMapperCustom" >
<select id="queryItemsBySpecIds" parameterType="list" resultType="com.xpf.pojo.vo.ShopcartVO">
    SELECT
      ti.id as itemId,
      ti.item_name as itemName,
      tii.url as itemImgUrl,
      tis.id as specId,
      tis.`name` as specName,
      tis.price_normal as priceNormal,
      tis.price_discount as priceDiscount
    FROM
      items_spec tis
        LEFT JOIN
      items ti
      ON
        tis.item_id = ti.id
        LEFT JOIN
      items_img tii
      ON
        ti.id = tii.item_id
    WHERE
      tii.is_main = 1
    AND
      tis.id
    IN
      <foreach collection="paramsList" index="index" item="specId" open="(" separator="," close=")">
        #{specId}
      </foreach>

  </select>
  <!-- ('1001','1002','1003') -->
</mapper>

3、根据返回查询结果封装vo

resultType="com.xpf.pojo.vo.ShopcartVO"

/**
 * 购物车详情VO（vo是指从后端往前端传的封装数据）
 */
public class ShopcartVO {
    private String itemId;
    private String itemImgUrl;
    private String itemName;
    private String specId;
    private String specName;
    private String priceDiscount;
    private String priceNormal;

    //get和set省略
}