map with one-to-many without foreign key

zdila · **Posted:** Sun Aug 27, 2006 3:27 pm

Hi :-)

Is it possible to create a <map> with <one-to-many> without having a foreign key? Consider this:

Code:

CREATE TABLE tab1 (
  id PK,
  map1 INT,
  map2 INT,
  map3 INT,
  ...
);

CREATE TABLE tab2 (
  id1 PK,
  id2 PK,
  value VARCHAR
);

id1 and id2 are composite primary key. Column map1 contains value of id1. What I want is to have property Map map1 where map-key will be value of id2. The same for map2, map3, ...

PS: I don't want any more auxiliary tables (i can solve this using 3 tables, but i think that third table is really redundant). Moreover I don't need to define database constraints for this to force reference integrity as it even seems not to be possible).

Thanks a lot in advance :-)

zdila · **Posted:** Mon Aug 28, 2006 1:59 pm

gurus, please help me to solve this problem i am struggling with now pretty long time...

at first view it looks simple, but i can't create such mapping. it would even be satisfactory for me to hear "it is not possible" if that's the case

THANKS IN ADVANCE

max · **Posted:** Mon Aug 28, 2006 2:13 pm

eh? what is the problem ? you just map it as any normal map mapping.

zdila · **Posted:** Mon Aug 28, 2006 2:35 pm

Hi. I can't because normal <map> would need a <key> - that's column in the Tab2 that refers to pk in Tab1 (that's "id"). Here's example of that tables:

Code:

tab1:
| id | map1 | map2 | map3 |
+----+------+------+------+
|  1 |    1 |    2 |    3 |
|  2 |    4 |    5 |    6 |
...

tab2:
| id1 | id2 | value  |
+-----+-----+--------+
|   1 |   x | value1 |
|   1 |   y | value2 |
|   2 |   x | value3 |
|   2 |   y | value4 |
|   3 |   x | value5 |
|   3 |   y | value6 |
|   4 |   x | value7 |
|   4 |   y | value8 |
|   5 |   x | value9 |
|   5 |   y | valueA |
|   6 |   x | valueB |
|   6 |   y | valueC |
...

I'd like to have in Java (JavaScript notation):

Code:

tab1_line1 = /*Tab1 object*/{ id: 1, map1: /*java.util.Map*/{ x: value1, y: value2 }, map2: { x: value3, y: value4 }, map3: { x: value5, y: value6 } };
tab1_line2 = /*Tab1 object*/{ id: 2, map1: { x: value7, y: value8 }, map2: { x: value9, y: value10 }, map3: { x: value11, y: value12 } };
...

What sohould the mapping look like?

Code:

<class name="Tab1" table="tab1">
   <id column="id" name="id">
      <generator class="native"/>
   </id>
   
   <map name="map1">
      <one-to-many class="Tab2" />
      ??? - using <key> needs aditional column creation in tab2
   </map>
   <map name="map2">
      <one-to-many class="Tab2" />
      ??? - using <key> needs aditional column creation in tab2
   </map>
   <map name="map3">
      <one-to-many class="Tab2" />
      ??? - using <key> needs aditional column creation in tab2
   </map>
</class>

<class name="Tab2" table="tab2">
   <composite-id>
      <key-property name="id1" column="id1" />
      <key-many-to-one name="id2" class="Tab3" column="id3"/>
   </composite-id>

   <property name="value" column="value" />
</class>

Thanks in advance. I really apreciate your help!

max · **Posted:** Mon Aug 28, 2006 3:15 pm

i haven't tested but something like:

<property name="field1" unique="true"/>

<set name=“map1" inverse="true">
<key column="MAP1" property-ref="field1"/>
<one-to-many class="Tab2"/>
</set>

but i'm not sure if one-to-many will work for you since that table does not have a proper dependent entity. should probably a composite-element.

zdila · **Posted:** Mon Aug 28, 2006 3:34 pm

Max, you're a Magician!

It seems to be working - updateSchema created my tables as expected :-)

Thanks a lot!