How Do You Know Many Columns to Use in a Scientific Data Table

Introduction

For a simple many-to-many database human relationship, y'all tin use the @ManyToMany JPA annotation and, therefore, hide the bring together table.

However, sometimes y'all need more than than the 2 Foreign Primal columns in the join tabular array, and, for this purpose, you need to replace the @ManyToMany association with two bidirectional @OneToMany associations. Unlike unidirectional @OneToMany, the bidirectional relationship is the all-time way to map a one-to-many database relationship that requires a drove of Child elements on the parent side

In this article, we are going to see how you tin can map a many-to-many database human relationship using an intermediary entity for the join table. This manner, nosotros can map boosted columns that would be otherwise impossible to persist using the @ManyToMany JPA notation.

Domain Model

Assuming we take the following database tables:

The first thing nosotros need is to map the composite Main Key which belongs to the intermediary join tabular array. As explained in this article, nosotros 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")     individual 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 truthful;          if (o == zip || getClass() != o.getClass())              return false;          PostTagId that = (PostTagId) o;         return Objects.equals(postId, that.postId) &&                 Objects.equals(tagId, that.tagId);     }      @Override     public int hashCode() {         render Objects.hash(postId, tagId);     } }        

In that location are two very important aspects to take into consideration when mapping an @Embeddable blended identifier:

1. You need the @Embeddable blazon to exist Serializable
2. The @Embeddable type must override the default equals and hashCode methods based on the two Chief Key identifier values.

Next, nosotros need to map the join table using a dedicated entity:

@Entity(proper noun = "PostTag") @Table(name = "post_tag") public class PostTag {      @EmbeddedId     private PostTagId id;      @ManyToOne(fetch = FetchType.LAZY)     @MapsId("postId")     individual Postal service mail service;      @ManyToOne(fetch = FetchType.LAZY)     @MapsId("tagId")     private Tag tag;      @Cavalcade(name = "created_on")     private Appointment createdOn = new Date();      private PostTag() {}      public PostTag(Mail postal service, 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() {         render Objects.hash(post, tag);     } }        

The Tag entity is going to map the @OneToMany side for the tag attribute in the PostTag bring together entity:

@Entity(name = "Tag") @Table(name = "tag") @NaturalIdCache @Enshroud(     usage = CacheConcurrencyStrategy.READ_WRITE ) public class Tag {      @Id     @GeneratedValue     individual Long id;      @NaturalId     private Cord name;      @OneToMany(         mappedBy = "tag",         cascade = CascadeType.ALL,         orphanRemoval = true     )     individual List<PostTag> posts = new ArrayList<>();      public Tag() {     }      public Tag(String name) {         this.name = proper name;     }      //Getters and setters omitted for brevity      @Override     public boolean equals(Object o) {         if (this == o) return true;         if (o == null || getClass() != o.getClass()) render false;         Tag tag = (Tag) o;         render Objects.equals(name, tag.name);     }      @Override     public int hashCode() {         return Objects.hash(name);     } }        

The Tag entity is marked with the following Hibernate-specific annotations:

1. The @NaturalId annotation allows us to fetch the Tag entity by its business organization primal.
2. The @Enshroud notation marks the cache concurrency strategy.
3. The @NaturalIdCache tells Hibernate to enshroud the entity identifier associated with a given business key.

For more details about the @NaturalId and @NaturalIdCache annotations, bank check out this article.

With these annotations in place, we can fetch the Tag entity without needing to striking the database.

And the Mail 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 = truthful     )     private List<PostTag> tags = new ArrayList<>();      public Post() {     }      public Mail(String title) {         this.title = championship;     }      //Getters and setters omitted for brevity      public void addTag(Tag tag) {         PostTag postTag = new PostTag(this, tag);         tags.add(postTag);         tag.getPosts().add together(postTag);     }      public void removeTag(Tag tag) {         for (Iterator<PostTag> iterator = tags.iterator();               iterator.hasNext(); ) {             PostTag postTag = iterator.side by side();              if (postTag.getPost().equals(this) &&                     postTag.getTag().equals(tag)) {                 iterator.remove();                 postTag.getTag().getPosts().remove(postTag);                 postTag.setPost(null);                 postTag.setTag(cypher);             }         }     }      @Override     public boolean equals(Object o) {         if (this == o) render true;          if (o == null || getClass() != o.getClass())              return imitation;          Post post = (Post) o;         return Objects.equals(championship, post.title);     }      @Override     public int hashCode() {         return Objects.hash(title);     } }        

Discover 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 exist prepare from the Tag entity considering the users operate with Postal service entities.

To amend visualize the entity relationships, bank check out the following diagram:

Testing time

Showtime, let's persist some Tag entities which nosotros'll later on associate to a Post:

Tag misc = new Tag("Misc"); Tag jdbc = new Tag("JDBC"); Tag hide = 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 2 Post entities:

Session session = entityManager     .unwrap( Session.grade );  Tag misc = session     .bySimpleNaturalId(Tag.class)     .load( "Misc" );  Tag jdbc = session     .bySimpleNaturalId(Tag.form)     .load( "JDBC" );  Tag hibernate = session     .bySimpleNaturalId(Tag.class)     .load( "Hibernate" );  Tag jooq = session     .bySimpleNaturalId(Tag.course)     .load( "jOOQ" );  Postal service hpjp1 = new Post(     "Loftier-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 Postal service(     "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 (championship, id)  VALUES ('High-Performance Coffee Persistence 1st edition', i)  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, iii)  INSERT INTO post_tag (created_on, post_id, tag_id)  VALUES ('2017-07-26 thirteen:14:08.99', one, 4)  INSERT INTO post_tag (created_on, post_id, tag_id)  VALUES ('2017-07-26 thirteen:14:08.99', 1, i)  INSERT INTO postal service (title, id)  VALUES ('High-Operation 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:fourteen:08.992', 2, 2)        

Now, since the Misc Tag entity was added by fault, nosotros tin can remove information technology as follows:

Tag misc = entityManager.unwrap( Session.class )     .bySimpleNaturalId(Tag.grade)     .load( "Misc" );  Post post = entityManager.createQuery(     "select p " +     "from Post p " +     "join fetch p.tags pt " +     "bring together fetch pt.tag " +     "where p.id = :postId", Post.class) .setParameter( "postId", 1L ) .getSingleResult();  postal service.removeTag( misc );        

Hibernate generating the following SQL statements:

SELECT p.id Equally 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 Every bit title2_0_0_,        p_t.post_id Every bit post_id2_1_0__,        p_t.created_on AS created_1_1_0__,        p_t.tag_id Every bit 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 Equally tag_id3_1_0_,        p_t.created_on Every bit 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 Equally 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 2nd SELECT query is needed by this line in the removeTag utility method:

postTag.getTag().getPosts().remove(postTag);        

Yet, if you don't need to navigate all Post entities associated to a Tag, you can remove the posts collection from the Tag entity and this secondary SELECT argument volition not exist executed anymore.

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 form Tag {      @Id     @GeneratedValue     private Long id;      @NaturalId     individual String name;      public Tag() {     }      public Tag(String proper noun) {         this.name = proper noun;     }      //Getters omitted for brevity      @Override     public boolean equals(Object o) {         if (this == o) return true;          if (o == zippo || getClass() != o.getClass())              return false;          Tag tag = (Tag) o;         return Objects.equals(name, tag.name);     }      @Override     public int hashCode() {         return Objects.hash(proper name);     } }        

The PostTag entity and its PostTagId @Embeddable are identical with the previous instance.

However, the Post entity addTag and removeTag are simplified every bit 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(zero);             postTag.setTag(null);         }     } }        

The rest of the Post entity is the same as with the previous example as seen in the post-obit diagram:

Inserting the PostTag entities is going to render the same SQL statements as seen before.

But when removing the PostTag entity, Hide is going to execute a single SELECT query equally well as a unmarried DELETE statement:

SELECT p.id As id1_0_0_,        p_t.created_on AS created_1_1_1_,        p_t.post_id Every bit post_id2_1_1_,        p_t.tag_id Equally tag_id3_1_1_,        t.id As id1_2_2_,        p.title AS title2_0_0_,        p_t.post_id Equally post_id2_1_0__,        p_t.created_on AS created_1_1_0__,        p_t.tag_id As tag_id3_1_0__,        t.proper noun As name2_2_2_ FROM   postal service p INNER Bring together         post_tag p_t ON p.id = p_t.post_id INNER Bring together         tag t ON p_t.tag_id = t.id WHERE  p.id = one  DELETE  FROM   post_tag  WHERE  post_id = 1 AND tag_id = ane        

I'm running an online workshop on the 4th of May about SQL Window Functions.

If y'all enjoyed this article, I bet you are going to love my Book and Video Courses besides.

Conclusion

While mapping the many-to-many database relationship using the @ManyToMany annotation is undoubtedly simpler, when y'all need to persist extra columns in the join table, you need to map the join tabular array as a dedicated entity.

Although a little bit more work, the association works but equally its @ManyToMany counterpart, and this time we can Listing collections without worrying about SQL statement functioning bug.

When mapping the intermediary join table, it'south better to map only one side equally a bidirectional @OneToMany clan since otherwise a second SELECT statement will be issued while removing the intermediary bring together entity.

pittsyousba.blogspot.com

Source: https://vladmihalcea.com/the-best-way-to-map-a-many-to-many-association-with-extra-columns-when-using-jpa-and-hibernate/

Share this post