Mybatis一對多嵌套查詢和分頁

需求：根據分類ID查詢分類下所屬的商品集合,每個商品又有一個圖片集合。

類似的需求有很多，比如經典的一個用戶有N個角色，一個角色有N個權限，那麼通過用戶的id來查詢角色和權限數據等等。

至於分頁插件，無論是Mybatis-PageHelper還是Mybatis-Plus都可以輔助，這裡主要記錄不同查詢方式對分頁的影響。

先展示結果：

{ "code": 0, "msg":  "success",  "data": { "total": 9, "size": 2, "pages": 5, "current": 1, "records": [ { "id": 1, "code": "1410854032", "name": "Esmeralda Kilback", "categoryId": "1", "originPrice": 359, "price": 103, "sales": 299,  "commentCount": 0,  "freight": 1,  "detail": "這裡是商品詳情",  "createdAt": "2018-04-09 18:52:05",  "updatedAt": "2018-04-24 23:41:49",  "images": [ {  "id": 40,  "productId": "1",  "link": "uploads/product/201804/18/78a6e4e4d73bfc64b7aef88a90e7f192.png",  "createdAt": "2018-04-09 18:52:05",  "updatedAt": "2018-04-18 16:37:09" }, {  "id": 41,  "productId": "1",  "link": "uploads/product/201804/18/fffdccaa36a8475ed3d2c71c2f43cb86.png",  "createdAt": "2018-04-09 18:52:05",  "updatedAt": "2018-04-18 16:37:09" }, {  "id": 301,  "productId": "1",  "link": "uploads/product/201804/18/68b18cbcb090a94123abd9d729528370.png",  "createdAt": "2018-04-18 16:35:56",  "updatedAt": "2018-04-18 16:35:56" } ] }, {  "id": 8,  "code": "1925117917",  "name": "Edgardo Osinski",  "categoryId": "1",  "originPrice": 389,  "price": 154,  "sales": 199,  "commentCount": 0,  "freight": 14,  "detail": "這裡是商品詳情...5052 Kyler Walk Suite 921",  "createdAt": "2018-04-09 18:52:05",  "updatedAt": "2018-04-09 18:52:05",  "images": [ {  "id": 58,  "productId": "8",  "link": "uploads/default.png",  "createdAt": "2018-04-09 18:52:05",  "updatedAt": "2018-04-09 18:52:05" }, {  "id": 59,   "productId": "8",  "link": "uploads/default2.png",  "createdAt": "2018-04-09 18:52:05",  "updatedAt": "2018-04-09 18:52:05" }, {  "id": 60,  "productId": "8",  "link": "uploads/default3.png",  "createdAt": "2018-04-09 18:52:05",  "updatedAt": "2018-04-09 18:52:05" } ] } ] }}

定義模型

Product用於數據庫映射，為了保持其簡潔，其他的二次封裝不在Product裡進行，而用繼承的方式。

定義模型 ProductVo ，

@Datapublic class ProductVo extends Product { private List images;}

方式1：結果查詢

1. 在ProductsMapper.xml中定義select語句，一次性將關聯數據全部查詢出來，然後進行結果映射

 selectp.id,p.name,p.code,...i.id images_id,i.product_id images_product_id,... from products pinner join product_images i on p.id = i.product_id where p.category_id = #{id} 

1. 定義 productsListMap 結果映射

-->

注意：

• property 就是在 ProductVo 中定義的商品圖片集合 images 字段

• 用到了 columnPrefix 列前綴，只是別名前綴，跟數據庫內的字段無關,這個 images_ 別名前綴跟 select 中定義別名時要保持一致。

• 用到了 extends 繼承已有的 BaseResultMap

  ，不用在這裡再重新寫 Product 表的每個字段的映射了。mapper.xml自動生成工具都會幫我們生成這個 BaseResultMap ，直接繼承即可。

• collection 用於一對多查詢，查詢的結果映射直接複用 ProductImagesMapper 中定義的 BaseResultMap

總結

優點

• 一次性查詢，集中映射，簡單，效率

缺點

• 會將collection中查詢到的條數作為分頁的約束條件,導致分頁數據不準確。

比如想查 page=1,limit=10

原因：

先通過表連接把表記錄關聯進來了，如果有3個商品，關聯圖片表之後每個商品有4條圖片記錄，那麼其實這時候雖然只有三個商品，但是這個內存中的臨時表已經有12條記錄了，在語句的最後加上 limit 0,10，其實分頁的時候分的是這12條記錄。最終就會導致最終的映射結果只出現了3個商品，而非我們期望的10個商品。

方式2：嵌套查詢

1. 在ProductsMapper.xml中定義select語句

 select  from products where category_id = #{id}

1. 定義 productsListMap 結果映射

注意：

• column 是參數傳遞，即將Product的哪個屬性傳遞給嵌套的查詢語句， {productId=id} 代表將Product的 id 屬性傳遞給參數 productId

• select 直接使用 ProductImagesMapper 中定義的select語句

1. 在 ProductImagesMapper 定義 selectByProductId 查詢語句

 SELECT  from product_images where product_id = #{productId}

總結

優點

• 準確分頁

缺點

• 沒有解決N+1的問題，多條SQL查詢語句，效率太差

todo

使用方式1結果查詢，並完成準確分頁。

閱讀更多 菜根譚 的文章

關鍵字: 技術 角色 權限