5 Ways to Implement Pagination in MyBatis: From Manual SQL to PageHelper

Pagination Principles

MyBatis supports two core pagination modes: logical (memory) pagination, which retrieves all matching rows from the database and then selects a subset in application memory, and physical pagination, which lets the database return only the required rows via SQL limits, offering far better performance.

Implementation Methods

1. Handwritten SQL with LIMIT

<select id="selectByPage" resultType="YourEntity">
  SELECT * FROM table
  LIMIT #{offset}, #{pageSize}
</select>

Pros: simple and direct, no extra configuration. Cons: tightly couples SQL to a specific database, requires repetitive LIMIT clauses, and increases maintenance when switching databases.

2. RowBounds

RowBounds rowBounds = new RowBounds(offset, limit);
List<YourEntity> list = sqlSession.selectList("userMapper.selectAll", null, rowBounds);

RowBounds still executes a full SELECT * query, loads all rows into memory, and then slices the result, which can cause OOM for large data sets. It is considered a logical pagination approach and is deprecated after MyBatis 3.4.0.

3. Custom SQL (e.g., Window Functions)

<select id="selectByPage" resultType="UserEntity">
  SELECT * FROM (
    SELECT name, ROW_NUMBER() OVER(ORDER BY className) AS rownum
    FROM user
  ) WHERE rownum BETWEEN #{startRow} AND #{endRow}
</select>

Use this when you need more complex pagination logic or want to leverage advanced database features such as window functions.

4. Interceptor‑Based Pagination

An interceptor can modify any incoming SQL statement before execution, appending a physical LIMIT clause (e.g., LIMIT 0,10). This provides high‑performance physical pagination while keeping pagination logic separate from business code, but requires writing a robust interceptor, raising the development barrier.

5. Mature Pagination Plugin (PageHelper)

PageHelper.startPage(pageNum, pageSize);
List<YourEntity> list = sqlSession.selectList("UserMapper.selectAll");

PageHelper injects the appropriate LIMIT clause via an internal interceptor, making pagination zero‑intrusive for business code, delivering excellent performance, and greatly improving developer productivity.

Conclusion

For most MyBatis projects, the recommended approach is to use a physical pagination plugin such as PageHelper because it is simple, efficient, and requires minimal code changes. RowBounds may be suitable for small‑scale quick prototypes, while handwritten SQL offers maximum flexibility for special requirements.