gu.sql2java.manager.parser.SqlServerParser Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of sql2java-manager Show documentation
Show all versions of sql2java-manager Show documentation
sql2java manager class package for accessing database
The newest version!
/*
* The MIT License (MIT)
*
* Copyright (c) 2014-2017 [email protected]
*
* Permission is hereby granted, free of charge, to any person obtaining a copy
* of this software and associated documentation files (the "Software"), to deal
* in the Software without restriction, including without limitation the rights
* to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
* copies of the Software, and to permit persons to whom the Software is
* furnished to do so, subject to the following conditions:
*
* The above copyright notice and this permission notice shall be included in
* all copies or substantial portions of the Software.
*
* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
* IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
* FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
* AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
* LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
* OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
* THE SOFTWARE.
*/
package gu.sql2java.manager.parser;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.operators.relational.GreaterThan;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.*;
import java.util.*;
import gu.sql2java.pagehelper.PageException;
/**
* 将sqlserver查询语句转换为分页语句
* 注意事项：
*
* - 请先保证你的SQL可以执行
* - sql中最好直接包含order by，可以自动从sql提取
* - 如果没有order by，可以通过入参提供，但是需要自己保证正确
* - 如果sql有order by，可以通过orderby参数覆盖sql中的order by
* - order by的列名不能使用别名
* - 表和列使用别名的时候不要使用单引号(')
*
* 该类设计为一个独立的工具类，依赖jsqlparser,可以独立使用
*
* @author liuzh
*/
public class SqlServerParser {
//开始行号
public static final String START_ROW = String.valueOf(Long.MIN_VALUE);
//结束行号
public static final String PAGE_SIZE = String.valueOf(Long.MAX_VALUE);
//外层包装表
protected static final String WRAP_TABLE = "WRAP_OUTER_TABLE";
//表别名名字
protected static final String PAGE_TABLE_NAME = "PAGE_TABLE_ALIAS";
//protected
public static final Alias PAGE_TABLE_ALIAS = new Alias(PAGE_TABLE_NAME);
//行号
protected static final String PAGE_ROW_NUMBER = "PAGE_ROW_NUMBER";
//行号列
protected static final Column PAGE_ROW_NUMBER_COLUMN = new Column(PAGE_ROW_NUMBER);
//TOP 100 PERCENT
protected static final Top TOP100_PERCENT;
//别名前缀
protected static final String PAGE_COLUMN_ALIAS_PREFIX = "ROW_ALIAS_";
//静态方法处理
static {
TOP100_PERCENT = new Top();
TOP100_PERCENT.setExpression(new LongValue(100));
TOP100_PERCENT.setPercentage(true);
}
/**
* 转换为分页语句
*
* @param sql
*/
public String convertToPageSql(String sql) {
return convertToPageSql(sql, null, null);
}
/**
* 转换为分页语句
*
* @param sql
* @param offset
* @param limit
*/
public String convertToPageSql(String sql, Integer offset, Integer limit) {
//解析SQL
Statement stmt;
try {
stmt = CCJSqlParserUtil.parse(sql);
} catch (Throwable e) {
throw new PageException("不支持该SQL转换为分页查询!", e);
}
if (!(stmt instanceof Select)) {
throw new PageException("分页语句必须是Select查询!");
}
//获取分页查询的select
Select pageSelect = getPageSelect((Select) stmt);
String pageSql = pageSelect.toString();
//缓存移到外面了，所以不替换参数
if (offset != null) {
pageSql = pageSql.replace(START_ROW, String.valueOf(offset));
}
if (limit != null) {
pageSql = pageSql.replace(PAGE_SIZE, String.valueOf(limit));
}
return pageSql;
}
/**
* 获取一个外层包装的TOP查询
*
* @param select
*/
protected Select getPageSelect(Select select) {
SelectBody selectBody = select.getSelectBody();
if (selectBody instanceof SetOperationList) {
selectBody = wrapSetOperationList((SetOperationList) selectBody);
}
//这里的selectBody一定是PlainSelect
if (((PlainSelect) selectBody).getTop() != null) {
throw new PageException("被分页的语句已经包含了Top，不能再通过分页插件进行分页查询!");
}
//获取查询列
List selectItems = getSelectItems((PlainSelect) selectBody);
//对一层的SQL增加ROW_NUMBER()
List autoItems = new ArrayList();
SelectItem orderByColumn = addRowNumber((PlainSelect) selectBody, autoItems);
//加入自动生成列
((PlainSelect) selectBody).addSelectItems(autoItems.toArray(new SelectItem[autoItems.size()]));
//处理子语句中的order by
processSelectBody(selectBody, 0);
//中层子查询
PlainSelect innerSelectBody = new PlainSelect();
//PAGE_ROW_NUMBER
innerSelectBody.addSelectItems(orderByColumn);
innerSelectBody.addSelectItems(selectItems.toArray(new SelectItem[selectItems.size()]));
//将原始查询作为内层子查询
SubSelect fromInnerItem = new SubSelect();
fromInnerItem.setSelectBody(selectBody);
fromInnerItem.setAlias(PAGE_TABLE_ALIAS);
innerSelectBody.setFromItem(fromInnerItem);
//新建一个select
Select newSelect = new Select();
PlainSelect newSelectBody = new PlainSelect();
//设置top
Top top = new Top();
top.setExpression(new LongValue(Long.MAX_VALUE));
newSelectBody.setTop(top);
//设置order by
List orderByElements = new ArrayList();
OrderByElement orderByElement = new OrderByElement();
orderByElement.setExpression(PAGE_ROW_NUMBER_COLUMN);
orderByElements.add(orderByElement);
newSelectBody.setOrderByElements(orderByElements);
//设置where
GreaterThan greaterThan = new GreaterThan();
greaterThan.setLeftExpression(PAGE_ROW_NUMBER_COLUMN);
greaterThan.setRightExpression(new LongValue(Long.MIN_VALUE));
newSelectBody.setWhere(greaterThan);
//设置selectItems
newSelectBody.setSelectItems(selectItems);
//设置fromIterm
SubSelect fromItem = new SubSelect();
fromItem.setSelectBody(innerSelectBody); //中层子查询
fromItem.setAlias(PAGE_TABLE_ALIAS);
newSelectBody.setFromItem(fromItem);
newSelect.setSelectBody(newSelectBody);
if (isNotEmptyList(select.getWithItemsList())) {
newSelect.setWithItemsList(select.getWithItemsList());
}
return newSelect;
}
/**
* 包装SetOperationList
*
* @param setOperationList
*/
protected SelectBody wrapSetOperationList(SetOperationList setOperationList) {
//获取最后一个plainSelect
SelectBody setSelectBody = setOperationList.getSelects().get(setOperationList.getSelects().size() - 1);
if (!(setSelectBody instanceof PlainSelect)) {
throw new PageException("目前无法处理该SQL，您可以将该SQL发送给[email protected]协助作者解决!");
}
PlainSelect plainSelect = (PlainSelect) setSelectBody;
PlainSelect selectBody = new PlainSelect();
List selectItems = getSelectItems(plainSelect);
selectBody.setSelectItems(selectItems);
//设置fromIterm
SubSelect fromItem = new SubSelect();
fromItem.setSelectBody(setOperationList);
fromItem.setAlias(new Alias(WRAP_TABLE));
selectBody.setFromItem(fromItem);
//order by
if (isNotEmptyList(plainSelect.getOrderByElements())) {
selectBody.setOrderByElements(plainSelect.getOrderByElements());
plainSelect.setOrderByElements(null);
}
return selectBody;
}
/**
* 获取查询列
*
* @param plainSelect
*/
protected List getSelectItems(PlainSelect plainSelect) {
//设置selectItems
List selectItems = new ArrayList();
for (SelectItem selectItem : plainSelect.getSelectItems()) {
//别名需要特殊处理
if (selectItem instanceof SelectExpressionItem) {
SelectExpressionItem selectExpressionItem = (SelectExpressionItem) selectItem;
if (selectExpressionItem.getAlias() != null) {
//直接使用别名
Column column = new Column(selectExpressionItem.getAlias().getName());
SelectExpressionItem expressionItem = new SelectExpressionItem(column);
selectItems.add(expressionItem);
} else if (selectExpressionItem.getExpression() instanceof Column) {
Column column = (Column) selectExpressionItem.getExpression();
SelectExpressionItem item = null;
if (column.getTable() != null) {
Column newColumn = new Column(column.getColumnName());
item = new SelectExpressionItem(newColumn);
selectItems.add(item);
} else {
selectItems.add(selectItem);
}
} else {
selectItems.add(selectItem);
}
} else if (selectItem instanceof AllTableColumns) {
selectItems.add(new AllColumns());
} else {
selectItems.add(selectItem);
}
}
// SELECT *, 1 AS alias FROM TEST
// 应该为
// SELECT * FROM (SELECT *, 1 AS alias FROM TEST)
// 不应该为
// SELECT *, alias FROM (SELECT *, 1 AS alias FROM TEST)
for (SelectItem selectItem : selectItems) {
if (selectItem instanceof AllColumns) {
return Collections.singletonList(selectItem);
}
}
return selectItems;
}
/**
* 获取 ROW_NUMBER() 列
*
* @param plainSelect 原查询
* @param autoItems 自动生成的查询列
* @return ROW_NUMBER() 列
*/
protected SelectItem addRowNumber(PlainSelect plainSelect, List autoItems) {
//增加ROW_NUMBER()
StringBuilder orderByBuilder = new StringBuilder();
orderByBuilder.append("ROW_NUMBER() OVER (");
if (isNotEmptyList(plainSelect.getOrderByElements())) {
orderByBuilder.append(PlainSelect.orderByToString(
getOrderByElements(plainSelect, autoItems)).substring(1));
//清空排序列表
plainSelect.setOrderByElements(null);
} else {
orderByBuilder.append("ORDER BY RAND()");
}
orderByBuilder.append(") ");
orderByBuilder.append(PAGE_ROW_NUMBER);
return new SelectExpressionItem(new Column(orderByBuilder.toString()));
}
/**
* 处理selectBody去除Order by
*
* @param selectBody
*/
protected void processSelectBody(SelectBody selectBody, int level) {
if (selectBody != null) {
if (selectBody instanceof PlainSelect) {
processPlainSelect((PlainSelect) selectBody, level + 1);
} else if (selectBody instanceof WithItem) {
WithItem withItem = (WithItem) selectBody;
if (withItem.getSubSelect() != null) {
processSelectBody(withItem.getSubSelect().getSelectBody(), level + 1);
}
} else {
SetOperationList operationList = (SetOperationList) selectBody;
if (operationList.getSelects() != null && operationList.getSelects().size() > 0) {
List plainSelects = operationList.getSelects();
for (SelectBody plainSelect : plainSelects) {
processSelectBody(plainSelect, level + 1);
}
}
}
}
}
/**
* 处理PlainSelect类型的selectBody
*
* @param plainSelect
*/
protected void processPlainSelect(PlainSelect plainSelect, int level) {
if (level > 1) {
if (isNotEmptyList(plainSelect.getOrderByElements())) {
if (plainSelect.getTop() == null) {
plainSelect.setTop(TOP100_PERCENT);
}
}
}
if (plainSelect.getFromItem() != null) {
processFromItem(plainSelect.getFromItem(), level + 1);
}
if (plainSelect.getJoins() != null && plainSelect.getJoins().size() > 0) {
List joins = plainSelect.getJoins();
for (Join join : joins) {
if (join.getRightItem() != null) {
processFromItem(join.getRightItem(), level + 1);
}
}
}
}
/**
* 处理子查询
*
* @param fromItem
*/
protected void processFromItem(FromItem fromItem, int level) {
if (fromItem instanceof SubJoin) {
SubJoin subJoin = (SubJoin) fromItem;
if (subJoin.getJoinList() != null && subJoin.getJoinList().size() > 0) {
for (Join join : subJoin.getJoinList()) {
if (join.getRightItem() != null) {
processFromItem(join.getRightItem(), level + 1);
}
}
}
if (subJoin.getLeft() != null) {
processFromItem(subJoin.getLeft(), level + 1);
}
} else if (fromItem instanceof SubSelect) {
SubSelect subSelect = (SubSelect) fromItem;
if (subSelect.getSelectBody() != null) {
processSelectBody(subSelect.getSelectBody(), level + 1);
}
} else if (fromItem instanceof ValuesList) {
} else if (fromItem instanceof LateralSubSelect) {
LateralSubSelect lateralSubSelect = (LateralSubSelect) fromItem;
if (lateralSubSelect.getSubSelect() != null) {
SubSelect subSelect = lateralSubSelect.getSubSelect();
if (subSelect.getSelectBody() != null) {
processSelectBody(subSelect.getSelectBody(), level + 1);
}
}
}
//Table时不用处理
}
/**
* List不空
*
* @param list
*/
public boolean isNotEmptyList(List list) {
if (list == null || list.size() == 0) {
return false;
}
return true;
}
/**
* 复制 OrderByElement
*
* @param orig 原 OrderByElement
* @param alias 新 OrderByElement 的排序要素
* @return 复制的新 OrderByElement
*/
protected OrderByElement cloneOrderByElement(OrderByElement orig, String alias) {
return cloneOrderByElement(orig, new Column(alias));
}
/**
* 复制 OrderByElement
*
* @param orig 原 OrderByElement
* @param expression 新 OrderByElement 的排序要素
* @return 复制的新 OrderByElement
*/
protected OrderByElement cloneOrderByElement(OrderByElement orig, Expression expression) {
OrderByElement element = new OrderByElement();
element.setAsc(orig.isAsc());
element.setAscDescPresent(orig.isAscDescPresent());
element.setNullOrdering(orig.getNullOrdering());
element.setExpression(expression);
return element;
}
/**
* 获取新的排序列表
*
* @param plainSelect 原始查询
* @param autoItems 生成的新查询要素
* @return 新的排序列表
*/
protected List getOrderByElements(PlainSelect plainSelect,
List autoItems) {
List orderByElements = plainSelect.getOrderByElements();
ListIterator iterator = orderByElements.listIterator();
OrderByElement orderByElement;
// 非 `*` 且 非 `t.*` 查询列集合
Map selectMap = new HashMap();
// 别名集合
Set aliases = new HashSet();
// 是否包含 `*` 查询列
boolean allColumns = false;
// `t.*` 查询列的表名集合
Set allColumnsTables = new HashSet();
for (SelectItem item : plainSelect.getSelectItems()) {
if (item instanceof SelectExpressionItem) {
SelectExpressionItem expItem = (SelectExpressionItem) item;
selectMap.put(expItem.getExpression().toString(), expItem);
Alias alias = expItem.getAlias();
if (alias != null) {
aliases.add(alias.getName());
}
} else if (item instanceof AllColumns) {
allColumns = true;
} else if (item instanceof AllTableColumns) {
allColumnsTables.add(((AllTableColumns) item).getTable().getName());
}
}
// 开始遍历 OrderByElement 列表
int aliasNo = 1;
while (iterator.hasNext()) {
orderByElement = iterator.next();
Expression expression = orderByElement.getExpression();
SelectExpressionItem selectExpressionItem = selectMap.get(expression.toString());
if (selectExpressionItem != null) { // OrderByElement 在查询列表中
Alias alias = selectExpressionItem.getAlias();
if (alias != null) { // 查询列含有别名时用查询列别名
iterator.set(cloneOrderByElement(orderByElement, alias.getName()));
} else { // 查询列不包含别名
if (expression instanceof Column) {
// 查询列为普通列，这时因为列在嵌套查询外时名称中不包含表名，故去除排序列的表名引用
// 例（仅为解释此处逻辑，不代表最终分页结果）：
// SELECT TEST.A FROM TEST ORDER BY TEST.A
// -->
// SELECT A FROM (SELECT TEST.A FROM TEST) ORDER BY A
((Column) expression).setTable(null);
} else {
// 查询列不为普通列时（例如函数列）不支持分页
// 此种情况比较难预测，简单的增加新列容易产生不可预料的结果
// 而为列增加别名是非常简单的，故此要求排序复杂列必须使用别名
throw new PageException("列 \"" + expression + "\" 需要定义别名");
}
}
} else { // OrderByElement 不在查询列表中，需要自动生成一个查询列
if (expression instanceof Column) { // OrderByElement 为普通列
Table table = ((Column) expression).getTable();
if (table == null) { // 表名为空
if (allColumns ||
(allColumnsTables.size() == 1 && plainSelect.getJoins() == null) ||
aliases.contains(((Column) expression).getColumnName())) {
// 包含`*`查询列 或者 只有一个 `t.*`列且为单表查询 或者 其实排序列是一个别名
// 此时排序列其实已经包含在查询列表中了，不需做任何操作
continue;
}
} else { //表名不为空
String tableName = table.getName();
if (allColumns || allColumnsTables.contains(tableName)) {
// 包含`*`查询列 或者 包含特定的`t.*`列
// 此时排序列其实已经包含在查询列表中了，只需去除排序列的表名引
((Column) expression).setTable(null);
continue;
}
}
}
// 将排序列加入查询列中
String aliasName = PAGE_COLUMN_ALIAS_PREFIX + aliasNo++;
SelectExpressionItem item = new SelectExpressionItem();
item.setExpression(expression);
item.setAlias(new Alias(aliasName));
autoItems.add(item);
iterator.set(cloneOrderByElement(orderByElement, aliasName));
}
}
return orderByElements;
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy