Hibernate version: 3.1
Name and version of the database you are using: Oracle 9i
Hi, we have a problem with Hibernate not building a correct query on a ternary relationship.
We have
An UtilisateurVo object mapped to a UTILISATEUR table
A SousAgenceVo object mapped to a SOUSAGENCE table
A component named InfoEmploye that we use to link the preceding objects through a map (component contains 4 fields : geographique, par_defaut, production, responsable)
A map available on UtilisateurVo, named mapInfosEmploy whose key (in java terms) is the SousAgenceVo and whose value (in java terms) is the component : utilisateur.mapInfosEmploye[sousAgence] returns an infoEmploye class
Mapping documents:
UtilisateurVo
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="fr.mdpa.referentiel.metier.utilisateur.donnees">
<!-- Mapping pour Utilisateur -->
<class name="UtilisateurVo" table="utilisateur">
<composite-id name="pk">
<key-many-to-one name="societe" class="fr.mdpa.referentiel.metier.societe.donnees.SocieteVo" column="uti_societe"/>
<key-property name="codePersonne" column="uti_code_personne" type="integer"/>
</composite-id>
<map name="mapInfosEmploye" table="travail_dans_sous_agence" lazy="true">
<key>
<column name="tsag_societe_uti"/>
<column name="tsag_code_personne"/>
</key>
<index-many-to-many class="fr.mdpa.referentiel.metier.agence.donnees.SousAgenceVo">
<column name="tsag_societe"/>
<column name="tsag_agt"/>
<column name="tsag_s_agt"/>
</index-many-to-many>
<composite-element class="fr.mdpa.referentiel.metier.agence.donnees.InfosEmploye">
<property name="geographique" column="tsag_geographique" type="string"/>
<property name="responsable" column="tsag_responsable" type="string"/>
<property name="production" column="tsag_production" type="string"/>
<property name="parDefaut" column="tsag_par_defaut" type="string"/>
</composite-element>
</map>
...
</class>
</hibernate-mapping>
Code:
<class name="SousAgenceVo" table="sous_agence">
<composite-id name="pk">
<key-many-to-one name="agenceAdministrative" class="AgenceAdministrativeVo">
<column name="sag_societe"/>
<column name="sag_agt"/>
</key-many-to-one>
<key-property name="codeSousAgence" column="sag_s_agt" type="string"/>
</composite-id>
<map name="mapInfosEmploye" table="travail_dans_sous_agence" lazy="true">
<key>
<column name="tsag_societe"/>
<column name="tsag_agt"/>
<column name="tsag_s_agt"/>
</key>
<index-many-to-many class="fr.mdpa.referentiel.metier.utilisateur.donnees.UtilisateurVo">
<column name="tsag_societe_uti"/>
<column name="tsag_code_personne"/>
</index-many-to-many>
<composite-element class="fr.mdpa.referentiel.metier.agence.donnees.InfosEmploye">
<property name="geographique" column="tsag_geographique" type="string"/>
<property name="responsable" column="tsag_responsable" type="string"/>
<property name="production" column="tsag_production" type="string"/>
<property name="parDefaut" column="tsag_par_defaut" type="string"/>
</composite-element>
</map>
...
</class>
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="fr.mdpa.referentiel.metier.agence.donnees" default-cascade="lock">
<!-- Mapping pour Agence administrative -->
<class name="AgenceAdministrativeVo" table="agence_administrative">
<composite-id name="pk">
<key-many-to-one name="societe" class="fr.mdpa.referentiel.metier.societe.donnees.SocieteVo" column="ag_societe"/>
<key-property name="codeAgence" column="ag_agt" type="string"/>
</composite-id>
...
</class>
</hibernate-mapping>
SocieteVoCode:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="fr.mdpa.referentiel.metier.societe.donnees">
<!-- Societe -->
<class name="SocieteVo" table="societe">
<composite-id name="pk">
<key-property name="codeSociete" column="soc_societe" type="string"/>
</composite-id>
<property name="adhesionIda" column="soc_adhesion_ida" type="string"/>
<property name="codeCca" column="soc_code_cca" type="string"/>
<property name="libelle" column="soc_libelle" type="string"/>
<property name="observation" column="soc_observation" type="string"/>
<property name="prefixeLibelle" column="soc_prefixe_libelle" type="string"/>
<property name="telephone" column="soc_telephone" type="string"/>
<component name="adresse">
<property name="rue1" column="soc_rue1" type="string"/>
<property name="rue2" column="soc_rue2" type="string"/>
<property name="rue3" column="soc_rue3" type="string"/>
<property name="codePostal" column="soc_code_postal" type="string"/>
<property name="ville" column="soc_ville" type="string"/>
<property name="cedex" column="soc_cedex" type="string"/>
<property name="pays" column="soc_pays" type="string"/>
</component>
...
</class>
</hibernate-mapping>
Code:
package fr.mdpa.referentiel.metier.agence.donnees;
import fr.mdpa.commun.metier.donnees.ObjetVo;
import java.util.*;
public class InfosEmploye {
private String geographique;
private String parDefaut;
private String production;
private String responsable;
public String getGeographique() {
return geographique;
}
public String getParDefaut() {
return parDefaut;
}
public String getProduction() {
return production;
}
public String getResponsable() {
return responsable;
}
public void setResponsable(String responsable) {
this.responsable = responsable;
}
public void setProduction(String production) {
this.production = production;
}
public void setParDefaut(String parDefaut) {
this.parDefaut = parDefaut;
}
public void setGeographique(String geographique) {
this.geographique = geographique;
}
}
InfoEmployeCode:
public class UtilisateurVo extends ObjetVo {
private UtilisateurPk pk;
private Map mapInfosEmploye;
}
public Map getMapInfosEmploye() {
return mapInfosEmploye;
}
public void setMapInfosEmploye(Map mapInfosEmploye) {
this.mapInfosEmploye = mapInfosEmploye;
}
...
}
UtilisateurPkCode:
package fr.mdpa.referentiel.metier.utilisateur.donnees;
import fr.mdpa.commun.metier.donnees.ObjetPk;
import java.util.*;
import fr.mdpa.referentiel.metier.agence.donnees.*;
import fr.mdpa.referentiel.metier.societe.donnees.*;
public class UtilisateurPk extends ObjetPk {
private SocieteVo societe = new SocieteVo();
private Integer codePersonne;
public UtilisateurPk(){}
public UtilisateurPk(SocieteVo societe, Integer codePersonne) {
this.societe = societe;
this.codePersonne = codePersonne;
}
public Integer getCodePersonne() {
return codePersonne;
}
public void setCodePersonne(Integer codePersonne) {
this.codePersonne = codePersonne;
}
public boolean equals(Object p_utilisateur) {
try {
UtilisateurPk l_utilisateur = (UtilisateurPk) p_utilisateur;
if (this.societe.getPk().equals(l_utilisateur.societe.getPk()) &&
this.codePersonne.equals(l_utilisateur.codePersonne)) {
return true;
}
else
return false;
}
catch(Exception ex) {return false;}
}
public int hashCode() {
return societe.getPk().hashCode() * codePersonne.hashCode();
}
public SocieteVo getSociete() {
return societe;
}
public void setSociete(SocieteVo societe) {
this.societe = societe;
}
}
Database columns:Code:
CREATE TABLE UTILISATEUR (
UTI_SOCIETE CHAR (4) NOT NULL,
UTI_CODE_PERSONNE NUMBER (11) NOT NULL,
UTI_NOM VARCHAR2 (50),
CONSTRAINT PK_UTILISATEUR
PRIMARY KEY ( UTI_SOCIETE, UTI_CODE_PERSONNE ) ) ;
ALTER TABLE UTILISATEUR ADD CONSTRAINT FK_UTILISATEUR_SOCIETE
FOREIGN KEY (UTI_SOCIETE)
REFERENCES MUTP.SOCIETE (SOC_SOCIETE) ;
Code:
CREATE TABLE TRAVAIL_DANS_SOUS_AGENCE (
TSAG_SOCIETE CHAR (4) NOT NULL,
TSAG_AGT CHAR (4) NOT NULL,
TSAG_S_AGT CHAR (1) NOT NULL,
TSAG_CODE_PERSONNE NUMBER (11) NOT NULL,
TSAG_GEOGRAPHIQUE CHAR (1),
TSAG_RESPONSABLE CHAR (1),
TSAG_PRODUCTION CHAR (1),
TSAG_PAR_DEFAUT CHAR (1),
TSAG_SOCIETE_UTI CHAR (4) DEFAULT 147 NOT NULL,
CONSTRAINT PK_TRAVAIL_DANS_SOUS_AGENCE
PRIMARY KEY ( TSAG_SOCIETE, TSAG_AGT, TSAG_S_AGT, TSAG_CODE_PERSONNE ) ) ;
Code:
CREATE TABLE SOUS_AGENCE (
SAG_SOCIETE CHAR (4) NOT NULL,
SAG_AGT CHAR (4) NOT NULL,
SAG_S_AGT CHAR (1) NOT NULL,
SAG_LIBELLE_COM VARCHAR2 (50),
SAG_LIBELLE_INTERNE VARCHAR2 (50), sa
PRIMARY KEY ( SAG_SOCIETE, SAG_AGT, SAG_S_AGT ) ) ;
What we're trying to achieve: Query for all utilisateur linked to a gi ven sousAgence and retrieve only some UtilisateurVo fields and the related InfoEmploye (hence as scalars) for that sousAgence for that Utilisateur
The HQL we wrote:select
distinct new SousAgencePersonnelPo( utilisateur.pk.societe.pk.codeSociete, utilisateur.pk.codePersonne, utilisateur.nom, mapInfo.responsable, mapInfo.parDefaut, mapInfo.production, mapInfo.geographique)
from
UtilisateurVo utilisateur, SousAgenceVo sousAgence
join utilisateur.mapInfosEmploye mapInfo
where index(mapInfo)=sousAgence
and sousAgence.pk.agenceAdministrative.pk.codeAgence like '5440%'
and sousAgence.pk.codeSousAgence like '0%'
The generated SQL :SELECT
DISTINCT utilisateu0_.uti_societe AS col_0_0_,
utilisateu0_.uti_code_personne AS col_1_0_,
utilisateu0_.uti_nom AS col_2_0_,
utilisateu0_.uti_prenom AS col_3_0_,
mapinfosem2_.tsag_responsable AS col_4_0_,
mapinfosem2_.tsag_par_defaut AS col_5_0_,
mapinfosem2_.tsag_production AS col_6_0_,
mapinfosem2_.tsag_geographique AS col_7_0_
FROM
UTILISATEUR utilisateu0_,
TRAVAIL_DANS_SOUS_AGENCE mapinfosem2_,
SOUS_AGENCE sousagence1_
WHERE
utilisateu0_.uti_societe=mapinfosem2_.tsag_societe_uti AND
utilisateu0_.uti_code_personne=mapinfosem2_.tsag_code_personne AND
mapinfosem2_.tsag_societe=(sousagence1_.sag_societe,sousagence1_.sag_agt,sousagence1_.sag_s_agt) AND
(sousagence1_.sag_agt LIKE '5440%') AND
(sousagence1_.sag_s_agt LIKE '0%')
Analysis: The SQL generated is incorrect because Hibernate has added the constraint
Code:
mapinfosem2_.tsag_societe=(sousagence1_.sag_societe,sousagence1_.sag_agt,sousagence1_.sag_s_agt)
which doesn't make any sense because it should be
Code:
mapinfosem2_.tsag_societe=sousagence1_.sag_societe AND
mapinfosem2_.tsag_agt=sousagence1_.sag_agt AND
mapinfosem2_.tsag_s_agt=sousagence1_.sag_s_agt AND
So it looks like hibernate is mixed up on the index for mapinfosem2_. I am starting to suspect this is linked to the primary keys who are composite and contain many-to-one relationships.
Any ideas as to why ?
Login
FAQ
Search