前言

pageHelper是一款優秀的Mybatis分頁插件，在項目中可以非常便利的使用，使開發效率得到很大的提升，但不支援一對多結果映射的分頁查詢，是以在平時的使用時，對于一對多分頁會出現分頁錯誤，這篇文章主要對pageHelper分頁錯誤進行重制以及提出解決方案。

分析

mybatis進行一對多查詢時，映射檔案（mapper.xml)中的sql語句中使用的左連接配接，pageHelper會自動對這條左連接配接sql語句進行select count（0）的處理，并把結果作為分頁結構的記錄總數，然後自動将limit拼接到sql語句末尾進行分頁，由于左連接配接查詢時，連接配接條件on條件不唯一（即一對多）時，結果會産生笛卡爾積，是以經過pagehelper插件分頁得到的記錄總數和分頁結果并不是預期的結果。

資料準備

共兩個表：user、address,使用者id與收貨位址表中userId對應。

使用者表【user】:11條資料

pageHelper分頁失效解決方案前言分析資料準備資料結構預期結果問題重制解決方案

收貨位址資訊表【address】：4條資料

資料結構

public class UserDto {
    public int id;
    public String name;
    List<Address> addressList;
}

複制

預期結果

要求對資料進行分頁（每頁5條），獲得使用者資訊，每個使用者資訊帶出對應收貨資訊，使用者id為2和3的使用者各有兩條收貨位址資訊，其餘沒有。期望結果如下

{
  "code": 200,
  "message": "success",
  "data": {
    "pageNum": 1,
    "pageSize": 5,
    "pages": 3,
    "size": 5,
    "total": 11,
    "data": [
      {
        "id": 1,
        "name": "張三",
        "addressList": []
      },
      {
        "id": 2,
        "name": "李四",
        "addressList": [
          {
            "id": 1,
            "address": "陝西省寶雞市",
            "userId": 2
          },
          {
            "id": 2,
            "address": "陝西省延安市",
            "userId": 2
          }
        ]
      },
      {
        "id": 3,
        "name": "王五",
        "addressList": [
          {
            "id": 3,
            "address": "陝西省西安市",
            "userId": 3
          },
          {
            "id": 4,
            "address": "陝西省漢中市",
            "userId": 3
          }
        ]
      },
      {
        "id": 4,
        "name": "錢六",
        "addressList": []
      },
      {
        "id": 5,
        "name": "劉七",
        "addressList": []
      }
    ]
  }
}

複制

問題重制

mybatis映射檔案

<resultMap id="list" type="UserDto">
    <id property="id" column="id" />
    <result property="name" column="name"/>
    <collection property="addressList" ofType="Address">
        <result property="address" column="address"/>
        <result property="userId" column="userId"/>
    </collection>
</resultMap>
 
<select id="findAll"  resultMap="list" >
    SELECT
    a.*,b.address,b.userId
    FROM user a
    LEFT JOIN address b on a.id=b.userId
</select>

複制

然後我們使用pageHelper進行分頁，并輸出日志

SELECT count(0) FROM user a LEFT JOIN address b ON a.id = b.userId
Preparing: SELECT a.*,b.address,b.userId FROM user a LEFT JOIN address b on a.id=b.userId LIMIT ?
Parameters: 5(Integer)
Total: 5

複制

日志分析

第1行：進行資料總數的查詢，作為資料的總條數total

第2-4行：進行分頁結果的查詢，查詢出5條資料

從日志中可以看出

1. pageHelper插件拼接後的sql語句就不會輸出正确的結果，更不會輸出符合期望的結果

2. pageHelper插件分兩步查詢，第一步查詢出記錄總數，第二步查詢出分頁結果

解決方案

方案一

思路：先分頁查詢出user表資料，然後在serviec服務層根據使用者id查詢對應的收貨位址資訊，并關聯使用者資訊與收貨資訊。

service檔案

public List<UserDto> findAll(){
    List<UserDto> userList=userMapper.findUser();
    userList.forEach((item)-> {
    item.setAddressList(userMapper.findByUserId(item.id));
    });
    return userList;
}

複制

mybatis映射檔案

<select id="findUser" resultType="UserDto">
    SELECT * FROM user
</select>
<select id="findByUserId" parameterType="integer" resultType="Address">
    SELECT * FROM address where userId=#{userId}
</select>

複制

方案二

思路：使用mybatis的嵌套子查詢

<resultMap id="getList" type="UserDto">
    <id property="id" column="id" />
    <result property="name" column="name"/>
    <collection property="addressList" ofType="Address" javaType="List" column="{userId=id}" select="getValueById" >
        <id property="id" column="id" />
        <result property="address" column="address"/>
        <result property="userId" column="userId"/>
    </collection>
</resultMap>
<!-- 主查詢 -->
<select id="findAll" resultMap="getList">
    select * from user
</select>
<!-- 子查詢 -->
<select id="getValueById" resultType="Address" >
    select a.* from address a where a.userId=#{userId}
</select>

複制

與嵌套映射結構的resultMap格式基本一緻，一對多查詢采用的依舊是collection，差別在于collection中多了select與column屬性，select用于加載子查詢映射語句的id，它會從column屬性指定的列中檢索資料，作為參數傳遞給目标select語句即子查詢。

缺點：這種方式雖然可以解決pagehelper一對多分頁的問題，但在大型資料表與資料集上性能表現不佳，即産生'1+N'問題。

輸出以下sql日志：首先通過主查詢語句獲得主表的資料總量作為分頁的total，第二步通過limit獲得前5條分頁資料（就是‘1’），第三步将第二步獲得結果作為參數通過子查詢獲得位址表的資訊（就是‘N’）

Preparing: SELECT count(0) FROM user
Parameters: 
Total: 1
Preparing: select * from user LIMIT ?
Parameters: 5(Integer)
Preparing: select a.* from address a where a.userId=?
Parameters: 1(Integer)
Total: 0
Preparing: select a.* from address a where a.userId=?
Parameters: 2(Integer)
Total: 2
Preparing: select a.* from address a where a.userId=?
Parameters: 3(Integer)
Total: 2
Preparing: select a.* from address a where a.userId=?
Parameters: 4(Integer)
Total: 0
Preparing: select a.* from address a where a.userId=?
Parameters: 5(Integer)
Total: 0

複制

方案三

思路：棄用pageHelper插件，自定義分頁查詢，先對主表（user）進行分頁，并把分頁結果作為虛拟表與副表（address）進行左連接配接查詢

<resultMap id="list" type="UserDto">
        <id property="id" column="id" />
        <result property="name" column="name"/>
        <collection property="addressList" ofType="Address">
            <result property="address" column="address"/>
            <result property="userId" column="userId"/>
        </collection>
 </resultMap>
<select id="findAll" resultMap="list"  parameterType="integer">
    SELECT
        a.*,
        b.address,
        b.userId
    FROM
            ( SELECT * FROM user LIMIT #{size} ) a
                LEFT JOIN address b ON a.id = b.userid
</select>

複制

pageHelper分頁失效解決方案前言分析資料準備資料結構預期結果問題重制解決方案

前言

分析

資料準備

資料結構

預期結果

問題重制

解決方案

方案一

方案二

方案三