com.alibaba.druid.sql.dialect.mysql.ast.expr.MySqlOrderingExpr Java Examples

@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 String[] buildGroupByCols(List<SQLExpr> groupByItems, Map<String, String> aliaColumns) {
    String[] groupByCols = new String[groupByItems.size()];
    for (int i = 0; i < groupByItems.size(); i++) {
        SQLExpr sqlExpr = groupByItems.get(i);
        String column = null;
        if (sqlExpr instanceof SQLIdentifierExpr) {
            column = ((SQLIdentifierExpr) sqlExpr).getName();
        } else if (sqlExpr instanceof SQLMethodInvokeExpr) {
            column = sqlExpr.toString();
        } else if (sqlExpr instanceof MySqlOrderingExpr) {
            // todo czn
            SQLExpr expr = ((MySqlOrderingExpr) sqlExpr).getExpr();

            if (expr instanceof SQLName) {
                column = StringUtil.removeBackQuote(((SQLName) expr).getSimpleName());
            } else {
                column = StringUtil.removeBackQuote(expr.toString());
            }
        } else if (sqlExpr instanceof SQLPropertyExpr) {
            /*
             * eg:select id from (select h.id from hotnews h union
             * select h.title from hotnews h ) as t1 group by t1.id;
             */
            column = sqlExpr.toString();
        }
        if (column == null) {
            column = sqlExpr.toString();
        }
        int dotIndex = column.indexOf(".");
        int bracketIndex = column.indexOf("(");
        // check it is a function
        if (dotIndex != -1 && bracketIndex == -1) {
            // get column from table.column
            column = column.substring(dotIndex + 1);
        }
        groupByCols[i] = getAliaColumn(aliaColumns, column); // column;
    }
    return groupByCols;
}
 

public MySqlOrderingExpr parseSelectGroupByItem() {
    MySqlOrderingExpr item = new MySqlOrderingExpr();

    item.setExpr(expr());

    if (lexer.token() == Token.ASC) {
        lexer.nextToken();
        item.setType(SQLOrderingSpecification.ASC);
    } else if (lexer.token() == Token.DESC) {
        lexer.nextToken();
        item.setType(SQLOrderingSpecification.DESC);
    }

    return item;
}
 

private String[] buildGroupByCols(List<SQLExpr> groupByItems,Map<String, String> aliaColumns) {
	String[] groupByCols = new String[groupByItems.size()];
	for(int i= 0; i < groupByItems.size(); i++) {
		SQLExpr sqlExpr = groupByItems.get(i);
		String column = null;
		if(sqlExpr instanceof SQLIdentifierExpr )
		{
			column=((SQLIdentifierExpr) sqlExpr).getName();
		} else if(sqlExpr instanceof SQLMethodInvokeExpr){
			column = ((SQLMethodInvokeExpr) sqlExpr).toString();
		} else if(sqlExpr instanceof MySqlOrderingExpr){
			//todo czn
			SQLExpr expr = ((MySqlOrderingExpr) sqlExpr).getExpr();

			if (expr instanceof SQLName)
			{
				column = StringUtil.removeBackquote(((SQLName) expr).getSimpleName());//不要转大写 2015-2-10 sohudo StringUtil.removeBackquote(expr.getSimpleName().toUpperCase());
			} else
			{
				column = StringUtil.removeBackquote(expr.toString());
			}
		} else if(sqlExpr instanceof SQLPropertyExpr){
			/**
			 * 针对子查询别名，例如select id from (select h.id from hotnews h  union select h.title from hotnews h ) as t1 group by t1.id;
			 */
			column = sqlExpr.toString();
		}
		if(column == null){
			column = sqlExpr.toString();
		}
		int dotIndex=column.indexOf(".") ;
		int bracketIndex=column.indexOf("(") ;
		//通过判断含有括号来决定是否为函数列
		if(dotIndex!=-1&&bracketIndex==-1)
		{
			//此步骤得到的column必须是不带.的，有别名的用别名，无别名的用字段名
			column=column.substring(dotIndex+1) ;
		}
		groupByCols[i] = getAliaColumn(aliaColumns,column);//column;
	}
	return groupByCols;
}
 

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));
    }
}