Hi !
I'm quite new to hibernate and my problem is that when I use a request with not-simple hql criteria, using implicit joins, hibernate generates a sql request with too many joins, which are useless and redundants.
I have 3 tables which mappings, generated by hibernate tools in Eclipse, are the following ;
Code:
<class name="vtpro.model.OrgPersonnel" table="ORG_PERSONNEL">
<id name="idPersonnel" type="integer">
<column name="ID_PERSONNEL" precision="22" scale="0" />
<generator class="assigned" />
</id>
<property name="compteSesame" type="string">
<column name="COMPTE_SESAME" length="100" />
</property>
<set name="orgAffectPersonnels" inverse="true">
<key>
<column name="ID_PERSONNEL" precision="22" scale="0" not-null="true" />
</key>
<one-to-many class="vtpro.model.OrgAffectPersonnel" />
</set>
</class>
Code:
<class name="vtpro.model.OrgPoste" table="ORG_POSTE">
<id name="idPoste" type="integer">
<column name="ID_POSTE" precision="22" scale="0" />
<generator class="assigned" />
</id>
<property name="descPoste" type="string">
<column name="DESC_POSTE" length="50" />
</property>
<property name="posteActif" type="string">
<column name="POSTE_ACTIF" length="1" />
</property>
<property name="dateDebutValidite" type="date">
<column name="DATE_DEBUT_VALIDITE" length="7" />
</property>
<property name="dateFinValidite" type="date">
<column name="DATE_FIN_VALIDITE" length="7" />
</property>
<set name="orgAffectPersonnels" inverse="true">
<key>
<column name="ID_POSTE" precision="22" scale="0" not-null="true" />
</key>
<one-to-many class="vtpro.model.OrgAffectPersonnel" />
</set>
</class>
Code:
<class name="vtpro.model.OrgAffectPersonnel" table="ORG_AFFECT_PERSONNEL">
<id name="idAffectPersonnel" type="integer">
<column name="ID_AFFECT_PERSONNEL" precision="22" scale="0" />
<generator class="assigned" />
</id>
<many-to-one name="orgPoste" class="vtpro.model.OrgPoste" fetch="select">
<column name="ID_POSTE" precision="22" scale="0" not-null="true" />
</many-to-one>
<many-to-one name="orgPersonnel" class="vtpro.model.OrgPersonnel" fetch="select">
<column name="ID_PERSONNEL" precision="22" scale="0" not-null="true" />
</many-to-one>
<property name="dateDebutOccupation" type="date">
<column name="DATE_DEBUT_OCCUPATION" length="7" not-null="true" />
</property>
<property name="dateFinOccupation" type="date">
<column name="DATE_FIN_OCCUPATION" length="7" />
</property>
</class>
My hql request is :
Code:
from OrgPersonnel as p
where p.compteSesame = 'toto@toto.fr'
and p.orgAffectPersonnels.dateDebutOccupation <= current_date()
and (p.orgAffectPersonnels.dateFinOccupation IS NULL
or p.orgAffectPersonnels.dateFinOccupation >= current_date())
And it generates :
Code:
select
orgpersonn0_.ID_PERSONNEL as ID1_4_,
orgpersonn0_.COMPTE_SESAME as COMPTE2_4_,
from
VTPRO.ORG_PERSONNEL orgpersonn0_,
VTPRO.ORG_AFFECT_PERSONNEL orgaffectp1_,
VTPRO.ORG_AFFECT_PERSONNEL orgaffectp2_,
VTPRO.ORG_AFFECT_PERSONNEL orgaffectp3_
where
orgpersonn0_.ID_PERSONNEL=orgaffectp3_.ID_PERSONNEL
and orgpersonn0_.ID_PERSONNEL=orgaffectp2_.ID_PERSONNEL
and orgpersonn0_.ID_PERSONNEL=orgaffectp1_.ID_PERSONNEL
and orgpersonn0_.COMPTE_SESAME='toto@toto.fr'
and orgaffectp1_.DATE_DEBUT_OCCUPATION<=current_date
and (
orgaffectp2_.DATE_FIN_OCCUPATION is null
or orgaffectp3_.DATE_FIN_OCCUPATION>=current_date
)
As you can see, it generates 3 joins on ORG_AFFECT_PERSONNEL, while only one is enough.
So, I must have a problem somewhere, in my request or in my mapping. But I can't see what it is.
--------------------------------------------------------------------
Moreover, I have to add some criteria on ORG_POSTE fields :
Code:
and p.orgAffectPersonnels.orgPoste.posteActif='1'
AND p.orgAffectPersonnels.orgPoste.dateDebutValidite >= current_date()
AND (p.orgAffectPersonnels.orgPoste.dateFinValidite IS NULL
OR p.orgAffectPersonnels.orgPoste.dateFinValidite <= current_date()))
But when I test the full request in HQL Editor (still in Eclipse), I get :
Code:
"org.hibernate.QueryException: could not resolve property: posteActif of: vtpro.model.OrgAffectPersonnel"
It seems that it doesn't understand what I want by typing: p.orgAffectPersonnels.orgPoste.posteActif
So I probably have a problem in my hql syntax but I don't really understand how to solve it. The documentation didn't help me as the example are simpler (or I didn't understand it ^^).
I suppose I should write something to be able to read an element that belongs to a set (p.orgAffectPersonnels). But I didn't find what.
Thanks !
Login
FAQ
Search