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

Another workaround instead of using Set is to use two queries to get the data :- 1st one to load the campaign and its associated promotions of the business. Then load the Business and its campaigns using fetch

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

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

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.