About Mybatis using collection paging

Directory Reason Scenario Scenario 1 Scenario 2 Extension 1. Nested Select Query for Collections 2. Nested Result Mapping Reference for Collections:

The mybatis paging scenario in the project is very high frequency. When using ResultMap and configuring collection for paging, we may encounter the problem of obtaining less data than the size of each page in the current page. This problem is also encountered with PagerHelper plug-in.

Cause

The reason for this problem is that when we use the nested result mapping of ResultMap set to process the result set queried by join and map it to Java entity type, the master data will be less than the data obtained from the database after being mapped and collapsed, thus resulting in less mapped data than the data of per page size.

Program

Option 1

Instead of using the nested result mapping of the collection, the nested select query of the collection is used. Using this scheme requires attention to performance problems, which will lead to "N+1 query problems".

Although this method is simple, it does not perform well on large data sets or large data tables. This problem is called "N+1 query problem". In a nutshell, the N+1 query problem looks like this:

You execute a separate SQL statement to get a list of results (that is, "+1"). For each record returned from the list, you execute an select query statement to load details for each record (that is, "N").

This problem can cause hundreds of SQL statements to be executed. Sometimes, we don't want such consequences.

The good news is that MyBatis can delay loading such queries, so it can spread out the overhead of running a large number of statements at the same time. However, if you load a list of records and iterate through the list immediately to get nested data, all the late load queries will be triggered, and performance may become poor.

Option 2

Remove the collection configuration and process it in the business logic. First, the data involved in paging is obtained, and then the data associated with paging data is obtained in the business code according to the needs. Bloggers prefer this solution to solve the paging problem of collection in mybatis.

Expand

There are two ways to configure loading a 1-to-many relationship in Mybatis:

1. Nested Select queries for collections

1 produces two SQL statements, one for the primary data and one for the associated data. As shown below:

<resultMap id="blogResult" type="Blog">
  <collection property="posts" javaType="ArrayList" column="id" ofType="Post" select="selectPostsForBlog"/>
</resultMap>
 
<select id="selectBlog" resultMap="blogResult">
  SELECT * FROM BLOG WHERE ID = #{id}
</select>
 
<select id="selectPostsForBlog" resultType="Post">
  SELECT * FROM POST WHERE BLOG_ID = #{id}
</select>

2. Nested result mapping of collections

Only one SQL statement will be produced, and the master data and associated data will be configured and mapped to the attributes of their respective objects through aliases. As shown in the play:

<resultMap id="blogResult" type="Blog">
  <id property="id" column="blog_id" />
  <result property="title" column="blog_title"/>
  <collection property="posts" ofType="Post" resultMap="blogPostResult" columnPrefix="post_"/>
</resultMap>
 
<resultMap id="blogPostResult" type="Post">
  <id property="id" column="id"/>
  <result property="subject" column="subject"/>
  <result property="body" column="body"/>
</resultMap>

Reference:

Introduction and use of official Mybatis advanced result mapping ResultMap (including use of collection)

PageHelper Plug-in Important Note (Nested result mapping is not supported by paging plug-ins)

Two Realization Modes of collection Aggregation of mybatis resultMap