Mybatis一对多查询的两种姿势，你值得拥有（收藏就完事了）-阿里云开发者社区

相关文章推荐

帅呆的烤地瓜 · Mybatis 批量插入数据 ...· 1 周前 ·

任性的香瓜 · mybatis中使用map类型参数，其中ke ...· 3 天前 ·

想旅行的双杠 · mybatis动态sql将map的key当键 ...· 3 天前 ·

风度翩翩的钢笔 · 证书链不完整，降级为6_问答-阿里云开发者社区· 1 周前 ·

豪气的香瓜 · 如何避免在解析输入日期为格式“dd/mm/y ...· 7 月前 ·

风度翩翩的煎鸡蛋 · C++使用ssl发送https请求_c++ ...· 1 年前 ·

淡定的红薯 · Set-MailUser ...· 1 年前 ·

含蓄的手套 · 深研院潘锋课题组在构建材料知识图谱及以此预测 ...· 1 年前 ·

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

    List<Order> queryOrderList(Map map);

OrderMapper.xml

<resultMap id="BaseResultMap" type="com.chouxiaozi.mybatisdruid.entity.Order" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="order_no" property="orderNo" jdbcType="VARCHAR" />
    <result column="order_time" property="orderTime" jdbcType="TIMESTAMP" />
    <result column="pay_time" property="payTime" jdbcType="TIMESTAMP" />
    <result column="remark" property="remark" jdbcType="VARCHAR" />
    <collection property="orderDetailList" ofType="com.chouxiaozi.mybatisdruid.entity.OrderDetail">
      <id column="d_id" property="id" jdbcType="INTEGER" />
      <result column="d_order_no" property="orderNo" jdbcType="VARCHAR" />
      <result column="good_name" property="goodName" jdbcType="VARCHAR" />
      <result column="good_id" property="goodId" jdbcType="INTEGER" />
      <result column="good_count" property="goodCount" jdbcType="INTEGER" />
    </collection>
  </resultMap>
<select id="queryOrderList" resultMap="BaseResultMap">
    SELECT
      o.*, d.id as d_id,d.order_no as d_order_no,d.good_name,d.good_id,d.good_count
      tbl_order o
        LEFT JOIN tbl_order_detail d ON d.order_no = o.order_no
    where 1=1
    <if test="orderNo != null and orderNo != ''">
      and o.order_no = #{orderNo}
    ORDER BY o.order_time desc
  </select>

查询结果展示

"id": 2, "orderNo": "DD000002", "orderTime": "2021-05-09 12:25:57", "payTime": "2021-05-09 12:25:59", "remark": "2号订单", "orderDetailList": [ "id": 5, "orderNo": "DD000002", "goodName": "耳机", "goodId": 5, "goodCount": 1 "id": 4, "orderNo": "DD000002", "goodName": "手机", "goodId": 4, "goodCount": 1 "id": 1, "orderNo": "DD000001", "orderTime": "2021-05-09 12:25:37", "payTime": "2021-05-09 12:25:41", "remark": "1号订单", "orderDetailList": [ "id": 2, "orderNo": "DD000001", "goodName": "饮料", "goodId": 2, "goodCount": 2 "id": 1, "orderNo": "DD000001", "goodName": "瓜子", "goodId": 1, "goodCount": 1 "id": 3, "orderNo": "DD000001", "goodName": "矿泉水", "goodId": 3, "goodCount": 2

原理：sql直接关联查询，然后结果集通过resultMap的collection映射，将order_detail表对应的字段映射到orderDetailList字段中。
优点：条件查询方便；无论是订单表还是详情表如果要进行一些条件过滤的话，非常方便，直接写在where中限制就行。
不足：因为是先关联查询，后映射；如果需要进行分页查询的话，这种方式就无法满足。主表2条数据，详情表5条数据，关联之后就是10条，无法得主表进行分页；解决方法，就是先给主表套个子查询limit分页后，然后结果集再跟详情表进行关联查询；

方法二：子查询映射

通过resultMap中collection标签的select属性去执行子查询

还以查询订单列表为例

OrderMapper.java

    List<Order> queryOrderList2(Map map);

OrderMapper.xml

<!--主查询的resultMap-->
  <resultMap id="BaseResultMap2" type="com.chouxiaozi.mybatisdruid.entity.Order" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="order_no" property="orderNo" jdbcType="VARCHAR" />
    <result column="order_time" property="orderTime" jdbcType="TIMESTAMP" />
    <result column="pay_time" property="payTime" jdbcType="TIMESTAMP" />
    <result column="remark" property="remark" jdbcType="VARCHAR" />
    <!--select子查询， column 传给子查询的参数-->
    <collection property="orderDetailList" ofType="com.chouxiaozi.mybatisdruid.entity.OrderDetail"
                select="queryDetail" column="order_no">
    </collection>
  </resultMap>
  <!--主查询的sql-->
  <select id="queryOrderList2" resultMap="BaseResultMap2">
    SELECT
    tbl_order o
    where 1=1
    <if test="orderNo != null and orderNo != ''">
      and o.order_no = #{orderNo}
    ORDER BY o.order_time desc
  </select>
  <!--子查询的resultMap-->
  <resultMap id="detailResuleMap" type="com.chouxiaozi.mybatisdruid.entity.OrderDetail">
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="order_no" property="orderNo" jdbcType="VARCHAR" />
    <result column="good_name" property="goodName" jdbcType="VARCHAR" />
    <result column="good_id" property="goodId" jdbcType="INTEGER" />
    <result column="good_count" property="goodCount" jdbcType="INTEGER" />
  </resultMap>
  <!--子查询的sql-->
  <select id="queryDetail" resultMap="detailResuleMap">
    SELECT
      `tbl_order_detail` where order_no = #{order_no}
  </select>

查询结果同上个例子一样；

原理：通过collection的select方法去调用子查询；所需参数通过column传递；
优点：无论是分页还是普通查询都能满足；主表增加过滤条件也很方便，直接在主查询的sql中增加where条件就行
缺点：子查询不好增加过滤条件；column只能传递主表已有的字段。下面提供解决方式；
ps：column传递多个参数 column=“{prop1=col1,prop2=col2}”

例如：实际场景中，详情表有个状态字段，只展示状态正常的详情，需要过滤详情记录。
本例子没有状态字段，就查询订单列表，详情中不展示瓜子，即详情记录中过滤掉good_id = 1的；

在上个例子基础上修改如下：
调用层传参

    Map map = new HashMap();
    map.put("goodId", 1);
    orderMapper.queryOrderList2(map);

orderMapper.xml中增加传参过滤
在这里插入图片描述

展示结果如下：详情中已成功过滤掉瓜子；记住，过滤子查询不会影响主表记录；

"id": 2, "orderNo": "DD000002", "orderTime": "2021-05-09 12:25:57", "payTime": "2021-05-09 12:25:59", "remark": "2号订单", "orderDetailList": [ "id": 4, "orderNo": "DD000002", "goodName": "手机", "goodId": 4, "goodCount": 1 "id": 5, "orderNo": "DD000002", "goodName": "耳机", "goodId": 5, "goodCount": 1 "id": 1, "orderNo": "DD000001", "orderTime": "2021-05-09 12:25:37", "payTime": "2021-05-09 12:25:41", "remark": "1号订单", "orderDetailList": [ "id": 2, "orderNo": "DD000001", "goodName": "饮料", "goodId": 2, "goodCount": 2 "id": 3, "orderNo": "DD000001", "goodName": "矿泉水", "goodId": 3, "goodCount": 2 联合查询映射子查询映射