Spring Boot Hibernate inserts Data into wrong Columns in Postgres-CodePudding

Initial Situation: I have these two entities, GroceriesList and Product.

GroceriesList: A GroceriesList can have several products.

@Entity
@Table(name = "lists")
public class GroceriesList {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "list_id")
    private Long listId;

    @Column(name = "list_name")
    private String listName;


    @ManyToMany(cascade = CascadeType.MERGE)
    @JoinTable(name = "lists_products",
            joinColumns = @JoinColumn(name = "product_id", referencedColumnName = "list_id"),
            inverseJoinColumns = @JoinColumn(name = "list_id", referencedColumnName = "product_id")
    )
    private Set<Product> products;

}

Products: A Product can be allocated to several GroceriesLists

@Entity
@Table(name = "products")
public class Product {

    public enum Category {
        Dairy,
        Fruit,
        Vegetable,
        Meat,
        Grains
    }

    // Product ID Column, GenerationType.Identity refers to auto incr in Postgres
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "product_id")
    private Long productId;

    @Column(name = "product_name")
    private String productName;

    @Column(name = "product_vendor")
    private String productVendor;

    @Enumerated(EnumType.STRING)
    @Column(name = "product_category")
    private Category productCategory;

    @Column(name = "product_storedquantity")
    private Integer productStoredQuantity;

    @JsonIgnore
    @ManyToMany(mappedBy = "products")
    private List<GroceriesList> groceriesLists;
}

The entities and relationships are stored in three different tables in Postgres(One for Lists, one for products, and one mapping table for the relationships).

The mapping table: Mapping Table "lists_products"

A sample Product: Sample Product with Id=4

The problem: I'm trying to create new lists, and that works. However, as you can spot from the Mapping Table Image, Hibernate inserts the IDs into the wrong columns. The list_id in the mapping table is currently getting the product_id, and the product_id in the mapping table is getting the list_id. I have tried to change the order of column names & references in the @JoinTable annotation in the GroceriesList Entity. However, that throws a mapping Error. I assume my error lies somewhere in that annotation. Interestingly, Hibernate uses the correct SQL-Query. What am I missing here?

CodePudding user response：

There is a typo in @JoinTable, the @JoinColumn names should be chosen after the Entity it references to avoid confusion:

@ManyToMany(cascade = CascadeType.MERGE)
@JoinTable(name = "lists_products",
        joinColumns = @JoinColumn(name = "list", // <- not "product"
           referencedColumnName = "list_id"),
        inverseJoinColumns = @JoinColumn(name = "product", // <- not "list"
           referencedColumnName = "product_id")
)
private Set<Product> products;

In which case do you use the JPA @JoinTable annotation?
https://docs.jboss.org/hibernate/jpa/2.1/api/javax/persistence/JoinTable.html

CodePudding user response：

After consulting fladdimir's solution, it gave me an idea and I solved my issue. The problem lied within the @JoinColumn annotation, where I thought the "name = ..." property refers to the column name in the database.

@ManyToMany(cascade = CascadeType.MERGE)
@JoinTable(name = "lists_products",
        joinColumns = @JoinColumn(name = "product_id", referencedColumnName = "list_id"),
        inverseJoinColumns = @JoinColumn(name = "list_id", referencedColumnName = "product_id")
)
private Set<Product> products;

However, that property refers to the declared variable inside the Entity in Spring Boot, i.e. listId

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "list_id")
private Long listId;

So, the working implementation of the @JoinTable Annotation should look like the following, where "name = ..." uses that variable name:

@ManyToMany(cascade = CascadeType.MERGE)
@JoinTable(name = "lists_products",
        joinColumns = @JoinColumn(name = "listId", referencedColumnName = "list_id"),
        inverseJoinColumns = @JoinColumn(name = "productId", referencedColumnName = "product_id")
)
private Set<Product> products;