After reading this section in the documentation, I am not sure I am clear on how to use the Collections of values to map a many-to-many relationship. I think an example would really help clarify the usage for me. The examples in the documentation leave quite a bit of important details out.

I hope this posting will start a discussion that will lead to a clear how-to on the topic. Thanks in advance.

OK, first of all, are you trying to use a "collection of values" or a "many-to-many" relationship? They are two different things.

Or do you mean a composite-element mapping?

In the external document section on the wiki there are some good references. Here is one that will help you with simple expalnations of various associations.

http://www.xylax.net/hibernate

Thanks David.

I have been confused about a variation of implementation on this for weeks, I haven't managed to figure it out despite reading all the documentation and examples I can find and posting to this forum.

I understand the sparsely and even in some cases outdated (such as the examples at the link posted two messages ago) documented examples but what if the primary key of the object you want to get the collection of other objects on is not in any way shape or form related to the key of the other objects? How on earth do you map a many-to-many if the key you want to use is not the primary key of the "parent" (used loosely) object but rather another field on that object?

Here are two examples of what I mean:
Example 1 (with a x-ref):



Nowhere is the primary key of table_A used to reference the table distinct_many_table which contains information relevant to all records in table_A. The key to the table distinct_many_table is a different field contained in each record of table_A because each record in table_A is interested in different "collections" of records in the distinct_many_table and there is no need to have millions of records in a cross reference table when there are only a few records in the distinct_many_table.


Example 2 (without a x-ref):



There is no cross reference between table_A and the generic descriptions for records in table_A. Let's say for example that there are only 15 descriptions (times the number of languages) in the table generic_reusable_i18n_descriptions but there could be thousands or even millions of records in table_A which reuse those 15 descriptions. You certainly would not want to duplicate the descriptions and there isn't really a point in having a cross reference to them but how do you map this in Hibernate?

I have both of these scenarios in the data model I am trying to map to and I have not been able to get mappings working for three weeks. Everything I have tried which actually validated and didn't throw an exception returned no results because it tried to use the containing objects primary key instead of the property that actually points to the data.
I do not want to use the primary key of the containing object to define the relationship, the primary key of the containing object has nothing to do with these particular relationships, how do I specify to use a different property?

I think I have what you're looking for.... have you considered trying this

table_A { a_id:int, data:varchar(50), xref_type_key:int }
xref_type {xref_type_key:int}
xref_table { xref_type_key:int, xref_many_key:int }
distinct_many_table { xref_many_key:int, something:varchar(50) }

xref_type could have more fields, but does not need to. Now, table_a has a 1:many (or 1:1) with xref_type and xref_type has a many:many with whatever is mapped to distinct_many_table.

in hibernate you would say tableA.getXrefType().getMany().

You may be able to simply create a java class that maps a simple object to table_a, but uses xref_type_key as the pk-id.

I wonder if I am missing something, or you are. If I have table A {a_id,name} and table B {b_id, desc} and I want to map m:m with table AB {a_id, b_id} and I have 7 million records in A and 10 in B, I do not have to have 7 million records in AB if there are only 100 instances where A record(s) have relationships with B record(s), I only have to have 100 records in AB. Using a different field to index a relationship will not change the number of mappings. I still will only have 100 records in AB regardless of the number of entries in A or B. To say if differently, if all 7 million A records have a relationship with B, you will have at least 7 million records in AB ( sum(A-B relations) ) if every entry in A has a relation with B regardless of your choice to use a_id or n_id, or any other index for that matter.

Remember that you are not forced to have a record in xref_table for every entry in your table_a. You only have to have an entry in xref_table for every relationship to your distinct_many_table.

You propose another x-ref which might well work to create a mapping, however, part of my problem is that I neither have, nor want, extra x-ref tables that don't server a purpose in the data model.


I like your out-of-the-box thinking on this one but the xref_type_key is not unique, in my database there will likely be thousands of records with the same value for that field because each record in the A table will have a non-unique value pointing to one of the collections in the B table.


Probably me.


Exactly! I don't want that! It serves no purpose because I already know that there will be X number of entries in B for every single record in A and I've already got the key to B in another field in A. In one specific scenario in my model I actually have three different links from A to B which are discriminated by a type field (which I didn't mention for brevity) and that would lead to 21 million records in what I consider a pointless x-ref table.


It does, three of them to be precise.


This is exactly where my biggest question/confusion lies, how is it "my choice"? How do I tell Hibernate to use n_id instead of a_id? If I could tell it to use n_id instead then all of my problems in this area would go away. My point is that I want to use n_id to link directly to B without a x-ref but I don't know how (and am begining to doubt it is possible) to tell Hibernate to use some field other than the primary key to create the association with. I want a many-to-many relationship where all of the values in table B are read-only lookups that get used repeatedly by records in table A.


Each record in table_a has three relationships to distinct_many_table which can all be looked up by the alternate key field I don't seem able to specify.

if i understand this correctly a has 3 references to b... maybe, hopefully, you have table_a{a_id, b1_id, b2_id, b3_id} and table_b {b_id}

In this case you have 3 m:1 relations. You can map that easily enough with three different get methods getB1() getB2() getB3(). I realize that this introduces repeating fields to your table, but I do not believe you can have m:m without a mapping without either a mapping table or repeating fields.

Please dont have table_a{a_id, b_id} where b_id holds, i.e. 1|3|5 as a value. If you do, remove that and simply have repeating fields. This will create a nightmare for someone else.

Does that help?

At first I thought that it is essentially what you descrive, however, technically it is more like table_a{a_id, b_id, b1type_value, b2type_value, b3type_value} and table_b {b_id, btype_id, btype_value} where class A has the fields in table_A as well as three Sets of values loaded from B which would be sets of the valid values, respectively, for b1type_value, b2type_value, b3type_value.


No because b_id is not unique (not even with the type_id). There will be (at the moment) between 1 and 4 records with the same b_id and btype_id (which is what I want). I want a little set of B's for each b?_id field in table_A. I have 3 m:m relations.


I already have the three getters for sets of B but I think now maybe we aren't thinking the same thing exactly...


Maybe that is what I have, I've been looking at this for so long I can't think straight anymore.


Ha, ha, it's already done it for me. ;)


Not really but I think we're making progress. Let me give my actual model as example because perhaps trying to make my question generic is confusing the issue.

I have a table which is a third level Map on a "worksheet" called worksheet_factor


The cost_factor table will never be accessed directly or mapped to by Hibernate, it has 18 records which will "never" change. It holds the default values for the 3 type fields. The worksheet_factor table holds the live data that represents one of the 18 cost factors on a "worksheet" (remember I said this is three levels deep). When a new record in worksheet_factor is created it is populated by default with the values in the cost_factor table. Each worksheet_factor instance has a unique id and it also has the id of the particular 1..18 record of cost_factor that it represents.
The internationalized descriptions of the 18 possible cost_factors are stored in the cost_factor_description table and keyed by cost_factor_id and locale.
The three type fields (calculated_type, currency_type, and prorating_type) in worksheet_factor (which get their defaults from the cost_factor table) are allowed to be set only to one of the values found for their type (this is I think similar to what you called b_id = 1|3|5 but it's not the b_id field (a.k.a. cost_factor_id) it's the type) found in the cost_factor_values table. The reason for loading the collection of allowed values is to populate select boxes on the JSP page with the values the user is allowed to choose from. Since I need to show the user a meaningful string name to go with the numbers behind the scenes I need to load the internationalized description from the respective xxxxx_type table (cross referenced by the allowable_type field in cost_factor_values).

I think now that I have had the opportunity to type through all of that that my problem lies in the fact that Hibernate would like my object model to match my data model (sorry for making such a generalising limiting statement) and that if I just make a "meaningless" placeholder object for the cost_factor table and create a m:1 from worksheet_factor to cost_factor and map all the interesting stuff through it instead of trying to map it directly onto an instance of a worksheet factor it will work because all of the primary keys for everything that Hibernate is so set on using will suddenly line up.

Why can't I just specify a different field instead of the primary key though?

Because that is a Bad relational model. Foreign keys should refer to primary keys. That is just basic.

dont mind this post if it gets a little garbled... its late.. but you've got me interested.

Your problem is database specific/business specific, and not Hibernate specific.

Lets stop a sec and just talk database, k? Given the tables you sent me and only the tables you sent me, for each distinct record(row) in table worksheet_factor, you can only have one related (via fk) cost_factor, cost_factor_description, cost_factor_values, calculated_type and prorating_type associated, period. No if's ands or buts about it.

If you want to have, for each record/row in work_sheet_factor, more than on record of calculated_type, then you either need to have a mapping table or you need to repeat the id in more then one field. You never, I repeat never, want to have a compound field. Please review relational database normalization if you even consider this. I will never forget un-screwing a db configured with compound fields this several years back where a given mapping was in one or two fields depending on how many mappings was created. Oh Joy. Though I did get the opportunity to write a nice, thow-away, script over a week. If you considered having 3 cost_factor_values for each worksheet_factor record/row, don't make it a varchar and do 3|4|1. Make 3 fields in that table cost_factor_value_a, cost_factor_value_b and cost_factor_value_c. I've read that de-normalization like that performs better then mapping tables in dbs like MySQL where less normalized structures perform better (though that was a couple years back.) If you were using someting like Oracle, there should be no real issue having a mapping table.

That being said, you may not have any real hibernate/db problem if you are only trying to tackle your jsp display. As I said earlier, based on the tables you sent me, every worksheet_factor object has a many:1 relationship with cost_factor, cost_factor_description, cost_factor_values, calculated_type and prorating_type associated, period. You object will determine it's cost_factor with a getCostFactor() method. If your jsp page has to display a drop down menu, you will have to build that up by getting all possible CostFactor objects. In hibernate, you would get a list of all CostFactor objects using the finder method. Iterate through your list of CostFactors to build the select menu. When you post to your save logic, it will simply create a new instance of CostFactor using the PK id gotten from the select box. You then create a new WorksheetFactor object and proceed to set the attributes, i.e. setCostFactor(costfactorInstance);

You only have two generally accepted methods to have more than one relationship with a distinct entity, and that is to have more then one field have a foreign key relationship with another entity, or to create a mapping table.

If I had 7 million worksheet_factor, and for each record I would have to have, at most 3 calculated_type, then I would seriously consider:

worksheet_factor {
worksheet_factor_id INT NOT NULL,
worksheet_product_id INT NOT NULL,
cost_factor_id INT NOT NULL,
calculated_type_1 SMALLINT NOT NULL,
calculated_type_2 SMALLINT NOT NULL,
calculated_type_3 SMALLINT NOT NULL,
cost_factor_value FLOAT NULL,
foreign_amount FLOAT NULL,
currency_type SMALLINT NOT NULL,
exchange_rate FLOAT NULL,
prorating_type SMALLINT NOT NULL
}

especially if my db could not handle speedy lookup of:

worksheet_factor_calc {
worksheet_factor_id INT NOT NULL,
calculated_type SMALLINT NOT NULL
}


I hope this helps you.

They do in the data model, the data model is normalized just fine. However, I fail to see why my object model should be required to have extra, pointless, granularity to make it match the data model just so I can load lookup values.
I can make an object model (and I did finally out of frustration) that matches the data model and makes Hibernate happy with all of the foreign keys pointing to primary keys etc. but this should not be necessary in the case of loading read only lookups.

In my data model worksheet_factor has a foreign key from cost_factor (cost_factor_id) and the descriptions are keyed to cost_factor and the lookup cross reference of valid values is keyed to cost_factor and the various lookup type descriptions tables are keyed to the cross reference of valid values et. al.

HOWEVER, I am firmly of the opinion that the Object model should not need to add an "empty" CostFactor object that sits between WorksheetFactor and the various lookups, nothing is ever going to be read from or written to the cost_factor table by the object model. Everything below WorksheetFactor is read-only and all I wanted to do was simplify the object structure and eliminate the unnecessary extra layer. I just wanted to load imutable lookups without having to define and load an imutable CostFactor object as a placeholder between them and the WorksheetFactor they are important to because that just adds a redundant layer of nesting to the object model.

Anyway, thank-you all for your help in this regard, I am now painfully aware of where the limitations lie and I will focus my attentions to the peculiar bugs I am encountering instead of functionality I thought must exist but doesn't.