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

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:



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:



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.

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.).

You say that:



I think you get more results than 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.

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

DISTINCT is a JPA keyword too, and JPA portability is a myth.