Introduction of Mybatis Pagination Methods

Pagination is commonly used in daily development design. Today's tutorial will introduce two methods to help us achieve pagination in Mybatis.

One: RowBounds method inside Mybatis.

There is a default Class in Mybatis package called RowBounds that can help us paginate our pages automatically.

public class RowBounds {
    public static final int NO_ROW_OFFSET = 0;
    public static final int NO_ROW_LIMIT = 2147483647;
    public static final RowBounds DEFAULT = new RowBounds();
    private final int offset;
    private final int limit;

    public RowBounds() {
        this.offset = 0;
        this.limit = 2147483647;
    }

    public RowBounds(int offset, int limit) {
        this.offset = offset;
        this.limit = limit;
    }

    public int getOffset() {
        return this.offset;
    }

    public int getLimit() {
        return this.limit;
    }
}

Here is the RowBounds class. Two parameters are necessary for the constructor. Offset means the start point of our list, and the limit parameter indicates the page size. For instance, {offset = 5, limit = 4} means start from List[4] to List[7].

@Select("SELECT * FROM t_student")
    @ResultMap("studentMap")
    public List<StudentBean> queryPagesByRowBounds(RowBounds rowBounds);

Above is our Mybatis map class. By using RowBounds class, we don't need to add "limit" in our SQL sentence. Mybatis will configure it itself;

 @Override
    public List<StudentBean> queryByRowBounds(int pageNum, int limit) {
//convert page number to start point.
     int offset = (pageNum-1)*limit;
        RowBounds rowBounds = new RowBounds(offset,limit);
        return studentDao.queryPagesByRowBounds(rowBounds);
    }

Therefore, in the service layer, we can directly obtain page numbers and limits. Convert it to a RowBounds object and inject it into the repository layer in parameter. However, it is still inconvenient as we need to get the page count and other information using different code. In Github, some teams developed a plugin called "PageHelper" can help us solve this issue.

Second method: PageHelper

The first step is setting up dependency in pom.

<dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.2.0</version>
        </dependency>

@Bean
    public PageHelper pageHelper(){
        PageHelper pageHelper = new PageHelper();
        Properties pageHelperProperties = new Properties();
        pageHelperProperties.put("helperDialect", "mysql");
        pageHelper.setProperties(pageHelperProperties);
        return pageHelper;
    }

After that, we need to add our pageHelper bean in Java configuration Class. You can also choose to add this in XML file. There are many different properties that can be set up including automatic limit last and start page. For more information, you can refer to Mybatis-PageHelper/HowToUse.md at master · pagehelper/Mybatis-PageHelper · GitHub

sqlSessionFactoryBean.setPlugins(new PageInterceptor());

One crucial point is don't forget setPlugins in sqlSessioFactoryBean configuration.

public List<StudentBean> queryByPageHelper(int pageNum, int pageSize) {
        PageHelper.startPage(pageNum,pageSize);
        PageInfo<StudentBean> page = PageInfo.of(studentDao.queryPagesByPageHelper());
        System.out.println(page);
        return page.getList();
    }

In our service layer, we can initialize the PageHelper directly using static method "startPage". Same as in RowBounds tutorial, we don't need to append "limit" in our SQL query. The pageHelper would process the page number and the page size automatically. The amazing characteristic is that the PageHelper will created a "pageInfo" object, which contains all useful information like page count, page size etc. 

PageInfo{pageNum=2, pageSize=3, size=3, startRow=4, endRow=6, total=10, pages=4, list=Page{count=true, pageNum=2, pageSize=3, startRow=3, endRow=6, total=10, pages=4, reasonable=false, pageSizeZero=false}[StudentBean{studentId=4, studentName='成华小次郎, StudentBean{studentId=5, studentName='陈浩楠, StudentBean{studentId=6, studentName='特朗普], prePage=1, nextPage=3, isFirstPage=false, isLastPage=false, hasPreviousPage=true, hasNextPage=true, navigatePages=8, navigateFirstPage=1, navigateLastPage=4, navigatepageNums=[1, 2, 3, 4]}
Page{count=true, pageNum=2, pageSize=3, startRow=3, endRow=6, total=10, pages=4, reasonable=false, pageSizeZero=false}[StudentBean{studentId=4, studentName='成华小次郎, StudentBean{studentId=5, studentName='陈浩楠, StudentBean{studentId=6, studentName='特朗普]

Above is inside the PageInfo object.

Conclusion

There are two ways for us to do pagination in Java using Mybatis framework. One is the RowBounds class, which is coded inside the Mybatis framework and can process offset point and page size automatically. The other is a developed plugin called pageHelper, which is useful since it can return many detailed information.