Sorry for cross-posting this, I posted to Miscellaneous before realizing that this is probably the place for it. Let me know if I'm still posting to the wrong place.. thanks!

I was wondering how you guys would do this. I'm new to hibernate and trying to come up with the best fetching strategy.

I have a number of instances where an entity has two or more many to many relationships to other entities. I have a User who is related to many Locations, and belongs to many Roles. I'm fetching the data for a SWING application which will also display the entire list of Roles and Locations, so I've already select *'d them.

My first question is:

Does it make sense to do one outer join fetch, and then iterate the results to get the other?

ie:

List users = session.find("from User as u left join fetch u.Locations");

if ((users != null) && (users.size() > 0)) {
HashSet distinctUsers = new HashSet(users);
Iterator i = distinctUsers.iterator();
while (i.hasNext()) {
User user = (User)i.next();
Hibernate.initialize(user.getRoles());
}
data.setUsers(new ArrayList(distinctUsers));
}

Or.. would it be better to do two outer joins and return one of the resulting lists? I tested this, and it seems to work exactly as I would have expected. It actually makes me wonder why Hibernate wouldn't do two queries if I'd simply asked for two outer-join collections? Seems like a reasonable implementation that avoids the cartesian product...

ie:

List users = session.find("from User as u left join fetch u.Locations");
List usersAndMore = session.find("from User as u left join fetch u.Roles");
HashSet distinctUsers = new HashSet(usersAndMore);
data.setUsers(distinctUsers);

Thoughts? The users table is going to hold something in the order of 100-200 users, each with maybe between 0-10 Locations and 0-10 Roles. Are either of these patterns feasible? Why would you choose one over the other. I'm like the second one, but I'm kind of bugged by how unreadable it might be to someone who didn't know why I was doing that.

A related question... My queries to select all Locations and Roles are always hitting the database, despite being set up to cache. I can guess why this would be, but is there a way to make use of the cache for them?

thanks in advance.
phill

Cartesian products are considered a Bad Thing.

Combine one outer join fetch, with batch fetching.

Haha. Um, agreed.



Ahhh. I looked at the batch fetching stuff. I suppose the pattern I'd use would be the first one I described in my post? Which was to do one outer join query, and then roll through the entire collection initializing any other collections. The batch fetching would ensure that most of my calls to intialize() don't incurr SQL queries (depending on batch size of course).

Yes?

I like this approach for a couple of reasons. Not the least of which is that it occurred to me that my ugly two-query-outer-join-idea might be flawed in that there's no guarantee without some ugly locking that the same User records will be returned by both calls (leaving some results half-initialized).

Thanks for that.

Now, for my lists that often get select * 'd... Is Hibernate's query caching the only way to do that? Is that something you'd recommend?

Thanks again,
phill