Using the MyBatis Pagination Plugin PageHelper for Paged Queries in Spring Boot

Preface: This doc uses Spring Boot. For plain Spring you’d also configure the interceptor in the MyBatis config XML. PageHelper is for MyBatis; MyBatis integration isn’t covered here, so integrate MyBatis first.

1. Add the PageHelper Pagination Plugin

There are two ways to add it: import the jar, or build with Maven. This doc uses Maven. If you prefer the jar, download it from:

• https://oss.sonatype.org/content/repositories/releases/com/github/pagehelper/pagehelper/

• http://repo1.maven.org/maven2/com/github/pagehelper/pagehelper/

Add the PageHelper dependency to pom.xml:

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

Since it’s a Spring Boot project, we use pagehelper-spring-boot-starter; for plain Spring use pagehelper.

2. Configure application.yml

# pagination config
pagehelper:
  helper-dialect: mysql
  reasonable: true
  support-methods-arguments: true
  params: count=countSql

3. Use PageHelper in the Service Layer

The simplest paged query, just pass the values in:

public List<Article> selectArticleListByTag(TTag tag, int page, int rows) {
    // use the pagination plugin; the core line is this one — page number, rows per page
    PageHelper.startPage(page, rows);
    // don't add limit in mapper.xml; the plugin intercepts and adds limit automatically
    return articleMapper.selectArticleListByTag(tag);
}

Getting the total count along with pagination, to compute total pages:

Since I use EasyUI, the DataGrid control’s pagination needs the total row count, so this is a classic demo. First define a Page variable (the class is com.github.pagehelper.Page), then again PageHelper.startPage(page, rows), run the normal query, and after the query pages.getTotal() gives the total row count — with that you can compute total pages.

public void selectAllArticle(EasyuiDatagrid easyuiDatagrid, int page, int rows) {
    Page pages = PageHelper.startPage(page, rows);
    easyuiDatagrid.setRows(articleMapper.selectAllArticle());
    easyuiDatagrid.setTotal(pages.getTotal());
}

4. On Abnormal Page Numbers

Some worry: if the passed page number is negative or exceeds the total pages, will it error? Do we need boundary checks?

Actually, if the passed page is negative, PageHelper shows the first page’s data; if it exceeds the total pages, PageHelper shows the last page’s data. It won’t error, so this plugin is carefree — no need to worry about an abnormal page number causing a business exception.

So far, regular needs are covered. For more usage — thread-safe calls, etc. — see the official docs: https://pagehelper.github.io/docs/