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

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

        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

        Query q = new Query();
        q.from(new FromItem(table1).setAlias("c"));
        q.select(table1.getColumns());
        OrderByItem countryOrderBy = new OrderByItem(q.getSelectClause().getItem(2), Direction.DESC);
        OrderByItem nameOrderBy = new OrderByItem(q.getSelectClause().getItem(1));
        q.orderBy(countryOrderBy, nameOrderBy);


        assertEquals(
                "SELECT c.contributor_id, c.name, c.country FROM MetaModelSchema.contributor c ORDER BY c.country DESC, c.name 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

        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

                str);
    }
    
    public void testRewriteFirstRowWithOrderByClause() throws Exception {
        Query q = new Query().from(table).select(col).setFirstRow(401);
        q.orderBy(col);
        String str = new DB2QueryRewriter(null).rewriteQuery(q);


        assertEquals(
                "SELECT metamodel_subquery.bar FROM (SELECT sch.foo.bar, ROW_NUMBER() OVER( ORDER BY sch.foo.bar ASC) AS metamodel_row_number FROM sch.foo) metamodel_subquery WHERE metamodel_row_number > 400",
                str);

View Full Code Here

                str);
    }
    
    public void testRewriteFirstRowAndMaxRowsWithOrderByClause() throws Exception {
        Query q = new Query().from(table).select(col).setFirstRow(401).setMaxRows(400);
        q.orderBy(col);
        String str = new DB2QueryRewriter(null).rewriteQuery(q);
        assertEquals(
                "SELECT metamodel_subquery.bar FROM (SELECT sch.foo.bar, ROW_NUMBER() OVER( ORDER BY sch.foo.bar ASC) AS metamodel_row_number FROM sch.foo) metamodel_subquery WHERE metamodel_row_number BETWEEN 401 AND 800",
                str);
    }

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

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.