What am I doing wrong that following query:

Code:

List list = session.find("select parent from Parent parent left outer join parent.children");

cause so many selects:

Hibernate: select parent0_.bbb_id as bbb_id, parent0_.bbb_name as bbb_name from deploy.dbo.bbb parent0_ left outer join deploy.dbo.aaa children1_ on parent0_.bbb_id=children1_.aaa_bbb_id
Hibernate: select aaa0_.aaa_id as aaa_id__, aaa0_.aaa_id as aaa_id, aaa0_.aaa_bbb_id as aaa_bbb_id from deploy.dbo.aaa aaa0_ where aaa0_.aaa_bbb_id=?
Hibernate: select aaa0_.aaa_id as aaa_id__, aaa0_.aaa_id as aaa_id, aaa0_.aaa_bbb_id as aaa_bbb_id from deploy.dbo.aaa aaa0_ where aaa0_.aaa_bbb_id=?
Hibernate: select aaa0_.aaa_id as aaa_id__, aaa0_.aaa_id as aaa_id, aaa0_.aaa_bbb_id as aaa_bbb_id from deploy.dbo.aaa aaa0_ where aaa0_.aaa_bbb_id=?etc.

How can I force Hibernate to do it in one retrieve Parents with Children in using one select.
My configuration looks like this:

1.It's classical case [Parent]<1---*>[Child].
2.Collection of children is not lazy initialized (lazy="false").
3.Hibernate 2.0.3.
4.Mapping and configuration files like below.




<hibernate-configuration>
<session-factory>
<property name="hibernate.dialect">net.sf.hibernate.dialect.SybaseDialect</property>
<property name="hibernate.connection.driver_class">com.jnetdirect.jsql.JSQLDriver</property>
<property name="hibernate.connection.url">jdbc:JSQLConnect://myserver:1268/database=family/logfile=jsql.log</property>
<property name="hibernate.connection.username">mi</property>
<property name="hibernate.connection.password">mi</property>
<property name="hibernate.connection.pool_size">1</property>
<property name="hibernate.statement_cache.size">25</property>
<property name="hibernate.jdbc.batch_size">0</property>
<property name="hibernate.jdbc.use_streams_for_binary">true</property>
<property name="hibernate.show_sql">true</property>
<property name="hibernate.use_outer_join">true</property>

<mapping resource="Child.hbm.xml"/>
<mapping resource="Parent.hbm.xml"/>
</session-factory>
</hibernate-configuration>

List list = session.find("select parent from Parent parent left outer join fetch parent.children");

fetch keyword is the key. Read the 9.3 section of the reference guide.

Hum, after a closer look, I wonder why this does not work.

select parent from Parent parent left outer join parent.children
parent has lazy="false", outer-join="auto", no proxy on child.

Fetch keyword will add proper attributes to the SQL select clause, but in the described case, we can expect to have the same behavior we have with the fetch keywork. In this case, it will do the left outer join for nothing.

As far as I can see, this is a bug, but it is linked to the 'only one fetch per request' limitation.

Two solutions :
- make an optimization and don't do the left outer join in this case.
- make an evolution and fill in the collection on request as fetch keyword do

Gavin, what do you think ?

Hum, after a closer look, I wonder why this does not work.

select parent from Parent parent left outer join parent.children
parent has lazy="false", outer-join="auto", no proxy on child.

Fetch keyword will add proper attributes to the SQL select clause, but in the described case, we can expect to have the same behavior we have with the fetch keywork. In this case, it will do the left outer join for nothing.

As far as I can see, this is a bug, but it is linked to the 'only one fetch per request' limitation.

Two solutions :
- make an optimization and don't do the left outer join in this case.
- make an evolution and fill in the collection on request as fetch keyword do

Gavin, what do you think ?

If I write the following SQL:

select foo from foos foo
left outer join bars bar on bar.foo = foo.id

then the behaviour is exactly the same.

HQL is completely consistent with SQL.

Use the FETCH keyword. Thats what its there for!

Agreed

But for the mapping point of view (lazy=false, outer-join="true"), I would expect a 1 select loading of my collection as it is in the "from foos" request.

Actually, collection return (ie lazyness) is different wether I explicitly set a join in my HQL query or not.

The lazy attribute does not affect outerjoining at all. In 2.1 there is a seperate outer-join attribute. Actually no mapping-level attributes affect HQL anyway. This is a Good Thing.