Hibernate version: 3.3.1

Name and version of the database you are using: Derby Embedded

<property name="show_sql">false</property>
<property name="format_sql">true</property>
<property name="use_outer_join">true</property>
<property name="hibernate.max_fetch_depth">5</property>


table XCPTN has two Many-To-One relationship with FIRM_REF and XCPTN_TYPE

if I run query this way:

There is only ONE query:


But if I run it this way:

There are three queries:


How to force Hibernate to use one query with joins instead of three simple?

You'll need to specify this in your HQL query. Something like:

Well, I mean automatically, not writing "left join fetch". I think that runnig one SQl query is cheaper then three. Why hibernate uses one big query in the first scenario and three small queries in the second one? in both cases it is just search by xcptn.id.

Because that is how HQL works. It will only fetch what you have specified in the HQL. It ignores whatever you have put in the "fetch" attribute of your mapping files. I assume you have fetch="join" since that is what is causing the first example to select everything with one query instead of three.

The reason that you are seing additional selects in the second example with HQL is probably because you have have disabled the use of proxies for the FIRM_REF and XCPTN_TYPE entities.

If you need all three entities (Xcptn, FIRM_REF and XCPTN_TYPE) and if you want to use HQL that only generates one select, you'll need to specify "left join fetch" in the HQL.

If you only need the Xcptn entity and want to use HQL and want to avoid the extra select statements you'll need to enable proxies for FIRM_REF and XCPTN_TYPE. Or... as an option, you could setup a second-level cache for FIRM_REF and XCPTN_TYPE.

Thank you for explanation.

But still do not understand why hibernate goes "not optimal" way running three queries instead of one.

Beacuase you issued a query that told Hibernate to do it that way.

Well. XCPNT entity has two @ManyToOne relationship with XCPTN_TYPE and FIRM_REF. By default @ManyToOne has fetch=EAGER. So if I look for XCPTN with id = 1000 JPA impl should fill entities on the other side of @ManyToOne. In this case there are XCPTN_TYPE and FIRM_REF.
And of course Hibernate does it. The question why it does it in "Not optimal" way, using three queries instead of one?

Why I need to write long query with left join fetch if I already specify relationships and fetches via annotations?

Is there any way to use small queries or whatever which relies on Relationship annotation information.

E.g. I need to get xcptn with xcpnt.type.name = "type1" how to do it easiest way? Plus I want that entities on the other side of fetch=eager relationship will be filled and I do not want to write join logic by hand anywhere except from relationship annotations. Plus I want that JPA impl does smart queries, and minimize their number.

I don't know how many times I need to repeat that this is because your HQL tells Hibernate to do it that way. If you want Hibernate to fetch everything in one SQL, you'll need to say that in your HQL by using "join fetch". Fetch settings in your mappings files/annotations are in most cases not used by HQL. What is non-optimal for you, may not be for someone else. It all depends on a lot of other things, such as how your proxies and second-level cache are configured etc. You can find more information here: http://www.hibernate.org/hib_docs/v3/re ... ing-custom