Beginner mapping question (associations)

neodem · **Joined:** Wed Aug 24, 2005 1:59 pm **Posts:** 4

First off, please don't flame me becasue I'm sure this question has been asked before. I have searched the forum as best I can but i'm not sure what to search for exacty. I have the book 'Hibernate in Action', I've read the FAQ, I've looked at teh documentation and I've about given up thinking that I just don't have the aptitude to solve this problem.

I'm a good database designer and a good java programmer, it's just that a friend told me I should be using Hibernate and after reading the HiA book, I agree that it would simplify my data access layer.

My problem is that I'm stuck trying to create what I believe to be two one directional one to many relationships. Here is my database schema:

Code:

CREATE TABLE Recipe (
  recipeID bigint unsigned NOT NULL AUTO_INCREMENT,
  name varchar(50),
  PRIMARY KEY (recipeID)
)

CREATE TABLE Ingredient (
  ingredientID bigint unsigned NOT NULL AUTO_INCREMENT,
  name varchar(20),
  PRIMARY KEY (ingredientID)
)

CREATE TABLE RecipeIngredient (
  recipeIngredientID bigint unsigned NOT NULL AUTO_INCREMENT,
  recipeID bigint unsigned NOT NULL,
  ingredientID bigint unsigned NOT NULL,
  amount varchar(20),
  PRIMARY KEY (recipeIngredientID),
  CONSTRAINT FK_recipe FOREIGN KEY (recipeID) REFERENCES Recipe(recipeID) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_ingredient FOREIGN KEY (ingredientID) REFERENCES Ingredient(ingredientID) ON DELETE CASCADE ON UPDATE CASCADE
)

My domain model is just as simple. I've got a Recipe, Ingredient and RecipeIngredient class. The Recipe and Ingredient classes hold sets of RecipeIngredient objects and the RecipeIngredient objects hold single references to the Recipe and Ingreident classes.

I've set up the db this way so I can search in either direction, I can find out what ingredients are in a recipe and also search reciepes by ingredients. The join table is there to connect the many to many and also has a property 'amount' .

This situation looks to me like the one to many / many to one bidirectional association with join table (8.5.1.) in the documentation, but I haven't been able to implement it for 2 reasons. First I don't know how to add in the 'amount' property and second I'm not sure how the domain objects should be modeled.

I'm sure most of you are looking at this question and groaning since either you've seen it 100 times before or it's just too simple to bother. But, please understand that I have literally been trying to get this to work for close to 2 days now and I've tried any number of combinations. As for reading the book, I've tried, but it's a bit too complex for me to follow.

Any help would be appreciated.

john_yeager · **Posted:** Wed Aug 24, 2005 4:13 pm

First let me say that I think you have correctly set up your tables. There are two choices for this kind of situation. You appear to be correctly on your way to implement one of them. Also, as you can read below, the other choice cannot be bidirectional, so it doesn't sound like an option for you.

What you have so far is two one-to-many mappings. This is appropriate for you situation. You have three tables mapping to three entities. There is a one-to-many association from Recipe to RecipeIngredient and a one-to-many association from Ingredient to RecipeIngredient.

If it were not for the Amount property, you could get by with a many-to-many association. In that case, you would still have three tables, but only two entities. The RecipeIngredient table would not map to its own entity, but would only exist to support the many-to-many association. Not only would the RecipeIngredient table not contain the Amount column, it would also not contain the recipeIngredientID column. Instead its primary key would be a composite of the recipeID and ingredientID columns.

Once a column like Amount is added to a join table, you have two choices. You apparently can still use a many-to-many association. This is discussed in Hibernate In Action, Chp. 8 in the section titled "Using a collection of components for a many-to-many association." I have not used this technique. At the end of the section, however, the book states that "there is no way to make this mapping bidirectional."

The other choice is to do what you have done. Instead of a many-to-many association, you are using two one-to-many associations with the RecipeIngredient table mapping to its own entity. I think you should continue with this choice.

I hope this is enough. I realize I am pretty much saying that you should just keep doing what you are doing, but you shouldn't be looking at 8.5.1. Instead, refer to the sections in the book and documentation that deal with one-to-many associations for mapping your tables to your entities, as you no longer have a many-to-many association.

----------

I have one question on terminology. I am currious if the RecipeIngredient table is still considered to be a "join" table in this situation, now that it has its own ID column and contains a property. Does anybody else know for sure?

-John

mrtz · **Joined:** Mon Aug 01, 2005 3:10 pm **Posts:** 22

Hi,

A while back I was stuck on a similar problem, but I think this is what you are looking for: bidirectional many-to-many association
http://www.hibernate.org/hib_docs/v3/re ... l-join-m2m
Please note that there is an error in the docs for which I had created a topic http://forum.hibernate.org/viewtopic.php?t=946450

Here is your mapping. You may need other optional attributes as per your needs.

Code:

<class name="Recipe">
   <id name="id" column="recipeID">
      <generator class="native" />
   </id>
   <set name="ingredients" table="RecipeIngredient">
      <key column="recipeID" />
      <many-to-many column="ingredientID" class="Ingredient" />
   </set>
</class>

<class name="Ingredient">
   <id name="id" column="ingredientID">
      <generator class="native" />
   </id>
   <property name="amount" type="string">
      <column name="amount" length="20" />
   </property>
   <set name="recipes" table="RecipeIngredient" inverse="true">
      <key column="ingredientID" />
      <many-to-many column="recipeID" class="Recipe" />
   </set>
</class>

neodem · **Joined:** Wed Aug 24, 2005 1:59 pm **Posts:** 4

to mrtz:

I'm not sure that I understand your solution. You are including 'amount' as a property in the Ingredient entity. Oviously that won't work since 'amount' is field in the RecipeIngredient table.

It seems to me that the 'amount' property is a huge stumbling block in implementing the bidirectional many-to-many association that you are suggesting I use.

neodem · **Joined:** Wed Aug 24, 2005 1:59 pm **Posts:** 4

to john_yeager :

Thank you for your reply. It's good to know that I'm on the right track. I can't find the section you mention in my copy of the book. What is the section number?

Also, I've been trying to treat this as two one-to-many mappings but I seem to keep running into erros like LazyIntializationException and the like. For those of you who are interested, here are the mappings I'm currently playing with:

Code:

<class name="Recipe" table="Recipe">
   
   <id name="id" column="recipeID" type="long">
      <generator class="native"/>
   </id>
   
   <property name="name" column="name" type="string"/>
   <property name="serves" column="serves" type="integer"/>   
   <property name="steps" column="steps" type="string"/>
   
    <set name="recipeIngredients" inverse="true" cascade="all-delete-orphan">
      <key column ="recipeID"/>
      <one-to-many class="RecipeIngredient"/>
    </set>

</class>

<class name="RecipeIngredient" table="RecipeIngredient">
   
   <id name="id" column="recipeIngredientID" type="long">
      <generator class="native"/>
   </id>
   
   <property name="amount" column="amount" type="string"/>
   <many-to-one name="recipe" column="recipeID" class="Recipe" not-null="true"/>
   <many-to-one name="ingredient" column="ingredientID" class="Ingredient" not-null="true"/>
</class>

<class name="Ingredient" table="Ingredient">
   
   <id name="id" column="ingredientID" type="long">
      <generator class="native"/>
   </id>
   
   <property name="name" column="name" type="string"/>
   
    <set name="recipeIngredients" inverse="true" cascade="all-delete-orphan">
      <key column ="ingredientID"/>
      <one-to-many class="RecipeIngredient"/>
    </set>
             
</class>

john_yeager · **Posted:** Wed Aug 24, 2005 5:11 pm

Rats. Sorry, that should have been Chp. 6. The section starts on p. 229 in my copy.

-John

neodem · **Joined:** Wed Aug 24, 2005 1:59 pm **Posts:** 4

Ahh, yes.. I played with that one.. didn't seem to work right for me and of course it was unidirectional..

on a brighther note, I found that the LAzyInitializationException was coming up somewhere not related to the mapping and I solved it. So it seems that as I quit for the day things are working.. I'm going to test a bunch tomarrow..

thank you

mrtz · **Joined:** Mon Aug 01, 2005 3:10 pm **Posts:** 22

neodem:
my applogies, the solution for many-to-many that I proposed is wrong due to the "amount" field being in the association table. Anyway, looks like you already have the solution!

srjacobs · **Joined:** Sat Jun 11, 2005 8:35 am **Posts:** 14

I think I am going to have to switch over to using an entity to represent the join table as discussed in this thread. With Hibernate 3.0.5 I tried implementing the join table as a collection of components (as discussed in the Hibernate In Action book) but I could not get that to work. The book talks about the fact that the entire table forms the primary key - which makes sense. But - if you update one of the additional properties of the component objects - and then save that - Hibernate will first do a delete of the old object, followed by an insert of the new - which is standard fare for how it works with components. BUT - when I set this up - the where clause of the delete does NOT include the foreign key to the other side of the relationship. So in my case all but that column was included in the delete.

Obviously that won't work since there could be many rows in the table that match the where clause that Hibernate generated - so you could end up replacing many rows with the single updated row.

Don't know if anyone else tried this approach and ran into this particular problem or not - but thought I would mention it as part of this thread.

john_yeager · **Posted:** Wed Mar 15, 2006 12:53 pm

Scott,

I'm not sure that a collection of components is appropriate for the example given in this thread. I haven't used one, but it looks like it is appropriate for a one-to-many relationship, not a many-to-many relationship as described in this thread.

I will try and clarify a few things. Hopefully this will help:

When you switch over to having an entity associated with the join table, the primary key of the join table no longer consists of all the columns in that table.

In the example the RecipeIngredient table represents a many-to-many relationship between the Recipe and Ingredient tables. If there were no attributes for a Recipe/Ingredient combination (that is, the amount attribute was not needed), the RecipeIngredient table could have only two columns: recipeID and ingredientID, and these two columns would make up the primary key. That is, all columns would be part of the primary key. There would be no entity associated with the RecipeIngredient table in this case.

But if a attribute is needed for a Recipe/Ingredient combination (such as the amount attribute in the example), then an entity needs to be associated with the RecipeIngredient table to hold that attribute. In this case, the RecipeIngredient table is changed to have its own ID value. The RecipeIngredient table still contains the recipeID and ingredientID columns, but they are no longer part of the primary key. Instead there is a recipeIngredientID column that is the primary key. There are now three entities, with a one-to-many relationship between Recipe and RecipeIngredient and another one-to-many relationship between Ingredient and RecipeIngredient. There is still a many-to-many relationship between Recipe and Ingredient, but only indirectly through another entity: RecipeIngredient.

-John

srjacobs · **Joined:** Sat Jun 11, 2005 8:35 am **Posts:** 14

John -

Yes - I think I understand exactly what you are talking about. I have almost the same exact situation that I was currently working through for the application that I am working on.

The only reason I went down the path of using a collection of components is because it was mentioned as an alternative to 2 one-to-many associations to a new entity that captures the relationship and additional state info on page 229 of the Hibernate In Action book.

Our exact situation is actually slightly different - we really have a one-to-many relationship that requires additional state info at the relationship level - vs a many-to-many - but that is just a slight mod from the many-to-many case anyhow.

Even if Hibernate did as I had hoped for using the collection of components - I really think representing the junction as a child entity is the better approach - at least for our application. I really don't like having to include every column in the table as part of the primary key - especially since in our application - the 'real' primary key from a true data relationship perspective is the "one" side of the one-to-many relationship since it can't be attached to two parents of the same type.

gagan090283 · **Joined:** Wed Mar 15, 2006 10:36 am **Posts:** 14

Hi all

I am sorry, but as I found max. view of this post so I thought it would be great If you can look at mine too. Really sorrry for takin this way.. reply to gagan090283

I am using EntityMode.Map for retrieving the objects from database. I am using only XML mapping, no pojo pbjects. If my associations are bi-directional, I am getting stackoverflow error. I tried running the same program given in test under dynamic folder, where the use has mentioned not to use set, to avoid stackoverflow, but its coming with this too.

eg, If Object 'A' has one-to-many association with object 'B'
and 'B' has one to many association with 'A', then if retrieve the object A as Map, it goes in infinite recursion, how to prevent that, condition of bidirectional access is must!!!

any help will be highly appreciated.

Thanks in advance.
_________________
Gagan Jain

john_yeager · **Posted:** Wed Mar 15, 2006 3:34 pm

Scott,

I see now what you were doing. When I looked up "Collection of Components" today, I looked at pages 217-220, but missed 229-231, which describe how a "Collection of Components" can be used to represent a many-to-many relationship. Sorry about that. I see I even referred to that technique in a previous post, but in my defense that was quite a while ago.

I see you are correct that all the columns are part of the primary key when using the "Collection of Components" technique. In my earlier post I pointed out that the book states the mapping is unidirectional with that technique. You've apparently found more issues with the technique.

Just curious: You say in your case you have a one-to-many and the one-side can't be attached to two parents "of the same type." Does that mean it can be attached to more than one parent of different types? Is that why you can't move the attributes from the join table into the entity on the one-side?

-John

srjacobs · **Joined:** Sat Jun 11, 2005 8:35 am **Posts:** 14

John -

Yes - exactly - the same child object can be associated with a different parent type. Actually in our application the table that contains these objects can be referenced by 3 different parent types. And some of those junctions require the additional state data, some do not.

So - for exactly the reason you specify - we need to have the additional data represented in the join table.

aviggen · **Posted:** Wed Mar 15, 2006 11:36 pm

I have the same type of setup..

I've got except it's using Users and Addresses, many to many with an association table between.

I'm currently using sets and I wanted to know how you fellas order things?

I'm strictly going to try to use Criteria to do the work. Basically, I use my UserDAO, to return the user..then find the addresses from that. I would like to order the addresses. I tried adding a criteria to the criteria of the user, but that didn't work right..it generated

Code:

22:39:02,525 INFO  [STDOUT] Hibernate: select this_.id as id401_1_, this_.firstname as firstname401_1_, this_.lastname as lastname401_1_, this_.email as email401_1_, this_.password as password401_1_, addresses3_.userid as userid, address1_.id as addressid, address1_.id as id399_0_, address1_.alias as alias399_0_, address1_.street as street399_0_, address1_1_.userid as userid400_0_ from user this_ inner join useraddress addresses3_ on this_.id=addresses3_.userid inner join address address1_ on addresses3_.addressid=address1_.id left outer join useraddress address1_1_ on address1_.id=address1_1_.addressid where this_.id=? order by address1_.alias asc
22:39:02,529 INFO  [STDOUT] Hibernate: select addresses0_.userid as userid1_, addresses0_.addressid as addressid1_, address1_.id as id399_0_, address1_.alias as alias399_0_, address1_.street as street399_0_, address1_1_.userid as userid400_0_ from useraddress addresses0_ left outer join address address1_ on addresses0_.addressid=address1_.id left outer join useraddress address1_1_ on address1_.id=address1_1_.addressid where addresses0_.userid=?

Any ideas?