Tablestore:How do I page query results?

Page query results from tables without search indexes

Page query results from tables with search indexes

If search indexes are created for a table, you can use one of the following methods to page query results from the table:

• Use the offset and limit parameters. You can use this method to specify the rows that you want to obtain in the query results. The sum of the values of the limit and offset parameters cannot exceed 50,000. If the sum exceeds 50,000, use the next_token parameter to page query results.

• Use the next_token parameter. The next_token parameter is returned in the response for each Search request. You can use the value of this parameter as a pagination token, and specify the pagination token in the next request to retrieve a new page of results.

• Use SearchIterator. You can call the iterator.next() method to keep reading the next row of data.

Examples for paged queries on tables without search indexes

The following sample code shows the implementation of a method that skips rows based on the offset parameter and reads a specific number of rows based on the specified page size.

/**
 * Query data whose primary key values are within a specific range, return a specific number of rows based on the specified page size, and skip rows based on the offset parameter. 
 */
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 you need to query a large amount of data that cannot be returned in a single request, you must send multiple requests. 
    while (limit > 0 && nextStart != null) {
        // Create query parameters for the GetRange operation. 
        // Note: You must set the startPrimaryKey parameter to the position where the last query stops. This way, you can send multiple range query requests to query the remaining data. 
        RangeRowQueryCriteria criteria = new RangeRowQueryCriteria(tableName);
        criteria.setInclusiveStartPrimaryKey(nextStart);
        criteria.setExclusiveEndPrimaryKey(endKey);
        criteria.setMaxVersions(1);
        // Correctly specify the limit parameter. In this example, the limit parameter is set to the sum of the maximum number of rows that can be returned on a page and the number of rows that need to be skipped based on the offset parameter. 
        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--; // Skip the data before the position specified by the offset parameter on the client after the data is read. 
            } else {
                rows.add(row);
                limit--;
            }
        }
        // Set the position from which the next query starts. 
        nextStart = response.getNextStartPrimaryKey();
    }
    return new Pair<List<Row>, PrimaryKey>(rows, nextStart);
}          

The following sample code shows the implementation of a method that reads all data whose primary key values are within a specific range by page in sequence.

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 from the 33rd row in the range. This is because the offset parameter is set to 33. 
        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());
        // Read all data whose primary key values are within a specific range in sequence and page the query results. 
        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());
        }

    }

Examples for paged queries on tables with search indexes

For more information, see Sorting and paging.