Hibernate version: 3.0.5

Mapping documents:


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


Full stack trace of any exception that occurs:No errors.

Name and version of the database you are using:MS SQL server 2000 sp4

The generated SQL (show_sql=true):



I feel like quite the noob for this, but this is returning way too many results (for me, about 86000 rather than 2000). Adding a "distinct" to the hql query gets the right number, but it's horribly inefficient and takes nearly 10 seconds. From what I see, the expression " and style0_.season=collective1_.id" should be a condition of the join, so that the generated sql should look like this:



Even this would be equivalent and work just as well for me:



That is a fast query and is what I want to do. I've been reading about joins, and have experimented with fetch="select|join" and haven't had any luck. What am I missing here?

The other option I can think of right now is to change the HQL query to something like this:


That works, and in an acceptable amount of time, but these queries are assembled at runtime through an advanced search type interface, so coming up with that HQL on the fly is a lot harder, but not impossible. Any ideas?

Thanks for looking.

The second condition [style.description like '%10%'] does not require presence of collection elements and therefore Hibernate’s translation to SQL seems logical and correct.

Note: your fast query will not pick Styles which do not have associated collection elements; that maybe OK or not depending on your requirements.

Therefore your last HQL seems like way to go.

Or you can use SQL support in Hibernate
http://www.hibernate.org/hib_docs/v3/re ... rysql.html

I'm not a database whiz, so pardon me if I sound like a clod here. When I do something like "where style.season.displayName like '%fall%'" I would expect that hibernate would do an inner join here. Is there any way to make hibernate do an inner join in that case? I know I can force hibernate to do an inner join in the HQL, as shown below and in my solution in the OP. Is there a way to force hibernate to do an inner join in the mapping document? I just don't understand why, in an association like this, an outer join would ever be desired or useful. I realized that my lack of understanding may be indicative of my lack of DB knowledge, but I'd like to know.


Thanks for the comments. I think what I really want to do is something along these lines:



This is roughly equivalent to the last HQL query in my first post. It's a lot less code to write, though, since I don't have to know that the season is in the ListItem table when I construct the query.

And Hibernate does the join for this condition that comes first in your query, I was pointing at the second condition, which does not require join anyhow.

The query result is kind union of two queries:
select style.* from Style style where style.season.displayName like '%fall%'
UNION
select style.* from Style style where style.description like '%10%'