Very exotic O/R mapping

andreas_eriksson · **Posted:** Fri Nov 28, 2003 9:32 am

Hi all,

I'm trying to map a very exotic relationship. Please don't bother to change the db model in order to make it work. This is what I got to work with, end of discussion. *sorry*
I cannot either post the hbm.xml or any code. But ask me for info, I'll try to post what I can. I'm very sorry!

We got tables: A, B, BC, C, BCD, D

The relationship is:

Code:

A --* B --* BC *-- C
            |
            *
           BCD *-- D

A one-to-many B
B one-to-many BC
BC many-to-one C
BC one-to-many BCD
BCD many-to-one D

I've mapped all of these as classes. Each of the classes have its own hbm.xml file. And the relationships (everywhere) is mapped as bi-directional. Unsaved-value strategy is "null".

The problem is that I get a "cannot insert value NULL into column..." on table BCD. The object graph I'm trying to save is:

Code:

A1 --- B1 --- BC1 --- C1
    |              |
    +- B2 --- BC2 -+

Contiuned (problems drawing ASCII database relationships) from above:

Code:

BC1 --- BCD1 --- D1
              |
BC2 --- BCD2 -+

Each relationship object (BC and BCD) contains en extra field in addition to the foreign keys.

The SQL generated for BCD is wrong (due to my mapping, unsaved-value strategy or something else).

It should be:

Code:

insert into BCD (extraField, foreignKeyBC, foreignKeyD, primaryKeyBCD)

But the generated SQL is:

Code:

insert into BCD (extraField, foreignKeyD, primaryKeyBCD)

I can see from the debug messages that the BC is assigned a primary key and inserted before BCD is issued (that's fine).

I'm using UUIDGenerator as primary key for all classes.

Could anyone tell me how to proceed with my problem?

Kindest regards, Andreas

emmanuel · **Posted:** Fri Nov 28, 2003 9:54 am

No offense, but wo mapping file this is hard to find, there is no psychic guy outhere.

I think you have
1.
BC
<one-to-many class="BCD" inverse="true">
...

BCD
You don't have <many-to-one class="BC">

2. you use dynamic insert / update and you don't do
bcd.setBc(bc);

andreas_eriksson · **Posted:** Fri Nov 28, 2003 10:04 am

Thanks for the reply!

I'm sorry 'bout the hbm mapping files. I convert them with dummy properties (exept for the relationships) and post them A.S.A.P.

Cannot post them as is, just can't!

1. Yes, I have both.

2. If I use dynamic insert/update I don't need to set the relation? Or, are you saying I'm not doing it?

I am doing: bcd.addBc(bc);

Kind regards, Andreas

andreas_eriksson · **Posted:** Fri Nov 28, 2003 10:25 am

Here's the hbm mapping files!

A.hbm.xml

Code:

<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD 2.0//EN" 
   "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping default-cascade="all">
   <!-- A VALUE OBJECT -->

   <class
        name="A"
        table="A"
        dynamic-update="true"
        dynamic-insert="true"
     >
     
        <jcs-cache usage="read-write"/>
    
       <!-- PRIMARY KEY -->
       <id name="a_id" column="a_id" type="string">
          <generator class="UidGenerator"/>
       </id>
    
       <property
          name="a1"
          type="string"
          column="a1"
          not-null="true"
          unique="true"
       />
    
       // Some other props
    
       <!-- A to B -->
   <set name="Bs" inverse="true" cascade="all">
         <jcs-cache usage="read-write"/>
         <key column="a_id"/>
             <one-to-many class="B"/>
   </set>
    </class>
</hibernate-mapping>

B.hbm.xml

Code:

<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD 2.0//EN" 
   "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping default-cascade="all">
   
   <class
        name="B"
        table="B"
        dynamic-update="true"
        dynamic-insert="true"
   >
     
        <jcs-cache usage="read-write"/>
    
       <!-- PRIMARY KEY -->
       <id name="b_id" column="b_id" type="string">
          <generator class="UidGenerator"/>
       </id>
    
       <property
          name="b1"
          type="string"
          column="b1"
          not-null="true"
       />
    
       // Some other props
    
       <!-- B to A -->
       <many-to-one
          name="A"
          class="A"
          column="a_id"
          not-null="true"
          cascade="save-update"
       />
    
       <!-- B to BC -->
       <set name="BCs" inverse="true" cascade="all">
           <jcs-cache usage="read-write"/>
         <key column="b_id"/>
         <one-to-many class="BC"/>
      </set>
   </class>
</hibernate-mapping>

BC.hbm.xml

Code:

<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD 2.0//EN" 
   "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping default-cascade="all">

   <class
        name="BC"
        table="BC"
        dynamic-update="true"
        dynamic-insert="true"
     >
     
        <jcs-cache usage="read-write"/>
  
       <!-- PRIMARY KEY -->
       <id name="bc_id" column="bc_id" type="string">
          <generator class="UidGenerator"/>
       </id>
    
       <property
          name="bc1"
          type="string"
          column="bc1"
          not-null="true"
       />
    
       <!-- BC to B -->
       <many-to-one
          name="B"
          class="B"
          column="b_id"
          not-null="true"
          cascade="save-update"
       />
    
       <!-- BC to C -->
       <many-to-one
          name="C"
          class="C"
          column="c_id"
          not-null="true"
          cascade="all"
       />
    
       <!-- BC to BCD -->
       <set name="BCDs" inverse="true" cascade="all">
          <jcs-cache usage="read-write"/>
            <key column="bc_id"/>
            <one-to-many class="BCD"/>
       </set>
  </class>
</hibernate-mapping>

C.hbm.xml

Code:

<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD 2.0//EN" 
   "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping default-cascade="all">

   <class
        name="C"
        table="C"
        dynamic-update="true"
        dynamic-insert="true"
     >
     
        <jcs-cache usage="read-write"/>
  
       <!-- PRIMARY KEY -->
       <id name="c_id" column="c_id" type="string">
          <generator class="UidGenerator"/>
       </id>
    
       <property
          name="c1"
          type="string"
          column="c1"
          not-null="true"
       />

   // And some other props
    
       <!-- C to BC -->
       <set name="BCs" inverse="true" cascade="all">
           <jcs-cache usage="read-write"/>
            <key column="c_id"/>
            <one-to-many class="BC"/>
       </set>
   </class>
</hibernate-mapping>

BCD.hbm.xml

Code:

<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD 2.0//EN" 
   "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping default-cascade="all">

   <class
        name="BCD"
        table="BCD"
        dynamic-update="true"
        dynamic-insert="true"
     >
     
        <jcs-cache usage="read-write"/>
  
       <!-- PRIMARY KEY -->
       <id name="bcd_id" column="bcd_id" type="string">
          <generator class="UidGenerator"/>
       </id>
    
       <property
          name="bcd1"
          type="string"
          column="bcd1"
          not-null="true"
       />
    
       <!-- BCD to BC -->
       <many-to-one
          name="BC"
          class="BC"
          column="bc_id"
          not-null="true"
          cascade="save-update"
       />
    
       <!-- BCD to D -->
       <many-to-one
          name="D"
          class="D"
          column="d_id"
          not-null="true"
          cascade="all"
       />
    </class>
</hibernate-mapping>

D.hbm.xml

Code:

<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD 2.0//EN" 
   "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping default-cascade="all">

   <class
        name="D"
        table="D"
        dynamic-update="true"
        dynamic-insert="true"
     >
     
        <jcs-cache usage="read-write"/>
  
       <!-- PRIMARY KEY -->
       <id name="d_id" column="d_id" type="string">
          <generator class="UidGenerator"/>
       </id>
    
       <property
          name="d1"
          type="string"
          column="d1"
          not-null="true"
       />
   
      // Some other props
    
       <!-- D to BCD -->
       <set name="BCDs" inverse="true" cascade="all">
           <jcs-cache usage="read-write"/>
         <key column="d_id"/>
            <one-to-many class="BCD"/>
         </set>
   </class>
</hibernate-mapping>

I've tried with cascade="all" and unsaved-value with different strategies without any good result.

Hope it's useful!

Kind regards, Andreas

emmanuel · **Posted:** Fri Nov 28, 2003 10:26 am

andreas_eriksson wrote:

2. If I use dynamic insert/update I don't need to set the relation? Or, are you saying I'm not doing it?

I am doing: bcd.addBc(bc);

You need to set the relationship.
addBc is not a regular setter, must be setBc(Bc).

andreas_eriksson · **Posted:** Fri Nov 28, 2003 10:29 am

Sorry, I was not clear enough.

addBc(bc) calls setBc(bc), added just 'cause of semantics.

Regards, Andreas

andreas_eriksson · **Posted:** Fri Nov 28, 2003 11:28 am

I mapped the BC as a composite-element in B:

B.hbm.xml

Code:

<set name="BCs" table="BC">
    <jcs-cache usage="read-write"/>
    <key column="b_id"/>
    <composite-element class="BC">
        <property name="bc1"/>
        <many-to-one name="C" class="C" inverse="true"/>
        <many-to-one name="B" class="B" inverse="true"/>
    </composite-element>
</set>

I left the BC.hbm.xml as is. Didn't changed any relationships in it, 'cause if I did then it didn't work.

Now I can store the object graph, but not load it. Probably 'cause composite elements may not appear in queries(?)

Well, good try, not lucky enough! :-/

Regards, Andreas

emmanuel · **Posted:** Fri Nov 28, 2003 1:06 pm

andreas_eriksson wrote:

<many-to-one name="C" class="C" inverse="true"/>

Cannot inverse=true a many-to-one http://www.hibernate.org/Documentation/InsideExplanationOfInverseTrue

andreas_eriksson wrote:

I left the BC.hbm.xml as is. Didn't changed any relationships in it, 'cause if I did then it didn't work.

Why did you map it as composite element ? Does it work in classical mapping ?

andreas_eriksson wrote:

Now I can store the object graph, but not load it. Probably 'cause composite elements may not appear in queries(?)

True, this is doable in hibernate 2.1 (since beta 1).

andreas_eriksson · **Posted:** Fri Nov 28, 2003 1:35 pm

1. The inverse="true" is a typo! *sorry*

2. Typo again! :-/ I mapped the BC class as a composite-element in B.hbm.xml of experimental purpose to solve this problem.
I'd tried to remove the BC.hbm.xml from hibernate.cfg.xml, but it didn't work. I'd also tried to leave the BC.hbm.xml in hibernate.cfg.xml and remove the many-to-one definitions from BC.hbm.xml (since they been defined in the composite-element in B.hbm.xml), but then I got a 'insert into c_id cannot be null'. I'm not getting this... :-/

3. The code is running on Hibernate 2.0.3, maybe I should try Hibernate 2.1 ???

Kind regards, Andreas

aibrahim · **Joined:** Fri Sep 26, 2003 11:44 am **Posts:** 13

Hi,

Just a quick observation. From your naming scheme it appears what you really want is a many-to-many from B to C and ternary relation with B,C,D. Might it not make more sense to map the many-to-many from B to C using hibernate and let hibernate generate the intermediate table and deal with the cascades for that intermediate table. Also you could use a map or composite element for the ternary relation with B, C, D. From your mapping it appears you do have extra properties for the the intermediate tables, but I am not sure whether this is for examples sake or whether there is actually semantic meaning. Any case the proper mapping depends on what lifecycle and semantic meaning you want the BC and BCD tables to have.

Regards,

andreas_eriksson · **Posted:** Fri Nov 28, 2003 8:30 pm

Hello Ali,

You're completly right!

I'm currently trying to map the intermediate table (BC) as an composite-element with some result (see previous posts).
The extra properties in the intermediate table SHOULD be there.

One problem with the composite-element mapping is that composite-elements cannot take part in a SQL query (Hibernate 2.0.3). But, as mentioned in previous posts it's duable since 2.1 beta 1 (I think).

Here's the semantics:

Code:

* 'B' can have zero to infinite 'C'
* If two 'B' shares the same 'C' and one of them gets deleted, the 'C'
   should NOT be deleted
* If a 'C' only have one parent 'B' and 'B' gets deleted, 'C' should also get
   deleted
* 'C' can have zero to infinite 'D'
* If a 'D' only have one parent 'C' and 'C' gets deleted, 'D' should also get
   deleted
* If two 'C' shares the same 'D' and one of them gets deleted, the 'D'
   should NOT be deleted

Could you contribute to this problem Ali?

Kind regards, Andreas

andreas_eriksson · **Posted:** Sat Nov 29, 2003 9:53 pm

Just pushing the post up.

Gavin, maybe you could contribute with something that will enlighten me?

Regards, Andreas

gavin · **Posted:** Sat Nov 29, 2003 10:17 pm

I dunno, I don't understand your problem.

Perhaps if you simplify your case and narrow down to one or two classes, it will be easier to explain.

andreas_eriksson · **Posted:** Mon Dec 01, 2003 3:53 am

Hi Gavin,

The problem is mapping a many-to-many where the intermediate table should be a part of a ternery relationship. Dunno how to describe that problem with one or two classes though?

Code:

B --* BC *-- C
       |
       *
      BCD
       *
       |
       D

I could map the intermediate table (BC) as a composite-element, but the I cannot load the object graph 'cause a composite-element cannot be part of a query.

Any suggestions?

gavin · **Posted:** Mon Dec 01, 2003 8:00 am

Either

(a) use a composite element, which can be queried in 2.1
(b) remodel the link table as an entity
(c) model it as a <map> with an <index-many-to-many>