Supercharging MyBatis Pagination with Druid’s PagerUtils and Custom Plugins

1. Introduction

A legacy system's growing data volume slowed down pagination queries, prompting a switch from

pagehelper

and

mybatis-plus

to direct use of the Druid connection pool and its pagination utilities.

2. Old Code

The original pagination relied on a MyBatis plugin, with the core count‑SQL generation looking like:

<code>// record‑count SQL
String countSql = "select count(0) from (" + sql + ")  tmp_count";
PreparedStatement countStmt = connection.prepareStatement(countSql);
BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), parameterObject);
setParameters(countStmt, mappedStatement, countBS, parameterObject);
</code>

This pattern wraps the original SQL in a

SELECT COUNT(0) FROM (...)

sub‑query, a common approach among pagination plugins.

3. Druid’s PagerUtil

Example SQL (with complex coordinate calculations)

<code>SELECT g.*,
       ROUND(6378.138 * 2 * ASIN(SQRT(POW(SIN((? * PI() / 180 - t.latitude * PI() / 180) / 2), 2) + COS(? * PI() / 180) * COS(t.latitude * PI() / 180) * POW(SIN((? * PI() / 180 - t.longitude * PI() / 180) / 2), 2))), 2) AS distancecd,
       t.agentname, t.agentlogo, t.compaddress
FROM t_bas_integral_goods g
LEFT JOIN t_bas_agent t ON g.agentid = t.AGENTID
WHERE t.AGENTTYPE = '2'
  AND t.pass = '0'
  AND t.dl_type = '4'
  AND g.type = 0
ORDER BY distancecd ASC;
</code>

Generating a concise count SQL with Druid

<code>String countSql = PagerUtils.count(sql, DbType.mysql);
System.out.println(countSql);
</code>

The output is a clean

SELECT COUNT(*) FROM …

statement that discards the heavy coordinate calculations.

Note that

PagerUtil

and its

limit

method can also generate pagination

LIMIT

clauses for further testing.

4. Refactoring the Pagination Plugin

4.1 Pitfalls

After replacing the count SQL generation, an error appeared because the original SQL contained

?

placeholders, which were removed in the optimized version while the plugin still attempted to set them. The solution required handling MyBatis placeholders (

#{xxx}

) correctly.

Both

pagehelper

and

mybatis-plus

rely on

jsqlparser

for SQL parsing. Instead of adding another parser, the author leveraged Druid’s built‑in SQL parser via

PagerUtils.count

.

4.2 Extending MyBatis

To retain the original MyBatis placeholders, a custom

MicaRawSqlSource

was created, exposing a

rawSql

field that preserves

#{}

parameters.

<code>/**
 * Original SQL for convenient Druid pagination
 */
public class MicaRawSqlSource implements SqlSource {
    private final String rawSql;
    private final SqlSource sqlSource;

    public MicaRawSqlSource(Configuration configuration, SqlNode rootSqlNode, Class<?> parameterType) {
        this(configuration, getSql(configuration, rootSqlNode), parameterType);
    }

    public MicaRawSqlSource(Configuration configuration, String sql, Class<?> parameterType) {
        SqlSourceBuilder sqlSourceParser = new SqlSourceBuilder(configuration);
        Class<?> clazz = parameterType == null ? Object.class : parameterType;
        this.rawSql = sql;
        this.sqlSource = sqlSourceParser.parse(sql, clazz, new HashMap<>());
    }
    // ... getters omitted
}
</code>

The core

PagePlugin

now handles three cases:

<code>// 1. No '?' placeholder – simple count generation
if (boundRawSql.indexOf('?') == -1) {
    String countSql = PagerUtils.count(boundRawSql, dbType);
    // parse, execute, and build page SQL
}
// 2. DynamicSqlSource – parse dynamic SQL, generate count with placeholders
else if (sqlSource instanceof DynamicSqlSource) {
    // build context, generate count, execute
}
// 3. MicaRawSqlSource – use rawSql directly for count generation
else if (sqlSource instanceof MicaRawSqlSource) {
    String rawSql = ((MicaRawSqlSource) sqlSource).getRawSql();
    String countSql = PagerUtils.count(rawSql, dbType);
    // parse, execute, etc.
}
else {
    throw new IllegalArgumentException("Unsupported SQL pagination form, please use XML or annotation");
}
</code>

5. Conclusion

Switching the legacy service to the deeply customized Mica micro‑service architecture dramatically improved query speed, even on a low‑memory single‑service deployment. The author invites readers to try the open‑source Mica version at https://gitee.com/596392912/mica and contribute.