join problem in hibernate 3.x

[peter tong]

Hibernate-Version: 3.2.4.sp1

I am a beginner in hibernate, I used to use microsoft sql server T-SQL and there is left outer join, inner join, right outer join keyword. but is it not support in hibernate?

I find left join fetch, right join fetch, inner join fetch keyword in hibernate, is it similar to left outer join, inner join, right outer join in T-Sql?

[Paul Sturrock]

By and large these HQL clauses are synonymous with their SQL equivalent (NB: these are ANSI SQL, not Transact-SQL, they will work on all complient databases).

The difference here is the word "fetch". This is used to support eager fetching, whereby you know you are going to use the data in the associated entity so you tell Hibernate to get it at the same time, rather than relying on lazy fetching. In this way you avoid multiple extra select queries when you access an associated property in your result set. If you turn on sql logging in Hibernate and try the two you'll see the difference.

Be aware that there are restrictions on when you can use eager fetching. Have a read of the documentation to see what these are.

[peter tong]

I feel the document is for expert user to see!!
it write "the associated objects should not be used in the where clause ..."

then can I write something like
1)"from Supplier s inner join fetch s.products as p where s.products.price > 100";

also, the document write ""Nor should fetch be used together with setMaxResults() or setFirstResult() as these operations are based on the result rows, which usually contain duplicates for eager collection fetching, hence, the number of rows is not what you'd expect."

I cannot understand what it said!! for example, I want to retrieve 1000 rows, even with duplicate record, then can I use .setMaxResults(1000); ??

[Paul Sturrock]

then can I write something like
1)"from Supplier s inner join fetch s.products as p where s.products.price > 100";

It doesn't make sense to write what you've written (and it wouldn't make much sense if this were a SQL query either). You've defined a join on products and assigned this association an alias of p, then you've refered to products without using the alias. The only situation where an eager fetch (remember, as I said earlier this is a different thing than a simple join) might need an alias is if you are fetching accross two associated collections. i.e. if products had a collection of items for example.

also, the document write ""Nor should fetch be used together with setMaxResults() or setFirstResult() as these operations are based on the result rows, which usually contain duplicates for eager collection fetching, hence, the number of rows is not what you'd expect."

I cannot understand what it said!! for example, I want to retrieve 1000 rows, even with duplicate record, then can I use .setMaxResults(1000); ??

No. But you can use them if you use a normal join, rather than one that uses eager fetching.

[peter tong]

then can I write something like
1)"from Supplier s inner join fetch s.products where s.products.price > 100"; ??

I ask this question becuase the document said "the associated objects should not be used in the where clause ..."

but now I use "...where s.products.price > 100", is it ok?

[Paul Sturrock]

Not sure what you are asking, these where clauses look the same to me?

[peter tong]

the initial statement contain alias "as p"
"from Supplier s inner join fetch s.products as p where s.products.price > 100";

the revised statement move out the alias "as p"
"from Supplier s inner join fetch s.products where s.products.price > 100";

is the revised statement ok?