mybatis学习笔记(11)-一对多查询

本文实现一对多查询，查询订单及订单明细的信息

示例

确定主查询表：订单表
确定关联查询表：订单明细表
在一对一查询基础上添加订单明细表关联即可。

SELECT 
  orders.*,
  user.username,
  user.sex,
  user.address,
  orderdetail.id orderdetail_id,
  orderdetail.items_id,
  orderdetail.items_num,
  orderdetail.orders_id
FROM
  orders,
  user,
  orderdetail
WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id

注意上面的orderdetail.id (AS) orderdetail_id,这里需要取别名，否则由于orders表也有id字段，在后面映射时会冲突

使用resultType将上边的查询结果映射到pojo中，订单信息的就是重复。

对orders映射不能出现重复记录。

在orders.java类中添加List orderDetails属性。
最终会将订单信息映射到orders中，订单所对应的订单明细映射到orders中的orderDetails属性中。

映射成的orders记录数为两条（orders信息不重复）,每个orders中的orderDetails属性存储了该订单所对应的订单明细。

//订单明细
private List orderdetails;

<code class="language-xml hljs "><!--{cke_protected}{C}%3C!%2D%2D%20%E6%9F%A5%E8%AF%A2%E8%AE%A2%E5%8D%95%E5%85%B3%E8%81%94%E6%9F%A5%E8%AF%A2%E7%94%A8%E6%88%B7%E5%8F%8A%E8%AE%A2%E5%8D%95%E6%98%8E%E7%BB%86%EF%BC%8C%E4%BD%BF%E7%94%A8resultmap%20%2D%2D%3E-->
<select id="findOrdersAndOrderDetailResultMap" resultmap="OrdersAndOrderDetailResultMap">
   SELECT
      orders.*,
      user.username,
      user.sex,
      user.address,
      orderdetail.id orderdetail_id,
      orderdetail.items_id,
      orderdetail.items_num,
      orderdetail.orders_id
    FROM
      orders,
      user,
      orderdetail
    WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id
</select></code>

<code class="language-xml hljs "><!--{cke_protected}{C}%3C!%2D%2D%20%E8%AE%A2%E5%8D%95%E5%8F%8A%E8%AE%A2%E5%8D%95%E6%98%8E%E7%BB%86%E7%9A%84resultMap%0A%E4%BD%BF%E7%94%A8extends%E7%BB%A7%E6%89%BF%EF%BC%8C%E4%B8%8D%E7%94%A8%E5%9C%A8%E4%B8%AD%E9%85%8D%E7%BD%AE%E8%AE%A2%E5%8D%95%E4%BF%A1%E6%81%AF%E5%92%8C%E7%94%A8%E6%88%B7%E4%BF%A1%E6%81%AF%E7%9A%84%E6%98%A0%E5%B0%84%0A%20%2D%2D%3E-->
<resultmap type="com.iot.mybatis.po.Orders" id="OrdersAndOrderDetailResultMap" extends="OrdersUserResultMap">
    <!--{cke_protected}{C}%3C!%2D%2D%20%E8%AE%A2%E5%8D%95%E4%BF%A1%E6%81%AF%20%2D%2D%3E-->
    <!--{cke_protected}{C}%3C!%2D%2D%20%E7%94%A8%E6%88%B7%E4%BF%A1%E6%81%AF%20%2D%2D%3E-->
    <!--{cke_protected}{C}%3C!%2D%2D%20%E4%BD%BF%E7%94%A8extends%E7%BB%A7%E6%89%BF%EF%BC%8C%E4%B8%8D%E7%94%A8%E5%9C%A8%E4%B8%AD%E9%85%8D%E7%BD%AE%E8%AE%A2%E5%8D%95%E4%BF%A1%E6%81%AF%E5%92%8C%E7%94%A8%E6%88%B7%E4%BF%A1%E6%81%AF%E7%9A%84%E6%98%A0%E5%B0%84%20%2D%2D%3E-->


    <!--{cke_protected}{C}%3C!%2D%2D%20%E8%AE%A2%E5%8D%95%E6%98%8E%E7%BB%86%E4%BF%A1%E6%81%AF%0A%20%20%20%20%E4%B8%80%E4%B8%AA%E8%AE%A2%E5%8D%95%E5%85%B3%E8%81%94%E6%9F%A5%E8%AF%A2%E5%87%BA%E4%BA%86%E5%A4%9A%E6%9D%A1%E6%98%8E%E7%BB%86%EF%BC%8C%E8%A6%81%E4%BD%BF%E7%94%A8collection%E8%BF%9B%E8%A1%8C%E6%98%A0%E5%B0%84%0A%20%20%20%20collection%EF%BC%9A%E5%AF%B9%E5%85%B3%E8%81%94%E6%9F%A5%E8%AF%A2%E5%88%B0%E5%A4%9A%E6%9D%A1%E8%AE%B0%E5%BD%95%E6%98%A0%E5%B0%84%E5%88%B0%E9%9B%86%E5%90%88%E5%AF%B9%E8%B1%A1%E4%B8%AD%0A%20%20%20%20property%EF%BC%9A%E5%B0%86%E5%85%B3%E8%81%94%E6%9F%A5%E8%AF%A2%E5%88%B0%E5%A4%9A%E6%9D%A1%E8%AE%B0%E5%BD%95%E6%98%A0%E5%B0%84%E5%88%B0com.iot.mybatis.po.Orders%E5%93%AA%E4%B8%AA%E5%B1%9E%E6%80%A7%0A%20%20%20%20ofType%EF%BC%9A%E6%8C%87%E5%AE%9A%E6%98%A0%E5%B0%84%E5%88%B0list%E9%9B%86%E5%90%88%E5%B1%9E%E6%80%A7%E4%B8%ADpojo%E7%9A%84%E7%B1%BB%E5%9E%8B%0A%20%20%20%20%20%2D%2D%3E-->
    <collection property="orderdetails" oftype="com.iot.mybatis.po.Orderdetail">
        <!--{cke_protected}{C}%3C!%2D%2D%20id%EF%BC%9A%E8%AE%A2%E5%8D%95%E6%98%8E%E7%BB%86%E5%94%AF%20%E4%B8%80%E6%A0%87%E8%AF%86%0A%20%20%20%20%20%20%20%20property%3A%E8%A6%81%E5%B0%86%E8%AE%A2%E5%8D%95%E6%98%8E%E7%BB%86%E7%9A%84%E5%94%AF%20%E4%B8%80%E6%A0%87%E8%AF%86%20%E6%98%A0%E5%B0%84%E5%88%B0com.iot.mybatis.po.Orderdetail%E7%9A%84%E5%93%AA%E4%B8%AA%E5%B1%9E%E6%80%A7%0A%20%20%20%20%20%20%20%20%20%20%2D%2D%3E-->
        <id column="orderdetail_id" property="id">
        <result column="items_id" property="itemsId">
        <result column="items_num" property="itemsNum">
        <result column="orders_id" property="ordersId">
    </result></result></result></id></collection>

</resultmap></code>

//查询订单(关联用户)及订单明细
public List  findOrdersAndOrderDetailResultMap()throws Exception;

小结

mybatis使用resultMap的collection对关联查询的多条记录映射到一个list集合属性中。

使用resultType实现：将订单明细映射到orders中的orderdetails中，需要自己处理，使用双重循环遍历，去掉重复记录，将订单明细放在orderdetails中。