JAVA - Sql解析工具jsqlparser简单使用

• SqlParser - github地址：https://github.com/JSQLParser/JSqlParser

• SqlParser最新版jar下载：http://search.maven.org/remotecontent?filepath=com/github/jsqlparser/jsqlparser/0.9.1/jsqlparser-0.9.1.jar

前面有一篇博客介绍了另一个fdb-sql-parser的简单使用。由于fdb-sql-parser针对性比较强，对不同数据库的通用性不好，所以尝试了另一个sql解析工具jsqlparser。

在jsqlparser的wiki页面有简单的使用方法：https://github.com/JSQLParser/JSqlParser/wiki

这里仍然用去除order by做例子。  

jsqlparser的数据结构比较简单，更清晰，因而我选择了一种正向强制设置order by属性为null的方式来去除order by  

下面是需要用到的几个方法：  

public String removeOrderBy(String sql) throws JSQLParserException {
    Statement stmt = CCJSqlParserUtil.parse(sql);
    Select select = (Select) stmt;
    SelectBody selectBody = select.getSelectBody();
    processSelectBody(selectBody);
    return select.toString();
}

public void processSelectBody(SelectBody selectBody) {
    if (selectBody instanceof PlainSelect) {
        processPlainSelect((PlainSelect) selectBody);
    } else if (selectBody instanceof WithItem) {
        WithItem withItem = (WithItem) selectBody;
        if (withItem.getSelectBody() != null) {
            processSelectBody(withItem.getSelectBody());
        }
    } else {
        SetOperationList operationList = (SetOperationList) selectBody;
        if (operationList.getPlainSelects() != null && operationList.getPlainSelects().size() > 0) {
            List<PlainSelect> plainSelects = operationList.getPlainSelects();
            for (PlainSelect plainSelect : plainSelects) {
                processPlainSelect(plainSelect);
            }
        }
        if (!orderByHashParameters(operationList.getOrderByElements())) {
            operationList.setOrderByElements(null);
        }
    }
}

public void processPlainSelect(PlainSelect plainSelect) {
    if (!orderByHashParameters(plainSelect.getOrderByElements())) {
        plainSelect.setOrderByElements(null);
    }
    if (plainSelect.getFromItem() != null) {
        processFromItem(plainSelect.getFromItem());
    }
    if (plainSelect.getJoins() != null && plainSelect.getJoins().size() > 0) {
        List<Join> joins = plainSelect.getJoins();
        for (Join join : joins) {
            if (join.getRightItem() != null) {
                processFromItem(join.getRightItem());
            }
        }
    }
}

public void processFromItem(FromItem fromItem) {
    if (fromItem instanceof SubJoin) {
        SubJoin subJoin = (SubJoin) fromItem;
        if (subJoin.getJoin() != null) {
            if (subJoin.getJoin().getRightItem() != null) {
                processFromItem(subJoin.getJoin().getRightItem());
            }
        }
        if (subJoin.getLeft() != null) {
            processFromItem(subJoin.getLeft());
        }
    } else if (fromItem instanceof SubSelect) {
        SubSelect subSelect = (SubSelect) fromItem;
        if (subSelect.getSelectBody() != null) {
            processSelectBody(subSelect.getSelectBody());
        }
    } else if (fromItem instanceof ValuesList) {

    } else if (fromItem instanceof LateralSubSelect) {
        LateralSubSelect lateralSubSelect = (LateralSubSelect) fromItem;
        if (lateralSubSelect.getSubSelect() != null) {
            SubSelect subSelect = (SubSelect) (lateralSubSelect.getSubSelect());
            if (subSelect.getSelectBody() != null) {
                processSelectBody(subSelect.getSelectBody());
            }
        }
    }
    //Table时不用处理
}

public boolean orderByHashParameters(List<OrderByElement> orderByElements) {
    if (orderByElements == null) {
        return false;
    }
    for (OrderByElement orderByElement : orderByElements) {
        if (orderByElement.toString().toUpperCase().contains("?")) {
            return true;
        }
    }
    return false;
}

由于jsqlparser结构简单，继承也不复杂，并且setter和getter方法很全面，所以处理起来很容易。

jsqlparser还能通过方法来编辑某部分的sql，例如增加一个查询列，增加一个表等等。更多的方法可以看wiki和源码。