In database design, many-to-many associations are used very frequently. Typically, a join table is created to connect two primary tables in order to make the association. The following entity relationship diagram illustrates a typical many-to-many association:

Here, we can see that the join table users_groups has only two fields (user_id and group_id) that refer to two primary keys of the main tables groups and users. This couple of fields is also called as a composite primary key. But what if we want to add some extra fields into the join table, e.g. activated and registered_date? Supposing the above diagram is updated as follows:

In the tutorial Hibernate Many-to-Many Association Annotations Example, we discussed a simple solution but it doesn’t cover the case with extra fields in the join table. Therefore, this tutorial is a supplement with solutions for such case.

We are going to introduce two different solutions to cope with the additional fields of the join table in the above relationship diagram. But both have the same key point: creating a separate entity class for the join table. Now, let’s walk through the first solution.

NOTE:This tutorial focuses on how to design the solutions, so we omit redundant sections like Maven dependency, Hibernate configuration, implement equals() or hashCode().

 

1. Solution #1: Using a Separate Primary Key for the Join Table

In this solution, we create a separate primary key field for the join table, instead of using the two foreign keys as a composite primary key as usual. The following table relationship diagram illustrates the database designed in this solution:

We recommend this solution because it is simple and using the separate primary key in the join table makes things easier.

MySQL Script:

The following MySQL script is used to create the above tables:

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(45) NOT NULL,
  `password` varchar(45) NOT NULL,
  `email` varchar(45) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1$$


CREATE TABLE `groups` (
  `group_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1$$


CREATE TABLE `users_groups` (
  `user_group_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  `activated` tinyint(1) NOT NULL,
  `registered_date` datetime NOT NULL,
  PRIMARY KEY (`user_group_id`),
  KEY `fk_user` (`user_id`),
  KEY `fk_group` (`group_id`),
  CONSTRAINT `fk_group` FOREIGN KEY (`group_id`) REFERENCES `groups` (`group_id`),
  CONSTRAINT `fk_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
)

 

Mapping the Users Table:

Write the following model class (User.java) to map the Users table:

package net.codejava.hibernate;

import java.util.HashSet;
import java.util.Set;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

@Entity
@Table(name = "USERS")
public class User {
	private long id;
	private String username;
	private String password;
	private String email;	

	private Set<UserGroup> userGroups = new HashSet<UserGroup>();

	public User() {
	}

	public User(String username, String password, String email) {
		this.username = username;
		this.password = password;
		this.email = email;
	}
	
	public void addGroup(UserGroup group) {
		this.userGroups.add(group);
	}

	@Id
	@GeneratedValue
	@Column(name = "USER_ID")
	public long getId() {
		return id;
	}

	public void setId(long id) {
		this.id = id;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	@OneToMany(mappedBy = "user")
	public Set<UserGroup> getUserGroups() {
		return userGroups;
	}

	public void setUserGroups(Set<UserGroup> groups) {
		this.userGroups = groups;
	}
	
	public void addUserGroup(UserGroup userGroup) {
		this.userGroups.add(userGroup);
	}	
}

In this table class, we use the @OneToMany annotation to specify a one-to-many association from the Users table to the Users_Groups table (code of the model class UserGroup will be described shortly):

private Set<UserGroup> userGroups = new HashSet<UserGroup>();

@OneToMany(mappedBy = "user")
public Set<UserGroup> getUserGroups() {
	return userGroups;
}

 

Mapping the Groups Table:

Code for the model class that maps the table Groups is very similar to the Users table. Here’s code of the Group.java file:

package net.codejava.hibernate;

import java.util.HashSet;
import java.util.Set;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

@Entity
@Table(name = "GROUPS")
public class Group {
	private long id;
	private String name;

	private Set<UserGroup> userGroups = new HashSet<UserGroup>();
	
	public Group() {
	}

	public Group(String name) {
		this.name = name;
	}
		
	@Id
	@GeneratedValue
	@Column(name = "GROUP_ID")
	public long getId() {
		return id;
	}

	public void setId(long id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	@OneToMany(mappedBy = "group")
	public Set<UserGroup> getUserGroups() {
		return userGroups;
	}

	public void setUserGroups(Set<UserGroup> groups) {
		this.userGroups = groups;
	}
	
	public void addUserGroup(UserGroup userGroup) {
		this.userGroups.add(userGroup);
	}

}

 

Mapping the Join Table (Users_Groups):

Mapping the join table is the most important task because it specifies how the many-to-many association is formed. The following code is of the UserGroup.java:

package net.codejava.hibernate;

import java.util.Date;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

@Entity
@Table(name = "USERS_GROUPS")
public class UserGroup {
	private long id;
	private User user;
	private Group group;
	
	// additional fields
	private boolean activated;
	private Date registeredDate;

	@Id
	@GeneratedValue
	@Column(name = "USER_GROUP_ID")	
	public long getId() {
		return id;
	}

	public void setId(long id) {
		this.id = id;
	}

	@ManyToOne(cascade = CascadeType.ALL)
	@JoinColumn(name = "USER_ID")	
	public User getUser() {
		return user;
	}

	public void setUser(User user) {
		this.user = user;
	}

	@ManyToOne(cascade = CascadeType.ALL)
	@JoinColumn(name = "GROUP_ID")
	public Group getGroup() {
		return group;
	}

	public void setGroup(Group group) {
		this.group = group;
	}

	public boolean isActivated() {
		return activated;
	}

	public void setActivated(boolean activated) {
		this.activated = activated;
	}

	@Column(name = "REGISTERED_DATE")
	@Temporal(TemporalType.DATE)
	public Date getRegisteredDate() {
		return registeredDate;
	}

	public void setRegisteredDate(Date registeredDate) {
		this.registeredDate = registeredDate;
	}
}

Here we use the @ManyToOne annotation to specify the many-to-one association from the join table to the Users table:

private User user;

@ManyToOne(cascade = CascadeType.ALL)
@JoinColumn(name = "USER_ID")
public User getUser() {
	return user;
}

And for the many-to-one association from the join table to the Groups table:

private Group group;

@ManyToOne(cascade = CascadeType.ALL)
@JoinColumn(name = "GROUP_ID")
public Group getGroup() {
	return group;
}

And mapping for the extra fields (activated and registered_date) is done just like usual.

 

Writing Test Code:

Here are some code snippets to test the many-to-many association we have mapped, assuming that the sessionis a Hibernate’s Session object.

Saving an association with all new entities (User, Group and UserGroup):

User user = new User("tommy", "ymmot", "tommy@gmail.com");
Group group = new Group("Coders");

UserGroup userGroup = new UserGroup();
userGroup.setGroup(group);
userGroup.setUser(user);
userGroup.setActivated(true);
userGroup.setRegisteredDate(new Date());

session.save(userGroup);

Saving a new UserGroup with existing User and Group entities:

// this user is obtained from the database with ID 34
User user = (User) session.get(User.class, new Long(34));

// this group is obtained from the database with ID 17
Group group = (Group) session.get(Group.class, new Long(17));

UserGroup userGroup = new UserGroup();
userGroup.setGroup(group);
userGroup.setUser(user);
userGroup.setActivated(true);
userGroup.setRegisteredDate(new Date());

session.save(userGroup);

Deleting an existing association is trivial, for example:

UserGroup userGroup = new UserGroup();
userGroup.setId(3);
session.delete(userGroup);

For further experiment, you can download the sample project (HibernateMany2ManyExtraColumnsSolution1.zip) under the Attachments section.

To see this solution in action, watch the following video:

 

2. Solution #2: Using Composite Key

In this solution, we don’t create a separate primary key in the join table. Instead we treat the user_id and group_id fields as a composite primary key. To recall, the following entity relationship diagram illustrates this many-to-many association:

Now, let’s dive into the details to see how this second solution differs from the first one.

MySQL Script:

The following script is used to create the database tables:

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(45) NOT NULL,
  `password` varchar(45) NOT NULL,
  `email` varchar(45) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1$$


CREATE TABLE `groups` (
  `group_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1$$


CREATE TABLE `users_groups` (
  `user_id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  `activated` tinyint(1) NOT NULL,
  `registered_date` datetime NOT NULL,
  KEY `fk_user` (`user_id`),
  KEY `fk_group` (`group_id`),
  CONSTRAINT `fk_group` FOREIGN KEY (`group_id`) REFERENCES `groups` (`group_id`),
  CONSTRAINT `fk_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) 

 

Creating a Composite-ID class for the composite key:

Create a class named UserGroupId.java with the following code:

package net.codejava.hibernate;

import java.io.Serializable;

import javax.persistence.CascadeType;
import javax.persistence.Embeddable;
import javax.persistence.ManyToOne;

@Embeddable
public class UserGroupId implements Serializable {	
	private User user;
	private Group group;

	@ManyToOne(cascade = CascadeType.ALL)
	public User getUser() {
		return user;
	}

	public void setUser(User user) {
		this.user = user;
	}

	@ManyToOne(cascade = CascadeType.ALL)
	public Group getGroup() {
		return group;
	}

	public void setGroup(Group group) {
		this.group = group;
	}
}

This class is very simple, as it specifies two many-to-one associations with the Users and Groups tables. Note that the @Embeddable annotation is used so this class can be embedded in other entities.

 

Mapping the Users Table:

Here’s code of the User model class (User.java) for mapping with the Users table:

package net.codejava.hibernate;

import java.util.HashSet;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

@Entity
@Table(name = "USERS")
public class User {
	private long id;
	private String username;
	private String password;
	private String email;	

	private Set<UserGroup> userGroups = new HashSet<UserGroup>();

	public User() {
	}

	public User(String username, String password, String email) {
		this.username = username;
		this.password = password;
		this.email = email;
	}
	
	public void addGroup(UserGroup group) {
		this.userGroups.add(group);
	}

	@Id
	@GeneratedValue
	@Column(name = "USER_ID")
	public long getId() {
		return id;
	}

	public void setId(long id) {
		this.id = id;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	@OneToMany(mappedBy = "primaryKey.user", 
			cascade = CascadeType.ALL)
	public Set<UserGroup> getUserGroups() {
		return userGroups;
	}

	public void setUserGroups(Set<UserGroup> groups) {
		this.userGroups = groups;
	}
	
	public void addUserGroup(UserGroup userGroup) {
		this.userGroups.add(userGroup);
	}	
}

This is very similar to the model class in the first solution. However, the difference is in configuration of the @OneToMany annotation:

@OneToMany(mappedBy = "primaryKey.user",
		cascade = CascadeType.ALL)
public Set<UserGroup> getUserGroups() {
	return userGroups;
}

Note that the cascade attribute is required in this case.

  

Mapping the Groups Table:

Here’s code of the Group model class (Group.java) for mapping with the Groups table:

package net.codejava.hibernate;

import java.util.HashSet;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

@Entity
@Table(name = "GROUPS")
public class Group {
	private long id;
	private String name;

	private Set<UserGroup> userGroups = new HashSet<UserGroup>();
	
	public Group() {
	}

	public Group(String name) {
		this.name = name;
	}
		
	@Id
	@GeneratedValue
	@Column(name = "GROUP_ID")
	public long getId() {
		return id;
	}

	public void setId(long id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	@OneToMany(mappedBy = "primaryKey.group",
			cascade = CascadeType.ALL)
	public Set<UserGroup> getUserGroups() {
		return userGroups;
	}

	public void setUserGroups(Set<UserGroup> groups) {
		this.userGroups = groups;
	}
	
	public void addUserGroup(UserGroup userGroup) {
		this.userGroups.add(userGroup);
	}
}

This is very similar to the User model class.

 

Mapping the Join Table (Users_Groups):

Because we use a composite-ID class (UserGroupId) so mapping for the join table is quite different than the first solution. Here’s the code of the UserGroup.java file:

package net.codejava.hibernate;

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 = "USERS_GROUPS")
@AssociationOverrides({
	@AssociationOverride(name = "primaryKey.user", 
		joinColumns = @JoinColumn(name = "USER_ID")),
	@AssociationOverride(name = "primaryKey.group", 
		joinColumns = @JoinColumn(name = "GROUP_ID")) })
public class UserGroup {
	// composite-id key
	private UserGroupId primaryKey = new UserGroupId();
	
	// additional fields
	private boolean activated;
	private Date registeredDate;

	@EmbeddedId
	public UserGroupId getPrimaryKey() {
		return primaryKey;
	}

	public void setPrimaryKey(UserGroupId primaryKey) {
		this.primaryKey = primaryKey;
	}

	@Transient
	public User getUser() {
		return getPrimaryKey().getUser();
	}

	public void setUser(User user) {
		getPrimaryKey().setUser(user);
	}

	@Transient
	public Group getGroup() {
		return getPrimaryKey().getGroup();
	}

	public void setGroup(Group group) {
		getPrimaryKey().setGroup(group);
	}

	public boolean isActivated() {
		return activated;
	}

	public void setActivated(boolean activated) {
		this.activated = activated;
	}

	@Column(name = "REGISTERED_DATE")
	@Temporal(TemporalType.DATE)
	public Date getRegisteredDate() {
		return registeredDate;
	}

	public void setRegisteredDate(Date registeredDate) {
		this.registeredDate = registeredDate;
	}	
}

Here are some noteworthy points regarding this mapping:

• The @EmbeddedId annotation is used for embedding a composite-id class as the primary key of this mapping.
• Because of an @EmbeddedId is used, we must use the @AssociationOverrides annotation in order to override the mappings for the user and group attributes of the composite key.
• We mark getters of the user and group properties with the @Transient annotation so that Hibernate doesn’t try to map these getters. These getters are provided for convenience in case we want to obtain a specific side of the relationship.

And the additional fields of the join table are mapped as usual.

 

Writing Test Code:

Assuming that the sessionis a Hibernate’s Session object, here are some code snippets for testing the many-to-many association we have mapped:

Saving an association with all new entities (User, Group and UserGroup):

User user = new User("sam", "mas", "sam@gmail.com");

Group group = new Group("Designer");
session.save(group);

UserGroup userGroup = new UserGroup();
userGroup.setGroup(group);
userGroup.setUser(user);
userGroup.setActivated(true);
userGroup.setRegisteredDate(new Date());

user.addUserGroup(userGroup);

session.save(user);

 

Saving a new UserGroup with existing User and Group entities:

// this user is obtained from the database with ID 40
User user = (User) session.get(User.class, new Long(40));

// this group is obtained from the database with ID 26
Group group = (Group) session.get(Group.class, new Long(26));

UserGroup userGroup = new UserGroup();
userGroup.setGroup(group);
userGroup.setUser(user);
userGroup.setActivated(true);
userGroup.setRegisteredDate(new Date());

session.save(userGroup);

 

Deleting an existing association:

UserGroup userGroup = new UserGroup();

User user = new User();
user.setId(39);
userGroup.setUser(user);

Group group = new Group();
group.setId(25);
userGroup.setGroup(group);

session.delete(userGroup);

For further experiment, you can download the sample project (HibernateMany2ManyExtraColumnsSolution2.zip) under the Attachments section.

 

Related Hibernate Many-to-Many Tutorials:

• Hibernate Many-to-Many Association Annotations Example
• Hibernate Many-to-Many XML Mapping Example

 

Other Hibernate Tutorials:

• Java Hibernate JPA Annotations Tutorial for Beginners
• Hibernate One-to-One Association on Primary Key Annotations Example
• Hibernate One-to-Many Association Annotations Example
• Hibernate Enum Type Mapping Example
• Hibernate Binary Data and BLOB Mapping Example
• Hibernate Query Language (HQL) Example
• Java Hibernate Reverse Engineering Tutorial with Eclipse and MySQL
• Hibernate Basics - 3 ways to delete an entity from the datastore