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

        assertEquals(1, tableModel.getValueAt(6, 0));
        assertEquals(1, tableModel.getValueAt(7, 0));
    }


    public void testGroupByWithoutSelecting() 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(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);
        assertEquals(1, data.getSelectItems().length);
        assertEquals("SUM(r.project_id)", data.getSelectItems()[0].toString());


        @SuppressWarnings("deprecation")
        TableModel tableModel = data.toTableModel();
        assertEquals(3, tableModel.getRowCount());
        assertEquals(1, tableModel.getColumnCount());
        assertEquals(1.0, tableModel.getValueAt(0, 0));
        assertEquals(9.0, tableModel.getValueAt(1, 0));
        assertEquals(2.0, tableModel.getValueAt(2, 0));


        q = dc.query().from(table2).select("name").orderBy("name").toQuery();
        q.getSelectClause().setDistinct(true);


        tableModel = new DataSetTableModel(dc.executeQuery(q));
        assertEquals(3, tableModel.getRowCount());
        assertEquals(1, tableModel.getColumnCount());
        assertEquals("advisor", tableModel.getValueAt(0, 0));

View Full Code Here

        assertEquals("developer", tableModel.getValueAt(1, 0));
        assertEquals("founder", tableModel.getValueAt(2, 0));
    }


    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);
        assertEquals("r.name", data.getSelectItems()[0].toString());
        TableModel tableModel = new DataSetTableModel(data);
        assertEquals(3, tableModel.getRowCount());


        q.select(new SelectItem(FunctionType.COUNT, "*", "c"));
        q.where(new FilterItem(new SelectItem(roleColumn), OperatorType.EQUALS_TO, "founder"));
        data = dc.executeQuery(q);
        assertEquals(2, data.getSelectItems().length);
        assertEquals("r.name", data.getSelectItems()[0].toString());
        assertEquals("COUNT(*) AS c", data.getSelectItems()[1].toString());
        tableModel = new DataSetTableModel(data);
        assertEquals(1, tableModel.getRowCount());
        assertEquals("founder", tableModel.getValueAt(0, 0));
        assertEquals(2l, tableModel.getValueAt(0, 1));


        q.select(new SelectItem(FunctionType.SUM, table2.getColumns()[0]));
        assertEquals(
                "SELECT r.name, COUNT(*) AS c, SUM(r.contributor_id) FROM MetaModelSchema.role r WHERE r.name = 'founder' GROUP BY r.name",
                q.toString());
        data = dc.executeQuery(q);
        assertEquals(3, data.getSelectItems().length);
        assertEquals("r.name", data.getSelectItems()[0].toString());
        assertEquals("COUNT(*) AS c", data.getSelectItems()[1].toString());
        assertEquals("SUM(r.contributor_id)", data.getSelectItems()[2].toString());

View Full Code Here

        assertEquals(2l, tableModel.getValueAt(0, 1));
        assertEquals(7.0, tableModel.getValueAt(0, 2));
    }


    public void testSimpleHaving() throws Exception {
        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(
                "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);
        assertTrue(data.next());
        assertEquals("Row[values=[founder, 2]]", data.getRow().toString());
        assertTrue(data.next());

View Full Code Here

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


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


        DataSet data = getDataContext().executeQuery(q);
        assertTrue(data.next());
        assertEquals("Row[values=[developer]]", data.getRow().toString());
        assertFalse(data.next());
        data.close();


        q.getHavingClause().removeItems();
        q.having(new FilterItem(SelectItem.getCountAllItem(), OperatorType.GREATER_THAN, 3));
        assertEquals("SELECT c.name FROM MetaModelSchema.role c GROUP BY c.name HAVING COUNT(*) > 3", q.toString());
        data = getDataContext().executeQuery(q);
        assertTrue(data.next());
        assertEquals("Row[values=[developer]]", data.getRow().toString());
        assertFalse(data.next());
        data.close();

View Full Code Here


    public void testCompiledQueryParameterInSubQuery() throws Exception {
        final DataContext dc = getDataContext();


        final QueryParameter param1 = new QueryParameter();
        final Query subQuery = dc.query().from(table1).select("name").where(COLUMN_CONTRIBUTOR_COUNTRY).eq(param1)
                .toQuery();


        final FromItem subQueryFromItem = new FromItem(subQuery);
        final Query query = new Query().select(new SelectItem(subQuery.getSelectClause().getItem(0), subQueryFromItem))
                .from(subQueryFromItem);


        final CompiledQuery compiledQuery = dc.compileQuery(query);


        try {

View Full Code Here

        }
    }


    public void testSelectCount() throws Exception {
        DataContext dc = getDataContext();
        Query q = new Query();
        q.from(table1);
        q.selectCount();


        Row row = MetaModelHelper.executeSingleRowQuery(dc, q);
        assertEquals("6", row.getValue(0).toString());
    }

View Full Code Here

        assertEquals("6", row.getValue(0).toString());
    }


    public void testSimpleSelect() throws Exception {
        DataContext dc = getDataContext();
        Query q = new Query();
        q.from(table1);
        q.select(table1.getColumns());
        DataSet dataSet = dc.executeQuery(q);
        assertTrue(dataSet.next());
        Row row = dataSet.getRow();
        assertEquals("Row[values=[1, kasper, denmark]]", row.toString());
        assertTrue(dataSet.next());

View Full Code Here

        assertFalse(dataSet.next());
    }


    public void testCarthesianProduct() throws Exception {
        DataContext dc = getDataContext();
        Query q = new Query();
        q.from(table1);
        q.from(table2);
        q.select(table1.getColumns());
        q.select(table2.getColumns());
        DataSet data = dc.executeQuery(q);
        assertEquals(table1.getColumnCount() + table2.getColumnCount(), data.getSelectItems().length);
        for (int i = 0; i < 6 * 8; i++) {
            assertTrue(data.next());
            if (i == 0) {

View Full Code Here


    public void testJoinAndFirstRow() throws Exception {
        DataSet data;


        DataContext dc = getDataContext();
        Query q = new Query();
        q.from(table1);
        q.from(table2);
        q.select(table1.getColumns());
        q.select(table2.getColumns());
        data = dc.executeQuery(q);
        assertEquals(48, data.toObjectArrays().size());


        q.setFirstRow(3);
        data = dc.executeQuery(q);
        assertEquals(46, data.toObjectArrays().size());
    }

View Full Code Here

        MutableColumn col = (MutableColumn) dc.getColumnByQualifiedLabel("tbl.baz");
        col.setType(ColumnType.INTEGER);
    };


    public void testQueryInLowerCase() throws Exception {
        Query q = MetaModelHelper.parseQuery(dc,
                "select a.foo as f from sch.tbl a inner join sch.tbl b on a.foo=b.foo order by a.foo asc");
        assertEquals("SELECT a.foo AS f FROM sch.tbl a INNER JOIN sch.tbl b ON a.foo = b.foo ORDER BY a.foo ASC",
                q.toSql());
    }

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.