-
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
来源:http://blog.csdn.net/isea533/article/details/40375183
前面有一篇博客介绍了另一个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方法很全面,所以处理起来很容易。