If you use Spring Data JPA, paging is as simple as passing a Pageable. With MyBatis, though, it’s easy to get stuck wondering, “So how am I supposed to write this?” You’ve probably also heard stories like “I tried RowBounds and it seems to fetch every row” or “I added PageHelper and got tripped up by the configuration.”

There are three main options for paging in MyBatis, and the decisive difference between them is whether the LIMIT is applied on the database side. This article compares the three approaches, hand-written LIMIT/OFFSET, RowBounds, and PageHelper, in terms of the SQL they emit, how you get the total count, and how sorting is handled. Along the way it provides code that works as-is and a set of criteria for choosing between them.

For Mapper definitions and XML basics, see the MyBatis implementation guide. Here we’ll focus on paging. The article assumes Spring Boot 3.x and mybatis-spring-boot-starter 3.x.

A Quick Comparison of the Three Approaches

Here’s the big picture first. Feel free to jump straight to the section you need.

AspectHand-written LIMIT/OFFSETRowBoundsPageHelper
LIMIT on the DB sideYes (you write it)No (skips over results)Yes (rewrites SQL automatically)
Getting the COUNTWrite the COUNT statement yourselfRequires a separate COUNT statementGenerated automatically (customizable)
Extra dependencyNoneNonepagehelper-spring-boot-starter
SortingWrite ORDER BY yourselfWrite ORDER BY yourselfPass as an argument to startPage
DB dialectHandle it yourselfNot dependentHandled via helper-dialect
Best suited forWhen you want full control over the SQLMaster data with a few hundred rowsMany list APIs where you want less boilerplate

To give the conclusion up front: when in doubt, go with hand-written SQL or PageHelper, and don’t use RowBounds for large datasets in production.

Assumptions for the Samples

The example is a list API over an articles table (id, title, category, created_at). If you keep the response DTO in the same shape as the one in the JPA pagination article, the API contract stays the same even if you later swap out the data access layer.

public record Article(Long id, String title, String category, LocalDateTime createdAt) {}

public record PageResponse<T>(List<T> content, int page, int size,
                              long totalElements, int totalPages) {
    public static <T> PageResponse<T> of(List<T> content, int page, int size, long total) {
        int totalPages = size == 0 ? 0 : (int) Math.ceil((double) total / size);
        return new PageResponse<>(content, page, size, total, totalPages);
    }
}

To match the JPA version, page numbers are consistently 0-based.

Approach 1: Hand-Written LIMIT/OFFSET in the Mapper XML

This is the simplest approach: no extra dependencies, and you know exactly what SQL is being emitted. You write two statements, one SELECT and one COUNT.

<select id="findPage" resultType="com.example.Article">
  SELECT id, title, category, created_at
  FROM articles
  ORDER BY ${sort.column} ${sort.direction}
  LIMIT #{size} OFFSET #{offset}
</select>

<select id="count" resultType="long">
  SELECT COUNT(*) FROM articles
</select>

On the Service side, compute the offset from the page number and combine the two results. If you don’t cap size, a request like ?size=100000 effectively becomes a full-table fetch, so clamp it.

@Mapper
public interface ArticleMapper {
    List<Article> findPage(@Param("sort") SortSpec sort,
                           @Param("size") int size, @Param("offset") int offset);
    long count();
}

@Service
public class ArticleService {
    private static final int MAX_SIZE = 100;
    private final ArticleMapper mapper;

    public ArticleService(ArticleMapper mapper) {
        this.mapper = mapper;
    }

    public PageResponse<Article> list(int page, int size, String sort, boolean desc) {
        int safeSize = Math.min(Math.max(size, 1), MAX_SIZE);
        int offset = page * safeSize;
        List<Article> content = mapper.findPage(SortSpec.of(sort, desc), safeSize, offset);
        long total = mapper.count();
        return PageResponse.of(content, page, safeSize, total);
    }
}

LIMIT n OFFSET m works on both PostgreSQL and MySQL. The MySQL-specific LIMIT m, n form hurts portability, so it’s safer to avoid it. On Oracle and SQL Server the equivalent is OFFSET m ROWS FETCH NEXT n ROWS ONLY.

Handle Sort Columns Safely with a Whitelist

Some of you may have noticed the ORDER BY ${sort.column} in the XML above. Because #{} is a bind variable, it would produce the string literal ORDER BY 'title', and the sort would have no effect. Column names have to be expanded as strings with ${}, and if you pass request values straight through, that becomes an entry point for SQL injection.

So define the sortable columns in an enum, and only ever convert request strings to column names by going through that enum.

public enum SortColumn {
    CREATED_AT("createdAt", "created_at"),
    TITLE("title", "title");

    private final String property;
    private final String column;

    SortColumn(String property, String column) {
        this.property = property;
        this.column = column;
    }

    static SortColumn from(String property) {
        return Arrays.stream(values())
            .filter(c -> c.property.equals(property))
            .findFirst()
            .orElseThrow(() -> new ResponseStatusException(
                HttpStatus.BAD_REQUEST, "ソートできない項目です: " + property));
    }
}

public record SortSpec(String column, String direction) {
    static SortSpec of(String property, boolean desc) {
        return new SortSpec(SortColumn.from(property).column, desc ? "DESC" : "ASC");
    }
}

The direction is also converted from a boolean to a fixed string, so every value that flows into ${} is something defined in code. Invalid column names are rejected with a 400. For details on receiving query parameters, see the request parameter binding article.

Approach 2: Why RowBounds Amounts to “In-Memory Paging”

RowBounds is a built-in MyBatis mechanism. You just add it as an argument to the Mapper method, with no XML changes needed.

List<Article> findAll();                     // XMLは ORDER BY だけの普通のSELECT
List<Article> findAll(RowBounds rowBounds);  // 引数に足すだけ

// 呼び出し側
List<Article> content = mapper.findAll(new RowBounds(page * size, size));

It’s convenient, but if you enable SQL logging via mybatis.configuration.log-impl, you’ll see that the emitted statement has no LIMIT on it.

-- RowBounds(20, 10) を渡しても発行されるのはこれ
SELECT id, title, category, created_at FROM articles ORDER BY created_at DESC

MyBatis’s DefaultResultSetHandler skips over the JDBC ResultSet by the offset, maps only limit rows to objects, and discards the rest. From the database’s point of view it’s a query without a LIMIT, so all rows (or successive batches of fetchSize rows) are transferred to the application. That suspicion that “it seems to fetch every row” is correct.

On top of that, RowBounds gives you no way to get the total count, so you end up writing a separate COUNT query anyway. Consider it acceptable only for cases like a category master table with a few hundred rows, where the database load isn’t a concern.

A related setting is safe-row-bounds-enabled. When set to true, combining RowBounds with a nested resultMap (collection or association) raises an error. This prevents row counts from going wrong when row skipping and the assembly of joined results don’t line up. The default is false.

Approach 3: Adding pagehelper-spring-boot-starter

PageHelper is a library that works as a MyBatis plugin, rewriting your SQL and taking care of both LIMIT and COUNT automatically. For Spring Boot 3.x (Jakarta EE), use the 2.x line.

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

The Spring Boot 2.x version is the 1.4.x line. The artifactId is the same and only the version differs, so be careful not to mix them up.

Configuration goes in application.yml. In particular, set helper-dialect explicitly.

pagehelper:
  helper-dialect: postgresql       # DB方言。mysql / oracle / sqlserver / h2 など
  reasonable: false                # trueだと範囲外のページ番号を自動補正する
  support-methods-arguments: false # trueだとMapper引数名 pageNum/pageSize で自動ページング
  params: count=countSql           # 引数名のマッピング。通常はデフォルトのままでOK

If you omit helper-dialect, it’s auto-detected from the JDBC URL, but with multiple data sources or a setup where the URL can’t be used for detection, this fails at startup or on the first query. There’s no downside to being explicit.

The implementation is three steps: PageHelper.startPage(), then the Mapper call, then wrapping in PageInfo. PageHelper’s page numbers are 1-based, so add +1 to align with a 0-based API.

public PageResponse<Article> list(int page, int size, String sort, boolean desc) {
    int safeSize = Math.min(Math.max(size, 1), MAX_SIZE);
    SortSpec spec = SortSpec.of(sort, desc);
    try {
        PageHelper.startPage(page + 1, safeSize, spec.column() + " " + spec.direction());
        List<Article> content = mapper.findAll();   // 直後のこのクエリだけが書き換わる
        PageInfo<Article> info = new PageInfo<>(content);
        return PageResponse.of(info.getList(), page, safeSize, info.getTotal());
    } finally {
        PageHelper.clearPage();
    }
}

Looking at the log, you can see the original SELECT rewritten with a LIMIT, and a COUNT query issued automatically.

SELECT count(0) FROM articles
SELECT id, title, category, created_at FROM articles ORDER BY created_at DESC LIMIT ? OFFSET ?

The third argument, orderBy, is concatenated directly into the SQL, so only pass values that have gone through the same whitelist validation as in Approach 1.

PageHelper Pitfalls

startPage() stores a Page object in a ThreadLocal, and the next Mapper query that executes consumes and clears it. If you don’t know about this “applies only to the very next query” behavior, accidents like the following happen.

// NG: 間に別のクエリを挟むと、そちらにLIMITが付く
PageHelper.startPage(page + 1, size);
if (!categoryMapper.exists(category)) {     // ← このクエリが書き換わる
    throw new NotFoundException();
}
List<Article> content = mapper.findByCategory(category);  // 本命は全件取得になる

// OK: startPage() は本命のMapper呼び出しの直前に書く
if (!categoryMapper.exists(category)) {
    throw new NotFoundException();
}
PageHelper.startPage(page + 1, size);
List<Article> content = mapper.findByCategory(category);

Another scary one is exceptions. If an exception occurs after startPage() but before the Mapper call, the ThreadLocal is left behind. Since Tomcat runs on a thread pool, the next unrelated request handled on that same thread will get a LIMIT attached to its query, a bug that’s very hard to reproduce. This is why the earlier code put PageHelper.clearPage() in a try/finally.

Here are a few more points worth knowing.

  • Setting reasonable to true corrects requests for non-existent page numbers to the last page. If you want the API to return an empty page instead, set it to false.
  • If the automatic COUNT is expensive, define a SQL statement whose id is the Mapper method name with _COUNT appended, and it will take precedence. This is effective for list queries involving JOINs or subqueries.
  • Make the Mapper return type List<T>. If you use Stream or Optional, it won’t be converted to a Page, and PageInfo won’t contain the total count.
<select id="findByCategory_COUNT" resultType="long">
  SELECT COUNT(*) FROM articles WHERE category = #{category}
</select>

Converting to Spring Data’s Pageable and Returning the Same Format as the JPA Version

If you’d like the Controller to accept a Pageable, you can use Pageable and PageImpl without JPA by adding just spring-data-commons as a dependency. spring-boot-starter-data-jpa is not required.

@GetMapping("/api/articles")
public PageResponse<Article> list(
        @PageableDefault(size = 20, sort = "createdAt", direction = Sort.Direction.DESC)
        Pageable pageable) {
    Sort.Order order = pageable.getSort().stream().findFirst()
        .orElse(Sort.Order.desc("createdAt"));
    SortSpec spec = SortSpec.of(order.getProperty(), order.isDescending());
    int size = Math.min(pageable.getPageSize(), MAX_SIZE);
    int offset = pageable.getPageNumber() * size;

    List<Article> content = mapper.findPage(spec, size, offset);
    Page<Article> result = new PageImpl<>(content, pageable, mapper.count());
    return PageResponse.of(result.getContent(), result.getNumber(),
                           result.getSize(), result.getTotalElements());
}

The key point is that the Sort property name is also validated through the same SortColumn before being converted to a column name. With this, the same query parameters as the JPA version, ?page=0&size=20&sort=createdAt,desc, return the same JSON.

You can also return Page<T> directly as JSON, but from Spring Data 3.3 onward the migration to PagedModel is underway, and the format depends on the spring.data.web.pageable.serialization-mode setting. If you want a stable API contract, repackaging into PageResponse is the recommended route. For details on Pageable itself, see the JPA pagination article.

With Large Datasets, Deep OFFSETs Get Slow

All three approaches so far are OFFSET-based. LIMIT 20 OFFSET 100000 means the database reads 100,020 rows from the start and throws them away, so the deeper the page, the slower it gets, linearly. On a table with millions of rows, COUNT(*) is itself an expensive operation.

One countermeasure is to revisit the requirements. Check whether you really need to return the total count every time, or whether hasNext alone would suffice. The other is keyset (cursor) paging, which uses the values from the end of the previous page as a boundary and filters with a WHERE clause.

SELECT id, title, category, created_at
FROM articles
WHERE (created_at, id) < (#{lastCreatedAt}, #{lastId})
ORDER BY created_at DESC, id DESC
LIMIT #{size}

The hand-written approach migrates naturally to this form. PageHelper assumes OFFSET, so once you need keyset paging, the decision becomes to switch just that API over to hand-written SQL. A complete implementation is outside the scope of this article, but keep it in mind as an option.

Criteria for Choosing Between the Three Approaches

CriterionHand-written LIMIT/OFFSETRowBoundsPageHelper
Data volumeNo limitUp to a few hundred rowsNo limit (OFFSET limitations apply to all)
Extra dependencyNoneNoneRequired
Control over SQLFully in your handsIn your handsLeft to the plugin
Multi-DB supportWrite variants yourselfNot neededSwitch via helper-dialect
Team conventionsNothing specialNothing specialMust be able to enforce ThreadLocal rules

If you don’t want extra dependencies, want to fully understand the SQL, and want to leave room to migrate to keyset paging, go hand-written. If you have many list APIs and want to reduce the COUNT+LIMIT boilerplate, PageHelper pays off, but only if the team can stick to the “immediately before” rule for startPage() and always call clearPage(). Limit RowBounds to small master tables where you’d rather not touch the XML.

Note that if you’re using MyBatis-Plus, paging via IPage is the standard there, and it’s cleaner not to mix it with the approaches in this article. If you’re still deciding between MyBatis and JPA in the first place, the MyBatis vs. JPA comparison article will help.

Summary

Here are the three key points to remember about paging in MyBatis.

  • RowBounds does not apply a LIMIT on the database side. It merely skips over results.
  • PageHelper’s startPage() only affects the very next query. Don’t forget clearPage() in case of exceptions.
  • With hand-written SQL, whitelist validation of the column name passed to ORDER BY is mandatory.

If you align the response format with the JPA version, the API contract stays the same even when you swap out the data access layer. Implementations for endpoints beyond the list are covered in the REST API CRUD tutorial, so give that a read as well.