Composite key behaviour

Philippe Chal · **Joined:** Fri Sep 12, 2003 5:13 am **Posts:** 22

Hello,

I'm using Hibernate with composite keys (in order to map an existing schema).

I've the 3 following tables :

Code:

create table ST_Produit (
   noProd INT not null,
   libelle VARCHAR(32) null,
   primary key (noProd)
)

Code:

create table ST_Lot (
   noProd INT not null,
   noLot INT not null,
   idExt VARCHAR(64) null,
   primary key (noProd, noLot)
)

Code:

create table ST_Piece (
   noProd INT not null,
   noLot INT not null,
   noPiece INT not null,
   idExt VARCHAR(16) null,
   primary key (noProd, noLot, noPiece)
)

and the following mappings :

Code:

<class name="fr.infologic.stocks.modele.Produit" table="ST_Produit" proxy="fr.infologic.stocks.modele.Produit">
   <id column="noProd" name="noProd">
      <generator class="assigned"/>
   </id>

   <property name="libelle" type="string">
      <column name="libelle" length="32"/>
   </property>
</class>

Code:

<class name="fr.infologic.stocks.modele.Lot" table="ST_Lot" proxy="fr.infologic.stocks.modele.Lot">
   <composite-id>
      <key-many-to-one name="prod" column="noProd" class="fr.infologic.stocks.modele.Produit"/>
      <key-property name="noLot" column="noLot"/>
   </composite-id>

   <property name="idExt" type="string">
      <column name="idExt" length="64"/>
   </property>
</class>

Code:

<class name="fr.infologic.stocks.modele.Piece" table="ST_Piece" proxy="fr.infologic.stocks.modele.Piece">
   <composite-id>
      <key-many-to-one name="lot">
         <column name="noProd"/>
         <column name="noLot"/>
      </key-many-to-one>
      <key-property name="noPiece" column="noPiece"/>
   </composite-id>

   <property name="idExt" type="string">
      <column name="idExt" length="16"/>
   </property>
</class>

When I do the following query :

Code:

List pieces = session.find(
            "from fr.infologic.stocks.modele.Piece as piece " +
            "where piece.lot.prod = ?",
            new Integer(64),
            Hibernate.INTEGER
);

the generated SQL is :

Code:

select piece0_.noProd as noProd, piece0_.noLot as noLot, piece0_.noPiece as noPiece, piece0_.idExt as idExt 
from ST_Piece piece0_, ST_Lot lot1_ 
where (lot1_.noProd=?  and piece0_.noProd=lot1_.noProd and piece0_.noLot=lot1_.noLot)

where I expected :

Code:

select piece0_.noProd as noProd, piece0_.noLot as noLot, piece0_.noPiece as noPiece, piece0_.idExt as idExt 
from ST_Piece piece0_ 
where piece0_.noProd=?

as far as noProd is part of the primary key of ST_Piece table.

My question is : how to manage to have the previous query on Piece class without having a join.

Thanks in advance and sorry for the long message.

Philippe[/code]

gavin · **Posted:** Fri Oct 31, 2003 12:01 pm

Ah good question. This is a fairly obscure area.

Try:

Code:

where piece.lot.id.prod = ?

I think this will work.

Philippe Chal · **Joined:** Fri Sep 12, 2003 5:13 am **Posts:** 22

gavin wrote:

Ah good question. This is a fairly obscure area.

Try:

Code:

where piece.lot.id.prod = ?

I think this will work.

And you know what ? It works !

Thanks for the quick answer.

Philippe

emmanuel · **Posted:** Fri Oct 31, 2003 12:34 pm

OK this is stupid naming but what if he had a mapped property with name 'id' on Lot class ?

Philippe Chal · **Joined:** Fri Sep 12, 2003 5:13 am **Posts:** 22

gavin wrote:

Ah good question. This is a fairly obscure area.

Try:

Code:

where piece.lot.id.prod = ?

I think this will work.

Unfortunately, it doesn't seem too be be "recursive"... Imagine I've one more level (and I have), and I try something like this :

Code:

"where mouv.piece.id.lot.id.prod = ?"

I've got this error :

Code:

could not resolve property type: piece.id.lot.id [from fr.infologic.stocks.modele.Mouv as mouv where mouv.piece.id.lot.id.prod = ?]

Philippe

gavin · **Posted:** Fri Oct 31, 2003 8:08 pm

Like I said, its obscure. If you would use composite id classes instead of "embedded" composite ids, as recommended by the documentation, it all works much more nicely.

Why don't you submit your example to JIRA, for me to integrate into the Hibernate test suite, and I will then have a play and see if anything should be improved.

Philippe Chal · **Joined:** Fri Sep 12, 2003 5:13 am **Posts:** 22

gavin wrote:

Like I said, its obscure. If you would use composite id classes instead of "embedded" composite ids, as recommended by the documentation, it all works much more nicely.

So, I've made a try with composite id classes. I now have the following mapping :

Code:

   <class name="fr.infologic.stocks.modele.Produit" table="ST_Produit" proxy="fr.infologic.stocks.modele.Produit" >
         <id column="noProd" name="noProd"><generator class="assigned"/></id>  
       <property name="libelle" type="string">
         <column name="libelle" 
               length="32"/>
      </property>      
   </class>

Code:

<class name="fr.infologic.stocks.modele.Lot" table="ST_Lot" proxy="fr.infologic.stocks.modele.Lot" >
        <composite-id name="lotPK" class="fr.infologic.stocks.modele.LotPK">
            <key-many-to-one name="prod" column="noProd" class="fr.infologic.stocks.modele.Produit"/>
            <key-property name="noLot" column="noLot"/>
        </composite-id>
  
       <property name="idExt" type="string">
         <column name="idExt" 
               length="64"/>
      </property>    
   </class>

Code:

   <class name="fr.infologic.stocks.modele.Piece" table="ST_Piece" proxy="fr.infologic.stocks.modele.Piece" >
        <composite-id  name="piecePK" class="fr.infologic.stocks.modele.PiecePK">>
     <key-many-to-one name="lot">
       <column name="noProd" column="noProd"
               length="9"/>
      <column name="noLot" column="noLot"
               length="9"/>
      </key-many-to-one>
            <key-property name="noPiece" column="noPiece"/>
        </composite-id>
  
       <property name="idExt" type="string">
         <column name="idExt" 
               length="16"/>
      </property>
   </class>

Code:

   <class name="fr.infologic.stocks.modele.Mouv" table="ST_Mouv" proxy="fr.infologic.stocks.modele.Mouv" >
         <id column="noMouv" name="noMouv"><generator class="assigned"/></id>
          <many-to-one name="piece" class="fr.infologic.stocks.modele.Piece" outer-join="false">
          <column name="noProd"/>
          <column name="noLot"/>
          <column name="noPiece"/>
           </many-to-one>      
   </class>

Note that in the last table, the reference to "Piece" is not part of the key.

When I try the following query :

Code:

List mouvs = session.find(
   "from fr.infologic.stocks.modele.Mouv as mouv " +
   "where mouv.piece.piecePK.lot.lotPK.prod = ? ",
      new Integer(149),
      Hibernate.INTEGER
);

I've the following error :

Code:

 net.sf.hibernate.QueryException: could not resolve property type: piece.id.lot.id [from fr.infologic.stocks.modele.Mouv as mouv where mouv.piece.piecePK.lot.lotPK.prod = ? ]

which strangely looks like what I had before trying composite id classes...

Do I do something wrong with this particular query ?

Thanks in advance

Philippe

gavin · **Posted:** Tue Nov 04, 2003 8:33 pm

My Bad. This only works in Hibernate 2.1.