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:

// 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);

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)

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;

Generating a concise count SQL with Druid

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

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.

/**
 * 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
}

The core PagePlugin now handles three cases:

// 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");
}

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.