Mastering MySQL Pagination in MyBatis: Limit, RowBounds, and PageHelper

Preface

Pagination is essential when dealing with large data sets; it reduces server load and improves rendering speed. Note: Java's maximum integer is 2147483647, so the LIMIT clause cannot exceed this value, otherwise memory overflow may occur.

1. Limit Pagination

Syntax

limit ${startPos},${pageSize}

In practice, a null‑check is usually added:

<if test="startPos!=null and pageSize!=null">
    limit ${startPos},${pageSize}
</if>

Business Layer Code

<select id="getUserInfo1" parameterType="map" resultType="dayu">
    select * from user
    <if test="startPos!=null and pageSize!=null">
        limit ${startPos},${pageSize}
    </if>
</select>

List<User> getUserInfo1(Map<String,Object> map);

@Test
public void selectUser() {
    SqlSession session = MybatisUtils.getSession();
    UserMapper mapper = session.getMapper(UserMapper.class);
    Map<String,Object> parms = new HashMap<>();
    parms.put("startPos","0");
    parms.put("pageSize","5");
    List<User> users = mapper.getUserInfo1(parms);
    for (User u : users) {
        System.out.println(u);
    }
    session.close();
}

Execution Result

When startPos=0, pageSize=10:

Summary

limit 0,10;

0 means start from the first record.

10 means retrieve ten records.

For the second page use limit 10,10; and so on.

These are standard MySQL pagination commands.

2. RowBounds Pagination (Not Recommended)

RowBounds abstracts the LIMIT clause, allowing pagination logic to be handled in the service layer without passing explicit limits.

However, this is logical pagination: the SQL still retrieves all rows, consuming more memory and potentially causing stale data, so it is discouraged.

offset: starting row number.

limit: number of rows to fetch.

The retrieved data starts from offset+1 and includes limit rows.

Business Layer Code

@Test
public void selectUserRowBounds() {
    SqlSession session = MybatisUtils.getSession();
    UserMapper mapper = session.getMapper(UserMapper.class);
    List<User> users = mapper.getUserInfoRowBounds(new RowBounds(0,5));
    for (User u : users) {
        System.out.println(u);
    }
    session.close();
}

List<User> getUserInfoRowBounds(RowBounds rowBounds);

<select id="getUserInfoRowBounds" resultType="dayu">
    select * from user
</select>

Execution Result

3. MyBatis PageHelper Pagination Plugin

Official GitHub

https://github.com/pagehelper/Mybatis-PageHelper

Dependency

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

MyBatis Core Configuration

<plugins>
    <plugin interceptor="com.github.pagehelper.PageInterceptor"/>
</plugins>

Business Layer Code

@Test
public void selectUserPageHelper() {
    SqlSession session = MybatisUtils.getSession();
    UserMapper mapper = session.getMapper(UserMapper.class);
    PageHelper.startPage(1, 3);
    List<User> list = mapper.getUserInfo();
    PageInfo page = new PageInfo(list);
    for (User u : list) {
        System.out.println(u);
    }
    System.out.println("page:---" + page);
    session.close();
}

Execution Result

Summary

PageHelper provides convenient physical pagination and is widely used; the mapper‑interface style (calling PageHelper.startPage before the mapper method) is the recommended approach.

// Example usages
List<User> list = sqlSession.selectList("x.y.selectIf", null, new RowBounds(0,10));
PageHelper.startPage(1,10);
List<User> list = userMapper.selectIf(1);
PageHelper.offsetPage(1,10);
List<User> list = userMapper.selectIf(1);
Page<User> page = PageHelper.startPage(1,10).doSelectPage(() -> userMapper.selectGroupBy());
long total = PageHelper.count(() -> userMapper.selectLike(user));

These methods simplify pagination handling and provide comprehensive pagination metadata via PageInfo.