java sqlparser_JAVA - Sql解析工具jsqlparser简单使用

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

这里仍然用去除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 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 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 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和源码。