Master MySQL Pagination with MyBatis: Limit, RowBounds, and PageHelper

Pagination is essential when dealing with large data sets; fetching all rows at once is impractical, so developers use pagination to reduce server load and improve rendering speed.

Note: Java's maximum integer is 2,147,483,647, so the limit clause cannot exceed this value without risking memory overflow.

1. LIMIT Pagination

SQL syntax: limit ${startPos},${pageSize} In practice, a null‑check is added:

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

Mapper XML example:

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

Java test method:

@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();
}

Result examples:

When using limit 0,10 the first ten rows are returned; limit 10,10 fetches the next page, and so on.

limit 0,10 – start from row 0, fetch 10 rows

limit 10,10 – start from row 10, fetch 10 rows

2. RowBounds Pagination (Not Recommended)

RowBounds abstracts the limit clause, allowing pagination logic to stay in the service layer. It performs logical pagination, meaning the SQL still retrieves all rows, which can consume more memory and may cause stale data.

RowBounds has two properties:

offset – starting row index

limit – number of rows to fetch

Mapper XML (no limit needed):

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

Java test method using RowBounds:

@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();
}

Result screenshot:

3. MyBatis‑PageHelper Plugin

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

Dependency:

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

Configure MyBatis core file:

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

Typical usage examples:

// 1. Direct RowBounds call (not recommended)
List<User> list = sqlSession.selectList("x.y.selectIf", null, new RowBounds(0,10));

// 2. Mapper interface with PageHelper (recommended)
PageHelper.startPage(1,10);
List<User> list = userMapper.selectIf(1);

// 3. Offset pagination
PageHelper.offsetPage(1,10);
List<User> list = userMapper.selectIf(1);

// 4. Pass pageNum and pageSize as method parameters
List<User> list = userMapper.selectByPageNumSize(user,1,10);

// 5. Use fields in the parameter object
List<User> list = userMapper.selectByPageNumSize(user);

// 6. ISelect interface (pre‑Java 8)
Page<User> page = PageHelper.startPage(1,10).doSelectPage(new ISelect() {
    @Override
    public void doSelect() {
        userMapper.selectGroupBy();
    }
});

// Java 8 lambda version
Page<User> page = PageHelper.startPage(1,10).doSelectPage(() -> userMapper.selectGroupBy());

// Count query
long total = PageHelper.count(() -> userMapper.selectLike(user));

Result screenshot of PageHelper pagination:

Summary: PageHelper provides convenient physical pagination and is widely used; most developers prefer the mapper‑interface style calls.