Java Query.select方法代码示例

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 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 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包/类
@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 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包/类
private long getRowCount(Query q) {
    q = q.clone();
    SelectItem countAllItem = SelectItem.getCountAllItem();
    if (q.getGroupByClause().getItemCount() > 0) {
        q = new Query().from(new FromItem(q).setAlias("sq")).select(countAllItem);
    } else {
        q.getSelectClause().removeItems();
        q.select(countAllItem);
    }
    Row row = MetaModelHelper.executeSingleRowQuery(_dataContext, q);
    Number count = (Number) row.getValue(countAllItem);
    return count.longValue();
}
 

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 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 testExecuteQuery() throws Exception {
    if (!isConfigured()) {
           return;
       }
	JdbcDataContext dataContext = getDataContext();
       Schema schema = dataContext.getDefaultSchema();
	Table departmentTable = schema.getTableByName("DEPARTMENT");
	Table employeeTable = schema.getTableByName("EMPLOYEE");
	Query q = new Query().from(new FromItem(JoinType.INNER, departmentTable.getRelationships(employeeTable).iterator().next()));
	q.select(departmentTable.getColumns().get(1));
	q.select(new SelectItem(employeeTable.getColumns().get(4)).setAlias("hire-date"));
	assertEquals(
			"SELECT \"DEPARTMENT\".\"DEPARTMENT\", \"EMPLOYEE\".\"HIRE_DATE\" AS hire-date FROM \"EMPLOYEE\" INNER JOIN \"DEPARTMENT\" ON \"EMPLOYEE\".\"EMP_NO\" = \"DEPARTMENT\".\"MNGR_NO\"",
			q.toString());

	DataSet data = dataContext.executeQuery(q);
	assertNotNull(data);

	TableModel tableModel = new DataSetTableModel(data);
	assertEquals(2, tableModel.getColumnCount());
	assertEquals(17, tableModel.getRowCount());
	assertEquals("Quality Assurance", tableModel.getValueAt(4, 0).toString());

	Date date = (Date) tableModel.getValueAt(4, 1);
	assertEquals("1989-04-17 00:00:00.000000", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSSSS").format(date));

}
 

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
public void testSimpleQuerySplit() throws Exception {
	Connection con = getTestDbConnection();
	DataContext dc = new JdbcDataContext(con);
	Schema schema = dc.getSchemaByName("PUBLIC");
	Table employeesTable = schema.getTableByName("EMPLOYEES");
	Table customersTable = schema.getTableByName("CUSTOMERS");
	Query q = new Query().from(employeesTable, "e").from(customersTable,
			"c");
	q.select(employeesTable.getColumns().get(0), customersTable.getColumns().get(0));
	assertEquals(
			"SELECT e._EMPLOYEENUMBER_, c._CUSTOMERNUMBER_ FROM PUBLIC._EMPLOYEES_ e, PUBLIC._CUSTOMERS_ c",
			q.toString().replace('\"', '_'));

	QuerySplitter qs = new QuerySplitter(dc, q);
	long rowCount = qs.getRowCount();
	assertEquals(2806, rowCount);

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

	assertEquals("[793, 610, 366, 714, 323]",
			Arrays.toString(getCounts(dc, splitQueries)));

	assertEquals(
			"[SELECT e._EMPLOYEENUMBER_, c._CUSTOMERNUMBER_ FROM PUBLIC._EMPLOYEES_ e, PUBLIC._CUSTOMERS_ c WHERE (c._CUSTOMERNUMBER_ < 299 OR c._CUSTOMERNUMBER_ IS NULL) AND (e._EMPLOYEENUMBER_ < 1352 OR e._EMPLOYEENUMBER_ IS NULL), SELECT e._EMPLOYEENUMBER_, c._CUSTOMERNUMBER_ FROM PUBLIC._EMPLOYEES_ e, PUBLIC._CUSTOMERS_ c WHERE (c._CUSTOMERNUMBER_ < 299 OR c._CUSTOMERNUMBER_ IS NULL) AND (e._EMPLOYEENUMBER_ > 1352 OR e._EMPLOYEENUMBER_ = 1352), SELECT e._EMPLOYEENUMBER_, c._CUSTOMERNUMBER_ FROM PUBLIC._EMPLOYEES_ e, PUBLIC._CUSTOMERS_ c WHERE (c._CUSTOMERNUMBER_ > 299 OR c._CUSTOMERNUMBER_ = 299) AND (e._REPORTSTO_ < 1072 OR e._REPORTSTO_ IS NULL), SELECT e._EMPLOYEENUMBER_, c._CUSTOMERNUMBER_ FROM PUBLIC._EMPLOYEES_ e, PUBLIC._CUSTOMERS_ c WHERE (c._CUSTOMERNUMBER_ > 299 OR c._CUSTOMERNUMBER_ = 299) AND (e._REPORTSTO_ > 1072 OR e._REPORTSTO_ = 1072) AND (c._SALESREPEMPLOYEENUMBER_ < 1433 OR c._SALESREPEMPLOYEENUMBER_ IS NULL), SELECT e._EMPLOYEENUMBER_, c._CUSTOMERNUMBER_ FROM PUBLIC._EMPLOYEES_ e, PUBLIC._CUSTOMERS_ c WHERE (c._CUSTOMERNUMBER_ > 299 OR c._CUSTOMERNUMBER_ = 299) AND (e._REPORTSTO_ > 1072 OR e._REPORTSTO_ = 1072) AND (c._SALESREPEMPLOYEENUMBER_ > 1433 OR c._SALESREPEMPLOYEENUMBER_ = 1433)]",
			Arrays.toString(splitQueries.toArray()).replace('\"', '_'));
	assertSameCount(dc, qs, splitQueries);
	assertEquals(5, splitQueries.size());

	splitQueries = qs.setMaxRows(300).splitQuery();
	assertSameCount(dc, qs, splitQueries);
	assertEquals(
			"[299, 221, 170, 299, 276, 253, 102, 289, 253, 138, 368, 138]",
			Arrays.toString(getCounts(dc, splitQueries)));

	splitQueries = qs.setMaxRows(5000).splitQuery();
	assertEquals(1, splitQueries.size());
	assertSame(q, splitQueries.get(0));
}
 

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 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 testQueryOnlyAggregate() throws Exception {
    File file = new File("src/test/resources/csv_people.csv");
    QueryPostprocessDataContext dc = new CsvDataContext(file);
    Table table = dc.getDefaultSchema().getTables().get(0);

    Query q = new Query().selectCount().from(table);
    assertEquals("SELECT COUNT(*) FROM resources.csv_people.csv", q.toString());

    List<Object[]> data = dc.executeQuery(q).toObjectArrays();
    assertEquals(1, data.size());
    Object[] row = data.get(0);
    assertEquals(1, row.length);
    assertEquals("[9]", Arrays.toString(row));

    q.select(table.getColumns().get(0));
    assertEquals("SELECT COUNT(*), csv_people.csv.id FROM resources.csv_people.csv", q.toString());
    data = dc.executeQuery(q).toObjectArrays();
    assertEquals(9, data.size());
    row = data.get(0);
    assertEquals(2, row.length);
    assertEquals("[9, 1]", Arrays.toString(row));

    row = data.get(1);
    assertEquals(2, row.length);
    assertEquals("[9, 2]", Arrays.toString(row));

    row = data.get(2);
    assertEquals(2, row.length);
    assertEquals("[9, 3]", Arrays.toString(row));

    row = data.get(8);
    assertEquals(2, row.length);
    assertEquals("[9, 9]", Arrays.toString(row));
}
 

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
public void testSelectItemReferencesToFromItems() throws Exception {
    MockDataContext dc = new MockDataContext("sch", "tab", "1");

    Table table = dc.getDefaultSchema().getTable(0);

    Query q = new Query();
    FromItem fromItem1 = q.from(table, "t1").getFromClause().getItem(0);
    FromItem fromItem2 = q.from(table, "t2").getFromClause().getItem(1);
    q.select(table.getColumnByName("foo"), fromItem1);
    q.select(table.getColumnByName("foo"), fromItem2);
    q.where(q.getSelectClause().getItem(0), OperatorType.EQUALS_TO, "2");
    assertEquals("SELECT t1.foo, t2.foo FROM sch.tab t1, sch.tab t2 WHERE t1.foo = '2'", q.toSql());

    DataSet ds = dc.executeQuery(q);
    List<SelectItem> selectItems = ds.getSelectItems();
    assertEquals(2, selectItems.size());
    assertEquals("t1.foo", selectItems.get(0).toSql());
    assertEquals("t2.foo", selectItems.get(1).toSql());
    assertTrue(ds.next());
    assertEquals("Row[values=[2, 1]]", ds.getRow().toString());
    assertTrue(ds.next());
    assertEquals("Row[values=[2, 2]]", ds.getRow().toString());
    assertTrue(ds.next());
    assertEquals("Row[values=[2, 3]]", ds.getRow().toString());
    assertTrue(ds.next());
    assertEquals("Row[values=[2, 4]]", ds.getRow().toString());
    assertFalse(ds.next());
    ds.close();
}
 

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