JPA: please help understanding "join fetch"

Question

I have the following entity structure: Business --> Campaign --> Promotion, where ONE Business can have MANY Campaigns and ONE Campaign can have MANY Promotions. Both one-to-many relations are declared as LAZY. One place in my code, I need to eagerly fetch both collections from a Business, so I do:

    Query query = entityManager.createQuery("select b from Business b " +
   "left join fetch b.campaigns c " +
   "left join fetch c.promotions where b.id=:id");
query.setParameter("id", b.getId());
business = (Business) query.getResultList().get(0);

However, the query returns a result list that contains 4 Business objects in it, all 4 objects are referring to the same Business instance. In my database, this business has 3 campaigns under it, and all the 3 campaigns have 3 promotions under them.

I have two questions:

1. At first, I use List to contains the many sides of the relations, but when program runs, I get "org.hibernate.HibernateException: cannot simultaneously fetch multiple bags" exception. Then I googled this exception and it looks like I have to use Set, instead of List. So I changed the collection to Set and it worked. Can someone tell me why List won't work in this situation?

2. I am expecting the query to return a single result, because it's querying against the id, which is the primary key and thus should only return a single result. But it turns out that it returns 4 instances in a List. Is this a problem? Or is this expected behavior?

Any help will be greatly appreciated.

Answer 1

1. I assume if you use a List, hibernate assumes that the order is important, and it will deduce the order of elements from the rows returned, but if you join with a nother table hibernate will get each campaign multiple times which confuses hibernate. Again, I am just assuming this

2. This is expected behavior Use a RootEntityResultTransformer to get a single root entity: http://www.hibernate.org/hib_docs/v3/api/org/hibernate/transform/RootEntityResultTransformer.html

Answer 2

The generated sql would look something like:

select * from Business b 
left outer join campaigns c on c.business_id = b.id
left join promotions  p on p.campaign_id = c.id
where b.id=:id

Internally Hibernate will have only one Business instance, however the duplicates will be preserved in the result set. This is expected behaviour. The behaviour you require can be acheived either by using the DISTINCT clause, or by using a LinkedHashSet to filter results:

Collection result = new LinkedHashSet(query.getResultList());

which will return only unique results, preserving insertion order.

The "org.hibernate.HibernateException: cannot simultaneously fetch multiple bags" happens whenever you try to eagerly fetch more than one collection in an ordered fashion (and possibly duplicated items). This does sort of make sense if you consider the generated SQL. Hibernate has no way of knowing whether a duplicated object was caused by the join or by actual duplicate data in the child table. Look at this for a good explanation.

Answer 3

1. List can have redundant elements because of the cartesian product and people weren't aware of that, so the hibernate guys started throwing this error to force people to use Set instead of List to avoid this problem. Source 1