Spring Boot Hibernate inserts Data into wrong Columns in Postgres

Question

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?

score 0 · Answer 1 · answered Dec 14 '21 at 15:33

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;

Then it is very strange that my solution is working. Could it be the case that hibernate inserts an underscore between "list" and "Id" and lowercases the "I" in "listId"? Like I mentioned, your solution does not work, as postgres throws an unrecognized column error. — PumpingSalad, Dec 14 '21 at 16:55
No, I used the one DB I had already created. But I'll give it a try with a new DB and see what happens. Thanks a lot for your help though, very appreciated! — PumpingSalad, Dec 14 '21 at 20:07

Spring Boot Hibernate inserts Data into wrong Columns in Postgres

1 Answers1