Is it posible to use only one call to database when pagination in Java?

Question

If the pagesize is 10 and pageNumber is 0, for the given condition, if I I get 50 documents from mongodb, then total_documents should be 50 and msList should have the details of 1st 10 documents(DTO's)(since the pageNumber is 0 i.e. pagination).

Query query = new Query();
List<Criteria> criteria = new ArrayList<>();
criteria.add(Criteria.where("di.ai").in(new ArrayList<>(Arrays.asList("123"))));
query.addCriteria(new Criteria().andOperator(criteria.toArray(new Criteria[criteria.size()])));
Long total_documents = this.mongoTemplate.count(query, NSDTO.class, MONGODB_MESSAGE_COLLECTION);
Pageable pageable = PageRequest.of((pageNumber - 1), pageSize);
query.with(pageable);    
List<NSDTO> nsList= this.mongoTemplate.find(query, NSDTO.class, MONGODB_MESSAGE_COLLECTION);"

I am able to achieve this functionality using the above code, but the problem here is we are making mongodb calls 2 times.

Would it be possible to achieve the same functionality using only one mongodb call?

Could someone please help on this regard.

Answer 1

It is possible to achieve the pagination in one call with $facet. Just be reminded not to have too large page size since it would fail due to 16MB MongoDB document size limit.

db.collection.aggregate([
  {
    "$facet": {
      "paginationMeta": [
        {
          "$match": {
            //your match criteria here
            
          }
        },
        {
          "$count": "total_documents"
        }
      ],
      page: [
        {
          "$match": {
            //your match criteria here
            
          }
        },
        {
          "$sort": {
            key: 1
          }
        },
        {
          "$skip": 20
        },
        {
          "$limit": 10
        }
      ]
    }
  }
])

Mongo Playground

Answer 2

When pagination is performed, usually, use two queries: first query will count a total size, second query returns actual records using two parameters: pageNumber and pageSize.

These two queries are independent of each other and cannot be joined. So, it's impossible using CreteriaQuery API and in pagination themselves.