前言

最近碰到了Mybatis一對(duì)多查詢的場(chǎng)景，在這里總結(jié)對(duì)比下常見的兩種實(shí)現(xiàn)方式。

本文以常見的訂單表和訂單詳情表來(lái)舉例說(shuō)明；

數(shù)據(jù)庫(kù)表準(zhǔn)備

訂單表 tbl_order

訂單詳情表 tlb_order_detail

ps: 一個(gè)訂單關(guān)聯(lián)多個(gè)訂單詳情，通過(guò)order_no訂單號(hào)關(guān)聯(lián)；

實(shí)例演示

方法一：聯(lián)合查詢ResultMap映射

sql直接關(guān)聯(lián)查詢，然后結(jié)果集通過(guò)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>

查詢結(jié)果展示

[
  {
    "id": 2,
    "orderNo": "DD000002",
    "orderTime": "2021-05-09 12:25:57",
    "payTime": "2021-05-09 12:25:59",
    "remark": "2號(hào)訂單",
    "orderDetailList": [
      {
        "id": 5,
        "orderNo": "DD000002",
        "goodName": "耳機(jī)",
        "goodId": 5,
        "goodCount": 1
      },
      {
        "id": 4,
        "orderNo": "DD000002",
        "goodName": "手機(jī)",
        "goodId": 4,
        "goodCount": 1
      }
    ]
  },
  {
    "id": 1,
    "orderNo": "DD000001",
    "orderTime": "2021-05-09 12:25:37",
    "payTime": "2021-05-09 12:25:41",
    "remark": "1號(hào)訂單",
    "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直接關(guān)聯(lián)查詢，然后結(jié)果集通過(guò)resultMap的collection映射，將order_detail表對(duì)應(yīng)的字段映射到orderDetailList字段中。

優(yōu)點(diǎn)：條件查詢方便；無(wú)論是訂單表還是詳情表如果要進(jìn)行一些條件過(guò)濾的話，非常方便，直接寫在where中限制就行。
不足：因?yàn)槭窍汝P(guān)聯(lián)查詢，后映射；如果需要進(jìn)行分頁(yè)查詢的話，這種方式就無(wú)法滿足。主表2條數(shù)據(jù)，詳情表5條數(shù)據(jù)，關(guān)聯(lián)之后就是10條，無(wú)法得主表進(jìn)行分頁(yè)；解決方法，就是先給主表套個(gè)子查詢limit分頁(yè)后，然后結(jié)果集再跟詳情表進(jìn)行關(guān)聯(lián)查詢；

方法二：子查詢映射

通過(guò)resultMap中collection標(biāo)簽的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 傳給子查詢的參數(shù)-->
    <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>

查詢結(jié)果同上個(gè)例子一樣；

原理：通過(guò)collection的select方法去調(diào)用子查詢；所需參數(shù)通過(guò)column傳遞；

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

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

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

本例子沒(méi)有狀態(tài)字段，就查詢訂單列表，詳情中不展示瓜子，即詳情記錄中過(guò)濾掉good_id = 1的；

在上個(gè)例子基礎(chǔ)上修改如下：

調(diào)用層傳參

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

orderMapper.xml中增加傳參過(guò)濾

展示結(jié)果如下：詳情中已成功過(guò)濾掉瓜子；記住，過(guò)濾子查詢不會(huì)影響主表記錄；

[
  {
    "id": 2,
    "orderNo": "DD000002",
    "orderTime": "2021-05-09 12:25:57",
    "payTime": "2021-05-09 12:25:59",
    "remark": "2號(hào)訂單",
    "orderDetailList": [
      {
        "id": 4,
        "orderNo": "DD000002",
        "goodName": "手機(jī)",
        "goodId": 4,
        "goodCount": 1
      },
      {
        "id": 5,
        "orderNo": "DD000002",
        "goodName": "耳機(jī)",
        "goodId": 5,
        "goodCount": 1
      }
    ]
  },
  {
    "id": 1,
    "orderNo": "DD000001",
    "orderTime": "2021-05-09 12:25:37",
    "payTime": "2021-05-09 12:25:41",
    "remark": "1號(hào)訂單",
    "orderDetailList": [
      {
        "id": 2,
        "orderNo": "DD000001",
        "goodName": "飲料",
        "goodId": 2,
        "goodCount": 2
      },
      {
        "id": 3,
        "orderNo": "DD000001",
        "goodName": "礦泉水",
        "goodId": 3,
        "goodCount": 2
      }
    ]
  }
]

總結(jié)

到此這篇關(guān)于Mybatis一對(duì)多查詢的文章就介紹到這了,更多相關(guān)Mybatis一對(duì)多查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論