Package org.apache.metamodel.query

Examples of org.apache.metamodel.query.Query.groupBy()

441
442
443
444
445
446
447
448
449
450
451
        DataContext dc = new CsvDataContext(new File("src/test/resources/csv_people.csv"));
        Table table = dc.getDefaultSchema().getTableByName("csv_people.csv");

        Query q = new Query();
        q.from(table);
        q.groupBy(table.getColumnByName("gender"));
        q.select(new SelectItem(table.getColumnByName("gender")),
                new SelectItem(FunctionType.MAX, table.getColumnByName("age")),
                new SelectItem(FunctionType.MIN, table.getColumnByName("age")), new SelectItem(FunctionType.COUNT, "*",
                        "total"), new SelectItem(FunctionType.MIN, table.getColumnByName("id")).setAlias("firstId"));
        DataSet data = dc.executeQuery(q);
View Full Code Here
340
341
342
343
344
345
346
347
348
349
350
        Query q = new Query();
        q.from(new FromItem(table2).setAlias("r"));
        Column roleColumn = table2.getColumnByName(COLUMN_ROLE_ROLE_NAME);
        Column projectIdColumn = table2.getColumnByName(COLUMN_ROLE_PROJECT_ID);
        q.select(new SelectItem(FunctionType.SUM, projectIdColumn));
        q.groupBy(new GroupByItem(new SelectItem(roleColumn)));
        q.orderBy(roleColumn);
        assertEquals("SELECT SUM(r.project_id) FROM MetaModelSchema.role r GROUP BY r.name ORDER BY r.name ASC",
                q.toString());

        DataContext dc = getDataContext();
View Full Code Here
374
375
376
377
378
379
380
381
382
383
384
    public void testSimpleGroupBy() throws Exception {
        Query q = new Query();
        q.from(new FromItem(table2).setAlias("r"));
        Column roleColumn = table2.getColumnByName(COLUMN_ROLE_ROLE_NAME);
        q.select(new SelectItem(roleColumn));
        q.groupBy(new GroupByItem(new SelectItem(roleColumn)));
        assertEquals("SELECT r.name FROM MetaModelSchema.role r GROUP BY r.name", q.toString());

        DataContext dc = getDataContext();
        DataSet data = dc.executeQuery(q);
        assertEquals(1, data.getSelectItems().length);
View Full Code Here
417
418
419
420
421
422
423
424
425
426
427
        Query q = new Query();
        q.from(table2, "c");
        Column roleColumn = table2.getColumnByName(COLUMN_ROLE_ROLE_NAME);
        Column contributorIdColumn = table2.getColumnByName(COLUMN_ROLE_CONTRIBUTOR_ID);

        q.groupBy(roleColumn);
        SelectItem countSelectItem = new SelectItem(FunctionType.COUNT, contributorIdColumn).setAlias("my_count");
        q.select(new SelectItem(roleColumn), countSelectItem);
        q.having(new FilterItem(countSelectItem, OperatorType.GREATER_THAN, 1));
        q.orderBy(new OrderByItem(countSelectItem));
        assertEquals(
View Full Code Here
440
441
442
443
444
445
446
447
448
449
450
        Query q = new Query();
        q.from(table2, "c");
        Column roleColumn = table2.getColumnByName(COLUMN_ROLE_ROLE_NAME);
        Column contributorIdColumn = table2.getColumnByName(COLUMN_ROLE_CONTRIBUTOR_ID);

        q.groupBy(roleColumn);
        SelectItem countSelectItem = new SelectItem(FunctionType.COUNT, contributorIdColumn).setAlias("my_count");
        q.select(new SelectItem(roleColumn));
        q.having(new FilterItem(countSelectItem, OperatorType.GREATER_THAN, 3));
        assertEquals("SELECT c.name FROM MetaModelSchema.role c GROUP BY c.name HAVING COUNT(c.contributor_id) > 3",
                q.toString());
View Full Code Here
707
708
709
710
711
712
713
714
715
716
717
        Column commonProdIdColumn = orderlinesTable.getColumnByName("prod_id");
        Column quantityColumn = orderlinesTable.getColumnByName("quantity");

        q.from(orderlinesTable);
        q.where(new FilterItem(new SelectItem(prodIdColumn), OperatorType.EQUALS_TO, new SelectItem(commonProdIdColumn)));
        q.groupBy(titleColumn);
        q.getSelectClause().removeItem(q.getSelectClause().getSelectItem(productPriceColumn));
        SelectItem quantitySum = new SelectItem(FunctionType.SUM, quantityColumn).setAlias("orderAmount");
        q.select(quantitySum);
        q.having(new FilterItem(quantitySum, OperatorType.GREATER_THAN, 25));
        q.orderBy(new OrderByItem(q.getSelectClause().getItem(0)));
View Full Code Here
414
415
416
417
418
419
420
421
422
423
424
        Query q = new Query();
        q.from(customersTable, "c");
        q.from(employeeTable, "o");
        SelectItem countrySelect = new SelectItem(countryColumn);
        q.select(countrySelect, new SelectItem(FunctionType.SUM, creditLimitColumn));
        q.groupBy(countryColumn);
        q.orderBy(new OrderByItem(countrySelect));
        q.where(new FilterItem(new SelectItem(employeeNumberColumn1), OperatorType.EQUALS_TO, new SelectItem(
                employeeNumberColumn2)));

        assertEquals(
View Full Code Here
84
85
86
87
88
89
90
91
92
93
94
    Query q = new Query().from(employeesTable, "e").from(orderDetailsTable,
        "c");
    q.select(orderDetailsTable.getColumns()[0])
        .select(new SelectItem(FunctionType.MAX, employeesTable
            .getColumns()[0]));
    q.groupBy(orderDetailsTable.getColumns()[0]);
    assertEquals(
        "SELECT c._ORDERNUMBER_, MAX(e._EMPLOYEENUMBER_) FROM PUBLIC._EMPLOYEES_ e, PUBLIC._ORDERDETAILS_ c GROUP BY c._ORDERNUMBER_",
        q.toString().replace('\"', '_'));

    QuerySplitter qs = new QuerySplitter(dc, q);
View Full Code Here
149
150
151
152
153
154
155
156
157
158
159
        final Query q = new Query();
        q.select(ageColumn).selectCount();
        q.from(table);
        q.where(ageColumn, OperatorType.GREATER_THAN, 18);
        q.groupBy(ageColumn);
        q.having(FunctionType.COUNT, nameColumn, OperatorType.LESS_THAN, 100);
        q.orderBy(ageColumn);

        final String sql = new DB2QueryRewriter(null).rewriteQuery(q);
View Full Code Here
172
173
174
175
176
177
178
179
180
181
182
        final Query q = new Query();
        q.select(dateColumn).selectCount();
        q.from(table);
        q.where(dateColumn, OperatorType.GREATER_THAN, "2012-10-31 08:09:54");
        q.groupBy(dateColumn);
        q.having(FunctionType.COUNT, nameColumn, OperatorType.LESS_THAN, 100);
        q.orderBy(dateColumn);

        final String sql = new DB2QueryRewriter(null).rewriteQuery(q);
View Full Code Here
TOP
Copyright © 2018 www.massapi.com. All rights reserved.
All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.