Druid在SQL格式化输出时,使用MySQL格式输出,会将limit n offset m修改为limit m, n

select * from foobar where id > 1 limit 10 offset 0;

会变成,

select * from foobar where id > 1 limit 0, 10;

在limit offset中传入具体值时,这种改写不会带来问题,但是如果传入的是占位通配符,则对应参数的顺序就进行了调整,

select * from foobar where id > 1 limit ? offset ?;

会变成,

select * from foobar where id > 1 limit ?, ?;

这个时候通过PreparedStatement进行参数设置的时候,参数就会传反。

为了应对这种情况,需要重写格式化输出部分,将limit子句的格式化输出方式进行调整,

public class CustomSqlVisitor extends MySqlOutputVisitor {

    public CustomSqlVisitor(Appendable appender) {
        super(appender);
    }

    public boolean visit(SQLLimit x) {
        this.print0(this.ucase ? "LIMIT " : "limit ");
        SQLExpr rowCount = x.getRowCount();
        this.printExpr(rowCount);
        SQLExpr offset = x.getOffset();
        if (offset != null) {
            this.print0(this.ucase ? " OFFSET " : " offset ");
            this.printExpr(offset);
        }
        return false;
    }
}

最后再实现类似SQLUtils.toSQLString方法,将其中的Visitor实现替换成上面自定义的CustomSqlVisitor,如此limit重写问题就能得到解决,

public static String toSQLString(SQLObject sqlObject, String dbType, SQLUtils.FormatOption option) {
    StringBuilder out = new StringBuilder();
    SQLASTOutputVisitor visitor = new CustomSqlVisitor(out);
    if (option == null) {
        option = DEFAULT_FORMAT_OPTION;
    }

    visitor.setUppCase(option.isUppCase());
    visitor.setPrettyFormat(option.isPrettyFormat());
    visitor.setParameterized(option.isParameterized());
    visitor.setFeatures(option.features);
    sqlObject.accept(visitor);
    return out.toString();
}