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
Tags :
Founder of Mkyong.com and HostingCompass.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 making a donation to this charity, thanks.

Related Posts

Popular Posts