Mapping question

stevecoh2 · **Joined:** Tue Mar 21, 2006 11:01 am **Posts:** 65

I need to map an association between two classes A and C, such that A will own a collection of C's. However, the association between A and C is a composite key of two fields, one of which is a member of A, and the other is accessible only through a linking table B.

In SQL terms we are talking about something like this

select A.*, C.*
from A, B, C
Where C.k1 = A.k1
and C.k2 = B.k2
and B.k3 = A.k3

Is there a Hibernate mapping strategy for such a mapping? I realize this is probably not the best design. I have some freedom here but it is not unlimited.

My alternative would be to redundantly store k2 in A so that the SQL becomes

select A.*, C.*
from A, C
Where C.k1 = A.k1
and C.k2 = A.k2

and eliminate the middleman.

Then my mapping would be easier, using the <properties> element to define the composite key. Is there a way to use the <properties> concept with the first design, or would I be better off biting the bullet. I should point out that k2 is a 60 character VARCHAR.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Hibernate can handle this just fine, in a variety of ways.

If A and B represent different parts of the same java object, you could <join> them: then fields in B would be part of the conjoined class. This allows you to use a simple <set> or any collection to contain the C objects.

If they're different logical objects, where each table is represented by its own java object, then you can use a map in class A, use map-key-many-to-many to refer to the B class, and use one-to-many to refer to the C class.

There are other options too. I'd need more details to give you more options, as thinking in the abstract is not a Monday morning task. Which reminds me: coffee time!

stevecoh2 · **Joined:** Tue Mar 21, 2006 11:01 am **Posts:** 65

Quote:

If they're different logical objects, where each table is represented by its own java object, then you can use a map in class A, use map-key-many-to-many to refer to the B class, and use one-to-many to refer to the C class.

Yes, they are different logical objects. In fact, B has other uses besides serving as a link between A and C.

Actually, it's a many-to-one between A and B and a many-to-many between B and C. I don't normally think of A having a B, but I suppose I could, with lazy initialization. But here's the potential rub. When I load an A, I certainly don't want all C's matching B to be loaded with a B. I want only those C's that match the field in A that they share. in addition to matching B. In other words I want Hibernate to generate the SQL in my original post. If the mapping you suggest will do that, I can use it, otherwise, I can't. Do you think it will?

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

The <map> element should handle it all for you. The generated SQL will probably be the more legible "join" versoin of your query, but it'll do the same thing.

Your mapping might look like this:

Code:

<class name="A" ...>
 ...
  <map name="Cs" ...>
    <key column="k1"/> <!-- This is the column in A that relates to a column in C -->
    <map-key-many-to-many
      column="k3" <!-- This is the column in A that relates to a column in B -->
      class="B"/>
    <many-to-many
      column="k2"<!-- This is the column in B that relates to a column in C -->
      class="C"/>
  </map>
  ...
</class>

In all the above comments, the column of the 2nd table listed is the primary key of that table. To override that, use property-ref="???".

If that doesn't work, inspect the generated SQL and play around with the columns. I'm working off memory here, and my memory isn't exactly ... umm.. what's the word I'm looking for? I forget.

stevecoh2 · **Joined:** Tue Mar 21, 2006 11:01 am **Posts:** 65

Thanks for your suggestion. This is really cool. If I understand it correctly we're essentially joining C to A through B without making B a member of A which is exactly what I want.

Since the Collection of Cs in A is just a Collection, not a key-value collection (Map) is there any reason why your mapping wouldn't work just as well this way:

Quote:

Code:

<class name="A" ...>
 ...
  <set name="Cs" ...>
    <key column="k1"/> <!-- This is the column in A that relates to a column in C -->
    <map-key-many-to-many
      column="k3" <!-- This is the column in A that relates to a column in B -->
      class="B"/>
    <many-to-many
      column="k2"<!-- This is the column in B that relates to a column in C -->
      class="C"/>
  </set>
  ...
</class>

Offhand, I can't think of a reason why it wouldn't. The "join" version of the query wouldn't bother me so long as it does essentially the same thing.

stevecoh2 · **Joined:** Tue Mar 21, 2006 11:01 am **Posts:** 65

In the previous post, I asked:

Quote:

Since the Collection of Cs in A is just a Collection, not a key-value collection (Map) is there any reason why your mapping wouldn't work just as well using a Set?

After doing my own research, the answer is clearly No. A <set> does not accept a <map-key-many-to-many>. Which sends me back to the drawing board.

Are you sure your solution understands the problem? Your solution is very similar to what is shown in the Hibernate Manual (v.3.0.5) under "Ternary Associations" (Section 7.3.3). But this is different. What this seems to be geared toward is building a collection of C's in A where A links to B via one key and B links to C via another.

In SQL terms:

Code:

Select A*, C* 
from A, B, C 
where A.k1 = B.k1 
and B.k2 = C.k2;

My use case again is

Code:

select A.*, C.*
from A, B, C
Where C.k1 = A.k1
and C.k2 = B.k2
and B.k3 = A.k3 

In my use case PART of the key joining A and C comes directly from A, while another PART of the key comes via a link through B.

In any case, I tried this using a Map and it doesn't seem to work. I may not be understanding this correctly, but I'm not sure. Possibly this is not the solution for this type of problem.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Your description suggests that this is a key-value collection, and that a map is appropriate: for a given A, there are a number of Cs that are accessed via B1, others that are accessed via B2, etc. If you want to completely drop the B part of the ownership chain, so that the B part is not apparent at all, then you could use a subselect in a formula: not terribly efficient if B or C are large tables (there's a table scan involved), but it does result in a reasonably elegant java model. Something like:

Code:

<class name="A" ...>
  ...
  <set name="Cs" ...>
    <key>
      <column name="k1">
      <formula>(select b.k2 from TABLE_B b where b.k3 = k3)</formula>
    </key>
    <many-to-many class="C" ...>
  </set>
  ...
</class>

Note that the final k3 in the formula has no table alias: columns with no table alias like this are taken from the class' "main" table. Also, C has a composite-id in this one, something that's avoided if you use B as part of the mapping.

stevecoh2 · **Joined:** Tue Mar 21, 2006 11:01 am **Posts:** 65

Quote:

Your description suggests that this is a key-value collection, and that a map is appropriate: for a given A, there are a number of Cs that are accessed via B1, others that are accessed via B2, etc.

That's not what I intended to convey.

There are lots of As, lots of C's, few Bs.

Any given A will have an association with exactly one B (A.k3=b.k3). (A->B is many-to-one). All C's that are associated to this A will use the same B.

However, this B will contain part of the key (k2) that is used to associate Cs. I could instead redundantly store that field k2 in every A, and not involve B at all. That would be wasteful of space, but I could do it. But that is my reason for involving B.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

stevecoh2 wrote:

All C's that are associated to this A will use the same B.

However, this B will contain part of the key (k2) that is used to associate Cs.

But if all Cs associated with a given A use the same B, can't you just drop the B entirely? Just use the A part of the key to load the Cs? What is lost if you do it this way?

stevecoh2 · **Joined:** Tue Mar 21, 2006 11:01 am **Posts:** 65

I'm an idiot.

Everything I've tried to explain here is wrong.

Forget all about joining on B. Forget all about my sample SQL. It doesn't describe what I am trying to do.

I can't even come up with a suitable mapping when it's just between two tables.

What I'm REALLY trying to do is this.

I want

Code:

class A {
   Set Cs; // a set of C's
   Long a_id;  // the primary key.
   String val1;
   String val2;
   ...  // other stuff
}

class C {
    Long c_id; // the primary key
    String val1;
    String val2;
    ...   // other stuff
}

A.val1 and A.val2 together are not unique in A.
C.val1 and C.val2 together are not unique in C.

Nonetheless, I want to populate A.Cs with all C's where A.val1=C.val1
and A.val2=C.val2.

There is no other link between A and C. This relation between A and C is purely incidental.
A and C come from different data sources. The relation is many-to-many.
Each A may have many matching C's, and although I don't think of C's as owning A's in any sense (the association is unidirectional), there may be many A's that match a C based on these two fields.

In other words, I want to populate A.Cs with the results of an ad-hoc query, for each A.

I can't come up with a Hibernate mapping that lets me do that. I don't know what I am missing.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

That one's much easier.

Use a normal unidirection set from A to C. Use the two-column key in A:

Code:

<set name="Cs" ...>
  <key property-ref="CtoAPropRef>
    <column name="col1"/> <!-- Columns in table A -->
    <column name="col2"/>
  </key>
  ...
</set>

In C, use <properties> to define CtoAPropRef:

Code:

<properties name="CtoAPropRef">
  <property name="val1" column="col1"/> <!-- Corresponding colulmns in table C -->
  <property name="val2" column="col2"/>
</properties>

Voila, one working mapping.

stevecoh2 · **Joined:** Tue Mar 21, 2006 11:01 am **Posts:** 65

Thanks, we're getting closer. I was trying to use a <properties> but I was placing it in A, not C. Oh, duh, I thought, but placing the <properties> in C while referencing it using a property-ref in A, as you suggest, fails with the message that it doesn't know the property ref.

in A.hbm.xml

Code:

<class name="A" table="A">
...
        <set name="Cs" table="C">
            <key property-ref="CtoAPropRef" >
               <column name="acol1"/>
               <column name="acol2"/>
            </key>
            <one-to-many class="C"/>
        </set>
...
</class>

in C.hbm.xml

Code:

<class name="C" table="C">
...
        <properties name="CtoAPropRef" insert="true" update="true" unique="false">
             <property name="val1" column="col1" not-null="true" length="50"/>
             <property name="val2" column="col2" not-null="true" length="20"/>
        </properties>
...
</class>

Error message:
Exception in thread "main" org.hibernate.MappingException: property not found: CtoAPropRef

So does this not work if the mappings are in separate files?

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

It's not the file that it's in that's important, it's the element that it's on. My typo, my bad, it should be on the one-to-many, not the key.

Note that one-to-many didn't have property-ref prior to.. umm.. 3.1.1, I think? So if you get a parse error from the XML validator, you have two options. Either upgrade your hibernate jar, or change the one-to-many to be many-to-many. If you do that, don't specify the table="" attrbiute on the many-to-many, as that tells hibernate that you're using a join table, which you're not. many-to-many with no join table is functionally equivalent to one-to-many, in this situation.

stevecoh2 · **Joined:** Tue Mar 21, 2006 11:01 am **Posts:** 65

OK, thanks. Alas, I am constrained by bureaucracies to use 3.0.5. Will let you know if this works.

stevecoh2 · **Joined:** Tue Mar 21, 2006 11:01 am **Posts:** 65

Aarggh!
"Caused by: org.xml.sax.SAXParseException: Attribute "property-ref" must be declared for element type "many-to-many".

Which makes no sense at all because the DTD - http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd says:

<!ELEMENT many-to-many (meta*,(column|formula)*,filter*)>
<!ATTLIST many-to-many class CDATA #IMPLIED>
<!ATTLIST many-to-many node CDATA #IMPLIED>
<!ATTLIST many-to-many embed-xml (true|false) "true">
<!ATTLIST many-to-many entity-name CDATA #IMPLIED>
<!ATTLIST many-to-many column CDATA #IMPLIED>
<!ATTLIST many-to-many formula CDATA #IMPLIED>
<!ATTLIST many-to-many not-found (exception|ignore) "exception">
<!ATTLIST many-to-many outer-join (true|false|auto) #IMPLIED>
<!ATTLIST many-to-many fetch (join|select) #IMPLIED>
<!ATTLIST many-to-many lazy (false|proxy) #IMPLIED>
<!ATTLIST many-to-many foreign-key CDATA #IMPLIED>
<!ATTLIST many-to-many unique (true|false) "false">
<!ATTLIST many-to-many where CDATA #IMPLIED>
<!ATTLIST many-to-many property-ref CDATA #IMPLIED>