AWS cloud DynamoDB 分页查询

参考文档：

https://docs.aws.amazon.com/zh_cn/amazondynamodb/latest/developerguide/Query.Pagination.html

https://docs.aws.amazon.com/zh_cn/amazondynamodb/latest/developerguide/QueryingJavaDocumentAPI.html

通过QueryRequest来查询(ScanRequet亦可，但是效率较低)。​

withLimit​ 设置查询的条数,条数满的话就停止查询

​withExclusiveStartKey 设置起始查询的key，就是上一页的最后一条数据

getLastEvaluatedKey​获取本页的最后一条数据，为下一页的查询做准备，null表示没有下一页的数据

setQueryFilter​ 设置数据filter

缺点：如果有filter的时候，只能对搜出的结果进行filter，最终返回的结果数量和需要的数量不一致.

 

注意：

这是部分分页代码，代码中#serialNo 表示占位符，可视作昵称别名，后续代码中可通过 #serialNo表示serialNo字段。作为分页token需要传回给客户端，客户端请求时也需要传递到后端，后端根据是否有值做出相应的分页查询结果返回。

   //步骤一：首次查询的时候app端传递空lastEvaluatedKey，结果返回后检测返回的结果集中lastEvaluatedKey是否有值
    //步骤二：检测步骤一中返回的结果集，如果返回的lastEvaluatedKey有值，则构造与步骤一中相同参数的请求，但是将步骤一返回的lastEvaluatedKey赋值给第二次分页查询的参数里。
    //步骤三：如果返回的结果中lastEvaluatedKey没有值，则表示检索了所有的数据不应再继续请求。
    //有则继续传参数
    private JSONObject getLogsByHomeId(String id, int pageSize, String startTime, String endTime, HashMap<String, AttributeValue> lastEvaluatedKey) {

        JSONObject jsonResult = new JSONObject();

        List<LockEvent> list = new ArrayList<>();
        List<Map<String, AttributeValue>> results = new ArrayList<>();
        Map<String, String> expressionAttributesNames = new HashMap<>();
        expressionAttributesNames.put("#id", "id");
        expressionAttributesNames.put("#historyTime", "historyTime");

        Map<String, AttributeValue> expressionAttributeValues = new HashMap<>();
        expressionAttributeValues.put(":id", new AttributeValue().withS(id));
        expressionAttributeValues.put(":startTime", new AttributeValue().withS(startTime));
        expressionAttributeValues.put(":endTime", new AttributeValue().withS(endTime));
        Map<String, AttributeValue> lastKey = null;

        QueryRequest request = new QueryRequest()
                .withTableName("LockEvent")
                .withKeyConditionExpression("#id= :idand #historyTime between :startTime and :endTime")
                .withExpressionAttributeNames(expressionAttributesNames)
                .withExpressionAttributeValues(expressionAttributeValues)
                .withLimit(pageSize)
                .withIndexName(LockEvent.INDEX)
                .withConsistentRead(false).withScanIndexForward(false);
        if (lastEvaluatedKey != null && lastEvaluatedKey.size() > 0)
            request.setExclusiveStartKey(lastEvaluatedKey);

        QueryResult queryResult = DefaultDBHolder.getInstance().getClient().query(request);
        results = queryResult.getItems();
        if (results.size() > 0)
            results.forEach((x) -> {
                LockEvent event = new LockEvent();
                event.setId(x.get("id").getS());
                event.setHistoryTime(null == x.get("historyTime") ? null : x.get("historyTime").getS());
                event.setId(null == x.get("id") ? null : x.get("id").getS());
                event.setSource(null == x.get("source") ? null : x.get("source").getS());
                list.add(event);
            });
        lastKey = queryResult.getLastEvaluatedKey();
        //results.add(lastKey);
        jsonResult.fluentPut("id", id).fluentPut("lastEvaluatedKey", lastKey)
                .fluentPut("payload", list);
        return jsonResult;


//        Table logTable = DefaultDBHolder.getInstance().getDB().getTable("LockEvent");
//        Index index = logTable.getIndex(LockEvent.INDEX);
//        QuerySpec spec = new QuerySpec().withKeyConditionExpression("serialNo = :serialNo")
//                .withValueMap(new ValueMap()
//                        .withString(":serialNo", serialNo))
//                .withConsistentRead(false).withMaxPageSize(pageSize);
//
//        ItemCollection<QueryOutcome> items = index.query(spec);
//
//        // Process each page of results
//        int pageNum = 0;
//        for (Page<Item, QueryOutcome> page : items.pages()) {
//            System.out.println("\nPage: " + ++pageNum);
//            int count = page.size();
//
//            // Process each item on the current page
//            Iterator<Item> item = page.iterator();
//
//            while (item.hasNext()) {
//                LockEvent log = new LockEvent();
//                Item xx = item.next();
//                System.out.println(xx.toJSONPretty());
//            }
//        }
//
//        HashMap<String, AttributeValue> eav = new HashMap();
//        eav.put(":serialNo", new AttributeValue().withS(serialNo));
//        DynamoDBQueryExpression<LockEvent> query = new DynamoDBQueryExpression<LockEvent>();
//        query.withConsistentRead(false).withIndexName(LockEvent.INDEX)
//                .withKeyConditionExpression("serialNo=:serialNo")
//                .withExpressionAttributeValues(eav).withScanIndexForward(false);
//        return DynamodbUtils.query(LockEvent.class, query);
    }

 

QuerySpec 亦可查询，支持数据分页懒加载，但是可用支持API调用的分页参数不同。

main，函数测试

   public static void main(String[] args) {
        LockLogService service = new LockLogService();
        ArrayList<LockEvent> list = new ArrayList<>();
        HashMap<String, AttributeValue> lastEvaluatedKey = new HashMap<>();
        int pageSize = 5;
        String stime = "2022-04-05 12:01:01";
        String etime = "2022-04-08 12:01:01";
        JSONObject jsonObject = service.getLogsById("211125800256440", pageSize, stime, etime, lastEvaluatedKey);

        JSONArray jsonArray = jsonObject.getJSONArray("payload");
        System.out.println("query size0:" + jsonArray.size());
        for (int i = 0; i < jsonArray.size(); i++) {
            LockEvent log = new LockEvent();
            log = (LockEvent) jsonArray.getObject(i, LockEvent.class);
            list.add(log);
        }
        String ss = jsonObject.toJSONString();
        JSONObject json = JSONObject.parseObject(ss);
        String lastKey = json.getString("lastEvaluatedKey");
        TypeReference<HashMap<String, AttributeValue>> typeReference = new TypeReference<HashMap<String, AttributeValue>>() {
        };
        HashMap<String, AttributeValue> map = JSON.parseObject(lastKey, typeReference);


        JSONObject nextPage = service.getLogsByHomeId("21112529156440", pageSize, stime, etime, map);
        JSONArray jsonArray1 = nextPage.getJSONArray("payload");
        System.out.println("query size1:" + jsonArray1.size());
        for (int i = 0; i < jsonArray1.size(); i++) {
            LockEvent log = new LockEvent();
            log = (LockEvent) jsonArray1.getObject(i, LockEvent.class);
            list.add(log);
        }
        String ss1 = nextPage.toJSONString();
        JSONObject json1 = JSONObject.parseObject(ss1);
        String lastKey1 = json1.getString("lastEvaluatedKey");
        TypeReference<HashMap<String, AttributeValue>> typeReference1 = new TypeReference<HashMap<String, AttributeValue>>() {
        };
        HashMap<String, AttributeValue> map1 = JSON.parseObject(lastKey1, typeReference1);


        JSONObject nextPage1 = service.getLogsById("21112580027829156440", pageSize, stime, etime, map1);
        JSONArray jsonArray2 = nextPage1.getJSONArray("payload");
        System.out.println("query size2:" + jsonArray2.size());
        for (int i = 0; i < jsonArray2.size(); i++) {
            LockEvent log = new LockEvent();
            log = (LockEvent) jsonArray2.getObject(i, LockEvent.class);
            list.add(log);
        }
        for (LockEvent log : list) {
            System.out.println("id:" + log.getId() + "  historyTime:" + log.getHistoryTime() + "\n");
        }

    }