Hello,

Can I change the example below to make Hibernate load this objet in one sql statement?

Thanks!

Mappings:

<?xml version='1.0' encoding='windows-1252'?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="Projeto" table="SAC.ESACPROJETO">
<composite-id name="projetoPK" class="ProjetoPK">
<key-property name="cdProjeto" type="int"/>
</composite-id>
<property name="nmProjeto" type="string"/>
<property name="sgProjeto" type="string"/>
<property name="deBasevss" type="string"/>
<bag name="sistemas">
<key column ="cdProjeto" />
<one-to-many class="Sistema"/>
</bag>
</class>
</hibernate-mapping>

<?xml version='1.0' encoding='windows-1252'?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="Sistema" table="SAC.ESACSISTEMA">
<composite-id name="sistemaPK" class="SistemaPK">
<key-property name="cdProjeto" type="int"/>
<key-property name="cdSistema" type="int" />
</composite-id>
<property name="nmSistema" type="string"/>
<property name="sgSistema" type="string"/>
<property name="deHomedigital" type="string"/>
</class>
</hibernate-mapping>

Find:

session.find("from Projeto p where p.projetoPK.cdProjeto=1");

Generated sqls by Hibernate:

Hibernate: select projeto0_.cdProjeto as cdProjeto, projeto0_.nmProjeto as nmProjeto, projeto0_.sgProjeto as sgProjeto, projeto0_.deBasevss as deBasevss from SAC.ESACPROJETO projeto0_ where (projeto0_.cdProjeto=1 )

Hibernate: select esacsist0_.cdProjeto as cdProjeto__, esacsist0_.cdSistema as cdSistema__, esacsist0_.cdProjeto as cdProjeto, esacsist0_.cdSistema as cdSistema, esacsist0_.nmSistema as nmSistema, esacsist0_.sgSistema as sgSistema, esacsist0_.deHomedigital as deHomedi5_ from SAC.ESACSISTEMA esacsist0_ where esacsist0_.cdProjeto=?

Hibernate: select sistema0_.cdProjeto as cdProjeto, sistema0_.cdSistema as cdSistema, sistema0_.nmSistema as nmSistema, sistema0_.sgSistema as sgSistema, sistema0_.deHomedigital as deHomedi5_ from SAC.ESACSISTEMA sistema0_ where sistema0_.cdProjeto=? and sistema0_.cdSistema=?

Hibernate: select sistema0_.cdProjeto as cdProjeto, sistema0_.cdSistema as cdSistema, sistema0_.nmSistema as nmSistema, sistema0_.sgSistema as sgSistema, sistema0_.deHomedigital as deHomedi5_ from SAC.ESACSISTEMA sistema0_ where sistema0_.cdProjeto=? and sistema0_.cdSistema=?

Hibernate: select sistema0_.cdProjeto as cdProjeto, sistema0_.cdSistema as cdSistema, sistema0_.nmSistema as nmSistema, sistema0_.sgSistema as sgSistema, sistema0_.deHomedigital as deHomedi5_ from SAC.ESACSISTEMA sistema0_ where sistema0_.cdProjeto=? and sistema0_.cdSistema=?

Hibernate: select sistema0_.cdProjeto as cdProjeto, sistema0_.cdSistema as cdSistema, sistema0_.nmSistema as nmSistema, sistema0_.sgSistema as sgSistema, sistema0_.deHomedigital as deHomedi5_ from SAC.ESACSISTEMA sistema0_ where sistema0_.cdProjeto=? and sistema0_.cdSistema=?

Hibernate: select sistema0_.cdProjeto as cdProjeto, sistema0_.cdSistema as cdSistema, sistema0_.nmSistema as nmSistema, sistema0_.sgSistema as sgSistema, sistema0_.deHomedigital as deHomedi5_ from SAC.ESACSISTEMA sistema0_ where sistema0_.cdProjeto=? and sistema0_.cdSistema=?

Hibernate: select sistema0_.cdProjeto as cdProjeto, sistema0_.cdSistema as cdSistema, sistema0_.nmSistema as nmSistema, sistema0_.sgSistema as sgSistema, sistema0_.deHomedigital as deHomedi5_ from SAC.ESACSISTEMA sistema0_ where sistema0_.cdProjeto=? and sistema0_.cdSistema=?

Hibernate: select sistema0_.cdProjeto as cdProjeto, sistema0_.cdSistema as cdSistema, sistema0_.nmSistema as nmSistema, sistema0_.sgSistema as sgSistema, sistema0_.deHomedigital as deHomedi5_ from SAC.ESACSISTEMA sistema0_ where sistema0_.cdProjeto=? and sistema0_.cdSistema=?

Hibernate: select sistema0_.cdProjeto as cdProjeto, sistema0_.cdSistema as cdSistema, sistema0_.nmSistema as nmSistema, sistema0_.sgSistema as sgSistema, sistema0_.deHomedigital as deHomedi5_ from SAC.ESACSISTEMA sistema0_ where sistema0_.cdProjeto=? and sistema0_.cdSistema=?

Result:

(Projeto:1;PD;Sistemas:[(Sistema:1;1;SAC), (Sistema:1;2;SP4/SIA), (Sistema:1;3;SPW), (Sistema:1;4;Fon

set hibernate.use_outer_join to true


will do the job,a nd this one

too

Thanks for reply.

It

I don't believe you. I think it tries to look for a field called cdProtejo__



Perhaps you JDBC driver is unable to handle having the same column aliased to two different aliases. Since it looks like you are using an unsupported driver, this would not surprise me.

Gavin,

Thanks for reply. You are right. My apology. I was using a SUN JDBC driver. I changed to DB2 driver and the query worked fine. The generated SQL was:



But Hibernate still was using more than one SQL to make this job. Beside this the result is not what I was expecting. It

So exactly which association is not being outerjoined?

The highlighted association. I would like to load the "Projeto" with "cdProjeto=1" and all "Sistemas" associated with this "Projeto".

<?xml version='1.0' encoding='windows-1252'?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="Projeto" table="SAC.ESACPROJETO">
<composite-id name="projetoPK" class="ProjetoPK">
<key-property name="cdProjeto" type="int"/>
</composite-id>
<property name="nmProjeto" type="string"/>
<property name="sgProjeto" type="string"/>
<property name="deBasevss" type="string"/>
<bag name="sistemas">
<key column ="cdProjeto" />
<one-to-many class="Sistema"/>
</bag> </class>
</hibernate-mapping>

<?xml version='1.0' encoding='windows-1252'?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="Sistema" table="SAC.ESACSISTEMA">
<composite-id name="sistemaPK" class="SistemaPK">
<key-property name="cdProjeto" type="int"/>
<key-property name="cdSistema" type="int" />
</composite-id>
<property name="nmSistema" type="string"/>
<property name="sgSistema" type="string"/>
<property name="deHomedigital" type="string"/>
</class>
</hibernate-mapping>

Just like Emmanuel said:



Will fetch a Projecto and all its Sistemas in a single SQL select.

I just do this and Hibernate (2.0.3) generated the folow SQLs:



And the result was eight rows:

[code]1. (Projeto:1;PD;[(Sistema:1;1;SAC)])
2. (Projeto:1;PD;[(Sistema:1;2;SP4/SIA)])
3. (Projeto:1;PD;[(Sistema:1;3;SPW)])
4. (Projeto:1;PD;[(Sistema:1;4;Fon

So, you are trying to tell me that Hibernate is putting each element of the collection in a different instance of Projecto???

You obviously have something very screwy somewhere.

I suggest you take one of the Hibernate examples from the doco, get that working, then try and adapt it to your usecase. 'Cos i can't quite see where your problem is. It might be anywhere from broken mapping to broken test data.

Gavin,

I will follow your suggestion.

Thanks!

Hello,

I changed my mappings to don

This is expected. We don't distinctify the result set. You can easily do it yourself by using




or, in this case, simply by calling query.uniqueResult().

Gavin,

It worked fine.

Why it didn't work as expected using "composite-id"?

Does Hibernate have any limitations using "composite-id"?

The example using "composite-id" shouldn't work exactly like the one using "id"?

Thanks!

No. Hibernate has no problems.

I think you forgot to implement equals()/hashCode() on your composite-id class, as is clearly required by the documentation.