Mybatis paging

Keywords: Programming Mybatis MySQL SQL Database

Preface

Paging can be said to be a very common function. Most mainstream databases provide physical paging methods, such as Mysql's limit keyword, Oracle's ROWNUM keyword, etc.; as an ORM framework, Mybatis also provides paging functions. Next, the paging functions of Mybatis are introduced in detail.

RowBounds page

1. Introduction to rowbounds

Mybatis provides RowBounds class for paging processing, and internally provides two values, offset and limit, which are used to specify the start position and query data amount of query data respectively:

public class RowBounds {

  public static final int NO_ROW_OFFSET = 0;
  public static final int NO_ROW_LIMIT = Integer.MAX_VALUE;
  public static final RowBounds DEFAULT = new RowBounds();

  private final int offset;
  private final int limit;

  public RowBounds() {
    this.offset = NO_ROW_OFFSET;
    this.limit = NO_ROW_LIMIT;
  }
}

By default, it starts from the 0 subscript, and the query data quantity is integer.max'value. When RowBounds is not specified, RowBounds.DEFAULT:

  public <E> List<E> selectList(String statement, Object parameter) {
    return this.selectList(statement, parameter, RowBounds.DEFAULT);
  }

2. Use of rowbounds

It is also very simple to use. You only need to know the total number of records, then set the number of queries to be queried on each page, calculate the total number of queries, and then specify the subscript through RowBounds. The approximate code is as follows:

    public String rowBounds() {
        int pageSize = 10;
        int totalCount = blogRepository.countBlogs();
        int totalPages = (totalCount % pageSize == 0) ? totalCount / pageSize : totalCount / pageSize + 1;
        System.out.println("[pageSize=" + pageSize + ",totalCount=" + totalCount + ",totalPages=" + totalPages + "]");
        for (int currentPage = 0; currentPage < totalPages; currentPage++) {
            List<Blog> blogs = blogRepository.selectBlogs("zhaohui", new RowBounds(currentPage * pageSize, pageSize));
            System.err.println("currentPage=" + (currentPage + 1) + ",current size:" + blogs.size());
        }
        return "ok";
    }

pageSize: the number of queries per time, the total number of records in totalCount, and the total pages;

3.RowBounds analysis

The related codes for Mybatis to process paging are in DefaultResultSetHandler. Some codes are as follows:

private void handleRowValuesForSimpleResultMap(ResultSetWrapper rsw, ResultMap resultMap, ResultHandler<?> resultHandler, RowBounds rowBounds, ResultMapping parentMapping)
      throws SQLException {
    DefaultResultContext<Object> resultContext = new DefaultResultContext<>();
    ResultSet resultSet = rsw.getResultSet();
    //Skip the specified subscript Offset
    skipRows(resultSet, rowBounds);
    ////Determine whether the current read is within the limit
    while (shouldProcessMoreRows(resultContext, rowBounds) && !resultSet.isClosed() && resultSet.next()) {
      ResultMap discriminatedResultMap = resolveDiscriminatedResultMap(resultSet, resultMap, null);
      Object rowValue = getRowValue(rsw, discriminatedResultMap, null);
      storeObject(resultHandler, resultContext, rowValue, parentMapping, resultSet);
    }
  }
  
  //Skip the specified subscript Offset
  private void skipRows(ResultSet rs, RowBounds rowBounds) throws SQLException {
    if (rs.getType() != ResultSet.TYPE_FORWARD_ONLY) {
      if (rowBounds.getOffset() != RowBounds.NO_ROW_OFFSET) {
        rs.absolute(rowBounds.getOffset());
      }
    } else {
      for (int i = 0; i < rowBounds.getOffset(); i++) {
        if (!rs.next()) {
          break;
        }
      }
    }
  }
  
  //Determine whether the current read is within the limit
  private boolean shouldProcessMoreRows(ResultContext<?> context, RowBounds rowBounds) {
    return !context.isStopped() && context.getResultCount() < rowBounds.getLimit();
  }

To process a ResultSet, you first need to skip the specified subscript Offset. Here, the skip method is divided into two situations: the resultSetType is of type ﹣ forward ﹣ only and the resultSetType is of non type ﹣ forward ﹣ only type. Mybatis also provides type configuration. The options include:

  • Forward? Only: can only scroll forward;
  • SCROLL_SENSITIVE: it can realize arbitrary back and forth scrolling and is sensitive to modification;
  • Scroll ﹣ innovative: it can realize any forward and backward rolling, and is sensitive to repair;
  • DEFAULT: the DEFAULT value is forward only;

When the type is forward only, you can only traverse to the specified subscript. The other two types can be directly located to the specified subscript through the absolute method. You can specify the type as follows:

    <select id="selectBlogs" parameterType="string" resultType="blog" resultSetType="SCROLL_INSENSITIVE ">
        select * from blog where author = #{author}
    </select>

Limit limit: record the current number of records read through the resultCount recorded in the ResultContext, and then determine whether the limit limit has been reached;

Pagehelper paging

In addition to the officially provided RowBounds paging mode, the third-party plug-in Pagehelper is more commonly used. Why there is an officially provided paging mode, there is also a third-party plug-in such as Pagehelper. The main reason is that RowBounds provides logical paging, while Pagehelper provides physical paging;

1.Pagehelper

Pagehelper mainly uses Mybatis plug-in Function, so you need to first configure the plug-in class PageInterceptor:

        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <property name="helperDialect" value="mysql" />
        </plugin>

helperDialect is used to specify which dialect. Here, mysql is used for testing. For more detailed parameter configuration, please refer to the official documents: Mybatis-PageHelper How to call it? Mybatis PageHelper also provides several ways. Here, we use RowBounds to call it. The specific code is exactly the same as the above instance code, but because of the existence of the plug-in, its implementation mode has changed;

2.Pagehelper analysis

Pagehelper intercepts the query method of Executor as follows:

@Intercepts(
        {
                @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
                @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
        }
)
public class PageInterceptor implements Interceptor {
}

Above Analysis of Mybatis plug-in The plug-in uses the dynamic proxy technology. When executing the query method of the Executor, it will automatically trigger the invoke method of the InvocationHandler. The method will call the intercept method of PageInterceptor

public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
    try {
      Set<Method> methods = signatureMap.get(method.getDeclaringClass());
      if (methods != null && methods.contains(method)) {
        return interceptor.intercept(new Invocation(target, method, args));
      }
      return method.invoke(target, args);
    } catch (Exception e) {
      throw ExceptionUtil.unwrapThrowable(e);
    }
  }

You can see that the args (4 or 6) of the final query are encapsulated in the Invocation, including the RowBounds class for paging; Pagehelper will map the offset and limit in RowBounds to the more powerful Page class, and Page contains many attributes. Here's a brief look at the RowBounds related parameters:

    public Page(int[] rowBounds, boolean count) {
        super(0);
        if (rowBounds[0] == 0 && rowBounds[1] == Integer.MAX_VALUE) {
            pageSizeZero = true;
            this.pageSize = 0;
        } else {
            this.pageSize = rowBounds[1];
            this.pageNum = rowBounds[1] != 0 ? (int) (Math.ceil(((double) rowBounds[0] + rowBounds[1]) / rowBounds[1])) : 0;
        }
        this.startRow = rowBounds[0];
        this.count = count;
        this.endRow = this.startRow + rowBounds[1];
    }

offset is mapped to startRow, and limit is mapped to pageSize; with parameters related to paging, different dialects are generated to generate sql through the configured database dialect type. For example, Mysql provides the Mysql rowboundsdialect class:

public String getPageSql(String sql, RowBounds rowBounds, CacheKey pageKey) {
        StringBuilder sqlBuilder = new StringBuilder(sql.length() + 14);
        sqlBuilder.append(sql);
        if (rowBounds.getOffset() == 0) {
            sqlBuilder.append(" LIMIT ");
            sqlBuilder.append(rowBounds.getLimit());
        } else {
            sqlBuilder.append(" LIMIT ");
            sqlBuilder.append(rowBounds.getOffset());
            sqlBuilder.append(",");
            sqlBuilder.append(rowBounds.getLimit());
            pageKey.update(rowBounds.getOffset());
        }
        pageKey.update(rowBounds.getLimit());
        return sqlBuilder.toString();
    }

The physical paging keyword of mysql is limit, which can be realized by providing offset and limit;

Performance comparison

RowBounds uses logical paging, while Pagehelper uses physical paging;
Logical paging: the advantage of using cursor paging is that all databases are unified, and the disadvantage is low efficiency. The next() method can be used to point to the next record because the ResultSet has a cursor. Of course, the scrollable resultsets can also be used to quickly locate the record row specified by a cursor , the absolute() method of ResultSet is used;
Physical paging: the database itself provides paging mode, such as mysql limit. The advantage is high efficiency. The disadvantage is that different databases have different paging modes, and each database needs to be paged separately;

Next, we test 100 pieces of data queried by three paging methods: logical paging forward, logical paging forward and backward, and physical paging. We use druid to monitor and use mysql as the database;

1. Logical paging scrolls forward

Because you can only scroll forward, the more pages you move to the back, the more data you traverse. The monitoring is as follows:

Although there are only 100 pieces of data, the read data is 550 lines and the performance is poor;

2. Scroll before and after logical paging

The resultSetType configured here is scroll ﹣ innovative, which can be located quickly. The monitoring is as follows:

3. Physical paging

Configure and use the Pagehelper plug-in. The monitoring is as follows:

It can be seen that physical paging is more advantageous in execution time and number of read rows;

summary

This paper introduces two paging methods, RowBounds and Pagehelper, which represent logical paging and physical paging respectively, and how these two methods are implemented internally. Finally, a simple performance test is done.

Sample code

Github

Posted by pankirk on Tue, 10 Dec 2019 11:49:56 -0800