How to Map a Many-To-Many Relationship with One Extra Column in JPA and Hibernate

In the previous articles Part 1 & Part 2, we have decomposed a monolithic Spring Boot application into microservices using Spring Cloud. In this article, we are gonna see how to add an extra column to an intermediary join table used for mapping a many-to-many relationship with JPA and Hibernate.

Introduction

Every many-to-many association has two sides, the owning side, and the non-owning, or inverse side. The join table is specified on the owning side. If the association is bidirectional, either side may be designated as the owning side. If the relationship is bidirectional, the non-owning side must use the mappedBy element of the ManyToMany annotation to specify the relationship field or property of the owning side.

Bidirectional vs Unidirectional association

A bi-directional relationship provides navigational access in both directions either from the parent or from the child’s side while a uni-directional relationship provides navigation on one end only.

Real World ManyToMany Association

One typical example of the many-to-many relationship is the User & Role relationship, where a user can be associated with many roles, and a role can be associated with many users.

In our earlier articles, we used to define the user & role association as a bi-directional many-to-many association to the Role in User entity.

@ManyToMany
@JoinTable(name = "user_role", joinColumns = {@JoinColumn(name = "USER_ID")}, inverseJoinColumns = {@JoinColumn(name = "ROLE_ID")})
private Set<Role> roles;

And, a bi-directional many-to-many association to the User in the Role entity.

@ManyToMany(mappedBy = "roles")
private Set<User> users;

For this many-to-many relationship, hibernate will create an intermediate table called user_role with columns user_id and role_id. In some scenarios, you may want to add an extra column to this intermediary join table.

For instance, let’s assume that we want to implement the soft delete functionality for the User entity which has a many-to-many association with the role entity. In this case, we should also implement that for the intermediate user_role table. Hence, we should add an extra column deleted to both the user & user_role intermediate join table as shown in the ER diagram below.

Domain Model

User_Role Domain Model (ER) Diagram

Defining Domain Model

Now, let’s see how we can define this mapping in the existing entities that we have created earlier.

Modifying Role Entity

The first thing we are gonna do is to remove the bi-directional many-to-many association to the User from the Role entity since we don’t need to navigate all the User entities associated to a Role.

Role.java

package com.javachinna.model;

import java.io.Serializable;
import java.util.Objects;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

/**
 * The persistent class for the role database table.
 * 
 */
@Entity
@Getter
@Setter
@NoArgsConstructor
public class Role implements Serializable {
	private static final long serialVersionUID = 1L;
	public static final String USER = "USER";
	public static final String ROLE_USER = "ROLE_USER";
	public static final String ROLE_ADMIN = "ROLE_ADMIN";
	public static final String ROLE_MODERATOR = "ROLE_MODERATOR";
	public static final String ROLE_PRE_VERIFICATION_USER = "ROLE_PRE_VERIFICATION_USER";

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Long id;

	private String name;

	public Role(String name) {
		this.name = name;
	}

	@Override
	public int hashCode() {
		return Objects.hash(name);
	}

	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		Role other = (Role) obj;
		return Objects.equals(name, other.name);
	}

	@Override
	public String toString() {
		final StringBuilder builder = new StringBuilder();
		builder.append("Role [name=").append(name).append("]").append("[id=").append(id).append("]");
		return builder.toString();
	}
}

Mapping Join Table

Creating Embeddable Type

Now, we are gonna create an embeddable type using @Embeddable annotation to map the composite primary key with columns USER_ID and ROLE_ID in the intermediary join table.

UserRolePK.java

package com.javachinna.model;
import java.io.Serializable;
import java.util.Objects;

import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.ManyToOne;
import javax.persistence.MapsId;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@Entity
@Getter
@Setter
@NoArgsConstructor
public class UserRole implements Serializable {

	private static final long serialVersionUID = 1L;

	/**
	 * @param id
	 * @param user
	 * @param role
	 */
	public UserRole(User user, Role role) {
		this.id = new UserRolePK(user.getId(), role.getId());
		this.role = role;
		this.user = user;
	}

	@EmbeddedId
	private UserRolePK id;

	@ManyToOne(fetch = FetchType.LAZY)
	@MapsId("userId")
	private User user;

	@ManyToOne(fetch = FetchType.LAZY)
	@MapsId("roleId")
	private Role role;

	protected boolean deleted;

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.lang.Object#hashCode()
	 */
	@Override
	public int hashCode() {
		return Objects.hash(role, user);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.lang.Object#equals(java.lang.Object)
	 */
	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		UserRole other = (UserRole) obj;
		return Objects.equals(role, other.role) && Objects.equals(user, other.user);
	}

}

Creating UserRole Entity

Now, we need to map the join table using the dedicated UserRole entity which will embed the UserRolePK type.

@EmbeddedId annotation is used to denote a composite primary key that is an embeddable class.

UserRole.java

package com.javachinna.model;
import java.io.Serializable;
import java.util.Objects;

import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.ManyToOne;
import javax.persistence.MapsId;

import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@Entity
@Getter
@Setter
@NoArgsConstructor
public class UserRole implements Serializable {

	private static final long serialVersionUID = 1L;

	/**
	 * @param id
	 * @param user
	 * @param role
	 */
	public UserRole(User user, Role role) {
		this.id = new UserRolePK(user.getId(), role.getRoleId());
		this.role = role;
		this.user = user;
	}

	@EmbeddedId
	private UserRolePK id;

	@ManyToOne(fetch = FetchType.LAZY)
	@MapsId("userId")
	private User user;

	@ManyToOne(fetch = FetchType.LAZY)
	@MapsId("roleId")
	private Role role;

	protected boolean deleted;

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.lang.Object#hashCode()
	 */
	@Override
	public int hashCode() {
		return Objects.hash(role, user);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.lang.Object#equals(java.lang.Object)
	 */
	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		UserRole other = (UserRole) obj;
		return Objects.equals(role, other.role) && Objects.equals(user, other.user);
	}

}

Modifying User Entity

Now, in the User entity, we are gonna map the @OneToMany side for the user attribute in the UserRole join entity:

User.java

@CreationTimestamp annotation marks a property as the creation timestamp of the containing entity. The property value will be set to the current VM date exactly once when saving the owning entity for the first time.

package com.javachinna.model;

import java.io.Serializable;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

import org.hibernate.annotations.CreationTimestamp;

import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

/**
 * The persistent class for the user database table.
 * 
 */
@Entity
@NoArgsConstructor
@Getter
@Setter
public class User implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 65981149772133526L;

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Long id;

	@Column(name = "PROVIDER_USER_ID")
	private String providerUserId;

	private String email;

	@Column(name = "enabled", columnDefinition = "BIT", length = 1)
	private boolean enabled;

	@Column(name = "DISPLAY_NAME")
	private String displayName;

	@Column(name = "created_date", nullable = false, updatable = false)
	@CreationTimestamp
	@Temporal(TemporalType.TIMESTAMP)
	protected Date createdDate;

	@Temporal(TemporalType.TIMESTAMP)
	protected Date modifiedDate;

	private String password;

	private String provider;

	@Column(name = "USING_2FA")
	private boolean using2FA;

	private String secret;

	private boolean deleted;
	
	@OneToMany(mappedBy = "user", cascade = CascadeType.ALL, orphanRemoval = true)
	private Set<UserRole> roles = new HashSet<>();
	
	public void addRole(Role role) {
		UserRole userRole = new UserRole(this, role);
		roles.add(userRole);
	}

	public void removeRole(Role role) {
		for (Iterator<UserRole> iterator = roles.iterator(); iterator.hasNext();) {
			UserRole userRole = iterator.next();

			if (userRole.getUser().equals(this) && userRole.getRole().equals(role)) {
				iterator.remove();
				userRole.setUser(null);
				userRole.setRole(null);
			}
		}
	}
}

The addRole and removeRole utility methods are used to add/remove roles to the user as shown below. However, these methods are not required on the Role entity since we operate on the User entities and these associations will not be set from the Role entity.

// Add a role to the user
user.addRole(roleRepository.findByName(Role.ROLE_USER));
userRepository.save(user);

// Remove a role from a user
user.removeRole(roleRepository.findByName(Role.ROLE_ADMIN));
userRepository.save(user);

Conclusion

That’s all folks. In this article, we have added an extra column to an intermediary join table. So that we can implement the soft delete functionality for the user entity in the following article.

Thank you for reading.

Leave a Reply