@Test
  public void testGroupByQuery() throws Exception {
    Table table = dataContext.getDefaultSchema().getTableByName(peopleIndexType);

    Query q = new Query();
    q.from(table);
    q.groupBy(table.getColumnByName("gender"));
    q.select(
        new SelectItem(table.getColumnByName("gender")),
        new SelectItem(FunctionType.MAX, table.getColumnByName("age")),
        new SelectItem(FunctionType.MIN, table.getColumnByName("age")),
        new SelectItem(FunctionType.COUNT, "*", "total"),
        new SelectItem(FunctionType.MIN, table.getColumnByName("id")).setAlias("firstId"));
    q.orderBy("gender");
    DataSet data = dataContext.executeQuery(q);
    assertEquals(
        "[peopletype.gender, MAX(peopletype.age), MIN(peopletype.age), COUNT(*) AS total, MIN(peopletype.id) AS firstId]",
        Arrays.toString(data.getSelectItems()));

    assertTrue(data.next());
    assertEquals("Row[values=[female, 20, 17, 5, 5]]", data.getRow().toString());
    assertTrue(data.next());
    assertEquals("Row[values=[male, 19, 17, 4, 1]]", data.getRow().toString());
    assertFalse(data.next());
  }
Esempio n. 2
0
  public void testGroupByQuery() throws Exception {
    Connection con = getTestDbConnection();
    DataContext dc = new JdbcDataContext(con);
    Schema schema = dc.getDefaultSchema();
    Table employeesTable = schema.getTableByName("EMPLOYEES");
    Table orderDetailsTable = schema.getTableByName("ORDERDETAILS");
    Query q = new Query().from(employeesTable, "e").from(orderDetailsTable, "c");
    q.select(orderDetailsTable.getColumns()[0])
        .select(new SelectItem(FunctionType.MAX, employeesTable.getColumns()[0]));
    q.groupBy(orderDetailsTable.getColumns()[0]);
    assertEquals(
        "SELECT c._ORDERNUMBER_, MAX(e._EMPLOYEENUMBER_) FROM PUBLIC._EMPLOYEES_ e, PUBLIC._ORDERDETAILS_ c GROUP BY c._ORDERNUMBER_",
        q.toString().replace('\"', '_'));

    QuerySplitter qs = new QuerySplitter(dc, q);
    assertEquals(326, qs.getRowCount());

    List<Query> splitQueries = qs.setMaxRows(250).splitQuery();

    assertEquals(
        "[SELECT c._ORDERNUMBER_, MAX(e._EMPLOYEENUMBER_) FROM PUBLIC._EMPLOYEES_ e, PUBLIC._ORDERDETAILS_ c WHERE (c._ORDERNUMBER_ < 10262 OR c._ORDERNUMBER_ IS NULL) GROUP BY c._ORDERNUMBER_, SELECT c._ORDERNUMBER_, MAX(e._EMPLOYEENUMBER_) FROM PUBLIC._EMPLOYEES_ e, PUBLIC._ORDERDETAILS_ c WHERE (c._ORDERNUMBER_ > 10262 OR c._ORDERNUMBER_ = 10262) GROUP BY c._ORDERNUMBER_]",
        Arrays.toString(splitQueries.toArray()).replace('\"', '_'));
    assertSameCount(dc, qs, splitQueries);
    assertEquals(2, splitQueries.size());
    assertEquals("[162, 164]", Arrays.toString(getCounts(dc, splitQueries)));

    DataSet data = qs.executeQueries();
    int count = 0;
    while (data.next()) {
      if (count == 2) {
        assertEquals("Row[values=[10102, 1702]]", data.getRow().toString());
      }
      count++;
    }
    data.close();
    assertEquals(326, count);
  }