Main Tutorials

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“.

Note
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.

many to many diagram

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.

many to many project folder

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.

Reference

  1. Hibernate Mapping Documentation

About Author

author image
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

Subscribe
Notify of
146 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
ashokayengar
10 years ago

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)

@RooJavaBean
@RooToString
@RooJpaActiveRecord
@Table(uniqueConstraints=@UniqueConstraint(columnN ames={"human","qualification"}), name="myUniqueConstraint")
public class HumanQualification {

@ManyToOne
private Human human;

@ManyToOne
private Qualification qualification;

private Float weight;
}

In Human and Qualification there must be “orphanRemoval=true” to delete corresponding join entities if a Human or Qualification is deleted:

@OneToMany(cascade = CascadeType.ALL, mappedBy = "human", orphanRemoval=true)
private Set<HumanQualification> humanQualification = new HashSet<HumanQualification>();

Hope this simplifies the solution

joff
6 years ago

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.

Ajit
6 years ago

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.

Tostis
7 years ago

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.

Amol
2 years ago
Reply to  Tostis

Have you found the solution for this issue?

Elias Castaño
8 years ago

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();

}
}

jose
8 years ago

Hi, thanks for the tutorial, was really helpful

Tiru
1 year ago

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

Tiru
1 year ago

Getting following error
Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: NULL not allowed for column “STOCK_ID”; SQL statement:

soraya
3 years ago

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

ARAJ
4 years ago

You should have explained the code at least!

Rizwan
4 years ago

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.

Htf
6 years ago

Coool Article! Thanks for sharing your knowledge 🙂

Suemayah Eldursi
6 years ago

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?

Andrew Behrman
7 years ago

Awesome tutorial! Unfortunately, I’m having trouble updating and/or deleting entries. Can someone please provide some examples of how to delete and update?

mantu
7 years ago

how to run if both table have data (stock and stok category)

Mehrdad
7 years ago

How can we get list of categories of a stock or stocks of a category?

Stefan Lobato
7 years ago

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

Mikhail Batcer
8 years ago

The solution looks overly complicated.

Mark
8 years ago

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.

moa
7 years ago
Reply to  Mark

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;

Elias Castaño
8 years ago

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();

}

Kieran Brady
8 years ago

Dear Sir Mykong – you have solved a problem for us! I have a chocolate bar for you here!

Steven Rowney
8 years ago
Reply to  Kieran Brady

Twix, Mars bar, snickers, other brands are available.

kamalben
8 years ago

Hi Sir,
what about an existing stock and category ?

Patrick Demasco
8 years ago

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?

Danilo Domingos
9 years ago

Thks dude!!!

Bruno Gomes de Oliveira
9 years ago

You rock Dude, thanks a lot for sharing your knowledge.

I followed the tutorial and works fine.

facilus
9 years ago

Hey, thank’s for this example, how to do this with xml mapping ?

thank’s

Pradeep
9 years ago

Is it possible to get all StockCategory objects for Category of consumer (new Category(“CONSUMER”, “CONSUMER COMPANY”)) using Criteria API .

Pradeep
9 years ago
Reply to  Pradeep

Obviously it should contain the corresponding Stock Objects.

SiVi
9 years ago

Nice Tutorial and I want to know the use of equals() and hash() function in StockCatagory and StockCatagoryId class

Grimmjow
9 years ago

Thank you, u saved my life =D

gabriel
9 years ago

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.