Java Code Examples for com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock#getGroupBy()

@Test
public void testGroupbyOrder() {
    MySqlSelectQueryBlock query = getQuery("select col1,col2 from table1 group by col1 desc,col2 asc ");
    SQLSelectGroupByClause groupBy = query.getGroupBy();
    int i = 0;
    for (SQLExpr p : groupBy.getItems()) {
        i++;
        String groupCol = "col" + i;
        MySqlOrderingExpr groupitem = (MySqlOrderingExpr) p;
        SQLExpr q = groupitem.getExpr();
        MySQLItemVisitor v = new MySQLItemVisitor(this.currentDb, utf8Charset, null, null);
        q.accept(v);
        Item item = v.getItem();
        Assert.assertEquals(true, groupCol.equals(item.getItemName()));
    }
}
 

private void parseAggGroupCommon(SchemaConfig schema, SQLStatement stmt, RouteResultset rrs,
                                 MySqlSelectQueryBlock mysqlSelectQuery, TableConfig tc) throws SQLException {
    Map<String, String> aliaColumns = new HashMap<>();
    boolean isDistinct = (mysqlSelectQuery.getDistionOption() == SQLSetQuantifier.DISTINCT) || (mysqlSelectQuery.getDistionOption() == SQLSetQuantifier.DISTINCTROW);
    parseAggExprCommon(schema, rrs, mysqlSelectQuery, aliaColumns, tc, isDistinct);
    if (rrs.isNeedOptimizer()) {
        tryAddLimit(schema, tc, mysqlSelectQuery, rrs);
        rrs.setSqlStatement(stmt);
        return;
    }

    // distinct change to group by
    if (isDistinct) {
        mysqlSelectQuery.setDistionOption(0);
        SQLSelectGroupByClause groupBy = new SQLSelectGroupByClause();
        for (String fieldName : aliaColumns.keySet()) {
            groupBy.addItem(new SQLIdentifierExpr(fieldName));
        }
        mysqlSelectQuery.setGroupBy(groupBy);
    }

    // setGroupByCols
    if (mysqlSelectQuery.getGroupBy() != null) {
        List<SQLExpr> groupByItems = mysqlSelectQuery.getGroupBy().getItems();
        String[] groupByCols = buildGroupByCols(groupByItems, aliaColumns);
        rrs.setGroupByCols(groupByCols);
    }

    if (isDistinct) {
        rrs.changeNodeSqlAfterAddLimit(statementToString(stmt), 0, -1);
    }
}
 

@Test
public void testGroupby() {
    MySqlSelectQueryBlock query = getQuery("select col1,col2 from table1 group by col1,col2");
    SQLSelectGroupByClause groupBy = query.getGroupBy();
    int i = 0;
    for (SQLExpr p : groupBy.getItems()) {
        i++;
        String groupCol = "col" + i;
        MySQLItemVisitor v = new MySQLItemVisitor(this.currentDb, utf8Charset, null, null);
        p.accept(v);
        Item item = v.getItem();
        Assert.assertEquals(true, groupCol.equals(item.getItemName()));
    }
}
 

@Test
public void testGroupbyHaving() {
    MySqlSelectQueryBlock query = getQuery("select col1  from table1 group by col1 having count(*)>1  ");
    SQLSelectGroupByClause groupBy = query.getGroupBy();
    SQLExpr q = groupBy.getHaving();
    MySQLItemVisitor v = new MySQLItemVisitor(this.currentDb, utf8Charset, null, null);
    q.accept(v);
    Item item = v.getItem();
    Assert.assertEquals(true, "COUNT(*) > 1".equals(item.getItemName()));
}
 

public boolean visit(MySqlSelectQueryBlock x) {
	if (x.getOrderBy() != null) {
		x.getOrderBy().setParent(x);
	}

	boolean rewriteDistinct = false;
	if (x.getSelectList() != null) {
		rewriteDistinct = visitSelectItems(x.getSelectList(), SQLSetQuantifier.DISTINCT == x.getDistionOption());
	}

	if (x.getFrom() != null) {
		println();
		print0(ucase ? "FROM " : "from ");
		x.getFrom().accept(this);
	}

	if (x.getWhere() != null) {
		println();
		print0(ucase ? "WHERE " : "where ");
		x.getWhere().setParent(x);
		x.getWhere().accept(this);
	}

	if (x.getGroupBy() != null) {
		println();
		x.getGroupBy().accept(this);
	}

	if (x.getOrderBy() != null) {
		println();
		x.getOrderBy().accept(this);
	}

	if (rewriteDistinct) {
		print0(") ZebraDaoDistinctTable");
	}

	return false;
}
 

/**
 * 解析groupby
 * @param result
 * @param plan
 * @param mysqlSelectQuery
 */
protected void parseGroupBy(ParseResult result, ExecutePlan plan, MySqlSelectQueryBlock mysqlSelectQuery){
    if(mysqlSelectQuery.getGroupBy() == null) {
        return;
    }
    List<SQLExpr> groupByItems = mysqlSelectQuery.getGroupBy().getItems();
    if (groupByItems == null || groupByItems.size() == 0){
        return;
    }
    List<SQLSelectItem> selectList      = mysqlSelectQuery.getSelectList();
    List<String> groupbyColumns  = new ArrayList<String>(groupByItems.size());
    for(SQLExpr item : groupByItems){
        String name = null;
        if (item instanceof MySqlSelectGroupByExpr){
            name = StringUtil.removeDot(((MySqlSelectGroupByExpr) item).getExpr().toString());
        }else if (item instanceof SQLIdentifierExpr){
            name = item.toString();
        }else if (item instanceof SQLName){
            name = ((SQLName) item).getSimpleName();
        }else {
            throw new BayMaxException("group by 不支持的表达式:" + item.toString());
        }
        if (result.getAliaColumns() != null){
            // 有别名,说明在select list中使用了别名
            String alias = result.getAliaColumns().get(name);
            if (alias != null){
                // select user_id as uid ....order by user_id
                // 要把oderby的user_id转换为uid,以便结果集合并,这个替换是等价的
                // 因为合并的时候需要根据orderby的字段,取值,比较
                groupbyColumns.add(alias);
                continue;
            }
        }
        if (!result.isHasAllColumnExpr()){
            // select列表中没有orderby的字段 添加，用于后面做合并
            SQLIdentifierExpr exp = new SQLIdentifierExpr(name);
            // item
            SQLSelectItem addItem = new SQLSelectItem();
            addItem.setExpr(exp);
            exp.setParent(item);
            selectList.add(addItem);
        }
        groupbyColumns.add(name);
    }
    plan.setGroupbyColumns(groupbyColumns);
}
 

private void findGroupBy(MySqlSelectQueryBlock query, Select select) throws SqlParseException {
    SQLSelectGroupByClause groupBy = query.getGroupBy();

    //modified by xzb group by 增加Having语法
    if (null != query.getGroupBy() && null != query.getGroupBy().getHaving()) {
        select.setHaving(query.getGroupBy().getHaving().toString());
    }

    SQLTableSource sqlTableSource = query.getFrom();
    if (groupBy == null) {
        return;
    }
    List<SQLExpr> items = groupBy.getItems();

    List<SQLExpr> standardGroupBys = new ArrayList<>();
    for (SQLExpr sqlExpr : items) {
        //todo: mysql expr patch
        if (sqlExpr instanceof MySqlOrderingExpr) {
            MySqlOrderingExpr sqlSelectGroupByExpr = (MySqlOrderingExpr) sqlExpr;
            sqlExpr = sqlSelectGroupByExpr.getExpr();
        }
        if ((sqlExpr instanceof SQLParensIdentifierExpr || !(sqlExpr instanceof SQLIdentifierExpr || sqlExpr instanceof SQLMethodInvokeExpr)) && !standardGroupBys.isEmpty()) {
            // flush the standard group bys
            // zhongshu-comment 先将standardGroupBys里面的字段传到select对象的groupBys字段中，然后给standardGroupBys分配一个没有元素的新的list
            select.addGroupBy(convertExprsToFields(standardGroupBys, sqlTableSource));
            standardGroupBys = new ArrayList<>();
        }

        if (sqlExpr instanceof SQLParensIdentifierExpr) {
            // single item with parens (should get its own aggregation)
            select.addGroupBy(FieldMaker.makeField(((SQLParensIdentifierExpr) sqlExpr).getExpr(), null, sqlTableSource.getAlias()));
        } else if (sqlExpr instanceof SQLListExpr) {
            // multiple items in their own list
            SQLListExpr listExpr = (SQLListExpr) sqlExpr;
            select.addGroupBy(convertExprsToFields(listExpr.getItems(), sqlTableSource));
        } else {
            // everything else gets added to the running list of standard group bys
            standardGroupBys.add(sqlExpr);
        }
    }
    if (!standardGroupBys.isEmpty()) {
        select.addGroupBy(convertExprsToFields(standardGroupBys, sqlTableSource));
    }
}