Java Query.from方法代码示例

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
public void testGroupByQuery() throws Exception {
    DataContext dc = new CsvDataContext(new File("src/test/resources/csv_people.csv"));
    Table table = dc.getDefaultSchema().getTableByName("csv_people.csv");

    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"));
    DataSet data = dc.executeQuery(q);
    assertEquals(
            "[csv_people.csv.gender, MAX(csv_people.csv.age), MIN(csv_people.csv.age), COUNT(*) AS total, MIN(csv_people.csv.id) AS firstId]",
            Arrays.toString(data.getSelectItems().toArray()));

    String[] expectations = new String[] { "Row[values=[female, 20, 17, 5, 5]]", "Row[values=[male, 19, 17, 4, 1]]" };

    assertTrue(data.next());
    assertTrue(Arrays.asList(expectations).contains(data.getRow().toString()));
    assertTrue(data.next());
    assertTrue(Arrays.asList(expectations).contains(data.getRow().toString()));
    assertFalse(data.next());
}
 

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 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());
    assertTrue(dataSet.next());
    assertTrue(dataSet.next());
    assertTrue(dataSet.next());
    assertTrue(dataSet.next());
    assertFalse(dataSet.next());
}
 

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
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().size());
    for (int i = 0; i < 6 * 8; i++) {
        assertTrue(data.next());
        if (i == 0) {
            assertEquals("Row[values=[1, kasper, denmark, 1, 1, founder]]", data.getRow().toString());
        } else if (i == 1) {
            assertEquals("Row[values=[1, kasper, denmark, 1, 1, developer]]", data.getRow().toString());
        }
    }
    assertFalse(data.next());
}
 

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

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
public void testWhereItemNotInSelectClause() throws Exception {
    File file = new File("src/test/resources/csv_people.csv");
    QueryPostprocessDataContext dc = new CsvDataContext(file);
    Table table = dc.getDefaultSchema().getTableByName("csv_people.csv");

    Query q = new Query();
    q.from(table);
    q.where(new FilterItem(new SelectItem(table.getColumnByName("id")), OperatorType.EQUALS_TO, 1));
    q.select(table.getColumnByName("name"));
    DataSet data = dc.executeQuery(q);
    assertTrue(data.next());
    assertEquals("Row[values=[mike]]", data.getRow().toString());
    assertFalse(data.next());
}
 

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
public void testExecuteQuery() throws Exception {
    Connection connection = getTestDbConnection();
    JdbcDataContext strategy = new JdbcDataContext(connection, new TableType[] { TableType.TABLE, TableType.VIEW },
            null);
    Schema schema = strategy.getSchemaByName(strategy.getDefaultSchemaName());

    Query q = new Query();
    Table table = schema.getTables().get(0);
    q.from(table, "a");
    q.select(table.getColumns());
    assertEquals(
            "SELECT a._CUSTOMERNUMBER_, a._CUSTOMERNAME_, a._CONTACTLASTNAME_, a._CONTACTFIRSTNAME_, a._PHONE_, "
                    + "a._ADDRESSLINE1_, a._ADDRESSLINE2_, a._CITY_, a._STATE_, a._POSTALCODE_, a._COUNTRY_, "
                    + "a._SALESREPEMPLOYEENUMBER_, a._CREDITLIMIT_ FROM PUBLIC._CUSTOMERS_ a", q.toString()
                    .replace('\"', '_'));
    DataSet result = strategy.executeQuery(q);
    assertTrue(result.next());
    assertEquals(
            "Row[values=[103, Atelier graphique, Schmitt, Carine, 40.32.2555, 54, rue Royale, null, Nantes, null, "
                    + "44000, France, 1370, 21000.0]]", result.getRow().toString());
    assertTrue(result.next());
    assertTrue(result.next());
    assertTrue(result.next());
    assertTrue(result.next());
    assertEquals(
            "Row[values=[121, Baane Mini Imports, Bergulfsen, Jonas, 07-98 9555, Erling Skakkes gate 78, null, "
                    + "Stavern, null, 4110, Norway, 1504, 81700.0]]", result.getRow().toString());
    result.close();
}
 

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
public void testExecuteQueryWithParams() throws Exception {
    Connection connection = getTestDbConnection();
    JdbcDataContext dataContext = new JdbcDataContext(connection,
            new TableType[] { TableType.TABLE, TableType.VIEW }, null);
    Schema schema = dataContext.getSchemaByName(dataContext.getDefaultSchemaName());

    QueryParameter queryParameter = new QueryParameter();

    Query q = new Query();
    Table table = schema.getTables().get(0);
    q.select(table.getColumns());
    q.from(table, "a");
    q.where(table.getColumnByName("CUSTOMERNUMBER"), OperatorType.EQUALS_TO, queryParameter);
    q.where(table.getColumnByName("CUSTOMERNAME"), OperatorType.EQUALS_TO, queryParameter);

    final CompiledQuery compiledQuery = dataContext.compileQuery(q);

    final JdbcCompiledQuery jdbcCompiledQuery = (JdbcCompiledQuery) compiledQuery;
    assertEquals(0, jdbcCompiledQuery.getActiveLeases());
    assertEquals(0, jdbcCompiledQuery.getIdleLeases());

    String compliedQueryString = compiledQuery.toSql();

    assertEquals(
            "SELECT a._CUSTOMERNUMBER_, a._CUSTOMERNAME_, a._CONTACTLASTNAME_, a._CONTACTFIRSTNAME_, a._PHONE_, "
                    + "a._ADDRESSLINE1_, a._ADDRESSLINE2_, a._CITY_, "
                    + "a._STATE_, a._POSTALCODE_, a._COUNTRY_, a._SALESREPEMPLOYEENUMBER_, "
                    + "a._CREDITLIMIT_ FROM PUBLIC._CUSTOMERS_ a WHERE a._CUSTOMERNUMBER_ = ? "
                    + "AND a._CUSTOMERNAME_ = ?", compliedQueryString.replace('\"', '_'));
    DataSet result1 = dataContext.executeQuery(compiledQuery, new Object[] { 103, "Atelier graphique" });
    assertTrue(result1.next());
    assertEquals(
            "Row[values=[103, Atelier graphique, Schmitt, Carine, 40.32.2555, 54, rue Royale, null, Nantes, null, "
                    + "44000, France, 1370, 21000.0]]", result1.getRow().toString());
    assertFalse(result1.next());

    assertEquals(1, jdbcCompiledQuery.getActiveLeases());
    assertEquals(0, jdbcCompiledQuery.getIdleLeases());

    DataSet result2 = dataContext.executeQuery(compiledQuery, new Object[] { 103, "Atelier graphique" });

    assertEquals(2, jdbcCompiledQuery.getActiveLeases());
    assertEquals(0, jdbcCompiledQuery.getIdleLeases());

    result1.close();

    assertEquals(1, jdbcCompiledQuery.getActiveLeases());
    assertEquals(1, jdbcCompiledQuery.getIdleLeases());

    result2.close();

    assertEquals(0, jdbcCompiledQuery.getActiveLeases());
    assertEquals(2, jdbcCompiledQuery.getIdleLeases());

    compiledQuery.close();

    assertEquals(0, jdbcCompiledQuery.getActiveLeases());
    assertEquals(0, jdbcCompiledQuery.getIdleLeases());
}
 

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

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
public void testCarthesianProductWithWhere() throws Exception {
    DataContext dc = getDataContext();

    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().size());
    assertTrue(data.next());
    assertEquals("Row[values=[kasper, founder]]", data.getRow().toString());
    assertTrue(data.next());
    assertEquals("Row[values=[kasper, developer]]", data.getRow().toString());
    assertTrue(data.next());
    assertEquals("Row[values=[kasper, developer]]", data.getRow().toString());
    assertTrue(data.next());
    assertEquals("Row[values=[asbjorn, developer]]", data.getRow().toString());
    assertTrue(data.next());
    assertEquals("Row[values=[asbjorn, developer]]", data.getRow().toString());
    assertTrue(data.next());
    assertEquals("Row[values=[daniel, advisor]]", data.getRow().toString());
    assertTrue(data.next());
    assertEquals("Row[values=[sasidhar, developer]]", data.getRow().toString());
    assertTrue(data.next());
    assertEquals("Row[values=[jesper, founder]]", data.getRow().toString());
    assertFalse(data.next());
}
 

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

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