edit-icon download-icon

How do I perform paging query?

Last Updated: Apr 11, 2018

Constraints

Compared with the paging query supported by conventional relational databases, the pagination feature within NoSQL data models and APIs has the following constraints:

  • The number of rows in the whole range cannot be obtained, which means the total number of pages cannot be calculated.
  • We recommend that you do not set offset for page jump, because the offset filter runs on the client, and data scanned and read by the server is fixed. If the offset value is too large, the query may take too long to resolve.
  • Only sequential paging is supported.

Sample code

The following sample code implements the paginated reading API, which contains the offset filter to read certain data from specified page numbers.

  1. /**
  2. * Query data in the specified range, return data on pages of specified numbers, and skip rows according to the offset value.
  3. */
  4. private static Pair<List<Row>, RowPrimaryKey> readByPage(OTSClient client, String tableName,
  5. RowPrimaryKey startKey, RowPrimaryKey endKey, int offset, int pageSize) {
  6. Preconditions.checkArgument(offset >= 0, "Offset should not be negative.");
  7. Preconditions.checkArgument(pageSize > 0, "Page size should be greater than 0.");
  8. List<Row> rows = new ArrayList<Row>(pageSize);
  9. int limit = pageSize;
  10. int skip = offset;
  11. RowPrimaryKey nextStart = startKey;
  12. // If a large amount of data needs to be queried, you must perform stream query to obtain the data.
  13. while (limit > 0 && nextStart != null) {
  14. // Construct a query parameter of GetRange.
  15. // NOTE: Set the startPrimaryKey to the most recently read point in time for the query to continue from the proper start point.
  16. RangeRowQueryCriteria criteria = new RangeRowQueryCriteria(tableName);
  17. criteria.setInclusiveStartPrimaryKey(nextStart);
  18. criteria.setExclusiveEndPrimaryKey(endKey);
  19. // Set a proper limit so that the number of data rows required to be read is a complete page of data, including data that needs to be filtered (offset).
  20. criteria.setLimit(skip + limit);
  21. GetRangeRequest request = new GetRangeRequest();
  22. request.setRangeRowQueryCriteria(criteria);
  23. GetRangeResult response = client.getRange(request);
  24. for (Row row : response.getRows()) {
  25. if (skip > 0) {
  26. skip--; // Data before offset must be filtered out. The policy will read the data first and then filter the data on the client.
  27. } else {
  28. rows.add(row);
  29. limit--;
  30. }
  31. }
  32. // Set the start point for the next query.
  33. nextStart = response.getNextStartPrimaryKey();
  34. }
  35. return new Pair<List<Row>, RowPrimaryKey>(rows, nextStart);
  36. }

The following sample code uses data within a specified range that is read page by page using the preceding API.

  1. private static void readByPage(OTSClient client, String tableName) {
  2. int pageSize = 8;
  3. int offset = 33;
  4. RowPrimaryKey startKey = new RowPrimaryKey();
  5. startKey.addPrimaryKeyColumn(COLUMN_GID_NAME, PrimaryKeyValue.INF_MIN);
  6. startKey.addPrimaryKeyColumn(COLUMN_UID_NAME, PrimaryKeyValue.INF_MIN);
  7. RowPrimaryKey endKey = new RowPrimaryKey();
  8. endKey.addPrimaryKeyColumn(COLUMN_GID_NAME, PrimaryKeyValue.INF_MAX);
  9. endKey.addPrimaryKeyColumn(COLUMN_UID_NAME, PrimaryKeyValue.INF_MAX);
  10. // Start reading data of the row with offset = 33 in the range on the first page.
  11. Pair<List<Row>, RowPrimaryKey> result = readByPage(client, tableName, startKey, endKey, offset, pageSize);
  12. for (Row row : result.getKey()) {
  13. System.out.println(row.getColumns());
  14. }
  15. System.out.println("Total rows count: " + result.getKey().size());
  16. // Read data on the following pages in sequence until all the data in the range is read.
  17. startKey = result.getValue();
  18. while (startKey != null) {
  19. System.out.println("============= start read next page ==============");
  20. result = readByPage(client, tableName, startKey, endKey, 0, pageSize);
  21. for (Row row : result.getKey()) {
  22. System.out.println(row.getColumns());
  23. }
  24. startKey = result.getValue();
  25. System.out.println("Total rows count: " + result.getKey().size());
  26. }
  27. }

Note: To download the complete example, click here.

Thank you! We've received your feedback.