SQLServer去除子查询中的order by

2017-03-21 08:19:40来源:CSDN作者:u014676619人点击

第七城市th7cn

SQL解析工具:SqlParser ,GitHub地址 https://github.com/JSQLParser/JSqlParser

本次演示版本:http://search.maven.org/remotecontent?filepath=com/github/jsqlparser/jsqlparser/0.9.1/jsqlparser-0.9.1.jar

转换工具类(先导入下载的jar包):SqlServerParse.java

package com.netide.plugin;import java.util.List;import net.sf.jsqlparser.JSQLParserException;import net.sf.jsqlparser.parser.CCJSqlParserUtil;import net.sf.jsqlparser.statement.Statement;import net.sf.jsqlparser.statement.select.FromItem;import net.sf.jsqlparser.statement.select.Join;import net.sf.jsqlparser.statement.select.LateralSubSelect;import net.sf.jsqlparser.statement.select.OrderByElement;import net.sf.jsqlparser.statement.select.PlainSelect;import net.sf.jsqlparser.statement.select.Select;import net.sf.jsqlparser.statement.select.SelectBody;import net.sf.jsqlparser.statement.select.SetOperationList;import net.sf.jsqlparser.statement.select.SubJoin;import net.sf.jsqlparser.statement.select.SubSelect;import net.sf.jsqlparser.statement.select.ValuesList;import net.sf.jsqlparser.statement.select.WithItem;public class SqlServerParse {		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());				}			}		}	}	public boolean orderByHashParameters(List<OrderByElement> orderByElements) {		if (orderByElements == null) {			return false;		}		for (OrderByElement orderByElement : orderByElements) {			if (orderByElement.toString().toUpperCase().contains("?")) {				return true;			}		}		return false;	}}

调用很简单,一句话搞定:

String newSql = SqlServerParse.class.newInstance().removeOrderBy(oldSql); //去除ORDER BY

第七城市th7cn

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台