Prevent duplicate entries in a join table in a many-to-many relationship in JPA

Tiny

I'm using EclipseLink 2.5.1 (and Hibernate 4.3.5 final). Given the following tables in MySQL.

  • product
  • prod_colour (join table)
  • colour

There is a many-to-many relationship between products and their colours.

A product can have many colours and a colour can in turn be associated with many products. This relationship is expressed in the database by these tables.

The prod_colour table has two reference columns prod_id and colour_id from its related parent tables product and colour respectively.

As obvious, the entity class Product has a list of colours - java.util.List<Colour> which is named colourList.

The entity class Colour has a list of products - java.util.List<Product> which is named productList.


The relationship in the Colour entity :

public class Colour implements Serializable {

    @JoinTable(name = "prod_colour", joinColumns = {
        @JoinColumn(name = "colour_id", referencedColumnName = "prod_id")}, inverseJoinColumns = {
        @JoinColumn(name = "prod_id", referencedColumnName = "colour_id")})
    @ManyToMany(mappedBy = "colourList", fetch = FetchType.LAZY)
    private List<Product> productList; //Getter and setter.

    //---Utility methods---

    //Add rows to the prod_colour table.
    public void addToProduct(Product product) {
        this.getProductList().add(product);
        product.getColourList().add(this);
    }

    //Delete rows from the prod_colour table.
    public void removeFromProduct(Product product) {
        this.getProductList().remove(product);
        product.getColourList().remove(this);
    }
}

The relationship in the Product entity :

public class Product implements Serializable {

    @JoinTable(name = "prod_colour", joinColumns = {
        @JoinColumn(name = "prod_id", referencedColumnName = "prod_id")}, inverseJoinColumns = {
        @JoinColumn(name = "colour_id", referencedColumnName = "colour_id")})
    @ManyToMany(fetch = FetchType.LAZY)
    private List<Colour> colourList; //Getter and setter.
}

From the associated EJB, the insert operation is performed as follows.

@Override
@SuppressWarnings("unchecked")
public boolean insert(List<Colour> colours, Product product)
{
    int i=0;
    Long prodId=product.getProdId();
    for(Colour colour:colours)
    {
        Product p = entityManager.getReference(Product.class, prodId);
        colour.addToProduct(p);

        if(++i%49==0)
        {
            entityManager.flush();
        }
        entityManager.merge(colour);
        entityManager.merge(p);
    }
    return true;
}

Everything works fine.


When duplicate rows are attempted (same Colour entities associated with the same Product entity), they are also inserted into the prod_colour table that I expect should not happen.

Do I need to perform some extra conditional checks to avoid duplicate insertion or EclipseLink/Hibernate have some mechanism to prevent duplicates in such situations?

Peter Wroblewski

It's also surprising for me. I always was thinking that it made reference columns in join table as composite primary key, but it does not. If you want to have unique records try use Set instead of List or create your own ManyToMany relationship table with composite primary key(color_id, product_id). I don't have better idea.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Prevent duplicate entries in a join table in a many-to-many relationship in JPA

From Dev

Duplicate entries in EF7 join table for many-to-many relationship

From Dev

No new entry in join table of many to many relationship JPA

From Dev

Entity Framework duplicate entries in many to many relationship

From Dev

Rails: many to many relationship join table design

From Dev

JPA/Hibernate: Map many-to-many relationship when join table has own primary key

From Dev

How to get all entries on a table by many to many relationship

From Dev

How to get all entries on a table by many to many relationship

From Dev

Get the "many" entries of a relationship

From Dev

JPA - how to prevent an unnecessary join while querying many to many relationships

From Dev

Many to Many relationship JPA, EJB and new table in db

From Dev

Django many to many relationship with join table-specific fields

From Dev

Core Data Join Table Many to Many Relationship - Object Creation

From Dev

Why create join table to make many to many relationship in Rails 3

From Dev

EF6 Many to Many relationship, multiple tables to join table

From Dev

Hibernate one to many relationship with join table with addition columns in join table

From Dev

merge in jpa many-to-many with extra column in join table

From Dev

merge in jpa many-to-many with extra column in join table

From Dev

Recursive relationship on a many to many table

From Dev

SQL many to many relationship table

From Dev

Many to many relationship in the same table?

From Dev

Junction table/many to many relationship

From Dev

Duplicate entries in one to many relation

From Dev

SQLAlchemy Many To Many relationship returning duplicate children

From Dev

Prevent duplicates in the database in a many-to-many relationship

From Dev

One to many relationship table

From Dev

Many to Many relation with join table

From Dev

JPA criteria query in a many-to-many relationship

From Dev

JPA, NetBeans and Many to Many relationship to self

Related Related

  1. 1

    Prevent duplicate entries in a join table in a many-to-many relationship in JPA

  2. 2

    Duplicate entries in EF7 join table for many-to-many relationship

  3. 3

    No new entry in join table of many to many relationship JPA

  4. 4

    Entity Framework duplicate entries in many to many relationship

  5. 5

    Rails: many to many relationship join table design

  6. 6

    JPA/Hibernate: Map many-to-many relationship when join table has own primary key

  7. 7

    How to get all entries on a table by many to many relationship

  8. 8

    How to get all entries on a table by many to many relationship

  9. 9

    Get the "many" entries of a relationship

  10. 10

    JPA - how to prevent an unnecessary join while querying many to many relationships

  11. 11

    Many to Many relationship JPA, EJB and new table in db

  12. 12

    Django many to many relationship with join table-specific fields

  13. 13

    Core Data Join Table Many to Many Relationship - Object Creation

  14. 14

    Why create join table to make many to many relationship in Rails 3

  15. 15

    EF6 Many to Many relationship, multiple tables to join table

  16. 16

    Hibernate one to many relationship with join table with addition columns in join table

  17. 17

    merge in jpa many-to-many with extra column in join table

  18. 18

    merge in jpa many-to-many with extra column in join table

  19. 19

    Recursive relationship on a many to many table

  20. 20

    SQL many to many relationship table

  21. 21

    Many to many relationship in the same table?

  22. 22

    Junction table/many to many relationship

  23. 23

    Duplicate entries in one to many relation

  24. 24

    SQLAlchemy Many To Many relationship returning duplicate children

  25. 25

    Prevent duplicates in the database in a many-to-many relationship

  26. 26

    One to many relationship table

  27. 27

    Many to Many relation with join table

  28. 28

    JPA criteria query in a many-to-many relationship

  29. 29

    JPA, NetBeans and Many to Many relationship to self

HotTag

Archive