The best way to map a JPA and Hibernate many-to-many association with extra columns
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
Introduction
For a simple many-to-many database relationship, you can use the @ManyToMany
JPA annotation and, therefore, hide the join table.
However, sometimes you need more than the two Foreign Key columns in the join table, and, for this purpose, you need to replace the @ManyToMany
association with two bidirectional @OneToMany
associations. Unlike unidirectional @OneToMany
, the bidirectional relationship is the best way to map a one-to-many database relationship that requires a collection of Child elements on the parent side
In this article, we are going to see how you can map a many-to-many database relationship using an intermediary entity for the join table. This way, we can map additional columns that would be otherwise impossible to persist using the @ManyToMany
JPA annotation.
Domain Model
Assuming we have the following database tables:
The first thing we need is to map the composite Primary Key which belongs to the intermediary join table. As explained in this article, we need an @Embeddable
type to hold the composite entity identifier:
@Embeddable public class PostTagId implements Serializable { @Column(name = "post_id") private Long postId; @Column(name = "tag_id") private Long tagId; private PostTagId() {} public PostTagId( Long postId, Long tagId) { this.postId = postId; this.tagId = tagId; } //Getters omitted for brevity @Override public boolean equals(Object o) { if (this == o) return true; if (o == null || getClass() != o.getClass()) return false; PostTagId that = (PostTagId) o; return Objects.equals(postId, that.postId) && Objects.equals(tagId, that.tagId); } @Override public int hashCode() { return Objects.hash(postId, tagId); } }
There are two very important aspects to take into consideration when mapping an @Embeddable
composite identifier:
- You need the
@Embeddable
type to beSerializable
- The
@Embeddable
type must override the default equals and hashCode methods based on the two Primary Key identifier values.
Next, we need to map the join table using a dedicated entity:
@Entity(name = "PostTag") @Table(name = "post_tag") public class PostTag { @EmbeddedId private PostTagId id; @ManyToOne(fetch = FetchType.LAZY) @MapsId("postId") private Post post; @ManyToOne(fetch = FetchType.LAZY) @MapsId("tagId") private Tag tag; @Column(name = "created_on") private Date createdOn = new Date(); private PostTag() {} public PostTag(Post post, Tag tag) { this.post = post; this.tag = tag; this.id = new PostTagId(post.getId(), tag.getId()); } //Getters and setters omitted for brevity @Override public boolean equals(Object o) { if (this == o) return true; if (o == null || getClass() != o.getClass()) return false; PostTag that = (PostTag) o; return Objects.equals(post, that.post) && Objects.equals(tag, that.tag); } @Override public int hashCode() { return Objects.hash(post, tag); } }
The Tag
entity is going to map the @OneToMany
side for the tag
attribute in the PostTag
join entity:
@Entity(name = "Tag") @Table(name = "tag") @NaturalIdCache @Cache( usage = CacheConcurrencyStrategy.READ_WRITE ) public class Tag { @Id @GeneratedValue private Long id; @NaturalId private String name; @OneToMany( mappedBy = "tag", cascade = CascadeType.ALL, orphanRemoval = true ) private List<PostTag> posts = new ArrayList<>(); public Tag() { } public Tag(String name) { this.name = name; } //Getters and setters omitted for brevity @Override public boolean equals(Object o) { if (this == o) return true; if (o == null || getClass() != o.getClass()) return false; Tag tag = (Tag) o; return Objects.equals(name, tag.name); } @Override public int hashCode() { return Objects.hash(name); } }
The Tag
entity is marked with the following Hibernate-specific annotations:
- The
@NaturalId
annotation allows us to fetch theTag
entity by its business key. - The
@Cache
annotation marks the cache concurrency strategy. - The
@NaturalIdCache
tells Hibernate to cache the entity identifier associated with a given business key.
For more details about the
@NaturalId
and@NaturalIdCache
annotations, check out this article.
With these annotations in place, we can fetch the Tag
entity without needing to hit the database.
And the Post
entity is going to map the @OneToMany
side for the post
attribute in the PostTag
join entity:
@Entity(name = "Post") @Table(name = "post") public class Post { @Id @GeneratedValue private Long id; private String title; @OneToMany( mappedBy = "post", cascade = CascadeType.ALL, orphanRemoval = true ) private List<PostTag> tags = new ArrayList<>(); public Post() { } public Post(String title) { this.title = title; } //Getters and setters omitted for brevity public void addTag(Tag tag) { PostTag postTag = new PostTag(this, tag); tags.add(postTag); tag.getPosts().add(postTag); } public void removeTag(Tag tag) { for (Iterator<PostTag> iterator = tags.iterator(); iterator.hasNext(); ) { PostTag postTag = iterator.next(); if (postTag.getPost().equals(this) && postTag.getTag().equals(tag)) { iterator.remove(); postTag.getTag().getPosts().remove(postTag); postTag.setPost(null); postTag.setTag(null); } } } @Override public boolean equals(Object o) { if (this == o) return true; if (!(o instanceof Post)) return false; return id != null && id.equals(((Post) o).getId()); } @Override public int hashCode() { return getClass().hashCode(); } }
Notice that the Post
entity features the addTag
and removeTag
utility methods which are needed by every bidirectional association so that all sides of the association stay in sync.
While we could have added the same add/remove methods to the Tag
entity, it’s unlikely that these associations will be set from the Tag
entity because the users operate with Post
entities.
To better visualize the entity relationships, check out the following diagram:
Testing time
First, let’s persist some Tag
entities which we’ll later associate to a Post
:
Tag misc = new Tag("Misc"); Tag jdbc = new Tag("JDBC"); Tag hibernate = new Tag("Hibernate"); Tag jooq = new Tag("jOOQ"); doInJPA(entityManager -> { entityManager.persist( misc ); entityManager.persist( jdbc ); entityManager.persist( hibernate ); entityManager.persist( jooq ); });
Now, when we persist two Post
entities:
Session session = entityManager .unwrap( Session.class ); Tag misc = session .bySimpleNaturalId(Tag.class) .load( "Misc" ); Tag jdbc = session .bySimpleNaturalId(Tag.class) .load( "JDBC" ); Tag hibernate = session .bySimpleNaturalId(Tag.class) .load( "Hibernate" ); Tag jooq = session .bySimpleNaturalId(Tag.class) .load( "jOOQ" ); Post hpjp1 = new Post( "High-Performance Java Persistence 1st edition" ); hpjp1.setId(1L); hpjp1.addTag(jdbc); hpjp1.addTag(hibernate); hpjp1.addTag(jooq); hpjp1.addTag(misc); entityManager.persist(hpjp1); Post hpjp2 = new Post( "High-Performance Java Persistence 2nd edition" ); hpjp2.setId(2L); hpjp2.addTag(jdbc); hpjp2.addTag(hibernate); hpjp2.addTag(jooq); entityManager.persist(hpjp2);
Hibernate generates the following SQL statements:
INSERT INTO post (title, id) VALUES ('High-Performance Java Persistence 1st edition', 1) INSERT INTO post_tag (created_on, post_id, tag_id) VALUES ('2017-07-26 13:14:08.988', 1, 2) INSERT INTO post_tag (created_on, post_id, tag_id) VALUES ('2017-07-26 13:14:08.989', 1, 3) INSERT INTO post_tag (created_on, post_id, tag_id) VALUES ('2017-07-26 13:14:08.99', 1, 4) INSERT INTO post_tag (created_on, post_id, tag_id) VALUES ('2017-07-26 13:14:08.99', 1, 1) INSERT INTO post (title, id) VALUES ('High-Performance Java Persistence 2nd edition', 2) INSERT INTO post_tag (created_on, post_id, tag_id) VALUES ('2017-07-26 13:14:08.992', 2, 3) INSERT INTO post_tag (created_on, post_id, tag_id) VALUES ('2017-07-26 13:14:08.992', 2, 4) INSERT INTO post_tag (created_on, post_id, tag_id) VALUES ('2017-07-26 13:14:08.992', 2, 2)
Now, since the Misc
Tag
entity was added by mistake, we can remove it as follows:
Tag misc = entityManager.unwrap( Session.class ) .bySimpleNaturalId(Tag.class) .load( "Misc" ); Post post = entityManager.createQuery( "select p " + "from Post p " + "join fetch p.tags pt " + "join fetch pt.tag " + "where p.id = :postId", Post.class) .setParameter( "postId", 1L ) .getSingleResult(); post.removeTag( misc );
Hibernate generates the following SQL statements:
SELECT p.id AS id1_0_0_, p_t.created_on AS created_1_1_1_, p_t.post_id AS post_id2_1_1_, p_t.tag_id AS tag_id3_1_1_, t.id AS id1_2_2_, p.title AS title2_0_0_, p_t.post_id AS post_id2_1_0__, p_t.created_on AS created_1_1_0__, p_t.tag_id AS tag_id3_1_0__, t.name AS name2_2_2_ FROM post p INNER JOIN post_tag p_t ON p.id = p_t.post_id INNER JOIN tag t ON p_t.tag_id = t.id WHERE p.id = 1 SELECT p_t.tag_id AS tag_id3_1_0_, p_t.created_on AS created_1_1_0_, p_t.post_id AS post_id2_1_0_, p_t.created_on AS created_1_1_1_, p_t.post_id AS post_id2_1_1_, p_t.tag_id AS tag_id3_1_1_ FROM post_tag p_t WHERE p_t.tag_id = 1 DELETE FROM post_tag WHERE post_id = 1 AND tag_id = 1
The second SELECT query is needed by this line in the removeTag
utility method:
postTag.getTag().getPosts().remove(postTag);
However, if you don’t need to navigate all Post
entities associated with a Tag
, you can remove the posts
collection from the Tag
entity and this secondary SELECT statement will not be executed anymore.
YouTube Video
I also published a YouTube video about the @ManyToMany
association with extra columns, so enjoy watching it if you’re interested in this topic.
Using a single-side bidirectional association
The Tag
entity will not map the PostTag
@OneToMany
bidirectional association anymore.
@Entity(name = "Tag") @Table(name = "tag") @NaturalIdCache @Cache(usage = CacheConcurrencyStrategy.READ_WRITE) public class Tag { @Id @GeneratedValue private Long id; @NaturalId private String name; public Tag() { } public Tag(String name) { this.name = name; } //Getters omitted for brevity @Override public boolean equals(Object o) { if (this == o) return true; if (o == null || getClass() != o.getClass()) return false; Tag tag = (Tag) o; return Objects.equals(name, tag.name); } @Override public int hashCode() { return Objects.hash(name); } }
The PostTag
entity and its PostTagId
@Embeddable
are identical to the previous example.
However, the Post
entity addTag
and removeTag
are simplified as follows:
public void addTag(Tag tag) { PostTag postTag = new PostTag(this, tag); tags.add(postTag); } public void removeTag(Tag tag) { for (Iterator<PostTag> iterator = tags.iterator(); iterator.hasNext(); ) { PostTag postTag = iterator.next(); if (postTag.getPost().equals(this) && postTag.getTag().equals(tag)) { iterator.remove(); postTag.setPost(null); postTag.setTag(null); } } }
The rest of the Post
entity is the same as in the previous example, as seen in the following diagram:
Inserting the PostTag
entities is going to render the same SQL statements as seen before.
But when removing the PostTag
entity, Hibernate is going to execute a single SELECT query as well as a single DELETE statement:
SELECT p.id AS id1_0_0_, p_t.created_on AS created_1_1_1_, p_t.post_id AS post_id2_1_1_, p_t.tag_id AS tag_id3_1_1_, t.id AS id1_2_2_, p.title AS title2_0_0_, p_t.post_id AS post_id2_1_0__, p_t.created_on AS created_1_1_0__, p_t.tag_id AS tag_id3_1_0__, t.name AS name2_2_2_ FROM post p INNER JOIN post_tag p_t ON p.id = p_t.post_id INNER JOIN tag t ON p_t.tag_id = t.id WHERE p.id = 1 DELETE FROM post_tag WHERE post_id = 1 AND tag_id = 1
I'm running an online workshop on the 11th of October about High-Performance SQL.If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
While mapping the many-to-many database relationship using the @ManyToMany
annotation is undoubtedly simpler, when you need to persist extra columns in the join table, you need to map the join table as a dedicated entity.
Although a little bit more work, the association works just as its @ManyToMany
counterpart, and this time we can List
collections without worrying about SQL statement performance issues.
When mapping the intermediary join table, it’s better to map only one side as a bidirectional @OneToMany
association since otherwise, a second SELECT statement will be issued while removing the intermediary join entity.
