Eager fetching collections in Hibernate with ScrollableResults

I'm trying to use Hibernate to retrieve approximately 100 million rows from a table. I have a persisted entity Item that contains a collection of Fees inside (another persisted entity). Given that I will iterate over the result and access the fees for every object, I want to eagerly fetch fees to avoid the n+1 problem.

I should also mention that I want to join it to another table called Provider (one-to-one mapping but no foreign key). I tried:

String query = "select new " + Order.class.getName() 
           + "(i, p) from Item i left join fetch i.fees f, Provider p where "
           + "p.factoryId=i.factoryId and p.factoryRef=i.factoryRef";

return session.createQuery(query).scroll();

My Order class contains a Provider field and an Item field. I get this error:

Caused by: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list

I would like to end up with a scrollable list of Order which contain Item (with the fees eagerly fetched) and Provider.

This code from SelectClause causes you the trouble :

if ( !fromElementsForLoad.contains( origin ) ) {
                        throw new QueryException(
                                "query specified join fetching, but the owner " +
                                "of the fetched association was not present in the select list " +
                                "[" + fromElement.getDisplayText() + "]"
                        );

As you can see, when the fetch keyword is mentioned, hibernate checks to see if you asked for the fetch decorated field parent. fromElementsForLoad.contains( origin )

They probably did it to defend you from making a redundant join that will cost you a lot in performance. It's a good thing cause there's no reason for fetching the association if you never use it.

I believe that in your case - wrapping the Item.class in the new Order.class hides the fact that you do use the fetch decorated field parent in the query.

I have no debugging abilities at the moment so I can't validate this. try and debug this exact row from SelectClause.class and see what elements the fromElementsForLoad collection holds.

If you want to avoid the n+1 problem I would recommend initializing the Order.class after the query. You can select only the Item and Provider.

If you can't validate this, I'll get to a proper computer next week and expand my answer.