Hi,

I need some help to write my HQL query. I use a criteria to execute a dynamic query. I'd like to find all users who belong to a GEH. I have the following code:



The problem is phyGeh.id, because phyGeh is not an attribute of my bean UsrUser. The SQL code to access to usr.ID on the basis of geh.ID is:

select usr.ID
from UsrUser usr, PhySiteProduction site, PhyCentrale cent, PhyGeh geh where usr.IDSITE = site.ID
and site.ID = cent.IDSITEPRODUCTION
and cent.IDGEH = geh.ID
and geh.ID = '3'

How could I reach usr.ID on the basis of geh.ID in HQL?

Hibernate version: 3.0
Database version: Oracle 9i

Thanks for your help.

This is the mapping files of my different pojos:


PhyCentrale.hbm.xml

<hibernate-mapping>
<class name="com.steria.varese.model.physique.PhyCentrale" table="PHY_CENTRALE">
<id name="id" type="integer">
<column name="ID" precision="6" scale="0" />
<generator class="sequence">
<param name="sequence">seq_centrale</param>
</generator>
</id>
<many-to-one name="phySiteProduction" class="com.steria.varese.model.physique.PhySiteProduction" fetch="select">
<column name="IDSITEPRODUCTION" precision="6" scale="0" not-null="true" />
</many-to-one>
<many-to-one name="refModeProduction" class="com.steria.varese.model.references.RefModeProduction" fetch="select">
<column name="IDMODEPRODUCTION" precision="1" scale="0" not-null="true" />
</many-to-one>
<many-to-one name="phyUp" class="com.steria.varese.model.physique.PhyUp" fetch="select">
<column name="IDUP" precision="6" scale="0" />
</many-to-one>
<many-to-one name="phyUrse" class="com.steria.varese.model.physique.PhyUrse" fetch="select">
<column name="IDURSE" precision="6" scale="0" not-null="true" />
</many-to-one>
<many-to-one name="phyGeh" class="com.steria.varese.model.physique.PhyGeh" fetch="select">
<column name="IDGEH" precision="6" scale="0" />
</many-to-one>
</hibernate-mapping>

PhyGEH.hbm.xml

<hibernate-mapping>
<class name="com.steria.varese.model.physique.PhyGeh" table="PHY_GEH">
<id name="id" type="integer">
<column name="ID" precision="6" scale="0" />
<generator class="sequence">
<param name="sequence">seq_geh</param>
</generator>
</id>
<many-to-one name="phyUp" class="com.steria.varese.model.physique.PhyUp" fetch="select">
<column name="IDUP" precision="6" scale="0" not-null="true" />
</many-to-one>
<property name="code" type="string">
<column name="CODE" length="5" not-null="true" unique="true" />
</property>
<property name="libelle" type="string">
<column name="LIBELLE" length="30" not-null="true" />
</property>
<bag name="usrContacts" inverse="true">
<key>
<column name="IDGEH" precision="6" scale="0" />
</key>
<one-to-many class="com.steria.varese.model.gestionUtilisateurs.UsrContact" />
</bag>
<bag name="phyCentrales" inverse="true">
<key>
<column name="IDGEH" precision="6" scale="0" />
</key>
<one-to-many class="com.steria.varese.model.physique.PhyCentrale" />
</bag>
</class>
</hibernate-mapping>

PhySiteProduction.hbm.xml

<hibernate-mapping>
<class name="com.steria.varese.model.physique.PhySiteProduction" table="PHY_SITE_PRODUCTION">
<id name="id" type="integer">
<column name="ID" precision="6" scale="0" />
<generator class="sequence">
<param name="sequence">seq_siteprod</param>
</generator>
</id>
<property name="code" type="string">
<column name="CODE" length="5" not-null="true" unique="true" />
</property>
<property name="libelle" type="string">
<column name="LIBELLE" length="30" not-null="true" />
</property>
<bag name="usrContacts" inverse="true">
<key>
<column name="IDSITE" precision="6" scale="0" />
</key>
<one-to-many class="com.steria.varese.model.gestionUtilisateurs.UsrContact" />
</bag>
<bag name="phyCentrales" inverse="true">
<key>
<column name="IDSITEPRODUCTION" precision="6" scale="0" not-null="true" />
</key>
<one-to-many class="com.steria.varese.model.physique.PhyCentrale" />
</bag>
</hibernate-mapping>

UsrUtilisateur.hbm.xml

<hibernate-mapping>
<class name="com.steria.varese.model.gestionUtilisateurs.UsrUtilisateur" table="USR_UTILISATEUR">
<id name="id" type="integer">
<column name="ID" precision="6" scale="0" not-null="true" />
<generator class="assigned" />
</id>
<many-to-one name="refModeProduction" class="com.steria.varese.model.references.RefModeProduction" fetch="select">
<column name="IDMODEPRODUCTION" precision="1" scale="0" />
</many-to-one>
<many-to-one name="usrProfil" class="com.steria.varese.model.gestionUtilisateurs.UsrProfil">
<column name="IDPROFIL" precision="2" scale="0" not-null="true" />
</many-to-one>
<many-to-one name="phySiteProduction" class="com.steria.varese.model.physique.PhySiteProduction" fetch="select">
<column name="IDSITE" precision="6" scale="0" />
</many-to-one>
</hibernate-mapping>

you need anything like:

Criteria criteria = getSession().createCriteria(UsrUser.class).createCriteria("phySiteProduction","site").createCriteria("phyCentrales","centrales").createCriteria("phyGeh","geh").add( Restrictions.eq("geh.idGeh", "3") )