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

org.apache.metamodel.query.Query.orderBy()

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


        assertEquals("SELECT sch.tab.age, COUNT(*) FROM sch.tab WHERE sch.tab.age > TIMESTAMP ('2012-10-31 08:09:54') "
                + "GROUP BY sch.tab.age HAVING COUNT(sch.tab.name) < 100 ORDER BY sch.tab.age ASC", sql);

View Full Code Here

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


        assertEquals("SELECT \"products\".\"title\" AS product-title, SUM(\"orderlines\".\"quantity\") AS orderAmount "
                + "FROM public.\"products\", public.\"orderlines\" "
                + "WHERE \"products\".\"prod_id\" = \"orderlines\".\"prod_id\" " + "GROUP BY \"products\".\"title\" "
                + "HAVING SUM(\"orderlines\".\"quantity\") > 25 " + "ORDER BY \"products\".\"title\" ASC", q.toString());

View Full Code Here

        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(
                "SELECT c.\"COUNTRY\", SUM(c.\"CREDITLIMIT\") FROM PUBLIC.\"CUSTOMERS\" c, PUBLIC.\"EMPLOYEES\" o WHERE c.\"SALESREPEMPLOYEENUMBER\" = o.\"EMPLOYEENUMBER\" GROUP BY c.\"COUNTRY\" ORDER BY c.\"COUNTRY\" ASC",

View Full Code Here

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


        assertEquals("SELECT sch.tab.age, COUNT(*) FROM sch.tab WHERE sch.tab.age > 18 "
                + "GROUP BY sch.tab.age HAVING COUNT(sch.tab.name) < 100 ORDER BY sch.tab.age ASC", sql);

View Full Code Here

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


        assertEquals("SELECT sch.tab.age, COUNT(*) FROM sch.tab WHERE sch.tab.age > TIMESTAMP ('2012-10-31 08:09:54') "
                + "GROUP BY sch.tab.age HAVING COUNT(sch.tab.name) < 100 ORDER BY sch.tab.age ASC", sql);

View Full Code Here

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


        assertEquals("SELECT \"products\".\"title\" AS product-title, SUM(\"orderlines\".\"quantity\") AS orderAmount "
                + "FROM public.\"products\", public.\"orderlines\" "
                + "WHERE \"products\".\"prod_id\" = \"orderlines\".\"prod_id\" " + "GROUP BY \"products\".\"title\" "
                + "HAVING SUM(\"orderlines\".\"quantity\") > 25 " + "ORDER BY \"products\".\"title\" ASC", q.toString());

View Full Code Here

        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(projectIdColumn));
        q.orderBy(roleColumn);
        assertEquals("SELECT r.project_id FROM MetaModelSchema.role r ORDER BY r.name ASC", q.toString());


        DataContext dc = getDataContext();
        DataSet data = dc.executeQuery(q);
        assertEquals(1, data.getSelectItems().length);

View Full Code Here

        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();
        DataSet data = dc.executeQuery(q);

View Full Code Here


        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(
                "SELECT c.name, COUNT(c.contributor_id) AS my_count FROM MetaModelSchema.role c GROUP BY c.name HAVING COUNT(c.contributor_id) > 1 ORDER BY COUNT(c.contributor_id) ASC",
                q.toString());


        DataSet data = getDataContext().executeQuery(q);

View Full Code Here

        MockDataContext dc = new MockDataContext("sch", "tab", "hello");


        Table table = dc.getTableByQualifiedLabel("sch.tab");
        Query q = dc.query().from(table).select("bar").toQuery();
        q.getSelectClause().setDistinct(true);
        q.orderBy(table.getColumnByName("bar"));


        DataSet ds = dc.executeQuery(q);
        assertTrue(ds.next());
        assertEquals("Row[values=[hello]]", ds.getRow().toString());
        assertTrue(ds.next());

View Full Code Here

0 1 2

TOP

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.