Correct way to query an entity with nested collections?

Question

It looks like basic queries don't work when collection elements have collections of their own.

Imagine a data model for a bank with customers, which have portfolios, which have investments. What is the correct way to get customers?

I tried this:

@Query("SELECT DISTINCT c FROM Customer c LEFT JOIN FETCH c.portfolios")

But it fails with "Failed to lazily initialize a collection of role".

Investments is defined as:

@ManyToMany(cascade = CascadeType.ALL)

The query will work, if I change the fecthType:

@ManyToMany(cascade = CascadeType.ALL, fetch=FetchType.EAGER)

But is there a way to make it work without changing the fetchtype?

EDIT:

I should mention that I used Spring's JpaRepository to define the above query. All entities have their respective repositories, but it looks like fetching Customers does not touch any methods in Portfolios repository (which would LEFT JOIN the Investments) and so the investments are never fetched.

Also, I can fetch a Portfolio with a similar query and it works fine as Investment does not have any collections. But fetching the chain customer -> portfolios -> investments fails.

This should help you : http://stackoverflow.com/questions/5404599/failed-to-lazily-initialize-a-collection-of-role — DessDess, Apr 08 '13 at 09:03
I still don't get why it works for entities with collections, but not for entities with collections that have collections... — wannabeartist, Apr 08 '13 at 10:40
Just a question, with a ManyToOne mapping do you still get the error ? — DessDess, Apr 08 '13 at 12:23
I have another case with @OneToMany and that fails just the same. The common denominator is that the elements of the collection also have collections. — wannabeartist, Apr 08 '13 at 12:42
Tried it, no difference. I added some more information in the original question. — wannabeartist, Apr 08 '13 at 12:51

JB Nizet · Answer 1 · 2013-04-08T13:56:13.940

2

Your query selects customers and fetches their portfolios, using a left join fetch. If you want to also fetch the invstments of the portfolios, you need an additional join fetch, just like in SQL:

select distinct c FROM Customer c 
left join fetch c.portfolios portfolio
left join fetch portfolio.investments

edited Apr 08 '13 at 13:56

answered Apr 08 '13 at 13:43

JB Nizet

678,734
91
1,224
1,255

Do you have any clue about why it throws an exception ? I would understand why he gets this exception if he tried to access investments as they are not initialized but here he is not trying to fetch investments so why does it fail ? I may be completly wrong ... – DessDess Apr 08 '13 at 13:49
Thanks, that works. But what if I don't need them? How do I tell hibernate/jpa to ignore the investments and only get customer and the portfolios? – wannabeartist Apr 08 '13 at 13:50
1

If you get this exception, it means that you're accessing the investments collection. If you don't need them, then execute the query in your question, without the `left join fetch portfolio.investments`. The exception you get precisely shows that the investments have not been loaded. – JB Nizet Apr 08 '13 at 13:56
1

"If you get this exception, it means that you're accessing the investments collection" - I was wondering where that could happen and then I realized that it's been converted into JSON (it's all part of a REST api). It must be the JSON converter that accesses investments! – wannabeartist Apr 08 '13 at 15:16

Correct way to query an entity with nested collections?

1 Answers1