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 the Author

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

Comments

Leave a Reply

avatar
newest oldest most voted
ashokayengar
Guest
ashokayengar

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

Ajit
Guest
Ajit

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.

Htf
Guest
Htf

Coool Article! Thanks for sharing your knowledge :)

joff
Guest
joff

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.

Suemayah Eldursi
Guest
Suemayah Eldursi

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
Guest
Andrew Behrman

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
Guest
mantu

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

Mehrdad
Guest
Mehrdad

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

Tostis
Guest
Tostis

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.

Stefan Lobato
Guest
Stefan Lobato

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
Guest
Mikhail Batcer

The solution looks overly complicated.

Mark
Guest
Mark

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
Guest
moa

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
Guest
Elias Castaño
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(); } }
Elias Castaño
Guest
Elias Castaño

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
Guest
Kieran Brady

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

Steven Rowney
Guest
Steven Rowney

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

jose
Guest
jose

Hi, thanks for the tutorial, was really helpful

kamalben
Guest
kamalben

Hi Sir,
what about an existing stock and category ?

Patrick Demasco
Guest
Patrick Demasco

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
Guest
Danilo Domingos

Thks dude!!!

Bruno Gomes de Oliveira
Guest
Bruno Gomes de Oliveira

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

I followed the tutorial and works fine.

facilus
Guest
facilus

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

thank’s

Pradeep
Guest
Pradeep

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

Pradeep
Guest
Pradeep

Obviously it should contain the corresponding Stock Objects.

SiVi
Guest
SiVi

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

Grimmjow
Guest
Grimmjow

Thank you, u saved my life =D

gabriel
Guest
gabriel

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.

Priya
Guest
Priya

Is there any way I can have an explicit id field for stock_category table. Now the primary key is combination of stockid and categoryid.. So I am not able to put a new row with same stock and category but different date

ari
Guest
ari

i dont think people should be using a third class for this. there should be a stock and a category class and one or both can have the extra properties as well as a list of the other class

Anil
Guest
Anil

Nice Article. Find one more.

@ManyToMany in hibernate, creates the association between the entities in such a way that many entities can be associated with many entities. It will be clearer by example. More than one student is associated with more than one teacher and vice-versa. To achieve Many-To-Many association below annotation are used.

http://www.concretepage.com/hibernate/example-manytomany-hibernate

DavidT
Guest
DavidT
XML mapping would be good here too. I have a similar setup but see issues when fetching the join table with the two foreign keys. I have a user table, group table, and a userGroup table with an extra field type. When fetching the Set of userGroup in group table, I can get the actual group but only the user id and not the content. When fetching the Set of userGroup in user table, I can get the actual user but only the group id and not the content. Is anyone having this issue? Ideal is that when fetching the… Read more »
Abou Abdi Allah Java
Guest
Abou Abdi Allah Java

Please make an example of that with XML Mapping.