Java Hibernate 分页机制

Hibernate pagination mechanism

提问by MartinJoo

I am trying to use Hibernate pagination for my query (PostgreSQL )

我正在尝试对我的查询使用 Hibernate 分页（PostgreSQL）

I set setFirstResult(0), setMaxResults(20)for my SQL query. My code is like below:

我为我的 SQL 查询设置了setFirstResult(0), setMaxResults(20)。我的代码如下：

Session session = getSessionFactory().getCurrentSession();
session.beginTransaction();
Query query = session.createQuery("FROM Customers");
query.setFirstResult(0);
query.setMaxResults(20);
List<T> entities = query.list();
session.getTransaction().commit();

but when viewing the SQL Hibernate log, I still see the full SQL query:

但是在查看 SQL Hibernate 日志时，我仍然看到完整的 SQL 查询：

Hibernate: select customer0_.id as id9_, customer0_.customer_name as dst2_9_, customer0_.addres as dst3_9_ from tbl_customers customer0_  

Why there is no LIMIT OFFSET in query of Hibernate pagination SQL log?

为什么在查询Hibernate分页SQL日志时没有LIMIT OFFSET？

Does anyone know about Hibernate pagination mechanism?

有谁知道 Hibernate 分页机制？

I guess that Hibernate will select all data, put data into Resultset, and then paging in Resultset, right?

我猜Hibernate会选择所有数据，将数据放入Resultset，然后在Resultset中分页，对吗？

采纳答案by CHowdappaM

I am using in query and in hibernate call back. both are working as expected. Hibernate Query executes for results in between First and Max size given. Here Seems like you passed SQL not HQL to query. if yes it shouldn't work.

我在查询和休眠回调中使用。两者都按预期工作。Hibernate Query 对介于 First 和 Max size 之间的结果执行。这里似乎您通过 SQL 而不是 HQL 来查询。如果是，它不应该工作。

-- See my code here.

- 请在此处查看我的代码。

        Query query = this.getSession().createQuery("FROM QueryType");
        query.setFirstResult(0);
        query.setMaxResults(20);
        List toDelete = query.list();

and in log:

并在日志中：

select * from ( select -- ALL column names. (dont want to share here.) from MY_TBL_NAME querytype0_ ) where rownum <= ?

select * from ( select -- ALL column names. (don't want to share here.) from MY_TBL_NAME querytype0_ ) where rownum <= ?

回答by Anand Rockzz

There are quite a few ways to paginate.

有很多方法可以分页。

HQL and setFirstResult, setMaxResults API

HQL 和 setFirstResult、setMaxResults API

Session session = sessionFactory.openSession();
Query query = session.createQuery("From Foo");
query.setFirstResult(0);
query.setMaxResults(10);
List<Foo> fooList = query.list();
//Total count
String countQ = "Select count (f.id) from Foo f";
Query countQuery = session.createQuery(countQ);
Long countResults = (Long) countQuery.uniqueResult();
//Last Page
int pageSize = 10;
int lastPageNumber = (int) ((countResult / pageSize) + 1);

HQL and the ScrollableResults API

HQL 和 ScrollableResults API

String hql = "FROM Foo f order by f.name";
Query query = session.createQuery(hql);
int pageSize = 10;

ScrollableResults resultScroll = query.scroll(ScrollMode.FORWARD_ONLY);
resultScroll.first();
resultScroll.scroll(0);
List<Foo> fooPage = Lists.newArrayList();
int i = 0;
while (pageSize > i++) {
    fooPage.add((Foo) resultScroll.get(0));
    if (!resultScroll.next())
        break;
}
//Total count
resultScroll.last();
int totalResults = resultScroll.getRowNumber() + 1;

Simply the Criteria API

简单的标准 API

Criteria criteria = session.createCriteria(Foo.class);
criteria.setFirstResult(0);
criteria.setMaxResults(pageSize);
List<Foo> firstPage = criteria.list();
//Total count
Criteria criteriaCount = session.createCriteria(Foo.class);
criteriaCount.setProjection(Projections.rowCount());
Long count = (Long) criteriaCount.uniqueResult();

baeldunglists them all with examples.

baeldung列出了所有示例。

回答by Vlad Mihalcea

As I explained in this article, you can use the JPA pagination for both entity queries and native SQL.

正如我在本文中所解释的，您可以将 JPA 分页用于实体查询和本机 SQL。

To limit the underlying query ResultSetsize, the JPA Queryinterface provides the setMaxResultsmethod.

为了限制底层查询的ResultSet大小，JPAQuery接口提供了setMaxResults方法.

Navigating the following page requires positioning the result set where the last page ended. For this purpose, the JPA Queryinterface provides the setFirstResultmethod.

导航下一页需要将结果集定位在上一页结束的位置。为此，JPAQuery接口提供了setFirstResult方法。

JPQL

JPQL

List<Post> posts = entityManager
.createQuery(
    "select p " +
    "from Post p " +
    "order by p.createdOn ")
.setFirstResult(10)
.setMaxResults(10)
.getResultList();

DTO projection queries

DTO 投影查询

The JPA query pagination is not limited to entity queries that return entities only. You can use it for DTO projections as well.

JPA 查询分页不限于仅返回实体的实体查询。您也可以将它用于 DTO 投影。

List<PostCommentSummary> summaries = entityManager
.createQuery(
    "select new " +
    "   com.vladmihalcea.book.hpjp.hibernate.fetching.PostCommentSummary( " +
    "       p.id, p.title, c.review " +
    "   ) " +
    "from PostComment c " +
    "join c.post p " +
    "order by c.createdOn")
.setMaxResults(10)
.getResultList();

Native SQL queries

本机 SQL 查询

The JPA query pagination is not limited to entity queries, such as JPQL or Criteria API. You can use it for native SQL queries as well.

JPA 查询分页不限于实体查询，例如 JPQL 或 Criteria API。您也可以将它用于本机 SQL 查询。

List<Tuple> posts = entityManager
.createNativeQuery(
    "select p.id as id, p.title as title " +
    "from post p " +
    "order by p.created_on", Tuple.class)
.setFirstResult(10)
.setMaxResults(10)
.getResultList();

JOIN FETCH and pagination

JOIN FETCH 和分页

However, if we try to use the JOIN FETCHclause in the entity query while also using JPA pagination:

但是，如果我们尝试JOIN FETCH在实体查询中使用该子句，同时还使用 JPA 分页：

List<Post> posts = entityManager.createQuery(
    "select p " +
    "from Post p " +
    "left join fetch p.comments " +
    "order by p.createdOn", Post.class)
.setMaxResults(10)
.getResultList();

Hibernate will issue the following warning message:

Hibernate 将发出以下警告消息：

HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!

And the executed SQL query will lack the pagination clause:

并且执行的 SQL 查询将缺少分页子句：

SELECT p.id AS id1_0_0_,
       c.id AS id1_1_1_,
       p.created_on AS created_2_0_0_,
       p.title AS title3_0_0_,
       c.created_on AS created_2_1_1_,
       c.post_id AS post_id4_1_1_,
       c.review AS review3_1_1_,
       c.post_id AS post_id4_1_0__,
       c.id AS id1_1_0__
FROM post p
LEFT OUTER JOIN post_comment c ON p.id=c.post_id
ORDER BY p.created_on

This is because Hibernate wants to fetch entities fully along with their collections as indicated by the JOIN FETCHclause while the SQL-level pagination could truncate the ResultSetpossibly leaving a parent Postentity with fewer elements in the commentscollection.

这是因为 Hibernate 想要按照JOIN FETCH子句的指示完全获取实体及其集合，而 SQL 级别的分页可能会截断ResultSet可能留下集合中Post元素较少的父实体comments。

The problem with the HHH000104warning is that Hibernate will fetch the product of Postand PostCommententities, and due to the result set size, the query response time is going to be significant.

HHH000104警告的问题在于 Hibernate 将获取Post和PostComment实体的乘积，并且由于结果集的大小，查询响应时间将很重要。

In order to work around this limitation, you have to use a Window Functionquery:

为了解决此限制，您必须使用窗口函数查询：

@NamedNativeQuery(
    name = "PostWithCommentByRank",
    query =
        "SELECT * " +
        "FROM (   " +
        "    SELECT *, dense_rank() OVER (ORDER BY \"p.created_on\", \"p.id\") rank " +
        "    FROM (   " +
        "        SELECT p.id AS \"p.id\", " +
        "               p.created_on AS \"p.created_on\", " +
        "               p.title AS \"p.title\", " +
        "               pc.id as \"pc.id\", " +
        "               pc.created_on AS \"pc.created_on\", " +
        "               pc.review AS \"pc.review\", " +
        "               pc.post_id AS \"pc.post_id\" " +
        "        FROM post p  " +
        "        LEFT JOIN post_comment pc ON p.id = pc.post_id " +
        "        WHERE p.title LIKE :titlePattern " +
        "        ORDER BY p.created_on " +
        "    ) p_pc " +
        ") p_pc_r " +
        "WHERE p_pc_r.rank <= :rank ",
    resultSetMapping = "PostWithCommentByRankMapping"
)
@SqlResultSetMapping(
    name = "PostWithCommentByRankMapping",
    entities = {
        @EntityResult(
            entityClass = Post.class,
            fields = {
                @FieldResult(name = "id", column = "p.id"),
                @FieldResult(name = "createdOn", column = "p.created_on"),
                @FieldResult(name = "title", column = "p.title"),
            }
        ),
        @EntityResult(
            entityClass = PostComment.class,
            fields = {
                @FieldResult(name = "id", column = "pc.id"),
                @FieldResult(name = "createdOn", column = "pc.created_on"),
                @FieldResult(name = "review", column = "pc.review"),
                @FieldResult(name = "post", column = "pc.post_id"),
            }
        )
    }
)

For more details about using Window Functions to fix the HHH000104issue as well as the code for DistinctPostResultTransformer, check out this article.

有关使用 Window Functions 解决HHH000104问题的更多详细信息以及 代码DistinctPostResultTransformer，请查看这篇文章。