public static SeQueryInfo parse(ISession session, PlainSelect select) throws SeException, IOException { String[] columns = null; String[] tables = null; String where = null; String orderAndOrGroupByClause = null; if (LOGGER.isLoggable(Level.FINER)) { LOGGER.finer("building SeQueryInfo to reflect " + select); } // obtain needed SeQueryInfo components columns = getColumns(session, select.getSelectItems()); tables = getTables(select.getFromItems()); Expression whereClause = select.getWhere(); if (whereClause != null) { where = whereClause.toString(); } if (select.getGroupByColumnReferences() != null && select.getGroupByColumnReferences().size() > 0) { String gb = PlainSelect.getFormatedList(select.getGroupByColumnReferences(), " GROUP BY "); orderAndOrGroupByClause = gb; } if (select.getOrderByElements() != null && select.getOrderByElements().size() > 0) { String ob = PlainSelect.orderByToString(select.getOrderByElements()); if (orderAndOrGroupByClause == null) { orderAndOrGroupByClause = ""; } orderAndOrGroupByClause += " " + ob; } // build SeQueryInfo SeQueryInfo qinfo = new SeQueryInfo(); qinfo.setColumns(columns); SeSqlConstruct sqlConstruct = new SeSqlConstruct(); sqlConstruct.setTables(tables); if (where != null) { sqlConstruct.setWhere(where); } qinfo.setConstruct(sqlConstruct); if (orderAndOrGroupByClause != null) { qinfo.setByClause(orderAndOrGroupByClause); } return qinfo; }
public void testGroupBy() throws JSQLParserException { String statement = "SELECT * FROM tab1 WHERE a > 34 GROUP BY tab1.b"; PlainSelect plainSelect = (PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody(); assertEquals(1, plainSelect.getGroupByColumnReferences().size()); assertEquals( "tab1.b", ((Column) plainSelect.getGroupByColumnReferences().get(0)).getWholeColumnName()); assertEquals(statement, "" + plainSelect); statement = "SELECT * FROM tab1 WHERE a > 34 GROUP BY 2, 3"; plainSelect = (PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody(); assertEquals(2, plainSelect.getGroupByColumnReferences().size()); assertEquals(2, ((ColumnIndex) plainSelect.getGroupByColumnReferences().get(0)).getIndex()); assertEquals(3, ((ColumnIndex) plainSelect.getGroupByColumnReferences().get(1)).getIndex()); assertEquals(statement, "" + plainSelect); }
@SuppressWarnings("unchecked") @Override public void visit(PlainSelect select) { if (!CollectionUtils.isEmpty(select.getGroupByColumnReferences()) || !CollectionUtils.isEmpty(select.getJoins())) { isSimple = false; } else { select.getFromItem().accept(this); for (SelectItem item : (List<SelectItem>) select.getSelectItems()) { item.accept(this); } } }
/** @brief The following functions override functions of the interfaces. */ public void visit(PlainSelect plainSelect) throws Exception { if (plainSelect.getFromItem() != null) { if (plainSelect.getFromItem().getAlias() != null) { this.aliasTableNameList.add(plainSelect.getFromItem().getAlias().getName()); } plainSelect.getFromItem().accept(this); } if (plainSelect.getJoins() != null) { for (Iterator joinsIt = plainSelect.getJoins().iterator(); joinsIt.hasNext(); ) { Join join = (Join) joinsIt.next(); if (join.getRightItem().getAlias() != null) { this.aliasTableNameList.add(join.getRightItem().getAlias().getName()); } if (join.getOnExpression() != null) { join.getOnExpression().accept(this); } join.getRightItem().accept(this); } } // Select selectItem From fromItem, joinItem Where whereClause. if (plainSelect.getSelectItems() != null) { for (SelectItem selectItem : plainSelect.getSelectItems()) { selectItem.accept(this); } } if (plainSelect.getWhere() != null) { plainSelect.getWhere().accept(this); } if (plainSelect.getGroupByColumnReferences() != null) { for (Iterator groupByIt = plainSelect.getGroupByColumnReferences().iterator(); groupByIt.hasNext(); ) { Expression groupBy = (Expression) groupByIt.next(); groupBy.accept(this); } } if (plainSelect.getClusterByElements() != null) { for (Iterator clusterByit = plainSelect.getClusterByElements().iterator(); clusterByit.hasNext(); ) { ClusterByElement clusterByElement = (ClusterByElement) clusterByit.next(); visit(clusterByElement); } } if (plainSelect.getDistributeByElements() != null) { for (Iterator distributeByIt = plainSelect.getDistributeByElements().iterator(); distributeByIt.hasNext(); ) { DistributeByElement distributeByElement = (DistributeByElement) distributeByIt.next(); visit(distributeByElement); } } if (plainSelect.getOrderByElements() != null) { for (Iterator orderByIt = plainSelect.getOrderByElements().iterator(); orderByIt.hasNext(); ) { OrderByElement orderByElement = (OrderByElement) orderByIt.next(); orderByElement.accept(this); } } if (plainSelect.getSortByElements() != null) { for (Iterator sortByIt = plainSelect.getSortByElements().iterator(); sortByIt.hasNext(); ) { SortByElement sortByElement = (SortByElement) sortByIt.next(); visit(sortByElement); } } if (plainSelect.getHaving() != null) { plainSelect.getHaving().accept(this); } }