Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 6 posts ] 
Author Message
 Post subject: HHH000104: firstResult/maxResults specified..warnings
PostPosted: Mon Apr 03, 2017 6:22 pm 
Newbie

Joined: Mon Oct 10, 2005 10:58 pm
Posts: 7
So the JPA spec allows us to specify fetch joins in order to pre-load the parts of the object graph we are interested in order to avoid the whole n+1 scenario. We went with LAZY mapping as defaults in our entity model and then each UI use case dictates what we want to eagerly load in a JPQL query fashion.

For example, you want to load a list of companies and eagerly load departments under each as such: "select c from Company c left join fetch c.departments d"

I found that by NOT invoking the "setFirstResult(0)" method, there is no warning(HHH0001014) which is fine. In my case, I don't need to paginate as I want all companies anyway.

However, when I DON'T invoke setFirstResult(0), my resulting list size(List<Company>) is larger than should be as if departments are being counted as Companies instead of being loaded within each company.

When I invoke a "setFirstResult(0)" method, I get the warning but my results are correct. Hibernate is correctly building the object graph out.

Question 1) If the JPA spec supports eagerly loading objects into the object graph(assumption) via a query, why is this a warning? I expect hibernate to correctly consolidate/aggregate the data into the appropriate graph as if I asked for departments from each company(minus the n+1 queries).

Question 2) Does it really make sense that invoking setFirstResult(0) with no max results return a different number of objects than if you did NOT invoke setFirstResult?

Thanks


Top
 Profile  
 
 Post subject: Re: HHH000104: firstResult/maxResults specified..warnings
PostPosted: Tue Apr 04, 2017 1:09 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1227
1. You get this warning because Hibernate must make sure that the pagination is done against parents. If you execute the plain SQL you'd see that the parent row info is duplicated for each joined child record referencing the parent. For this reason, Hibernate cannot simply truncate the Resultset after N entries because it would mean that the last parent would not return all of its children, right?

2. No, it does not make any sense to only call setFirstResult(0) if your intention is to do pagination.

So, how can you fix this?

As I explained in this post, collections are not very useful if they grow too large. You are better off replacing those with queries which are easier to paginate and don't suffer from the aforementioned limitation.

You can just fetch the parents firsts with one query:

Code:
List<Company> companies =
entityManager.createQuery(
   "select c from Company c order by c.id", Company.class )
.setFirstResult( 40 )
.setMaxResults( 10 )
.getResultList();


You need to use the ORDER BY clause because result sets are not guaranteed to be ordered by default.

Then with the parent references, you can just issue a secondary query:

Code:
List<Department> phones = entityManager.createQuery(
   "select d " +
   "from Department d " +
   "where d.company in :companies", Department.class )
.setParameter( "companies", companies )
.getResultList();


Then you can combine the results and send them to the UI.

But why do you really need to select entities for a paginated projection?

A DTO projection is much more efficient and you can use the Hibernate ResultTransformer to customize the DTO result any way you like.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: HHH000104: firstResult/maxResults specified..warnings
PostPosted: Tue Apr 04, 2017 9:45 am 
Newbie

Joined: Mon Oct 10, 2005 10:58 pm
Posts: 7
Thanks for your reply. I do understand what the warning means when pagination is configured and why.

Sorry for the confusion regarding what I did to make hibernate behave different. I will simplify the use case here.

Assume the domain set is small and I don't want any pagination(for sake of this discussion). I want all my company entities with all the departments loaded in each company object.

I believe the JPQL reference (10.2.3.5.3. JPQL Fetch Joins) in this link http://docs.oracle.com/html/E13946_04/e ... etch_joins

indicates that a query such as this "select m from Company m left join fetch m.departments" should return a list of companies with the departments property/attribute fully initialized.

I do not get this result so I think this is a bug or something up with our entity model I am not seeing.

MAIN QUESTION: I was looking for ideas why the above use case is happening as its contrary to what I read in the reference link above.

I know there are alternatives(projection queries or by separating the domain entities and using IN clause(though there are some limitations on IN as well) but that would cause us to re-write quite a bit of code. We have been working right now only because we are setting first result to 0 so hibernate returns the right number of entities back but then we get thousands of those warning messages in our log file even when we are not limiting results and starting with row 0. so trying to figure out how to get rid of the warning and get the same results back without re-writing a bunch of code. I tried simply NOT setting first result because we do not need to paginate BUT I am getting now different results(the part that is a bug IMO).

MISC INFO:
The whole comment about setFirstResult was a sidebar just saying that if I set first result to 0(which should be the default anyway) and that's all I do, I get back the correct number of companies back with the warning(which I know why its warning me but I didn't indicate an order nor did I tell it to limit my results anyway since and I started with the first row so the warning makes no sense in this specific use case, etc.).


Top
 Profile  
 
 Post subject: Re: HHH000104: firstResult/maxResults specified..warnings
PostPosted: Tue Apr 04, 2017 10:05 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1227
You say that:

Quote:
However, when I DON'T invoke setFirstResult(0), my resulting list size(List<Company>) is larger than should be as if departments are being counted as Companies instead of being loaded within each company.


I think you get more results that expected due to parent-child fan out. So if you have 10 Companies, each with 5 Departments, then the ResultSet is 50.

To fix this, you need to use DISTINCT along with the PASS_THROUGH hint so that the JPA DISTINCT is not passed to the underlying SQL.

Anyway, the setFirstResult(0) argument makes no sense and I don't know why it provides a different result in your case.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: HHH000104: firstResult/maxResults specified..warnings
PostPosted: Tue Apr 04, 2017 12:12 pm 
Newbie

Joined: Mon Oct 10, 2005 10:58 pm
Posts: 7
Quote:
To fix this, you need to use DISTINCT along with the PASS_THROUGH hint so that the JPA DISTINCT is not passed to the underlying SQL.


Thanks so much for response. I will give this a try. Unfortunately, we are at Hibernate 5.1.0 so will have to upgrade to get this pass through hint I believe.

Question:
I am gathering there is no JPA SPEC way to do this except for what we stumbled into by simply doing a setFirstResult(0) which causes that warning but maybe we should just ignore the warning since we know we are not limiting results anyway?

We are trying to be as agnostic as we can on ORM provider relying on JPA spec though its not a simple feat.

will post back on solution you proposed.

Ken


Top
 Profile  
 
 Post subject: Re: HHH000104: firstResult/maxResults specified..warnings
PostPosted: Tue Apr 04, 2017 12:26 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1227
DISTINCT is a JPA keyword too, and JPA portability is a myth.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 6 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.