Friday, April 28, 2006

Hibernate annotations: The composite primary key with foreign keys references

written by Marcel Panse

A very common database design pattern is when you have a many-to-many table which creates a 3th table to create the many to many. You don't have to create a POJO for the 3th table but simply map the many-to-many between the two tables. But what if you want an extra property in your 3th table. You have to create a POJO with a primary key over multiple columns. But the columns exists of foreign keys to the other table's primary key. Notice you have to use the latest hibernate-annotations beta10.

Here's the picture to clarify it a bit:




And here is the code:


@Entity
@Table(name = "Product")
public class Product extends AbstractRecordImpl
{
@NotNull private String name;
private String description;

//----bidirectional association
@OneToMany(mappedBy="product")
private List productItems = new ArrayList();
//----

//Some getters & setters
...
}

@Entity
@Table(name = "Item")
public class Item extends AbstractRecordImpl
{
@NotNull private String name;

//----bidirectional association
@OneToMany(mappedBy="item")
private List productItems = new ArrayList();
//----

//Some getters & setters
...
}

Easy so far, just the normal POJO's.. Now for the magic:
I've added 2 extra getters and setters for Product and Item to make it easier to get or set a product or item on your primary key. I added to extra columns to make hibernate think there is a Product mapped to this table (and an Item) but hibernate never updates or inserts this property (becuase it doensn't exists in our db.) But when hibernate does the getProduct() method it will retrieve the Product from our composite foreign key and all will work fine! ^_^


@Entity
@Table(name = "ProductItem")
public class ProductItem
{
@Id
private ProductItemPK primaryKey = new ProductItemPK();
private String description;


//bidirectional association! Needed to trick hibernate ;P
@SuppressWarnings("unused")
@Column(name="item_id", nullable=false, updatable=false, insertable=false)
private Long item;

@SuppressWarnings("unused")
@Column(name="product_id", nullable=false, updatable=false, insertable=false)
private Long product;
//----


public String getDescription() {
return description;
}

public void setDescription(String description) {
this.description = description;
}

public void setProduct(Product product) {
primaryKey.setProduct(product);
}

public Product getProduct(){
return primaryKey.getProduct();
}

public void setItem(Item item) {
primaryKey.setItem(item);
}

public Item getItem() {
return primaryKey.getItem();
}
}

@Embeddable
private class ProductItemPK implements Serializable
{
@ManyToOne
private Item item;

@ManyToOne
private Product product;

//Some getters and setters
...
}

28 comments:

Anonymous said...

Hi,

This is exactly what i want to do.. You do not happen to have a pom-file for hibernate-annotations-3.1beta10 (i use maven2 for build)?

Thanks
/M

Marcel Panse said...

This is exactly what i want to do.. You do not happen to have a pom-file for hibernate-annotations-3.1beta10 (i use maven2 for build)?

Nope, using our own buildsystem for adding jars to our project from a shared repo.

Anonymous said...

Hi again,

I have still trouble to get this to work with the bidirectional stuff. I have seen the posts in hibernate forums but when I try to add the bidirectional stuff I end up in an endless loop (it executes the same sql over and over again untill i get stack overflow).. Could you please add the annotations for bidirectional relations in your example so that I can see how they really should be.

Thanks
/M

Anonymous said...
This comment has been removed by a blog administrator.
David said...

Thanks a lot. I really needed this.
I've had to change this to make it work with Hibernate 3.2.1.ga:

@Entity
@Table(name="ProductItem")
@AssociationOverrides({
@AssociationOverride(name="primaryKey.product", joinColumns = @JoinColumn(name="product_id")),
@AssociationOverride(name="primaryKey.item", joinColumns = @JoinColumn(name="item_id"))
})
public class ProductItem implements Serializable {


pom.xml:

<dependencies>
...

<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate</artifactId>
<version>3.2.1.ga</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-annotations</artifactId>
<version>3.2.1.ga</version>
</dependency>
<dependency>

...
</dependencies>

Toi said...

To David,

How do you persist a ProductItem instance? Is this what you did:

Product p = new Product();
p.set....;
p = entityManager.merge(p);

Item i = new Item();
i.set. ...
i = entityManager.merge(i);

ProductItem pi = new ProductItem();
pi.setProduct(p);
pi.setItem(i);
pi = entityManager.merge(pi);
// pi = entityManager.persist(pi);

I did that and it didn't work (hibernate 3.2.2, annotations & entitymanager 3.2.1 GA).

If I use pi = entityManager.merge(pi);, the log file showed a SELECT statement like: select ... from ProductItem productitem0_ where productitem0_.product = ? and productitem0_.item = ?

If I use pi = entityManager.persist(pi);, no SQL statement was showed. :(

Christof Dallermassl said...

Thanks a lot for your blog! It worked perfectly under hibernate 3.2.3.ga and hibernate-annotations 3.2.1.ga.

It worked without the @AssociationOverrides, those seem to be the only way to change the column names "item_id" and "product_id".

But I found another thing: you do not need the dummy private Long members (ProductItem.product and ProductItem.item) if you declare the mappedBy in Product and Item like:

@OneToMany(mappedBy="primaryKey.product")
private List<ProductItem>productItems;

Another question: how do you declare the cascadetypes so that saving a Product also saves the ProductItem?

Thanks!
Christof

julius said...

Cool, But can you map a pojo with a composite primary key in a flex application (I use fds) ? I think no !

whitenoise said...

Hi, i found this article interesting. I tried to apply on my application but I don't understand why don't work...I posted a question on hibernate forum...here:
http://forum.hibernate.org/viewtopic.php?t=958753&start=15&sid=5577e5d72e72b29ababa4c7c969a7fea

if someone can help me...this problem fool me....

bye and thanks
Luca

oggie said...

this is what I wanted to do as well. However, we use hibernate to generate our database. When I do so, it actually creates the table (IE - productItem) with 2 more fields.

So we have (for example) product_id, item_id, product_id_fk, and item_id_fk.

Is there a way to prevent this?

Anonymous said...

Just for the record. It works great!
You can avoid using @Override... and @OneToMany(mappedBy="primaryKey.item... just by removing the item and product identifiers from ProductItem class.

Then keep the mapping at it was used to be @OneToMany(mappedBy="product") in Product class and @OneToMany(mappedBy="item") in Item class. No need to reference the primaryKey embbeded class.




But you can remove the item and product ids from ProductItem.

mrrajesh1982 said...

Hello, This is really helpful for me... can you please send me this in OR mapping standard..as i am new to this hibernate... really appreciate...for you help.. thanks to all in advance...

mrrajesh1982@gmail.com

Jason said...

I got this to work without putting the dummy fields in the ProductItem, you just need to change the mappedby to "primarykey.product" and "primarykey.item" :)

Vlad said...

Thanks a lot Marcel for WORKING example with composite primary key annotations!

Flo said...

Perfect! I was looking for something like that - thank you!

Yatin said...

Hey Peeps,

If you are getting a stackoverflowerror doing something like this


@OneToMany(mappedBy="product")
private List productItems = new ArrayList();


as i was getting. Here is the solution http://opensource.atlassian.com/projects/hibernate/browse/EJB-225

You have to add the following line to your composite key object

@ManyToOne(fetch=FetchType.EAGER)
private Product product;


Hope this helps.

Rich Chamberlain said...

Thanks for this, worked first time and easy to follow for a hibernate n00b like me!

blog said...

Hi Marcel

Hibernate Annotations advanced a bit since 2006 :)

Here is my fine-tunig to your solution: http://boris.kirzner.info/blog/archives/2008/07/19/hibernate-annotations-the-many-to-many-association-with-composite-key/

Boris Kirzner

aurelio pascual jr. said...

thanks a lot, this article is such a big help

Anderson said...

Hi,

This is a very good reference. For latest hibernate annotation version (3.4 as of aug 20, 2009) please use the updated guide of Boris Kirzner.

http://boris.kirzner.info/blog/archives/2008/07/19/hibernate-annotations-the-many-to-many-association-with-composite-key/

If you want the ProductItem table to be automatically updated whenever you create a new Product with Item relation or Item with Product relation (many-to-many bidirectional relationship), please add the following codes:

In the Product and Item class, add
@OneToMany( mappedBy="Product", cascade = {CascadeType.PERSIST, CascadeType.MERGE} )
@Cascade({org.hibernate.annotations.CascadeType.SAVE_UPDATE,
org.hibernate.annotations.CascadeType.DELETE_ORPHAN})

place this in the productItems declaration or getProductItems() method (which ever is your convention) for Product and Item class.

The important is to have the CascadeType.PERSIST, CascadeType.MERGE and also the CascadeType.SAVE_UPDATE and ascadeType.DELETE_ORPHAN of hibernation annotation.

reference:
http://docs.jboss.org/hibernate/stable/annotations/reference/en/html_single/#entity-hibspec-cascade

Sample use for creating a new Product that links to one or many Items:
—————————————————————————————————-
Product product = new Product();
//set product values

Item item = new Item();
//set item values

List productItemList = new ArrayList();

ProductItem productItem = new ProductItem();
productItem.setItem(item);
//set other productItem values (if there are any more attributes other than Product and Item)
//no need to set product because the product is the one to be created

productItemList.add(productItem);

product.setProductItem(productItemList);

productManager.save(product);
—————————————————————————————————-

This also works for creating new Item with one or more Product relation since it's bidirectional. Update and Delete should also cascade changes to the ProductItem table automatically.

Hope this helps! Ü

Anonymous said...

Hello, This is really good job, but I have following exception.

Unknow column ProductItem.item_item_id

thx.

Anderson said...

Hmm I'm not sure what you mean by "ProductItem.item_item_id"
Perhaps it's "ProductItem.item.item_id"?

Also, I made a mistake on my old comment regarding this statement:
"//no need to set product because the product is the one to be created"

Actually, you still need to set product to ProductItem so that it would know which product it refers to.

This perfectly works! And I'm currently using it in a deployed project! Ü

Anonymous said...

Many thanks...it works.

Anonymous said...

Good example. I was searching for this info for two days unsuccessfully until I found the blog.

I could get my program working after following the example you have used.

thanks
~v

raki said...

I have gone through an article which explain the concept with example very neatly . http://j2eereference.com/2011/01/implementing-composit-primary-key-with-jpa-and-hibernate/

Singh said...

I have gone through one site , and found sample code for implementing the composite primary key using hibernate and JPA.

http://j2eereference.com/2011/01/implementing-composit-primary-key-with-jpa-and-hibernate/

Th3dz said...

Thanks a lot. Was having some trouble with this! +1

Arat Kumar Rana said...

Hi,
Can somebody help me on hibernate composite key annotation?

I have one table mobile_territory

ID primary key,
profile_oid bigint,
outlet_oid bigint,
outlet_id,
day bigint,
modified_datetime timestamp

where ID,profile_oid and outlet_oid is the composite key

how can I write pojo class(one class) to insert/update record?