EclipseLink (JPA 2.1) Query not returning results where it clearly should

Question

I am trying to retrieve the next page of results from the database using the code below.

   public Collection<Product> getCategoryProducts(Category selectedCategory, String start) {
        Query query = em.createQuery("SELECT p FROM Product p WHERE p.categoryId = :categoryId")
                .setParameter("categoryId", selectedCategory)
                .setMaxResults(20);
        if (null != start) {
            query.setFirstResult(Integer.parseInt(start));
        }
        return query.getResultList();
    }

My Product table has about 1000 rows where each product belongs to a category. If I pick a category like category 1 which has 80 products. I am able to view 4 pages of products each with 20 products. Problem comes in when I try to access products belonging to a higher categoryId. I can only view the first 20 results but the next page returns 0 results. E.g category 15 has products ranging from id=459 to id=794 where id is the primary key.

the query will return the first 20 results when I access the category page from https://localhost:8181/myapp/category?categoryId=15 but clicking the more link at the bottom https://localhost:8181/myapp/category?categoryId=15&start=478 returns 0 results. What am I missing?

see also this question is similar to this

Answer 1

I finally found my error. Apparently the position I am setting for first result should be a product of the page number and the page size. In my case I was setting first result as the Id of the entity I am retrieving. as a result for the first few pages, the result size contained the ID but as the page numbers increased the id is not contained within the result set. This is the kind of mistakes you don't know that you don't know until you know that you don't know.

public Collection<Product> getCategoryProducts(Category selectedCategory, String page) {
    int pageSize = 20;
    EntityManager entityManager = Persistence.createEntityManagerFactory("techbayPU").createEntityManager();
    Query query = entityManager.createQuery("SELECT p FROM Product p WHERE p.categoryId = :categoryId ORDER BY p.id", Product.class);
    query.setMaxResults(pageSize);
    if (null != page) {
        query.setFirstResult(Integer.parseInt(page) * pageSize);
    }
    query.setParameter("categoryId", selectedCategory);
    return query.getResultList();
}