pagehelper-org/Mybatis-PageHelper · error · PageException

The order by in the original SQL

Error message

The order by in the original SQL[${sql}] contains parameters, so it cannot be modified using the OrderBy plugin!

What it means

Thrown when PageHelper's OrderBy plugin rewrites the outer ORDER BY of the SQL and finds a '?' placeholder in the parsed order-by clause. Since the plugin rebuilds the SQL string without binding the original order-by parameters, it refuses to modify such SQL.

Solutions

Remove parameters from the ORDER BY clause of the base SQL and supply sort columns via PageHelper.orderBy instead
Use ${} substitution for trusted sort columns instead of ? in the ORDER BY clause
Keep orderBy parameters out of the outer-most SELECT; move sorting into the caller-supplied orderBy string

Example fix

// before
String sql = "SELECT * FROM user ORDER BY ?";
// after
String sql = "SELECT * FROM user";
PageHelper.orderBy("create_time desc");

Defensive patterns

Strategy: validation

Validate before calling

String upper = sql.toUpperCase();
int ob = upper.lastIndexOf(" ORDER BY ");
if (ob != -1 && sql.substring(ob).contains("?")) {
    throw new IllegalArgumentException("ORDER BY contains ?; move sorting to PageHelper.orderBy");
}

Try / catch

try { PageHelper.orderBy("col desc"); } catch (PageException e) {
    log.warn("order-by rewrite failed, falling back to raw sql", e);
}

Prevention

Never parameterize the ORDER BY clause with ? in SQL handed to the OrderBy plugin
Pass sort criteria via PageHelper.orderBy rather than embedding in SQL
Sanitize/whitelist sort column names instead of using bind parameters
Watch the plugin's 'Failed to handle sorting' warnings in logs

When it happens

Trigger: Calling PageHelper.orderBy/startPage with orderBy on SQL whose ORDER BY clause already contains JDBC parameter placeholders, e.g. 'SELECT * FROM t ORDER BY ?' or parameters inside expressions in the order-by.

Common situations: Dynamically generated SQL with parameterized sort expressions; user code passing SQL with order-by placeholders through the OrderBy plugin; stored SQL fragments using ? in ORDER BY for sort direction/columns.

Understand the failure class

Background: UnsupportedOperationException and "is not supported" errors: when a library deliberately refuses a call — this error's family across 30 libraries.

Related errors

AI-assisted analysis of pagehelper-org/Mybatis-PageHelper@c692616c5b (2026-09-08). Data as JSON: /api/errors/9e11c88b7c628a89. Report an issue: GitHub.

Appendix: source

Thrown at src/main/java/com/github/pagehelper/parser/defaults/DefaultOrderBySqlParser.java:64

    /**
     * convert to order by sql
     *
     * @param sql
     * @param orderBy
     * @return
     */
    @Override
    public String converToOrderBySql(String sql, String orderBy) {
        //解析SQL
        Statement stmt = null;
        try {
            stmt = SqlParserUtil.parse(sql);
            Select select = (Select) stmt;
            //处理body-去最外层order by
            List<OrderByElement> orderByElements = extraOrderBy(select);
            String defaultOrderBy = PlainSelect.orderByToString(orderByElements);
            if (defaultOrderBy.indexOf('?') != -1) {
                throw new PageException("The order by in the original SQL[" + sql + "] contains parameters, so it cannot be modified using the OrderBy plugin!");
            }
            //新的sql
            sql = select.toString();
        } catch (Throwable e) {
            log.warn("Failed to handle sorting: " + e + ", downgraded to a direct splice of the order by parameter");
        }
        return sql + " order by " + orderBy;
    }

    /**
     * extra order by and set default orderby to null
     *
     * @param select
     */
    public static List<OrderByElement> extraOrderBy(Select select) {
        if (select != null) {
            if (select instanceof PlainSelect || select instanceof SetOperationList) {
                List<OrderByElement> orderByElements = select.getOrderByElements();

View on GitHub (pinned to c692616c5b)