Mybatis一对多查询的两种姿势，你值得拥有（收藏就完事了）

ゝ一纸荒年。 2023-01-19 08:27 536阅读 0赞

### 文章目录 ###

*   *  数据库表准备
     *  实例演示
     *   *  方法一：联合查询ResultMap映射
         *  方法二：子查询映射
     *  总结

前言

> 最近碰到了Mybatis一对多查询的场景，在这里总结对比下常见的两种实现方式。
> 
> 本文以常见的订单表和订单详情表来举例说明；

## 数据库表准备 ##

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

**订单表 tbl\_order**

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

**订单详情表 tlb\_order\_detail**

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

***ps: 一个订单关联多个订单详情，通过order\_no订单号关联；***

## 实例演示 ##

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

### 方法一：联合查询ResultMap映射 ###

> sql直接关联查询，然后结果集通过resultMap的collection映射

例如 查询订单列表，包括订单详情

**Order.java** 中新增字段orderDetailList，用于存详情列表

public class Order { 
        private Integer id;
    
        private String orderNo;
    
        private Date orderTime;
    
        private Date payTime;
    
        private String remark;
        /**订单详情*/
        private List<OrderDetail> orderDetailList;
        //省略get、set

**OrderMapper.java** 新增查询方法

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
        FROM
          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}
        </if>
        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 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" />
        
        <collection property="orderDetailList" ofType="com.chouxiaozi.mybatisdruid.entity.OrderDetail" select="queryDetail" column="order_no">
        </collection>
      </resultMap>
      
      <select id="queryOrderList2" resultMap="BaseResultMap2">
        SELECT
        o.*
        FROM
        tbl_order o
        where 1=1
        <if test="orderNo != null and orderNo != ''">
          and o.order_no = #{orderNo}
        </if>
        ORDER BY o.order_time desc
      </select>
      
      <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>
      
      <select id="queryDetail" resultMap="detailResuleMap">
        SELECT
          *
        FROM
          `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中增加传参过滤  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NodWFpODYyNA_size_16_color_FFFFFF_t_70]

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

[
      {
        "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
          }
        ]
      }
    ]

## 总结 ##

<table> 
 <thead> 
  <tr> 
   <th>方式</th> 
   <th>联合查询映射</th> 
   <th>子查询映射</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>原理</td> 
   <td>sql查询完成后再通过resultmap映射结果</td> 
   <td>主表的数据集循环调用子查询</td> 
  </tr> 
  <tr> 
   <td>分页</td> 
   <td>不支持分页查询，主表套子查询也能实现</td> 
   <td>支持分页</td> 
  </tr> 
  <tr> 
   <td>条件过滤</td> 
   <td>方便条件过滤</td> 
   <td>传参也能实现，复杂参数例如list不好传递给子查询 ；子查询过滤不影响主表数据</td> 
  </tr> 
 </tbody> 
</table>

[2021050913090050.png_pic_center]: /images/20221021/8342c8ec0de8430fa389c11b2c36f958.png
[2021050913091784.png_pic_center]: /images/20221021/d209ddcec16d40d7b10ca66d77118893.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NodWFpODYyNA_size_16_color_FFFFFF_t_70]: /images/20221021/2b9c1d524e884f728fa8f02c055d0f58.png