Say I want to fetch a person with all his sons and daughters from the database:
"select p from Person p left join fetch p.sons left join fetch p.daughters where p.id = 5"
Person has the properties Set<Son> sons and Set<Daughter> daughters.

If person with id 5 has 7 sons and 3 daughters, the select generates resultset of 21 rows, which is the cartesian product 7 times 3.

For a person with 20 sons and 10 daughters, this generates 400 rows, which is starting to scare me for performance reasons.

I need all sons and daughters to be eagerily fetched: directly after the query I am closing the persistence context (hibernate session) and serializing it. I process the person and his sons and daughters in a client.

How can I avoid the cartesian product?

I believe the simple answer is to use subselects, with eager fetching.

In your mapping config for each of the "sons" and "daughters" sets, add attributes lazy="false" and fetch="subselect". Then, you should be able to simplify your query to "from Person p where p.id = 5". It should eagerly fetch the sets, with subselects instead of joins.

To see what SQL it's generating, turn on SQL logging by turning on the following properties:

* hibernate.show_sql
* hibernate.format_sql
* hibernate.use_sql_comments

Thanks, that would cut it, but it turns out I also need to eager fetch a toOne property of Son and Daughter, for example son.favoriteGiJoe and daughter.favoriteDoll

Using Hibernate.initialize(p.getSons()) and Hibernate.initialize(p.getDaughters()) won't initialize son.favoriteGiJoe and daughter.favoriteDoll

I don't suppose it's possible to write a query like this:

select p from Person p
left join subselect p.sons s
left join fetch s.favoriteGiJoe
left join subselect p.daughters d
left join fetch d.favoriteDoll

This way I 'd avoid the n+1 query problem and the cartesian product problem, as hibernate could figure out to do 3 queries:
- one for person
- one for the sons and their favorite GiJoe
- one for the daughters and their favorite Doll