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

        assertNull(q.getWhereClause().getItem(0).getOperand());
        assertEquals(OperatorType.DIFFERENT_FROM, q.getWhereClause().getItem(0).getOperator());
    }


    public void testLimitAndOffset() throws Exception {
        Query q = MetaModelHelper.parseQuery(dc, "SELECT foo FROM sch.tbl LIMIT 1234 OFFSET 5");
        assertEquals("SELECT tbl.foo FROM sch.tbl", q.toSql());
        assertEquals(1234, q.getMaxRows().intValue());
        assertEquals(6, q.getFirstRow().intValue());
    }

View Full Code Here

        assertEquals(1234, q.getMaxRows().intValue());
        assertEquals(6, q.getFirstRow().intValue());
    }


    public void testWhereIn() throws Exception {
        Query q = MetaModelHelper.parseQuery(dc, "SELECT foo FROM sch.tbl WHERE foo IN ('a','b',5)");
        assertEquals("SELECT tbl.foo FROM sch.tbl WHERE tbl.foo IN ('a' , 'b' , '5')", q.toSql());


        FilterItem whereItem = q.getWhereClause().getItem(0);
        assertEquals(OperatorType.IN, whereItem.getOperator());
        Object operand = whereItem.getOperand();
        assertTrue(operand instanceof List);
        assertEquals("a", ((List<?>) operand).get(0));
        assertEquals("b", ((List<?>) operand).get(1));

View Full Code Here

        assertEquals("b", ((List<?>) operand).get(1));
        assertEquals(5, ((List<?>) operand).get(2));
    }


    public void testSimpleSubQuery() throws Exception {
        Query q = MetaModelHelper.parseQuery(dc, "SELECT f.foo AS fo FROM (SELECT * FROM sch.tbl) f");
        assertEquals("SELECT f.foo AS fo FROM (SELECT tbl.foo, tbl.bar, tbl.baz FROM sch.tbl) f", q.toSql());
    }

View Full Code Here

        Query q = MetaModelHelper.parseQuery(dc, "SELECT f.foo AS fo FROM (SELECT * FROM sch.tbl) f");
        assertEquals("SELECT f.foo AS fo FROM (SELECT tbl.foo, tbl.bar, tbl.baz FROM sch.tbl) f", q.toSql());
    }
    
    public void testSelectEverythingFromSubQuery() throws Exception {
        Query q = MetaModelHelper.parseQuery(dc, "SELECT * FROM (SELECT foo, bar FROM sch.tbl) f");
        assertEquals("SELECT f.foo, f.bar FROM (SELECT tbl.foo, tbl.bar FROM sch.tbl) f", q.toSql());
    }

View Full Code Here

            assertEquals("FROM not found in query: SELECT foobar", e.getMessage());
        }
    }


    public void testFullQuery() throws Exception {
        Query q = MetaModelHelper.parseQuery(dc,
                "SELECT foo, COUNT(* ), MAX( baz ) FROM sch.tbl WHERE bar = 'baz' AND foo = bar AND baz > 5 "
                        + "GROUP BY foo HAVING COUNT(*) > 2 ORDER BY foo LIMIT 20 OFFSET 10");
        assertEquals(
                "SELECT tbl.foo, COUNT(*), MAX(tbl.baz) FROM sch.tbl WHERE tbl.bar = 'baz' AND tbl.foo = tbl.bar AND tbl.baz > 5 "
                        + "GROUP BY tbl.foo HAVING COUNT(*) > 2 ORDER BY tbl.foo ASC", q.toSql());
        assertEquals(20, q.getMaxRows().intValue());
        assertEquals(11, q.getFirstRow().intValue());


        // SELECT ...
        // tbl.foo
        assertNotNull("SelectItem 1 should be a column", q.getSelectClause().getItem(0).getColumn());


        // COUNT(*)
        assertNotNull("SelectItem 2 should be a Function", q.getSelectClause().getItem(1).getFunction());
        assertNotNull("SelectItem 2 should be a Function of '*'", q.getSelectClause().getItem(1).getExpression());


        // MAX(tbl.baz)
        assertNotNull("SelectItem 3 should be a Function", q.getSelectClause().getItem(2).getFunction());
        assertNotNull("SelectItem 4 should be a Function of a column", q.getSelectClause().getItem(2).getColumn());


        // FROM tbl.foo
        assertNotNull(q.getFromClause().getItem(0).getTable());


        // GROUP BY tbl.foo
        assertNotNull(q.getGroupByClause().getItem(0).getSelectItem().getColumn());


        // HAVING COUNT(*) > 2
        FilterItem havingItem = q.getHavingClause().getItem(0);
        assertNull(havingItem.getExpression());
        assertNotNull(havingItem.getSelectItem().getFunction());
        assertEquals("*", havingItem.getSelectItem().getExpression());


        // ORDER BY tbl.foo ASC
        OrderByItem orderByItem = q.getOrderByClause().getItem(0);
        assertNull(orderByItem.getSelectItem().getExpression());
        assertNotNull(orderByItem.getSelectItem().getColumn());
        assertEquals(Direction.ASC, orderByItem.getDirection());
    }

View Full Code Here

        dc.query().from(table1).selectCount().select(col1).groupBy(col1).having(FunctionType.SUM, col1).greaterThan(3)
                .orderBy(col1).asc();
    }


    public void testMultipleTables() throws Exception {
        Query q = dc.query().from(table1).as("t1").and(table2).as("t2").select(col1).where(col1).greaterThan(col2)
                .orderBy(col2).desc().toQuery();
        assertEquals("SELECT t1.foo FROM sch.tab1 t1, sch.tab2 t2 " + "WHERE t1.foo > t1.bar ORDER BY t1.bar DESC",
                q.toSql());
    }

View Full Code Here

  private Query query;


  @Override
  protected void setUp() throws Exception {
    super.setUp();
    query = new Query();
    GroupedQueryBuilder queryBuilder = new GroupedQueryBuilderImpl(null,
        query);
    whereBuilder = new WhereBuilderImpl(col1, query, queryBuilder);
  }

View Full Code Here

            }
        }


        assertTrue(obj instanceof Query);


        final Query q = (Query) obj;
        final Table table = q.getFromClause().getItem(0).getTable();
        final Column[] columns = table.getColumns();


        assertEquals("Table[name=mytable,type=TABLE,remarks=null]", table.toString());
        assertEquals("Column[name=mycol1,columnNumber=0,type=INTEGER,nullable=false,nativeType=int,columnSize=16]",
                columns[0].toString());
        assertEquals("Column[name=mycol1,columnNumber=1,type=VARCHAR,nullable=true,nativeType=text,columnSize=255]",
                columns[1].toString());


        assertEquals("SELECT mytable.\"mycol1\" FROM myschema.mytable WHERE mytable.mycol1 = 'foo'", q.toSql());
    }

View Full Code Here

        assertEquals(46, data.toObjectArrays().size());
    }


    public void testSimpleWhere() throws Exception {
        DataContext dc = getDataContext();
        Query q = new Query();
        q.from(table1);
        q.select(table1.getColumns());
        SelectItem countrySelectItem = q.getSelectClause().getSelectItem(
                table1.getColumnByName(COLUMN_CONTRIBUTOR_COUNTRY));
        q.where(new FilterItem(countrySelectItem, OperatorType.EQUALS_TO, "denmark"));


        DataSet data = dc.executeQuery(q);
        for (int i = 0; i < 3; i++) {
            assertTrue("Assertion failed at i=" + i, data.next());
        }

View Full Code Here

        assertFalse(data.next());
    }


    public void testMaxRows() throws Exception {
        DataContext dc = getDataContext();
        Query q = new Query();
        q.from(table1);
        q.select(table1.getColumns());
        q.setMaxRows(3);
        DataSet data1 = dc.executeQuery(q);


        assertTrue(data1.next());
        assertEquals("Row[values=[1, kasper, denmark]]", data1.getRow().toString());
        assertTrue(data1.next());
        assertEquals("Row[values=[2, asbjorn, denmark]]", data1.getRow().toString());
        assertTrue(data1.next());
        assertEquals("Row[values=[3, johny, israel]]", data1.getRow().toString());


        assertFalse(data1.next());
        data1.close();


        q = new Query();
        q.from(table1);
        q.select(table1.getColumns());
        q.setFirstRow(2);
        q.setMaxRows(2);
        DataSet data2 = dc.executeQuery(q);
        assertTrue(data2.next());
        assertEquals("Row[values=[2, asbjorn, denmark]]", data2.getRow().toString());
        assertTrue(data2.next());
        assertEquals("Row[values=[3, johny, israel]]", data2.getRow().toString());

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.