com.gitee.qdbp.jdbc.utils.CountSqlParser Maven / Gradle / Ivy
/*
* 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 com.gitee.qdbp.jdbc.utils;
import java.util.ArrayList;
import java.util.List;
import com.gitee.qdbp.tools.utils.IndentTools;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.Function;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
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.SelectExpressionItem;
import net.sf.jsqlparser.statement.select.SelectItem;
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.WithItem;
/**
* sql解析类，提供更智能的count查询sql
*
* @author liuzh
* @since 3.2.0
*/
public class CountSqlParser {
public static final String KEEP_ORDERBY = "/*keep orderby*/";
private static final Alias TABLE_ALIAS;
static {
TABLE_ALIAS = new Alias("TABLE_COUNT");
TABLE_ALIAS.setUseAs(false);
}
/**
* 获取智能的countSql
*
* @param sql 原查询SQL
* @return 返回count查询sql
*/
public String getSmartCountSql(String sql) {
return getSmartCountSql(sql, "0");
}
/**
* 获取智能的countSql
*
* @param sql 原查询SQL
* @param name 查询列名，默认 0
* @return 返回count查询sql
*/
public String getSmartCountSql(String sql, String name) {
//解析SQL
Statement stmt;
//特殊sql不需要去掉order by时，使用注释前缀
if(sql.contains(KEEP_ORDERBY)){
return getSimpleCountSql(sql);
}
try {
stmt = CCJSqlParserUtil.parse(sql);
} catch (Throwable e) {
//无法解析的用一般方法返回count语句
return getSimpleCountSql(sql);
}
Select select = (Select) stmt;
SelectBody selectBody = select.getSelectBody();
try {
//处理body-去order by
processSelectBody(selectBody);
} catch (Exception e) {
//当 sql 包含 group by 时，不去除 order by
return getSimpleCountSql(sql);
}
//处理with-去order by
processWithItemsList(select.getWithItemsList());
//处理为count查询
sqlToCount(select, name);
return select.toString();
}
/**
* 获取普通的Count-sql
*
* @param sql 原查询sql
* @return 返回count查询sql
*/
public String getSimpleCountSql(final String sql) {
return getSimpleCountSql(sql, "0");
}
/**
* 获取普通的Count-sql
*
* @param sql 原查询sql
* @param name 查询列名，默认 0
* @return 返回count查询sql
*/
public String getSimpleCountSql(final String sql, String name) {
StringBuilder stringBuilder = new StringBuilder(sql.length() + 40);
stringBuilder.append("SELECT").append(' ');
stringBuilder.append("COUNT").append('(').append(name).append(')').append(' ');
stringBuilder.append("FROM").append(' ').append('(').append('\n');
stringBuilder.append(IndentTools.space.indentAll(sql, 1));
stringBuilder.append('\n').append(')').append(' ').append("TMP_COUNT");
return stringBuilder.toString();
}
/**
* 将sql转换为count查询
*
* @param select 查询语句
* @param name 查询列名，默认 0
*/
public void sqlToCount(Select select, String name) {
SelectBody selectBody = select.getSelectBody();
// 是否能简化count查询
List COUNT_ITEM = new ArrayList<>();
COUNT_ITEM.add(new SelectExpressionItem(new Column("COUNT(" + name +")")));
if (selectBody instanceof PlainSelect && isSimpleCount((PlainSelect) selectBody)) {
((PlainSelect) selectBody).setSelectItems(COUNT_ITEM);
} else {
PlainSelect plainSelect = new PlainSelect();
SubSelect subSelect = new SubSelect();
subSelect.setSelectBody(selectBody);
subSelect.setAlias(TABLE_ALIAS);
plainSelect.setFromItem(subSelect);
plainSelect.setSelectItems(COUNT_ITEM);
select.setSelectBody(plainSelect);
}
}
/**
* 是否可以用简单的count查询方式
*
* @param select 查询语句
* @return 是否可以转换为简单的count查询方式
*/
public boolean isSimpleCount(PlainSelect select) {
//包含group by的时候不可以
if (select.getGroupBy() != null && select.getGroupBy().getGroupByExpressions() != null) {
return false;
}
//包含distinct的时候不可以
if (select.getDistinct() != null) {
return false;
}
for (SelectItem item : select.getSelectItems()) {
//select列中包含参数的时候不可以，否则会引起参数个数错误
if (item.toString().contains("?")) {
return false;
}
//如果查询列中包含函数，也不可以，函数可能会聚合列
if (item instanceof SelectExpressionItem) {
if (((SelectExpressionItem) item).getExpression() instanceof Function) {
return false;
}
}
}
return true;
}
/**
* 处理selectBody去除Order by
*
* @param selectBody 查询体
*/
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.getSelects() != null && operationList.getSelects().size() > 0) {
List plainSelects = operationList.getSelects();
for (SelectBody plainSelect : plainSelects) {
processSelectBody(plainSelect);
}
}
if (!orderByHashParameters(operationList.getOrderByElements())) {
operationList.setOrderByElements(null);
}
}
}
/**
* 处理PlainSelect类型的selectBody
*
* @param plainSelect PlainSelect类型的selectBody
*/
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());
}
}
}
}
/**
* 处理WithItem
*
* @param withItemsList With内容
*/
public void processWithItemsList(List withItemsList) {
if (withItemsList != null && withItemsList.size() > 0) {
for (WithItem item : withItemsList) {
processSelectBody(item.getSelectBody());
}
}
}
/**
* 处理子查询
*
* @param fromItem From内容
*/
public void processFromItem(FromItem fromItem) {
if (fromItem instanceof SubJoin) {
SubJoin subJoin = (SubJoin) fromItem;
if (subJoin.getJoinList() != null) {
for (Join item : subJoin.getJoinList()) {
if (item.getRightItem() != null) {
processFromItem(item.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 = lateralSubSelect.getSubSelect();
if (subSelect.getSelectBody() != null) {
processSelectBody(subSelect.getSelectBody());
}
}
}
//Table时不用处理
}
/**
* 判断Orderby是否包含参数，有参数的不能去
*
* @param orderByElements OrderBy排序项
* @return 是否包含参数
*/
public boolean orderByHashParameters(List orderByElements) {
if (orderByElements == null) {
return false;
}
for (OrderByElement orderByElement : orderByElements) {
if (orderByElement.toString().contains("?")) {
return true;
}
}
return false;
}
} © 2015 - 2025 Weber Informatics LLC | Privacy Policy