I have an address class with 2 one to many relationships (points and rectangles). I need to eager fetch both the points and the rectangles belonging to an address when I load the address.

I am wondering how to load say 1000 of these addresses without ending up with a N+1 selects problem.

My understanding is that I can only use outer join on one of the relationships, say rectangles. The other relationship (points) is then loaded with 1 query for each address. (n+1).

If I was working with straight SQL I would load in everything with two querys (Address join Rectangles and Address join Points) and merge the result.

How do I do this in Hibernate.

Another issue I have seems to be the way that hibernate uses the second level cache. It seems to cache the proprerties of the object but not the associatioans. So when I fully materialise the class that had lazy loaded collections, it ends up querying the database to find out what is in the collections. It may load the objects in the collection from the cache but it queries the database to decide which objects to load. I can't cache this query by using named queries so how do I optimise this ????

What prevents you from doing exactly what you are saying, i.e. doing your two straight SQL query and then merge ? Hibernate can load objects using straight SQL, or even better, HQL.

this is not a problem the rule is: you can fetch only one collection in one query, so if you need to fetch 2 collections, just execute 2 queries... and let the first level do his job.