Hibernate – Many-to-Many example – join table + extra column (Annotation)
In this tutorial, we show you how to use Hibernate to implements “many-to-many table relationship, with extra column in the join table“.
For many to many relationship with NO extra column in the join table, please refer to this @many-to-many tutorial
1. Many-to-many table + extra columns in join table
The STOCK and CATEGORY many to many relationship is linked with a third / join table named STOCK_CATEGORY, with extra “created_by
” and “created_date
” columns.
MySQL table script
CREATE TABLE `stock` (
`STOCK_ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`STOCK_CODE` VARCHAR(10) NOT NULL,
`STOCK_NAME` VARCHAR(20) NOT NULL,
PRIMARY KEY (`STOCK_ID`) USING BTREE,
UNIQUE KEY `UNI_STOCK_NAME` (`STOCK_NAME`),
UNIQUE KEY `UNI_STOCK_ID` (`STOCK_CODE`) USING BTREE
)
CREATE TABLE `category` (
`CATEGORY_ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`NAME` VARCHAR(10) NOT NULL,
`DESC` VARCHAR(255) NOT NULL,
PRIMARY KEY (`CATEGORY_ID`) USING BTREE
)
CREATE TABLE `stock_category` (
`STOCK_ID` INT(10) UNSIGNED NOT NULL,
`CATEGORY_ID` INT(10) UNSIGNED NOT NULL,
`CREATED_DATE` DATE NOT NULL,
`CREATED_BY` VARCHAR(10) NOT NULL,
PRIMARY KEY (`STOCK_ID`,`CATEGORY_ID`),
CONSTRAINT `FK_CATEGORY_ID` FOREIGN KEY (`CATEGORY_ID`)
REFERENCES `category` (`CATEGORY_ID`),
CONSTRAINT `FK_STOCK_ID` FOREIGN KEY (`STOCK_ID`)
REFERENCES `stock` (`STOCK_ID`)
)
2. Project Structure
Review the file project structure of this tutorial.
3. Hibernate / JPA Annotation
The Hibernate / JBoss tools generated annotation codes are not working in this third table extra column scenario. To make it works, you should customize the code to use “@AssociationOverride
“, in StockCategory.java
to represent the many to many relationship.
See following customized codes :
File : Stock.java
package com.mkyong.stock;
import java.util.HashSet;
import java.util.Set;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import static javax.persistence.GenerationType.IDENTITY;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.persistence.UniqueConstraint;
@Entity
@Table(name = "stock", catalog = "mkyongdb", uniqueConstraints = {
@UniqueConstraint(columnNames = "STOCK_NAME"),
@UniqueConstraint(columnNames = "STOCK_CODE") })
public class Stock implements java.io.Serializable {
private Integer stockId;
private String stockCode;
private String stockName;
private Set<StockCategory> stockCategories = new HashSet<StockCategory>(0);
public Stock() {
}
public Stock(String stockCode, String stockName) {
this.stockCode = stockCode;
this.stockName = stockName;
}
public Stock(String stockCode, String stockName,
Set<StockCategory> stockCategories) {
this.stockCode = stockCode;
this.stockName = stockName;
this.stockCategories = stockCategories;
}
@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "STOCK_ID", unique = true, nullable = false)
public Integer getStockId() {
return this.stockId;
}
public void setStockId(Integer stockId) {
this.stockId = stockId;
}
@Column(name = "STOCK_CODE", unique = true, nullable = false, length = 10)
public String getStockCode() {
return this.stockCode;
}
public void setStockCode(String stockCode) {
this.stockCode = stockCode;
}
@Column(name = "STOCK_NAME", unique = true, nullable = false, length = 20)
public String getStockName() {
return this.stockName;
}
public void setStockName(String stockName) {
this.stockName = stockName;
}
@OneToMany(fetch = FetchType.LAZY, mappedBy = "pk.stock", cascade=CascadeType.ALL)
public Set<StockCategory> getStockCategories() {
return this.stockCategories;
}
public void setStockCategories(Set<StockCategory> stockCategories) {
this.stockCategories = stockCategories;
}
}
File : StockCategory.java
package com.mkyong.stock;
import java.util.Date;
import javax.persistence.AssociationOverride;
import javax.persistence.AssociationOverrides;
import javax.persistence.Column;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.JoinColumn;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import javax.persistence.Transient;
@Entity
@Table(name = "stock_category", catalog = "mkyongdb")
@AssociationOverrides({
@AssociationOverride(name = "pk.stock",
joinColumns = @JoinColumn(name = "STOCK_ID")),
@AssociationOverride(name = "pk.category",
joinColumns = @JoinColumn(name = "CATEGORY_ID")) })
public class StockCategory implements java.io.Serializable {
private StockCategoryId pk = new StockCategoryId();
private Date createdDate;
private String createdBy;
public StockCategory() {
}
@EmbeddedId
public StockCategoryId getPk() {
return pk;
}
public void setPk(StockCategoryId pk) {
this.pk = pk;
}
@Transient
public Stock getStock() {
return getPk().getStock();
}
public void setStock(Stock stock) {
getPk().setStock(stock);
}
@Transient
public Category getCategory() {
return getPk().getCategory();
}
public void setCategory(Category category) {
getPk().setCategory(category);
}
@Temporal(TemporalType.DATE)
@Column(name = "CREATED_DATE", nullable = false, length = 10)
public Date getCreatedDate() {
return this.createdDate;
}
public void setCreatedDate(Date createdDate) {
this.createdDate = createdDate;
}
@Column(name = "CREATED_BY", nullable = false, length = 10)
public String getCreatedBy() {
return this.createdBy;
}
public void setCreatedBy(String createdBy) {
this.createdBy = createdBy;
}
public boolean equals(Object o) {
if (this == o)
return true;
if (o == null || getClass() != o.getClass())
return false;
StockCategory that = (StockCategory) o;
if (getPk() != null ? !getPk().equals(that.getPk())
: that.getPk() != null)
return false;
return true;
}
public int hashCode() {
return (getPk() != null ? getPk().hashCode() : 0);
}
}
File : StockCategoryId.java
package com.mkyong.stock;
import javax.persistence.Embeddable;
import javax.persistence.ManyToOne;
@Embeddable
public class StockCategoryId implements java.io.Serializable {
private Stock stock;
private Category category;
@ManyToOne
public Stock getStock() {
return stock;
}
public void setStock(Stock stock) {
this.stock = stock;
}
@ManyToOne
public Category getCategory() {
return category;
}
public void setCategory(Category category) {
this.category = category;
}
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
StockCategoryId that = (StockCategoryId) o;
if (stock != null ? !stock.equals(that.stock) : that.stock != null) return false;
if (category != null ? !category.equals(that.category) : that.category != null)
return false;
return true;
}
public int hashCode() {
int result;
result = (stock != null ? stock.hashCode() : 0);
result = 31 * result + (category != null ? category.hashCode() : 0);
return result;
}
}
File : Category.java
package com.mkyong.stock;
import java.util.HashSet;
import java.util.Set;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import static javax.persistence.GenerationType.IDENTITY;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;
@Entity
@Table(name = "category", catalog = "mkyongdb")
public class Category implements java.io.Serializable {
private Integer categoryId;
private String name;
private String desc;
private Set<StockCategory> stockCategories = new HashSet<StockCategory>(0);
public Category() {
}
public Category(String name, String desc) {
this.name = name;
this.desc = desc;
}
public Category(String name, String desc, Set<StockCategory> stockCategories) {
this.name = name;
this.desc = desc;
this.stockCategories = stockCategories;
}
@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "CATEGORY_ID", unique = true, nullable = false)
public Integer getCategoryId() {
return this.categoryId;
}
public void setCategoryId(Integer categoryId) {
this.categoryId = categoryId;
}
@Column(name = "NAME", nullable = false, length = 10)
public String getName() {
return this.name;
}
public void setName(String name) {
this.name = name;
}
@Column(name = "[DESC]", nullable = false)
public String getDesc() {
return this.desc;
}
public void setDesc(String desc) {
this.desc = desc;
}
@OneToMany(fetch = FetchType.LAZY, mappedBy = "pk.category")
public Set<StockCategory> getStockCategories() {
return this.stockCategories;
}
public void setStockCategories(Set<StockCategory> stockCategories) {
this.stockCategories = stockCategories;
}
}
Done, the many to many relationship should be working now.
4. Run it – Case 1
For a new category and a new stock.
session.beginTransaction();
Stock stock = new Stock();
stock.setStockCode("7052");
stock.setStockName("PADINI");
Category category1 = new Category("CONSUMER", "CONSUMER COMPANY");
//new category, need save to get the id first
session.save(category1);
StockCategory stockCategory = new StockCategory();
stockCategory.setStock(stock);
stockCategory.setCategory(category1);
stockCategory.setCreatedDate(new Date()); //extra column
stockCategory.setCreatedBy("system"); //extra column
stock.getStockCategories().add(stockCategory);
session.save(stock);
session.getTransaction().commit();
Output…
Hibernate:
insert
into
mkyongdb.category
(`DESC`, NAME)
values
(?, ?)
Hibernate:
insert
into
mkyongdb.stock
(STOCK_CODE, STOCK_NAME)
values
(?, ?)
Hibernate:
select
stockcateg_.CATEGORY_ID,
stockcateg_.STOCK_ID,
stockcateg_.CREATED_BY as CREATED1_2_,
stockcateg_.CREATED_DATE as CREATED2_2_
from
mkyongdb.stock_category stockcateg_
where
stockcateg_.CATEGORY_ID=?
and stockcateg_.STOCK_ID=?
Hibernate:
insert
into
mkyongdb.stock_category
(CREATED_BY, CREATED_DATE, CATEGORY_ID, STOCK_ID)
values
(?, ?, ?, ?)
5. Run it – Case 2
Get an existing category and a new stock.
session.beginTransaction();
Stock stock = new Stock();
stock.setStockCode("7052");
stock.setStockName("PADINI");
//assume category id is 7
Category category1 = (Category)session.get(Category.class, 7);
StockCategory stockCategory = new StockCategory();
stockCategory.setStock(stock);
stockCategory.setCategory(category1);
stockCategory.setCreatedDate(new Date()); //extra column
stockCategory.setCreatedBy("system"); //extra column
stock.getStockCategories().add(stockCategory);
session.save(stock);
session.getTransaction().commit();
Output…
Hibernate:
select
category0_.CATEGORY_ID as CATEGORY1_1_0_,
category0_.`DESC` as DESC2_1_0_,
category0_.NAME as NAME1_0_
from
mkyongdb.category category0_
where
category0_.CATEGORY_ID=?
Hibernate:
insert
into
mkyongdb.stock
(STOCK_CODE, STOCK_NAME)
values
(?, ?)
Hibernate:
select
stockcateg_.CATEGORY_ID,
stockcateg_.STOCK_ID,
stockcateg_.CREATED_BY as CREATED1_2_,
stockcateg_.CREATED_DATE as CREATED2_2_
from
mkyongdb.stock_category stockcateg_
where
stockcateg_.CATEGORY_ID=?
and stockcateg_.STOCK_ID=?
Hibernate:
insert
into
mkyongdb.stock_category
(CREATED_BY, CREATED_DATE, CATEGORY_ID, STOCK_ID)
values
(?, ?, ?, ?)
Done.
There is a simpler way to implement this.
reference : http://forum.springsource.org/showthread.php?126461-How-to-realize-a-many-to-many-relation-type-with-attributes
Although the example is using spring roo however it is perfectly applicable to regular JPA entity.
Here is the example:
Join entity needs the following annotation to ensure that there are not two entries with the same composite (not primary) key (human, qualification)
In Human and Qualification there must be “orphanRemoval=true” to delete corresponding join entities if a Human or Qualification is deleted:
Hope this simplifies the solution
isn’t the job of jpa/hibernate is to make us focus on object oriented programming rather than thinking in solving relational model problem? because this solution is relational model oriented.
Hi I am getting below compile time error.
Persistent type of override attribute “pk.stock” cannot be resolved
Persistent type of override attribute “pk.catagory” cannot be resolved
I am using JPA,jdk7,eclipse mars2…. Please help me out.
Everything works until i add @Audited annotation. Seems that Envers can’t process “mapped by” attribute.
Caused by: org.hibernate.MappingException: Unable to read the mapped by attribute for stockCategories in com.mkyong.stock.StockCategory!
I’m using Hibernate and Envers 4.3.7.Final.
Have you found the solution for this issue?
Good tutorial.
I have a prblem when I try to save more records . I get the following error:
org.hibernate.NonUniqueObjectException: A different object with the same identifier value was already associated with the session :
This is my code:
public void addContacto(Proyecto proyecto, Contacto contacto, String tipo) {
Session session = sessionFactory.openSession();
Transaction tx = null;
try{
tx = session.beginTransaction();
ContactoProyecto contactoProyecto = new ContactoProyecto();
Proyecto p = (Proyecto)session.get(Proyecto.class, proyecto.getId());
Contacto c = (Contacto)session.get(Contacto.class, contacto.getId());
contactoProyecto.setContacto(c);
contactoProyecto.setProyecto(p);
contactoProyecto.setTipo(tipo);
p.getContactoProyectos().add(contactoProyecto);
session.saveOrUpdate(p);
tx.commit();
}catch (HibernateException e) {
if (tx!=null) tx.rollback();
e.printStackTrace();
}finally {
session.close();
}
}
Hi, thanks for the tutorial, was really helpful
Hello Sir,
When i am trying to execute <stock.getStockCategories()> statement then i am getting following error:
ClassName=o.h.e.loading.internal.LoadContexts Message=HHH000100: Fail-safe cleanup (collections) : org.hibernate.engine.loading.internal.CollectionLoadContext@6c1548c0<rs=HikariProxyResultSet@1324915780 wrapping oracle.jdbc.driver.ForwardOnlyResultSet@7ab2d65e>
can you please help me here please
Getting following error
Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: NULL not allowed for column “STOCK_ID”; SQL statement:
Hi, thanks for your example. I have a problem I want to add unique constraint on table stockCategory on two fields ” pk.stock and pk.category” when I create this IN StockCategory table I give this error:
Unable to create unique key constraint (STOCK_ID, CATEGORY_ID) on table StockCategory : database column ‘STOCK_ID’, ‘CATEGORY_ID’ not found. Make sure that you use the correct column name which depends on the naming strategy in use (it may not be the same as the property name in the entity, especially for relational type
You should have explained the code at least!
Hi,
Nice explanation, good article.
However, when I am saving the parent entity it is throwing
org.springframework.dao.DataIntegrityViolationException: A different object with the same identifier value was already associated with the session.
My use case is pretty much same,
Category table already has records and trying to update the references from Stock and Category Set with references to create mapping.
Problems faced:-
1. That above exception.
2. orphanRemoval=true is also not working.
3. update and delete to the stock_category doesn’t work
Anybody who faced the same problem as mine? Please help me out.
Coool Article! Thanks for sharing your knowledge 🙂
mkyong, thank you for this tutorial. Can you tell me if there is a way to use hibernate ‘criteria’ to query with conditions on the ‘middle’ table. So for example I want to get the stock for a particular category that has been created by a certain person?
Awesome tutorial! Unfortunately, I’m having trouble updating and/or deleting entries. Can someone please provide some examples of how to delete and update?
how to run if both table have data (stock and stok category)
How can we get list of categories of a stock or stocks of a category?
What is the reason why you did not use two many-to-one’s in the junction class? So you neither have to touch Student nor Class
The solution looks overly complicated.
If I try to convert the objects to json using Jackson, I either get org.codehaus.jackson.map.JsonMappingException: failed to lazily initialize a collection of role: com.mkyong.stock.Stock.stockCategories or if I fetch EAGER, I get infinite recursion exception. Do you know a solution to this? I tried @JsonIgnore, @JsonManagedReference, and @JsonIdentityInfo but none work.
In this situations, you need to avoid using bidirectional relation – that is what is creating infinite recursion.
For example : Instead of
@OneToMany(mappedBy=”property_in_other_Entity”)
private Set columns;
…you could use
@javax.persistence.Transient
private Set columns;
Good Tutorial.
I have a problem when recording multiple records.
I get the error
org.hibernate.NonUniqueObjectException:
My code of transaction is:
Session session = sessionFactory.openSession();
Transaction tx = null;
try{
tx = session.beginTransaction();
ContactoProyecto contactoProyecto = new ContactoProyecto();
Proyecto p = (Proyecto)session.get(Proyecto.class, proyecto.getId());
Contacto c = (Contacto)session.get(Contacto.class, contacto.getId());
contactoProyecto.setContacto(c);
contactoProyecto.setProyecto(p);
contactoProyecto.setTipo(tipo);
p.getContactoProyectos().add(contactoProyecto);
session.saveOrUpdate(p);
tx.commit();
}catch (HibernateException e) {
if (tx!=null) tx.rollback();
e.printStackTrace();
}finally {
session.close();
}
Dear Sir Mykong – you have solved a problem for us! I have a chocolate bar for you here!
Twix, Mars bar, snickers, other brands are available.
Hi Sir,
what about an existing stock and category ?
Great tutorial!!!
I’m having difficulty getting this approach (or the one that used @IdClass) working when the entities are audited with Envers. Any thoughts?
Thks dude!!!
You rock Dude, thanks a lot for sharing your knowledge.
I followed the tutorial and works fine.
Hey, thank’s for this example, how to do this with xml mapping ?
thank’s
Is it possible to get all StockCategory objects for Category of consumer (new Category(“CONSUMER”, “CONSUMER COMPANY”)) using Criteria API .
Obviously it should contain the corresponding Stock Objects.
Nice Tutorial and I want to know the use of equals() and hash() function in StockCatagory and StockCatagoryId class
Thank you, u saved my life =D
It works. But when I try to create another association, table stock_market for example, when I already has the table stock_category the hibernate blows up and give lots of errors and doesn’t give any clues to solve the problem.
The Hibernate create the tables but don’t allow me to add data in the table stock_market.