Java Query.orderBy方法代码示例

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
@Override
public Query optimizeQuery(Query q, Category category) {
    if (category == Category.VALID) {
        q.setMaxRows(maxRows);

        if (firstRow > 1) {
            q.setFirstRow(firstRow);
        }

        if (orderColumn != null) {
            Column physicalColumn = orderColumn.getPhysicalColumn();
            q.orderBy(physicalColumn);
        }
    } else {
        throw new IllegalStateException("Can only optimize the VALID max rows category");
    }
    return q;
}
 

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
public void testFullyQualifiedColumnNames() throws Exception {
    final MutableSchema schema = new MutableSchema("sch");
    final MutableTable table = new MutableTable("tab", TableType.TABLE, schema);
    final MutableColumn nameColumn = new MutableColumn("name", ColumnType.VARCHAR).setTable(table);
    final MutableColumn ageColumn = new MutableColumn("age", ColumnType.INTEGER).setTable(table);
    schema.addTable(table);
    table.addColumn(nameColumn);
    table.addColumn(ageColumn);

    final Query q = new Query();
    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);
}
 

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
public void testFullyQualifiedColumnNamesWithFilterItemContainingTimestamp() throws Exception {
    final MutableSchema schema = new MutableSchema("sch");
    final MutableTable table = new MutableTable("tab", TableType.TABLE, schema);
    final MutableColumn nameColumn = new MutableColumn("name", ColumnType.VARCHAR).setTable(table);
    final MutableColumn dateColumn = new MutableColumn("age", ColumnType.TIMESTAMP).setTable(table);
    schema.addTable(table);
    table.addColumn(nameColumn);
    table.addColumn(dateColumn);

    final Query q = new Query();
    q.select(dateColumn).selectCount();
    q.from(table);
    q.where(dateColumn, OperatorType.GREATER_THAN, "2012-10-31 08:09:54");
    q.groupBy(dateColumn);
    q.having(FunctionType.COUNT, nameColumn, OperatorType.LESS_THAN, 100);
    q.orderBy(dateColumn);

    final String sql = new DB2QueryRewriter(null).rewriteQuery(q);

    assertEquals("SELECT sch.tab.age, COUNT(*) FROM sch.tab WHERE sch.tab.age > TIMESTAMP ('2012-10-31 08:09:54') "
            + "GROUP BY sch.tab.age HAVING COUNT(sch.tab.name) < 100 ORDER BY sch.tab.age ASC", sql);
}
 

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
@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().toArray()));

    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());
}
 

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
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());
    assertEquals("Row[values=[developer, 5]]", data.getRow().toString());
    assertFalse(data.next());
}
 

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
public void testSelectDistinct() throws Exception {
    // 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());
    assertEquals("Row[values=[hi]]", ds.getRow().toString());
    assertTrue(ds.next());
    assertEquals("Row[values=[yo]]", ds.getRow().toString());
    assertFalse(ds.next());
}
 

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
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);
}
 

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
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);
}
 

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
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().size());

    TableModel tableModel = new DataSetTableModel(data);

    // should correspond to these lines:

    // data.add(new Object[] { 4, 1, "advisor" });
    // data.add(new Object[] { 1, 1, "developer" });
    // data.add(new Object[] { 1, 2, "developer" });
    // data.add(new Object[] { 2, 1, "developer" });
    // data.add(new Object[] { 2, 3, "developer" });
    // data.add(new Object[] { 5, 2, "developer" });
    // data.add(new Object[] { 1, 1, "founder" });
    // data.add(new Object[] { 6, 1, "founder" });

    assertEquals(8, tableModel.getRowCount());
    assertEquals(1, tableModel.getColumnCount());
    assertEquals(1, tableModel.getValueAt(0, 0));
    assertEquals(1, tableModel.getValueAt(1, 0));
    assertEquals(2, tableModel.getValueAt(2, 0));
    assertEquals(1, tableModel.getValueAt(3, 0));
    assertEquals(3, tableModel.getValueAt(4, 0));
    assertEquals(2, tableModel.getValueAt(5, 0));
    assertEquals(1, tableModel.getValueAt(6, 0));
    assertEquals(1, tableModel.getValueAt(7, 0));
}
 

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
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());
    assertEquals("Row[values=[3, johny, israel]]", data.getRow().toString());
    assertTrue(data.next());
    assertEquals("Row[values=[2, asbjorn, denmark]]", data.getRow().toString());
    assertTrue(data.next());
    assertEquals("Row[values=[6, jesper, denmark]]", data.getRow().toString());
    assertTrue(data.next());
    assertEquals("Row[values=[1, kasper, denmark]]", data.getRow().toString());
    assertTrue(data.next());
    assertEquals("Row[values=[4, daniel, canada]]", data.getRow().toString());
    assertFalse(data.next());
}
 

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
public SatisfiedOrderByBuilderImpl(Column column, Query query,
		GroupedQueryBuilder queryBuilder) {
	super(queryBuilder);
	orderByitem = new OrderByItem(new SelectItem(column));
	query.orderBy(orderByitem);
}