Hibernate version: 3.1

Mapping documents:
state mapping


county mapping

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:
NA
Name and version of the database you are using:
MySQL
The generated SQL (show_sql=true):
NA
Debug level Hibernate log excerpt:
NA

How come when ever I use the follwing query I get 277 State objects returned in the list ...

"FROM VsiState s join fetch s.vsiCounties WHERE s.stateCode = '42'"

but when I use this query..

"FROM VsiState WHERE s.stateCode = '42'"

I get what I expect, one State. From my understanding joing fetch tells hibernate to eagerly retrieve the counites instead of the default lazy fetching defined in the mapping file. So, my question is ... why is join fetch causing Hibernate to return 277 State objects when all I want is one State object with its counties already (eagerly) fetch.? How do I fix this?

Thanks for any help :)

My guess is that since you're joining to the County object, there are 277 counties in the table with StateId 42. (Seems odd since I'm pretty sure no state has that many counties, but I digres)

Are the state objects all the same ? Sometimes Join queries require you to put the results into a Set to remove the duplicates. Using the Criteria API, you can set the ResultTransformer - ResultTransformer.DISTINCT_ROOT_ENTITY - so that only distinct objects are returned.

Well I looked at the resulting list (really kind of a spot check), and all the values are the same. I have no idea why?

Your HQL query "FROM VsiState s join fetch s.vsiCounties WHERE s.stateCode = '42" asks hibenate to return list of state and country objects. If you want only state objects you should change your query to "select c FROM VsiState s join fetch s.vsiCounties c WHERE s.stateCode = '42

correction:
select s FROM VsiState s join fetch s.vsiCounties WHERE s.stateCode = '42

If you're positive you're only expecting 1 State object, you can use Query.uniqueResult() instead of List.

Otherwise do this after you get your results.

Set states = new HashSet(results); Make sure that your State.hashCode() method is implemented properly.

BTW: Leonid's query won't change the results.

Your right that did not work, and I understnad that I could use uniqueResult but I just ran this query ...

"SELECT s FROM VsiState s join fetch s.vsiCounties WHERE s.stateCode = '42' OR s.stateCode = '03'"


and I received 352 entries ... 75 for state 03 and 277 for state 42 so this will not work.

It seems from the examples that join fetch is only supposed to return 1 object for the first query and 2 for the one I just ran above.

Set states = new HashSet(results);

Make sure that your State.hashCode() method is implemented properly.

Truely, if that ends up being the correct way to it then I will create the Set, but it seems odd that I would have to create Set for this situtation when it is returning the same 277 State objects for state '42'. I think either there is something wrong with the mapping or something wrong with the query, but which one it is and whats the best path to resolving the problem I don't know.

Is there something wrong with the mapping? Something wrong with the query? I am not sure. The examples shows this is poosible ...


from Cat as cat
inner join fetch cat.mate
left join fetch cat.kittens

and that this should return Cat objects, one for each unique Cat. So if there where 5 cats and 5 Kittens per Cat, then there should be 5 Cat objects in the resulting list not 25 Cat objects. :)

I was just providing you with the information that I've seen over the past few months from the Hibernate guys..

Christain - http://forum.hibernate.org/viewtopic.ph ... et+results

Anthony - http://forum.hibernate.org/viewtopic.ph ... ts+hashset

Gavin - http://forum.hibernate.org/viewtopic.ph ... ts+hashset

"This is known and intended behaviour (though contraversial). Applying DISTINCT in memory can be a quite expensive operation, so we prefer to leave it to the application. Just add the query results to a hashset and you will be fine...."

Wow, that pretty much answer my question, surprised though. Some of the posts in the links said wait for Hibernate 3 or 3.1 ... has it changed any?

I haven't seen any change. Maybe someone could correct me if there has been.

The only time I have issues with the behavior is when you want to limit your results to say the first 20 States or whatever. Since the Limit ends up on the SQL statement, and the SQL Statement returns the JOINED results, you might not actaully get more than 1 actual State object.

Thanks, from what it seems this nullifies using "fetch" and forcing the change to be in the mapping, which according to the documentation suggest not altering the mapping, but using "fetch" in the query (or setting the fetch mode for Criteria searches). This is an Object relational mapping tool and in that respects, to me Hibernate should return the results as unique Cat objects or in my case unique State objects. If the user decides to setRowMax (which is a JDBC ... relational setting) then, maybe make a note in the documentation that by doing so will return incomplete objects. Beside when I execute this....

FROM State WHERE code = '42'

Does this not give one State object with 277 counties in it? Maybe that should be a hint as to what this should return ...

FROM State as s join fetch s.counties WHERE code = '42'

... since fetch is only suppose to eagerly retrieve the association!