Mybatis一對多查詢的兩種姿勢，你值得擁有（收藏就完事了）

@[toc]

前言

最近碰到了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字段中。

優點：條件查詢友善；無論是訂單表還是詳情表如果要進行一些條件過濾的話，非常友善，直接寫在where中限制就行。

不足：因為是先關聯查詢，後映射；如果需要進行分頁查詢的話，這種方式就無法滿足。主表2條資料，詳情表5條資料，關聯之後就是10條，無法得主表進行分頁；解決方法，就是先給主表套個子查詢limit分頁後，然後結果集再跟詳情表進行關聯查詢；

方法二：子查詢映射

通過resultMap中collection标簽的select屬性去執行子查詢

還以查詢訂單清單為例

OrderMapper.java

List<Order> queryOrderList2(Map map);

<!--主查詢的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傳遞；

優點：無論是分頁還是普通查詢都能滿足；主表增加過濾條件也很友善，直接在主查詢的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
      }
    ]
  }
]

總結

方式	聯合查詢映射	子查詢映射
原理	sql查詢完成後再通過resultmap映射結果	主表的資料集循環調用子查詢
分頁	不支援分頁查詢，主表套子查詢也能實作	支援分頁
條件過濾	友善條件過濾	傳參也能實作，複雜參數例如list不好傳遞給子查詢；子查詢過濾不影響主表資料

Mybatis一對多查詢的兩種姿勢，你值得擁有（收藏就完事了）

資料庫表準備

執行個體示範

方法一：聯合查詢ResultMap映射

方法二：子查詢映射

總結

繼續閱讀

GitHub連夜封殺！這份阿裡 10W 字内部 Java 字面試手冊到底有多強？

用mybatis的generator插件在項目中自動生成dao及entity

spark/scala關于【資源檔案】加載方法概述外部檔案加載方案測試資源檔案打包入jar包中小結

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

AOP程式設計_Android優雅權限架構(1)概念基礎，2021金三銀四前言正文大綱正文

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

Effective Java 8:通用程式設計

SequoiaDB巨杉資料庫C++驅動概述

OOM三種類型

工廠模式-三種類型

【遞歸】高效率求2的n次幂

從mybatis到mybatis plus(mp)

win10本地scala和spark安裝安裝scala安裝spark

scala (3) Function 和 Method