Java Query.setFirstRow方法代码示例

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包/类
@Test
public void testOffsetFetchConstruct() {
    final int offset = 1000;
    final int rows = 100;
    final String where = "x > 1";

    final String offsetClause = " OFFSET " + (offset - 1) + " ROWS";
    final String fetchClause = " FETCH NEXT " + rows + " ROWS ONLY";

    final Query query = new Query().from(table).select(column);
    Assert.assertEquals("There shouldn't be OFFSET-FETCH clause.", query.toSql(), qr.rewriteQuery(query));

    query.setFirstRow(offset);
    Assert.assertEquals("Wrong or missing OFFSET clause.", query.toSql() + offsetClause, qr.rewriteQuery(query));

    query.setMaxRows(rows);
    Assert.assertEquals("Wrong or missing OFFSET and FETCH clauses.", query.toSql() + offsetClause + fetchClause,
            qr.rewriteQuery(query));

    query.setFirstRow(null);
    Assert.assertEquals("Wrong or missing FETCH clause.", query.toSql() + fetchClause, qr.rewriteQuery(query));
}
 

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
public void testOffsetFetchConstruct() {
    final int offset = 1000;
    final int rows = 100;

    final String baseQuery = "SELECT MY_SCHEMA.\"foo\".\"bar\" FROM MY_SCHEMA.\"foo\" ORDER BY id ASC";
    final String baseQueryWithTop =
            "SELECT TOP " + rows + " MY_SCHEMA.\"foo\".\"bar\" FROM MY_SCHEMA.\"foo\" ORDER BY id ASC";
    final String offsetClause = " OFFSET " + (offset - 1) + " ROWS";
    final String fetchClause = " FETCH NEXT " + rows + " ROWS ONLY";

    Query query = new Query();
    query.from(table).select(column).orderBy("id");
    Assert.assertEquals("There shouldn't be OFFSET-FETCH clause.", baseQuery, qr.rewriteQuery(query));

    query.setFirstRow(offset);
    Assert.assertEquals("Wrong or missing OFFSET clause.", baseQuery + offsetClause, qr.rewriteQuery(query));

    query.setMaxRows(rows);
    Assert.assertEquals("Wrong or missing OFFSET and FETCH clauses.", baseQuery + offsetClause + fetchClause,
            qr.rewriteQuery(query));

    query.setFirstRow(null);
    Assert.assertEquals("Using FETCH clause instead of TOP clause.", baseQueryWithTop, qr.rewriteQuery(query));
}
 

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 static QueryResponse executeQuery(TenantContext tenant, String dataSource, DataContext dataContext,
        Query query, Integer offset, Integer limit) {

    if (offset != null) {
        query.setFirstRow(offset);
    }
    if (limit != null) {
        query.setMaxRows(limit);
    }

    logger.info("{}/{} - Executing query: {}", tenant.getTenantName(), dataSource, query);

    final List<String> headers;
    final List<List<Object>> data = new ArrayList<>();

    try (final DataSet dataSet = dataContext.executeQuery(query)) {
        headers = dataSet.getSelectItems().stream().map((si) -> si.toString()).collect(Collectors.toList());
        while (dataSet.next()) {
            final Object[] values = dataSet.getRow().getValues();
            data.add(Arrays.asList(values));
        }
    }

    final QueryResponse resp = new QueryResponse();
    resp.type("dataset");
    resp.headers(headers);
    resp.data(data);
    return resp;
}
 

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
private Supplier<Integer> createExpectedRowsRef() {
    // TODO: This only seems valid for source tables
    return new LazyRef<Integer>() {

        @Override
        protected Integer fetch() {
            int expectedRows = -1;
            {
                final Query originalQuery = getQuery();
                final Query countQuery = originalQuery.clone();
                countQuery.setMaxRows(null);
                countQuery.setFirstRow(null);
                countQuery.getSelectClause().removeItems();
                countQuery.getOrderByClause().removeItems();
                countQuery.selectCount();
                countQuery.getSelectClause().getItem(0).setFunctionApproximationAllowed(true);

                final Datastore datastore = _publisher.getStream().getAnalysisJob().getDatastore();
                try (DatastoreConnection connection = datastore.openConnection()) {
                    try (DataSet countDataSet = connection.getDataContext().executeQuery(countQuery)) {
                        if (countDataSet.next()) {
                            final Number count =
                                    ConvertToNumberTransformer.transformValue(countDataSet.getRow().getValue(0));
                            if (count != null) {
                                expectedRows = count.intValue();
                            }
                        }
                    }
                }

                final Integer maxRows = originalQuery.getMaxRows();
                if (maxRows != null) {
                    expectedRows = Math.min(expectedRows, maxRows.intValue());
                }
            }

            return expectedRows;
        }
    };
}
 

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
private Ref<Integer> createExpectedRowsRef() {
    return new LazyRef<Integer>() {

        @Override
        protected Integer fetch() {
            int expectedRows = -1;
            {
                final Query originalQuery = getQuery();
                final Query countQuery = originalQuery.clone();
                countQuery.setMaxRows(null);
                countQuery.setFirstRow(null);
                countQuery.getSelectClause().removeItems();
                countQuery.getOrderByClause().removeItems();
                countQuery.selectCount();
                countQuery.getSelectClause().getItem(0).setFunctionApproximationAllowed(true);

                final Datastore datastore = _publishers.getDatastore();
                try (final DatastoreConnection connection = datastore.openConnection()) {
                    try (final DataSet countDataSet = connection.getDataContext().executeQuery(countQuery)) {
                        if (countDataSet.next()) {
                            final Number count = ConvertToNumberTransformer.transformValue(countDataSet.getRow()
                                    .getValue(0));
                            if (count != null) {
                                expectedRows = count.intValue();
                            }
                        }
                    }
                }

                final Integer maxRows = originalQuery.getMaxRows();
                if (maxRows != null) {
                    expectedRows = Math.min(expectedRows, maxRows.intValue());
                }
            }

            return expectedRows;
        }
    };
}
 

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
private void parseOffsetClause(Query query, String offsetClause) {
    offsetClause = offsetClause.trim();
    if (!offsetClause.isEmpty()) {
        try {
            final int offset = Integer.parseInt(offsetClause);
            // ofset is 0-based, but first-row is 1-based
            final int firstRow = offset + 1;
            query.setFirstRow(firstRow);
        } catch (NumberFormatException e) {
            throw new QueryParserException("Could not parse OFFSET value: " + offsetClause);
        }
    }
}
 

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

    Table table = dc.getDefaultSchema().getTables().get(0);
    Query q = dc.query().from(table).select(table.getColumnByName("name")).toQuery();
    q.setFirstRow(3);
    q.setMaxRows(2);

    DataSet ds;

    ds = dc.executeQuery(q);
    assertEquals(1, ds.getSelectItems().size());
    assertTrue(ds.next());
    assertEquals("peter", ds.getRow().getValue(0).toString());
    assertTrue(ds.next());
    assertEquals("bob", ds.getRow().getValue(0).toString());
    assertFalse(ds.next());
    ds.close();

    // try with iterator
    ds = dc.executeQuery(q);
    int i = 0;
    for (Row row : ds) {
        assertNotNull(row);
        i++;
    }
    assertEquals(2, i);
}
 

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
@Override
public String rewriteQuery(Query query) {
    final Integer firstRow = query.getFirstRow();
    final Integer maxRows = query.getMaxRows();

    if (maxRows == null && (firstRow == null || firstRow.intValue() == 1)) {
        return super.rewriteQuery(query);
    }

    if ((firstRow == null || firstRow.intValue() == 1) && maxRows != null && maxRows > 0) {
        // We prefer to use the "FETCH FIRST [n] ROWS ONLY" approach, if
        // firstRow is not specified.
        return super.rewriteQuery(query) + " FETCH FIRST " + maxRows + " ROWS ONLY";

    } else {
        // build a ROW_NUMBER() query like this:

        // SELECT [original select clause]
        // FROM ([original select clause],
        // ROW_NUMBER() AS metamodel_row_number
        // FROM [remainder of regular query])
        // WHERE metamodel_row_number BETWEEN [firstRow] and [maxRows];

        final Query innerQuery = query.clone();
        innerQuery.setFirstRow(null);
        innerQuery.setMaxRows(null);

        final Query outerQuery = new Query();
        final FromItem subQuerySelectItem = new FromItem(innerQuery).setAlias("metamodel_subquery");
        outerQuery.from(subQuerySelectItem);

        final List<SelectItem> innerSelectItems = innerQuery.getSelectClause().getItems();
        for (SelectItem selectItem : innerSelectItems) {
            outerQuery.select(new SelectItem(selectItem, subQuerySelectItem));
        }

        final String rewrittenOrderByClause = rewriteOrderByClause(innerQuery, innerQuery.getOrderByClause());
        final String rowOver = "ROW_NUMBER() " + "OVER(" + rewrittenOrderByClause + ")";
        innerQuery.select(new SelectItem(rowOver, "metamodel_row_number"));
        innerQuery.getOrderByClause().removeItems();

        final String baseQueryString = rewriteQuery(outerQuery);

        if (maxRows == null) {
            return baseQueryString + " WHERE metamodel_row_number > " + (firstRow - 1);
        }

        return baseQueryString + " WHERE metamodel_row_number BETWEEN " + firstRow + " AND "
                + (firstRow - 1 + maxRows);
    }
}