CODEWITHSUNDEEP
oracle-databasehibernatejpapaginationpersistence
Stephan
Stephan

Reputation: 736

Why does Hibernate ignore setMaxResults?

I am using a server side pagination for one of my tables using a CriteriaQuery and a TypedQuery and set following values: typedQuery.setFirstResult(0); typedQuery.setMaxResults(100);

Unfortunately, in the generated SQL query which is executed on Oracle DB, i never see the ROWNUM condition. I added also an ORDER BY in my TypedQuery, but still, the query does a simple select withouut limiting on the DB the results.

As a result i am getting following warning HHH000104: firstResult/maxResults specified with collection fetch; applying in memory! . In other words, Hibernate does the pagination on memory as it is not performed on the DB. For this warning i read following article https://vladmihalcea.com/fix-hibernate-hhh000104-entity-fetch-pagination-warning-message/ but before spliting my query into two queries (retrieve id and then retrieve data for those id), i thought of giving setMaxResults . Still i wonder why isn't the generated query as expected with a ROWNUM.

Furtehr information:

Upvotes: 1

Views: 1201

Answers (2)

Christian Beikov
Christian Beikov

Reputation: 16400

You have to understand that the first/max results apply on entity level if you select an entity. If you fetch join collection attributes or use an entity graph for collection attributes you change the cardinality of the rows returned by JDBC for each entity i.e. every row for the main entity row is duplicated for every collection element. The effect of that is, that Hibernate can't do pagination with ROWNUM anymore which is why you are not seeing it in the query. If you remove the fetch join you will see the use of ROWNUM.

Having said that, this is a perfect use case for Blaze-Persistence.

Blaze-Persistence is a query builder on top of JPA which supports many of the advanced DBMS features on top of the JPA model. The pagination support it comes with handles all of the issues you might encounter.

It also has a Spring Data integration, so you can use the same code like you do now, you only have to add the dependency and do the setup: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-setup

Blaze-Persistence has many different strategies for pagination which you can configure. The default strategy is to inline the query for ids into the main query. Something like this:

select u 
from User u 
left join fetch u.notes 
where u.id IN (
  select u2.id
  from User u2
  order by ...
  limit ...
)
order by ...

Upvotes: 2

Conrad
Conrad

Reputation: 564

When joining data, parent will be duplicated n times. For example:

select p from Post p join p.comments

If post have 20 comments under one post, then this one post will be returned 20 times with 20 different comments. Limiting rows in this case doesn't make sense because actual number of returned post won't be equal to page size. In other words limiting page to 20 records will return only one post.

Upvotes: 1

Related Questions