Java PlainSelect.getDistinct方法代码示例

import net.sf.jsqlparser.statement.select.PlainSelect; //导入方法依赖的package包/类
/**
 * 获取select的count语句
 *
 * @param originalSql
 *            selectSQL
 * @return
 */
public static String sqlCountOptimize(String originalSql) {
    Assert.hasLength(originalSql);
    String sqlCount;
    try {
        Select selectStatement = (Select) CCJSqlParserUtil.parse(originalSql);
        PlainSelect plainSelect = (PlainSelect) selectStatement.getSelectBody();
        Distinct distinct = plainSelect.getDistinct();
        List<Expression> groupBy = plainSelect.getGroupByColumnReferences();
        // 优化Order by
        List<OrderByElement> orderBy = plainSelect.getOrderByElements();
        // 添加包含groupby 不去除orderby
        if (CollectionUtils.isEmpty(groupBy) && CollectionUtils.isNotEmpty(orderBy)) {
            plainSelect.setOrderByElements(null);
        }
        if (distinct != null || CollectionUtils.isNotEmpty(groupBy)) {
            return String.format(SQL_BASE_COUNT, selectStatement.toString());
        }
        List<SelectItem> selectCount = countSelectItem();
        plainSelect.setSelectItems(selectCount);
        sqlCount = selectStatement.toString();
    } catch (Exception e) {
        sqlCount = String.format(SQL_BASE_COUNT, originalSql);
    }
    return sqlCount;
}
 

import net.sf.jsqlparser.statement.select.PlainSelect; //导入方法依赖的package包/类
/**
 * jsqlparser方式获取select的count语句
 *
 * @param originalSql selectSQL
 * @return
 */
public static String jsqlparserCount(String originalSql) {
    String sqlCount;
    try {
        Select selectStatement = (Select) CCJSqlParserUtil.parse(originalSql);
        PlainSelect plainSelect = (PlainSelect) selectStatement.getSelectBody();
        Distinct distinct = plainSelect.getDistinct();
        List<Expression> groupBy = plainSelect.getGroupByColumnReferences();
        // 优化Order by
        List<OrderByElement> orderBy = plainSelect.getOrderByElements();
        // 添加包含groupby 不去除orderby
        if (CollectionUtils.isEmpty(groupBy) && CollectionUtils.isNotEmpty(orderBy)) {
            plainSelect.setOrderByElements(null);
        }
        // 包含 distinct、groupBy不优化
        if (distinct != null || CollectionUtils.isNotEmpty(groupBy)) {
            sqlCount = String.format(SqlUtils.SQL_BASE_COUNT, selectStatement.toString());
            return sqlCount;
        }
        List<SelectItem> selectCount = countSelectItem();
        plainSelect.setSelectItems(selectCount);
        sqlCount = selectStatement.toString();
    } catch (Exception e) {
        sqlCount = String.format(SqlUtils.SQL_BASE_COUNT, originalSql);
    }
    return sqlCount;
}
 

import net.sf.jsqlparser.statement.select.PlainSelect; //导入方法依赖的package包/类
@Override
public void visit(PlainSelect plainSelect) {
    if (plainSelect.getDistinct() != null && plainSelect.getDistinct().getOnSelectItems() != null) {
        plainSelect.getDistinct().getOnSelectItems().forEach(s -> s.accept(this));
    }
    if (plainSelect.getFromItem() != null) {
        plainSelect.getFromItem().accept(this);
    }
    if (plainSelect.getGroupByColumnReferences() != null) {
        plainSelect.getGroupByColumnReferences().forEach(s -> s.accept(this));
    }
    if (plainSelect.getHaving() != null) {
        plainSelect.getHaving().accept(this);
    }
    if (plainSelect.getJoins() != null) {
        plainSelect.getJoins().forEach(
            j -> {
                visitJoin(j);
            }
        );
    }
    if (plainSelect.getOrderByElements() != null) {
        plainSelect.getOrderByElements().forEach(s -> s.accept(this));
    }
    if (plainSelect.getSelectItems() != null) {
        plainSelect.getSelectItems().forEach(s -> s.accept(this));
    }
    if (plainSelect.getTop() != null) {
        plainSelect.getTop().getExpression().accept(this);
    }
    if (plainSelect.getWhere() != null) {
        plainSelect.getWhere().accept(this);
    }
    if (plainSelect.getLimit() != null) {
        visitLimit(plainSelect.getLimit());
    }

}
 

import net.sf.jsqlparser.statement.select.PlainSelect; //导入方法依赖的package包/类
@Override
public SqlInfo optimizeSql(MetaObject metaObject, String sql) {
    if (logger.isDebugEnabled()) {
        logger.debug(" JsqlParserCountOptimize sql=" + sql);
    }
    SqlInfo sqlInfo = SqlInfo.newInstance();
    try {
        Select selectStatement = (Select) CCJSqlParserUtil.parse(sql);
        PlainSelect plainSelect = (PlainSelect) selectStatement.getSelectBody();
        Distinct distinct = plainSelect.getDistinct();
        List<Expression> groupBy = plainSelect.getGroupByColumnReferences();
        List<OrderByElement> orderBy = plainSelect.getOrderByElements();

        // 添加包含groupBy 不去除orderBy
        if (CollectionUtils.isEmpty(groupBy) && CollectionUtils.isNotEmpty(orderBy)) {
            plainSelect.setOrderByElements(null);
            sqlInfo.setOrderBy(false);
        }
        //#95 Github, selectItems contains #{} ${}, which will be translated to ?, and it may be in a function: power(#{myInt},2)
        for (SelectItem item : plainSelect.getSelectItems()) {
            if (item.toString().contains("?")) {
                sqlInfo.setSql(String.format(SqlUtils.SQL_BASE_COUNT, selectStatement.toString()));
                return sqlInfo;
            }
        }
        // 包含 distinct、groupBy不优化
        if (distinct != null || CollectionUtils.isNotEmpty(groupBy)) {
            sqlInfo.setSql(String.format(SqlUtils.SQL_BASE_COUNT, selectStatement.toString()));
            return sqlInfo;
        }
        // 优化 SQL
        plainSelect.setSelectItems(countSelectItem);
        sqlInfo.setSql(selectStatement.toString());
        return sqlInfo;
    } catch (Throwable e) {
        // 无法优化使用原 SQL
        sqlInfo.setSql(String.format(SqlUtils.SQL_BASE_COUNT, sql));
        return sqlInfo;
    }
}
 

import net.sf.jsqlparser.statement.select.PlainSelect; //导入方法依赖的package包/类
private static String plainSelectToStringAppendWithNoLock(PlainSelect plain) {
    StringBuilder sql = new StringBuilder("SELECT ");
    if (plain.getDistinct() != null)
        sql.append(plain.getDistinct()).append(" ");

    if (plain.getTop() != null)
        sql.append(plain.getTop()).append(" ");

    sql.append(PlainSelect.getStringList(plain.getSelectItems()));
    if (plain.getFromItem() != null) {
        sql.append(" FROM ").append(plain.getFromItem()).append(" WITH (NOLOCK) ");
        if (plain.getJoins() != null) {
            Iterator<Join> it = plain.getJoins().iterator();
            while (it.hasNext()) {
                Join join = it.next();
                if (join.isSimple()) {
                    sql.append(", ").append(join).append(" WITH (NOLOCK) ");
                } else {
                    String temp = join.toString().replace(join.getRightItem().toString(),
                            join.getRightItem().toString() + " WITH (NOLOCK) ");
                    sql.append(" ").append(temp);
                }
            }
        }

        if (plain.getWhere() != null)
            sql.append(" WHERE ").append(plain.getWhere());

        if (plain.getOracleHierarchical() != null)
            sql.append(plain.getOracleHierarchical().toString());

        sql.append(PlainSelect.getFormatedList(plain.getGroupByColumnReferences(), "GROUP BY"));
        if (plain.getHaving() != null)
            sql.append(" HAVING ").append(plain.getHaving());

        sql.append(PlainSelect.orderByToString(plain.isOracleSiblings(), plain.getOrderByElements()));
        if (plain.getLimit() != null)
            sql.append(plain.getLimit());

    }
    return sql.toString();
}
 

import net.sf.jsqlparser.statement.select.PlainSelect; //导入方法依赖的package包/类
private void checkContainDistinct(PlainSelect plainSelect)
{
   mHasDistinct = (plainSelect.getDistinct() == null) ? false : true;
}