Mybatis一對多查詢的兩種姿勢（值得收藏）

更新時間：2021年05月12日 09:23:12 作者：臭小子帥

這篇文章主要給大家介紹了關于Mybatis一對多查詢的兩種姿勢，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧

前言

最近碰到了Mybatis一對多查詢的場景，在這里總結對比下常見的兩種實現方式。

本文以常見的訂單表和訂單詳情表來舉例說明；

數據庫表準備

訂單表 tbl_order

訂單詳情表 tlb_order_detail

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字段中。

優(yōu)點：條件查詢方便；無論是訂單表還是詳情表如果要進行一些條件過濾的話，非常方便，直接寫在where中限制就行。
不足：因為是先關聯查詢，后映射；如果需要進行分頁查詢的話，這種方式就無法滿足。主表2條數據，詳情表5條數據，關聯之后就是10條，無法得主表進行分頁；解決方法，就是先給主表套個子查詢limit分頁后，然后結果集再跟詳情表進行關聯查詢；

方法二：子查詢映射

通過resultMap中collection標簽的select屬性去執(zhí)行子查詢

還以查詢訂單列表為例

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
    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-->
  <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
      *
    FROM
      `tbl_order_detail` where order_no = #{order_no}
  </select>

查詢結果同上個例子一樣；

原理：通過collection的select方法去調用子查詢；所需參數通過column傳遞；

優(yōu)點：無論是分頁還是普通查詢都能滿足；主表增加過濾條件也很方便，直接在主查詢的sql中增加where條件就行

缺點：子查詢不好增加過濾條件；column只能傳遞主表已有的字段。下面提供解決方式；
ps：column傳遞多個參數 column=“{prop1=col1,prop2=col2}”

例如：實際場景中，詳情表有個狀態(tài)字段，只展示狀態(tài)正常的詳情，需要過濾詳情記錄。

本例子沒有狀態(tài)字段，就查詢訂單列表，詳情中不展示瓜子，即詳情記錄中過濾掉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
      }
    ]
}
]

總結

方式	聯合查詢映射	子查詢映射
原理	sql查詢完成后再通過resultmap映射結果	主表的數據集循環(huán)調用子查詢
分頁	不支持分頁查詢，主表套子查詢也能實現	支持分頁
條件過濾	方便條件過濾	傳參也能實現，復雜參數例如list不好傳遞給子查詢；子查詢過濾不影響主表數據