EclipseLink(JPA 2.1)查询未在明显应返回的地方返回结果 [英] EclipseLink (JPA 2.1) Query not returning results where it clearly should

问题描述

我正在尝试使用下面的代码从数据库中检索结果的下一页.

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();
    }

我的产品"表大约有1000行，每个产品都属于一个类别. 如果我选择类别1之类的商品，其中有80种商品.我能够查看4页的产品，每页包含20个产品.当我尝试访问属于较高categoryId的产品时，就会出现问题.我只能查看前20个结果，但下一页返回0个结果. 例如，类别15的产品范围为id=459 to id=794 where id is the primary key.

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.

当我从https://localhost:8181/myapp/category?categoryId=15访问类别页面时，查询将返回前20个结果，但是单击底部https://localhost:8181/myapp/category?categoryId=15&start=478处的更多链接将返回0个结果.我想念什么?

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?

另请参阅此问题与此类似

推荐答案

我终于找到了我的错误.显然，我为第一个结果设置的位置应该是page number and the page size的乘积.就我而言，我将第一个结果设置为要检索的实体的Id.作为前几页的结果，结果大小包含ID，但是随着页码的增加，id不包含在结果集中.直到您知道自己不知道，您才知道这种错误.

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();
}

这篇关于EclipseLink(JPA 2.1)查询未在明显应返回的地方返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！