Examples of org.apache.metamodel.query.Query

org.apache.metamodel.query.Query
Represents a query to retrieve data by. A query is made up of six clauses, equivalent to the SQL standard:
- the SELECT clause, which define the wanted columns of the resulting DataSet
- the FROM clause, which define where to retrieve the data from
- the WHERE clause, which define filters on the retrieved data
- the GROUP BY clause, which define if the result should be grouped and aggregated according to some columns acting as categories
- the HAVING clause, which define filters on the grouped data
- the ORDER BY clause, which define sorting of the resulting dataset
In addition two properties are applied to queries to limit the resulting dataset:
- First row: The first row (aka. offset) of the result of the query.
- Max rows: The maximum amount of rows to return when executing the query.
Queries are executed using the DataContext.executeQuery method or can alternatively be used directly in JDBC by using the toString() method. @see DataContext

        SelectItem s1 = new SelectItem(table1.getColumnByName(COLUMN_CONTRIBUTOR_NAME));
        SelectItem s2 = new SelectItem(table2.getColumnByName(COLUMN_ROLE_ROLE_NAME));
        FromItem f1 = new FromItem(table1);
        FromItem f2 = new FromItem(table2);


        Query q = new Query();
        q.select(s1);
        q.select(s2);
        q.from(f1);
        q.from(f2);
        SelectItem s3 = new SelectItem(table1.getColumnByName(COLUMN_CONTRIBUTOR_CONTRIBUTOR_ID));
        SelectItem s4 = new SelectItem(table2.getColumnByName(COLUMN_ROLE_CONTRIBUTOR_ID));
        q.where(new FilterItem(s3, OperatorType.EQUALS_TO, s4));
        assertEquals(
                "SELECT contributor.name, role.name FROM MetaModelSchema.contributor, MetaModelSchema.role WHERE contributor.contributor_id = role.contributor_id",
                q.toString());


        DataSet data = dc.executeQuery(q);
        assertEquals(2, data.getSelectItems().length);
        assertTrue(data.next());
        assertEquals("Row[values=[kasper, founder]]", data.getRow().toString());

View Full Code Here

        // there will be three distinct values in bar column: hello (x2), hi,
        // howdy
        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


        SelectItem s1 = new SelectItem(table1.getColumnByName(COLUMN_CONTRIBUTOR_NAME));
        SelectItem s2 = new SelectItem(table2.getColumnByName(COLUMN_ROLE_ROLE_NAME));
        FromItem fromItem = new FromItem(JoinType.INNER, table1.getRelationships(table2)[0]);


        Query q = new Query();
        q.select(s1);
        q.select(s2);
        q.from(fromItem);
        assertEquals(
                "SELECT contributor.name, role.name FROM MetaModelSchema.contributor INNER JOIN MetaModelSchema.role ON contributor.contributor_id = role.contributor_id",
                q.toString());


        DataSet data = dc.executeQuery(q);
        assertEquals(2, data.getSelectItems().length);
        assertTrue(data.next());
        assertEquals("Row[values=[kasper, founder]]", data.getRow().toString());

View Full Code Here

        assertEquals("Row[values=[jesper, founder]]", data.getRow().toString());
        assertFalse(data.next());
    }


    public void testSubquery() throws Exception {
        Query q1 = new Query();
        q1.from(table1);
        q1.select(table1.getColumns());


        Query q2 = new Query();
        FromItem fromItem = new FromItem(q1);
        q2.from(fromItem);
        SelectItem selectItem = new SelectItem(q1.getSelectClause().getItems().get(1), fromItem);
        selectItem.setAlias("e");
        q2.select(selectItem);
        assertEquals(
                "SELECT name AS e FROM (SELECT contributor.contributor_id, contributor.name, contributor.country FROM MetaModelSchema.contributor)",
                q2.toString());


        fromItem.setAlias("c");
        assertEquals(
                "SELECT c.name AS e FROM (SELECT contributor.contributor_id, contributor.name, contributor.country FROM MetaModelSchema.contributor) c",
                q2.toString());


        DataContext dc = getDataContext();
        DataSet data = dc.executeQuery(q2);
        assertEquals(1, data.getSelectItems().length);
        assertTrue(data.next());
        assertEquals("Row[values=[kasper]]", data.getRow().toString());
        assertTrue(data.next());
        assertEquals("Row[values=[asbjorn]]", data.getRow().toString());
        assertTrue(data.next());
        assertEquals("Row[values=[johny]]", data.getRow().toString());
        assertTrue(data.next());
        assertEquals("Row[values=[daniel]]", data.getRow().toString());
        assertTrue(data.next());
        assertEquals("Row[values=[sasidhar]]", data.getRow().toString());
        assertTrue(data.next());
        assertEquals("Row[values=[jesper]]", data.getRow().toString());
        assertFalse(data.next());


        // Create a sub-query for a sub-query
        Query q3 = new Query();
        fromItem = new FromItem(q2);
        q3.from(fromItem);
        selectItem = new SelectItem(q2.getSelectClause().getItems().get(0), fromItem);
        selectItem.setAlias("f");
        q3.select(selectItem);
        fromItem.setAlias("d");
        assertEquals(
                "SELECT d.e AS f FROM (SELECT c.name AS e FROM (SELECT contributor.contributor_id, contributor.name, contributor.country FROM MetaModelSchema.contributor) c) d",
                q3.toString());
        data = dc.executeQuery(q3);
        assertEquals(1, data.getSelectItems().length);
        assertTrue(data.next());
        assertEquals("Row[values=[kasper]]", data.getRow().toString());
        assertTrue(data.next());

View Full Code Here

        assertEquals("Row[values=[jesper]]", data.getRow().toString());
        assertFalse(data.next());
    }


    public void testOrderBy() throws Exception {
        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());


        DataSet data = getDataContext().executeQuery(q);
        assertTrue(data.next());
        assertEquals("Row[values=[5, sasidhar, unknown]]", data.getRow().toString());
        assertTrue(data.next());

View Full Code Here

    public void testMixedAggregateAndRawQuery() throws Exception {
        MockDataContext dc = new MockDataContext("sch", "tab", "1");
        Table table = dc.getDefaultSchema().getTables()[0];
        Column[] columns = table.getColumns();


        Query query = dc.query().from(table).select(FunctionType.MAX, columns[0]).and(columns[1]).toQuery();
        assertEquals("SELECT MAX(tab.foo), tab.bar FROM sch.tab", query.toSql());


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

View Full Code Here

    public void testSelectItemReferencesToFromItems() throws Exception {
        MockDataContext dc = new MockDataContext("sch", "tab", "1");


        Table table = dc.getDefaultSchema().getTables()[0];


        Query q = new Query();
        FromItem fromItem1 = q.from(table, "t1").getFromClause().getItem(0);
        FromItem fromItem2 = q.from(table, "t2").getFromClause().getItem(1);
        q.select(table.getColumnByName("foo"), fromItem1);
        q.select(table.getColumnByName("foo"), fromItem2);
        q.where(q.getSelectClause().getItem(0), OperatorType.EQUALS_TO, "2");
        assertEquals("SELECT t1.foo, t2.foo FROM sch.tab t1, sch.tab t2 WHERE t1.foo = '2'", q.toSql());


        DataSet ds = dc.executeQuery(q);
        SelectItem[] selectItems = ds.getSelectItems();
        assertEquals(2, selectItems.length);
        assertEquals("t1.foo", selectItems[0].toSql());

View Full Code Here


    public void testDistinct() throws Exception {


        Column roleColumn = table2.getColumnByName(COLUMN_ROLE_ROLE_NAME);


        Query q = new Query().select(roleColumn).from(table2).orderBy(roleColumn);
        q.getSelectClause().setDistinct(true);


        DataContext dc = getDataContext();
        DataSet data = dc.executeQuery(q);
        assertTrue(data.next());
        assertEquals("advisor", data.getRow().getValue(roleColumn));

View Full Code Here

        assertFalse(dataSet.next());
        dataSet.close();


        Relationship relationship = tablesTable.getRelationships(columnsTable)[0];
        FromItem joinFromItem = new FromItem(JoinType.INNER, relationship);
        Query q = new Query().select(tablesTable.getColumnByName("name")).select(columnsTable.getColumnByName("name"))
                .select(columnsTable.getBooleanColumns()).from(joinFromItem);


        assertEquals("SELECT tables.name, columns.name, columns.nullable, columns.indexed "
                + "FROM information_schema.tables INNER JOIN information_schema.columns "
                + "ON tables.name = columns.table", q.toString());


        dataSet = dc.executeQuery(q);
        assertTrue(dataSet.next());
        assertEquals("Row[values=[contributor, contributor_id, false, true]]", dataSet.getRow().toString());
        assertTrue(dataSet.next());

View Full Code Here

        assertEquals("Row[values=[project, name, false, false]]", dataSet.getRow().toString());
        dataSet.close();
    }


    public void testOrderByWithoutSelecting() throws Exception {
        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

0 1 2 3 4 5 6 7 8 9

TOP

Related Classes of org.apache.metamodel.query.Query

org.apache.metamodel.AbstractDataContext

org.apache.metamodel.CompositeDataContextTest

org.apache.metamodel.CompositeQueryDelegate

org.apache.metamodel.convert.ConvertedDataSetInterceptorTest

org.apache.metamodel.convert.Converters

org.apache.metamodel.convert.ConvertersTest

org.apache.metamodel.csv.CsvBigFileMemoryTest

org.apache.metamodel.csv.CsvDataContextTest

org.apache.metamodel.DB2Test

org.apache.metamodel.dbmains.PostgresqlMain

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.