博客信息

mysql 分页 count 去除 order by

发布时间:『 2019-04-26 14:49:15』  博客类别:  阅读(55) 评论(0)

使用阿里Druid 的 AST 

public static String removeOrderBySql(String sql) {
    SQLStatementParser parser = new MySqlStatementParser(sql);
    // 使用Parser解析生成AST,这里SQLStatement就是AST
    SQLStatement statement = parser.parseStatement();
    // 使用visitor来访问AST
    StringBuilder out=new StringBuilder();
    RemoveOrderByInMySqlOutputVisitor visitor = new RemoveOrderByInMySqlOutputVisitor(out);
    SQLUtils.FormatOption  option = SQLUtils.DEFAULT_LCASE_FORMAT_OPTION;
    visitor.setUppCase(true);
    visitor.setPrettyFormat(option.isPrettyFormat());
    visitor.setParameterized(option.isParameterized());
    visitor.setFeatures(VisitorFeature.of(VisitorFeature.OutputUCase
            , VisitorFeature.OutputPrettyFormat));
    statement.accept(visitor);
    return out.toString();
}

 

public class RemoveOrderByInMySqlOutputVisitor extends SQLASTOutputVisitor {


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

    @Override
    protected void printFetchFirst(SQLSelectQueryBlock x) {
        SQLLimit limit = x.getLimit();
        if (limit == null) {
            return;
        }

        SQLExpr offset = limit.getOffset();
        SQLExpr first = limit.getRowCount();

        if (limit != null) {
            if (JdbcConstants.INFORMIX.equals(dbType)) {
                if (offset != null) {
                    print0(ucase ? "SKIP " : "skip ");
                    offset.accept(this);
                }

                print0(ucase ? " FIRST " : " first ");
                first.accept(this);
                print(' ');
            } else if (JdbcConstants.DB2.equals(dbType)
                    || JdbcConstants.ORACLE.equals(dbType)
                    || JdbcConstants.SQL_SERVER.equals(dbType)) {

                println();

                if (offset != null) {
                    print0(ucase ? "OFFSET " : "offset ");
                    offset.accept(this);
                    print0(ucase ? " ROWS" : " rows");
                }

                if (first != null) {
                    if (offset != null) {
                        print(' ');
                    }
                    if (JdbcConstants.SQL_SERVER.equals(dbType) && offset != null) {
                        print0(ucase ? "FETCH NEXT " : "fetch next ");
                    } else {
                        print0(ucase ? "FETCH FIRST " : "fetch first ");
                    }
                    first.accept(this);
                    print0(ucase ? " ROWS ONLY" : " rows only");
                }
            } else {
                println();
                limit.accept(this);
            }
        }
    }

    @Override
    public boolean visit(SQLOrderBy x) {
        return false;
    }
} 

 

  public static void main(String[] args) throws JSQLParserException {
        String sql ="SELECT id, content, create_time, image, type , is_show, is_top, status, summary, title , description, keywords, top_time, update_time, user_name , user_image FROM uw_news WHERE status = ? AND is_show = ? AND is_top = ? AND type = ? ORDER BY create_time DESC ";
        System.out.println(removeOrderBySql(sql));
    }

 

关键字:     sql       count       order       by  
评论信息
暂无评论