1. Preface

One to many paging of MySQL data is a very common requirement. For example, we need to query the product and the image information of the product. But many people will encounter the mistake of pagination here and get incorrect results. Let's analyze and solve this problem today.

2. Problem analysis

First, we create a simple product table and a corresponding product image relationship table, which are one to many relationships:

Then I wrote the pictures of some commodities and corresponding commodities respectively. Through the following left connection query, we can see that there is an obvious one to many relationship between them:

SELECT P.PRODUCT_ID, P.PROD_NAME, PI.IMAGE_URL
FROM PRODUCT_INFO P
         LEFT JOIN PRODUCT_IMAGE PI
                   ON P.PRODUCT_ID = PI.PRODUCT_ID

According to the traditional thinking, our pagination statement will read as follows:

    <resultMap id="ProductDTO" type="cn.felord.mybatis.entity.ProductDTO">
        <id property="productId" column="product_id"/>
        <result property="prodName" column="prod_name"/>
        <collection property="imageUrls"  ofType="string">
            <result column="image_url"/>
        </collection>
    </resultMap>

    <select id="page" resultMap="ProductDTO">
        SELECT P.PRODUCT_ID, P.PROD_NAME,PI.IMAGE_URL
        FROM PRODUCT_INFO P
                 LEFT JOIN PRODUCT_IMAGE PI
                           ON P.PRODUCT_ID = PI.PRODUCT_ID
        LIMIT #{current},#{size}
    </select>               

When I passed in (0, 2) data of the first two products as expected, the result was not what I expected:

2020-06-21 23:35:54.515 DEBUG 10980 --- [main] c.f.m.mappers.ProductInfoMapper.page     : ==>  Preparing: SELECT P.PRODUCT_ID, P.PROD_NAME,PI.IMAGE_URL FROM PRODUCT_INFO P LEFT JOIN PRODUCT_IMAGE PI ON P.PRODUCT_ID = PI.PRODUCT_ID limit ?,? 
2020-06-21 23:35:54.541 DEBUG 10980 --- [main] c.f.m.mappers.ProductInfoMapper.page     : ==> Parameters: 0(Long), 2(Long)
2020-06-21 23:35:54.565 DEBUG 10980 --- [main] c.f.m.mappers.ProductInfoMapper.page     : <==      Total: 2
page = [ProductDTO{productId=1, prodName='glass', imageUrls=[http://asset.felord.cn/cup1.png, http://asset.felord.cn/cup2.png]}]

Two pieces of data I expected were cups and laptops, but only one. It turns out that when one to many mapping is performed, the result set is output according to the multiple side (expect 4 pieces of data, actually 7 pieces). The first two pieces only show the data of the cup (as shown in the figure above). After merging, there is only one result, so the paging cannot be matched. So how can we achieve the desired paging effect?

3. The right way

Regardless of the framework, our SQL should first query the product table in pages and then query the left associated picture table:

SELECT P.PRODUCT_ID, P.PROD_NAME, PI.IMAGE_URL
FROM (SELECT PRODUCT_ID, PROD_NAME
      FROM PRODUCT_INFO
      LIMIT #{current},#{size}) P
         LEFT JOIN PRODUCT_IMAGE PI
                   ON P.PRODUCT_ID = PI.PRODUCT_ID

The advantage of this writing method is that it is more general. But MyBatis offers a relatively elegant way of thinking, which is still what was said at the beginning. But we need to modify the above Mybatis XML configuration:

<resultMap id="ProductDTO" type="cn.felord.mybatis.entity.ProductDTO">
    <id property="productId" column="product_id"/>
    <result property="prodName" column="prod_name"/>
     <!-- utilize collection Provided by label select Characteristics and column   -->
    <collection property="imageUrls" ofType="string" select="selectImagesByProductId" column="product_id"/>
</resultMap>
<!-- Query the paging data of the main table first    -->
<select id="page" resultMap="ProductDTO">
    SELECT PRODUCT_ID, PROD_NAME
    FROM PRODUCT_INFO
    LIMIT #{current},#{size}
</select>
<!--according to productId Query the corresponding picture-->
<select id="selectImagesByProductId" resultType="string">
    SELECT IMAGE_URL
    FROM PRODUCT_IMAGE
    WHERE PRODUCT_ID = #{productId}
</select>

4. Summary

Paging is easy in most cases, but there are some small pitfalls in one to many. Once we understand the mechanism, it is not difficult to solve. Of course, if you have a better solution, you can leave a message to discuss and brainstorm. Pay more attention to: small fat brother, get more development skills.

Pay attention to the official account: Felordcn for more information

Personal blog: https://felord.cn