How to implement a paged query
Tablestore is a distributed storage system that offers several methods to paginate query results. This topic describes how to perform a paged query.
Table
To page through data in a table that does not have a search index, you can use one of the following methods:
Querying data by primary key does not support retrieving the total number of rows or pages for the entire range.
Page using `nextStartPrimaryKey`: Each GetRange response contains a `nextStartPrimaryKey`. Set this value in the next request to retrieve the subsequent page of results.
Page using the `GetRangeIterator` iterator: Continuously retrieve the next data entry using the
iterator.next()method.
Querying data by primary key does not support using an offset to skip pages. If this functionality is required, you can simulate page skipping on the client using `nextStartPrimaryKey` or the `Iterator` iterator.
Search index
To page through data in a table that has a search index, you can use one of the following methods:
When you query data using a search index, you can retrieve the total number of rows and pages. To do this, set `getTotalCount` to `true` in the request. The total number of pages is the total number of rows divided by the `limit` value. Setting `getTotalCount` to `true` increases resource consumption during queries and decreases query performance.
Page using `offset` and `limit`: This method supports page skipping. However, the sum of `offset` and `limit` cannot exceed 100,000. If this limit is exceeded, you must use `next_token` for paging.
Page using `next_token`: Each Search response contains the `next_token` for the next page. Set this `next_token` in the next request to retrieve the subsequent page of results.
Page using the `SearchIterator` iterator: Continuously retrieve the next data entry using the
iterator.next()method.
Table example
The following example shows how to implement a paged read interface that provides offset filtering and reads a specified number of pages.
/**
* Queries data within a specified range, returns data of a specified page size, and can skip rows based on an offset.
*/
private static Pair<List<Row>, PrimaryKey> readByPage(SyncClient client, String tableName,
PrimaryKey startKey, PrimaryKey endKey, int offset, int pageSize) {
Preconditions.checkArgument(offset >= 0, "Offset should not be negative.");
Preconditions.checkArgument(pageSize > 0, "Page size should be greater than 0.");
List<Row> rows = new ArrayList<Row>(pageSize);
int limit = pageSize;
int skip = offset;
PrimaryKey nextStart = startKey;
// If the data volume is large, a single request may not return all data. Use a stream query to get all the required data.
while (limit > 0 && nextStart != null) {
// Construct the query parameters for GetRange.
// Note: Set startPrimaryKey to the offset from the last read operation to continue reading from where you left off. This implements a streaming range query.
RangeRowQueryCriteria criteria = new RangeRowQueryCriteria(tableName);
criteria.setInclusiveStartPrimaryKey(nextStart);
criteria.setExclusiveEndPrimaryKey(endKey);
criteria.setMaxVersions(1);
// Set the correct limit. Here, the maximum number of rows to be read is the sum of a full page of data and the data to be filtered (offset).
criteria.setLimit(skip + limit);
GetRangeRequest request = new GetRangeRequest();
request.setRangeRowQueryCriteria(criteria);
GetRangeResponse response = client.getRange(request);
for (Row row : response.getRows()) {
if (skip > 0) {
skip--; // Data before the offset must be filtered out. The policy is to read the data and then filter it on the client.
} else {
rows.add(row);
limit--;
}
}
// Set the starting offset for the next query.
nextStart = response.getNextStartPrimaryKey();
}
return new Pair<List<Row>, PrimaryKey>(rows, nextStart);
} The following example shows how to use the paged read interface to read all data within a specified range, page by page.
private static void readByPage(SyncClient client, String tableName) {
int pageSize = 8;
int offset = 33;
PrimaryKeyBuilder primaryKeyBuilder= PrimaryKeyBuilder.createPrimaryKeyBuilder();
primaryKeyBuilder.addPrimaryKeyColumn("gid", PrimaryKeyValue.INF_MIN);
primaryKeyBuilder.addPrimaryKeyColumn("uid", PrimaryKeyValue.INF_MIN);
PrimaryKey startKey = primaryKeyBuilder.build();
primaryKeyBuilder= PrimaryKeyBuilder.createPrimaryKeyBuilder();
primaryKeyBuilder.addPrimaryKeyColumn("gid", PrimaryKeyValue.INF_MAX);
primaryKeyBuilder.addPrimaryKeyColumn("uid", PrimaryKeyValue.INF_MAX);
PrimaryKey endKey = primaryKeyBuilder.build();
// Read the first page, starting from the row at offset=33 in the range.
Pair<List<Row>, PrimaryKey> result = readByPage(client, tableName, startKey, endKey, offset, pageSize);
for (Row row : result.getFirst()) {
System.out.println(row);
}
System.out.println("Total rows count: " + result.getFirst().size());
// Page through the results sequentially to read all data in the range.
startKey = result.getSecond();
while (startKey != null) {
System.out.println("============= start read next page ==============");
result = readByPage(client, tableName, startKey, endKey, 0, pageSize);
for (Row row : result.getFirst()) {
System.out.println(row);
}
startKey = result.getSecond();
System.out.println("Total rows count: " + result.getFirst().size());
}
}Search index example
For more information, see Sorting and paging.