SATURN  RING  STATION

At a distant space view post, we chat about codes.

MyBatis default RowBounds logical pagination vs PageHelper plugin physical pagination with comparison and both implementations.

The Story

I was trying to set up back-end pagination on an entity for my RESTful api. I immediately look into MyBatis for the solution. Under the MyBatis APIs, I found out RowBounds for the task. After I look deeply into  MyBatis default RowBounds, I discovered that it’s logical pagination. The outcome is great for my task, my client( a browser api test tool) renders the result around 800ms instead of freezing for 8 sec ( without any pagination).

 

MyBatis RowBounds example

Let’s take a look at the DAO:

@Component
public class EmployeeDAO {

    private final SqlSession sqlSession;

    public EmployeeDAO(SqlSession sqlSession){
        this.sqlSession = sqlSession;
    }

    public List<Employee> selectPaginationEmployees( int offset, int limit) {
        return sqlSession.selectList("com.tom.mappers.EmployeeMapper.employeeList", null, new RowBounds(offset, limit));
    }
}

Note: There is NO overloading for the Object null parameter. Have to keep it there otherwise pagination is skipped.

 

Here is the SQL that  mapped to the employeeList, the table size is 15 mb with 400,000 rows.

        SELECT emp_no, birth_date, first_name, last_name, gender, hire_date, version
        FROM employees ORDER BY emp_no DESC

Connection pool monitoring outcome:

 

 

I checked the SQL that being queried, it’s exactly the same one written in my mapper XML ( the one above). No pagination in query. I searched and dig, here is the source code of MyBatis:

private void handleRowValuesForSimpleResultMap(ResultSetWrapper rsw, ResultMap resultMap, ResultHandler<?> resultHandler, RowBounds rowBounds, ResultMapping parentMapping)  
      throws SQLException {  
    DefaultResultContext<Object> resultContext = new DefaultResultContext<Object>(); 
    skipRows(rsw.getResultSet(), rowBounds);                                                 //  offset method called
    while (shouldProcessMoreRows(resultContext, rowBounds) && rsw.getResultSet().next()) {   //  limit method called
      ResultMap discriminatedResultMap = resolveDiscriminatedResultMap(rsw.getResultSet(), resultMap, null);  
      Object rowValue = getRowValue(rsw, discriminatedResultMap);  
      storeObject(resultHandler, resultContext, rowValue, parentMapping, rsw.getResultSet());  
    }  
  }  
  private boolean shouldProcessMoreRows(ResultContext<?> context, RowBounds rowBounds) throws SQLException {  
    return !context.isStopped() && context.getResultCount() < rowBounds.getLimit();          // check rows if we hit rowBounds.limit, then stop.
  }  
  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());                      // skip rows by rowBounds.offset
      }  
    } else {  
      for (int i = 0; i < rowBounds.getOffset(); i++) {          // skip row one by one till we hit rowBounds.offset
        rs.next(); 
      }  
    }  
  }  
}

Mybatis gets every row of data in a ResultMap, then processes it to fit the pagination requirement.

 

Although it’s alright for my app, we can do better. And in this case, doing better is actually very simple!

 

PageHelper Plugin example

Thank to the newest release of PageHelper Spring Boot support. We can get it going with little effort.

Maven pmo.xml, of course you will need MyBatis Spring Boot starter package also.

	<dependency>
		<groupId>com.github.pagehelper</groupId>
		<artifactId>pagehelper-spring-boot-starter</artifactId>
		<version>1.0.0</version>
	</dependency>

Spring Boot application.properties:

pagehelper.helperDialect=mysql
pagehelper.supportMethodsArguments=true

There are more config options and extra functionalities, check out PageHelper on GitHub!

 

Yep, that’s all the configs and changes I need to get MyBatis utilizing physical pagination with PageHelper. I don’t even need to change single word for my DAO selectList() mehtod invoking RowBounds. It’s a clean upgrade, can’t really get better than this imo. By the way, PageHelper has couple more advanced ways to do pagination, check it out if you’re not satisfied with the default way or limitations of MyBatis RowBounds.

 

 

Let’s check out the result, as promised.

RESTful api request response comparison:

 

Smoking fast, as expected.

 

This is SQL query PageHelper generated:

SELECT emp_no, birth_date, first_name, last_name, gender
	, hire_date, version
FROM employees
ORDER BY emp_no DESC
LIMIT 5000, 200

 

And if you don’t like plugin for some reasons, you can write query with variables for LIMIT.

1 Comment

  • Prachi Singh

    09/18/2019 at 5:58 AM → Reply

    Hi, your post was very enlightening. Currently I’m using simply RowBounds to restrict the number of results that I’m fetching, but I need to use PageHelper plugin to enable Pagination. Though the problem is I’m working on a legacy application Gradle project. The changes you mentioned correspond to a maven project. Do you have any clue how and where to make changes for Gradle project in order to enable the plugin?

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

MyBatis default RowBounds logical pagination vs PageHelper plugin physical pagination with comparison and both implementations.