Mastering MyBatis-Plus IPage: A Unified Solution for Pagination and Full-Data Export

Classic requirement and common mistakes

Backend list interfaces often need to support both paginated display and full‑data export using the same query conditions. Teams frequently implement two mapper methods—one returning IPage for pagination and another returning List for export—duplicating SQL logic and risking inconsistency when query conditions change.

Setting pageSize to Integer.MAX_VALUE for export forces a massive COUNT query and loads the entire result set into memory, causing severe performance degradation and OOM errors on large tables.

How MyBatis‑Plus IPage works

The pagination capability is provided by PaginationInnerInterceptor. Its execution flow:

Intercept the mapper method and check whether the parameters contain an IPage object.

If it is a pagination request, automatically execute a COUNT query to obtain the total record count.

Append the dialect‑specific pagination clause (e.g., LIMIT offset, size for MySQL) using the current and size values.

Wrap the paginated result and return it.

Key feature: As long as a method parameter includes an IPage , the pagination interceptor triggers, handling COUNT , dialect pagination, and result wrapping automatically.

Typical pitfalls

Using size=MAX_VALUE to simulate full export

Still executes a COUNT query; on a large table the count can take seconds.

Loads tens or hundreds of thousands of rows into JVM memory, causing OOM under moderate concurrency.

Generates a deep pagination clause like LIMIT 0, 100000, whose performance degrades sharply with offset.

Root cause: the interceptor still treats the request as a normal pagination query, merely with a huge page size.

Maintaining two separate methods (pagination & full export)

Any change in query conditions must be applied to both methods, easily leading to mismatched results.

Complex queries increase maintenance cost and constitute classic duplicated‑code smell.

Custom SQL count inaccuracy

Multi‑table joins with one‑to‑many relationships cause row duplication, making the automatically generated COUNT incorrect.

Complex sub‑queries or GROUP BY clauses may cause the interceptor to drop the ORDER BY when generating the count, resulting in syntax errors.

Inconsistent boundary parameter behavior

Passing pageSize=0, negative page numbers, or page numbers beyond the total pages yields different behaviours across MyBatis‑Plus versions (empty result, full result, or exception).

Root cause: different versions implement boundary handling differently, making the “large page size = full export” approach unreliable.

Full export OOM

Exporting all data by loading the entire result set into a List and then writing to Excel exhausts JVM memory when the data volume reaches hundreds of thousands of rows.

Root cause: the same “load‑all‑at‑once” mindset from pagination is applied to export without batch or streaming processing.

Unified single‑SQL solution

Keep a single mapper method that always receives an IPage object. The service layer decides whether to enable pagination, disable COUNT, or switch to full‑export mode by configuring the Page instance.

DTO for unified pagination parameters

@Data
public class PageQueryDTO {
    /** page number, default 1 */
    private Integer pageNum = 1;
    /** page size, default 10 */
    private Integer pageSize = 10;
    /** whether to query total count; true for paginated list, false for export */
    private Boolean searchCount = true;
    /** whether to fetch all records; true skips pagination */
    private Boolean fetchAll = false;
    /** order field */
    private String orderBy;
    /** order direction, asc/desc */
    private String orderDirection = "desc";
}

Smart page builder

public class PageBuilder {
    /** Maximum page size to prevent abuse */
    private static final int MAX_PAGE_SIZE = 1000;

    /** Build a Page object based on the unified DTO. */
    public static <T> Page<T> build(PageQueryDTO query) {
        // 1. Full‑export mode: no pagination, no count
        if (Boolean.TRUE.equals(query.getFetchAll())) {
            Page<T> page = new Page<>();
            page.setSize(-1);               // official "size=-1" means no limit
            page.setSearchCount(false);      // skip count query
            return page;
        }
        // 2. Normal pagination with safety checks
        int pageNum = Optional.ofNullable(query.getPageNum()).orElse(1);
        int pageSize = Optional.ofNullable(query.getPageSize()).orElse(10);
        if (pageNum < 1) pageNum = 1;
        if (pageSize < 1) pageSize = 10;
        if (pageSize > MAX_PAGE_SIZE) pageSize = MAX_PAGE_SIZE;
        Page<T> page = new Page<>(pageNum, pageSize);
        page.setSearchCount(Boolean.TRUE.equals(query.getSearchCount()));
        // 3. Order injection with whitelist check (example uses StrUtil)
        if (StrUtil.isNotBlank(query.getOrderBy())) {
            boolean isAsc = "asc".equalsIgnoreCase(query.getOrderDirection());
            page.addOrder(isAsc ? OrderItem.asc(query.getOrderBy()) : OrderItem.desc(query.getOrderBy()));
        }
        return page;
    }

    /** Build a Page without count (lightweight pagination) */
    public static <T> Page<T> buildNoCount(int pageNum, int pageSize) {
        Page<T> page = new Page<>(pageNum, pageSize);
        page.setSearchCount(false);
        return page;
    }
}

Mapper layer – single method

public interface UserMapper extends BaseMapper<User> {
    /** Complex condition query supporting both pagination and full export. */
    IPage<UserVO> selectUserList(Page<UserVO> page, @Param("query") UserQueryDTO query);
}

XML – one query logic

<select id="selectUserList" resultType="com.example.vo.UserVO">
    SELECT u.id, u.username, u.phone, d.dept_name, u.create_time
    FROM sys_user u
    LEFT JOIN sys_dept d ON u.dept_id = d.id
    <where>
        <if test="query.keyword != null and query.keyword != ''">
            AND u.username LIKE CONCAT('%', #{query.keyword}, '%')
        </if>
        <if test="query.deptId != null">
            AND u.dept_id = #{query.deptId}
        </if>
        <if test="query.status != null">
            AND u.status = #{query.status}
        </if>
    </where>
    <!-- No ORDER BY here; the pagination interceptor will append it -->
</select>

Because the mapper method always receives an IPage, the interceptor automatically handles standard pagination (with count), lightweight pagination (count disabled), and full export (size = -1) without any additional SQL changes.

Handling custom count and deep pagination

Custom count SQL for complex queries

public interface UserMapper extends BaseMapper<User> {
    @Select("selectUserList")
    @ResultMap("UserVOResultMap")
    IPage<UserVO> selectUserList(Page<UserVO> page,
                                 @Param("query") UserQueryDTO query,
                                 @Param("countId") String countId);
}

Corresponding XML adds a dedicated count statement:

<!-- Main query -->
<select id="selectUserList" resultType="com.example.vo.UserVO">
    -- complex multi‑table SQL --
</select>

<!-- Custom count – accurate for one‑to‑many joins -->
<select id="selectUserList_count" resultType="long">
    SELECT COUNT(DISTINCT u.id)
    FROM sys_user u
    LEFT JOIN sys_dept d ON u.dept_id = d.id
    <where>
        -- same filter conditions as main query --
    </where>
</select>

Count optimization configuration

@Configuration
public class MybatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        PaginationInnerInterceptor pagination = new PaginationInnerInterceptor(DbType.MYSQL);
        // Enable join and count SQL optimizations (remove ORDER BY, etc.)
        pagination.setOptimizeJoin(true);
        pagination.setOptimizeCountSql(true);
        // Do not throw exception on overflow; just continue querying
        pagination.setOverflow(false);
        interceptor.addInnerInterceptor(pagination);
        return interceptor;
    }
}

Cursor‑based deep pagination

When the page number becomes large (e.g., page 1000), the traditional LIMIT 100000, 10 scans millions of rows. Cursor pagination replaces the offset with a primary‑key condition:

-- Traditional (slow)
SELECT * FROM sys_user ORDER BY id LIMIT 100000, 10;

-- Cursor pagination (fast)
SELECT * FROM sys_user WHERE id > 100000 ORDER BY id LIMIT 10;

This approach is ideal for infinite scroll, batch sync, or any scenario where total page count is unnecessary.

Large‑scale export with streaming

MyBatis cursor query

public interface UserMapper extends BaseMapper<User> {
    /** Stream all user data for export */
    Cursor<UserVO> selectAllUserCursor(@Param("query") UserQueryDTO query);
}

Service layer – batch write to Excel

@Transactional(readOnly = true)
public void exportAllUser(UserQueryDTO query, HttpServletResponse response) throws IOException {
    try (Cursor<UserVO> cursor = userMapper.selectAllUserCursor(query);
         ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), UserVO.class).build()) {
        WriteSheet sheet = EasyExcel.writerSheet("用户数据").build();
        List<UserVO> batch = new ArrayList<>(1000);
        for (UserVO user : cursor) {
            batch.add(user);
            if (batch.size() >= 1000) {
                excelWriter.write(batch, sheet);
                batch.clear();
            }
        }
        if (!batch.isEmpty()) {
            excelWriter.write(batch, sheet);
        }
    }
}

Key advantage: memory usage stays constant regardless of total rows, allowing million‑row exports without OOM. The cursor keeps the DB connection open, so concurrency must be limited to avoid exhausting connections.

Production‑level best practices

Never use an oversized pageSize to simulate full export; use the official size=-1 flag.

Disable count when it is not needed via setSearchCount(false) to halve query cost.

Validate order‑by fields against a whitelist to prevent SQL injection.

Enforce an upper limit on page size (e.g., 1000) at the API layer.

For multi‑table joins, write a custom COUNT(DISTINCT primary_key) to obtain accurate totals.

Exports exceeding 10 000 rows must use cursor streaming and batch writes.

Full summary

One mapper + one XML eliminates duplicated SQL and guarantees data consistency.

The unified DTO + PageBuilder provides three modes (standard pagination, lightweight pagination, full export) with a single query.

Custom count, cursor pagination, and streaming export cover scenarios from simple lists to massive data dumps.