JPA ManyToMany fails with "must have same number of columns as the referenced primary key"

user_17346592 0
Viewed 275

I have three tables customer, product and sales. sales is the join table to store the customer's products as shown below: customer, product and Sales tables

My Entities defined as described below:

Customer.java

@Entity
@Table(name="customer")
public class Customer {
    @Id
    @Column(name="c_id")
    private String customerId;
    
    @Column(name="customer_name")
    private String customerName;
    
    @ManyToMany
    @JoinTable(
            name = "sale",
            joinColumns = @JoinColumn(name = "c_id"),
            inverseJoinColumns = @JoinColumn(name = "p_id"))
    private Set<Product> customerProducts = new HashSet<>();
}

Product.java

@Entity
@Table(name="product")
public class Product {
    @Id
    @Column(name="p_id")
    private String productId;
    
    @Column(name="product_name")
    private String productName;
    
    @Column(name="price")
    private Double price;

    // ... Setters & Getters
}

Sales.java

@Entity
@Table(name="sales")
public class Sales {
    @EmbeddedId
    private SalesPK salesId;
    
    @Column(name="qty")
    private Long qty;

    // ... Setters & Getters
}

SalesPK.java

@Embeddable
public class SalesPK implements Serializable {
    @Column(name = "c_id")
    private String customerId;
    @Column(name = "p_id")
    private String productId;
    public SalesPK() {}

    public SalesPK(String customerId, String productId) {
        this.customerId = customerId;
        this.productId = productId;
    }
}

CustomerRepository.java

@Repository
public interface CustomerRepository extends CrudRepository<Customer, String> {
    @Query("select customer from Customer customer " +
            "left join fetch customer.customerProducts " +
            "where customer.customerName = :customerName")
    public Customer getCustomerPurchasedProducts(String customerName);
}

My Spring boot application fail to start with following exception:

org.hibernate.MappingException: Foreign key (FK7wwx8x75009xqb1y0tawm8rty:SALES [p_id])) must have same number of columns as the referenced primary key (SALES [c_id,p_id])

What am I missing here? I have followed the notes as described here in https://www.baeldung.com/jpa-many-to-many

UPDATE:

There is no issue with above solution, I have misspelled "sales" table in @ManyToMany declaration changing from "sale" to "sales" fixed the issue. Strange behavior why it didn't compline about missing table instead it complain about actual composite primary key definition.

Following code Fixed the issue:

    @ManyToMany
    @JoinTable(
            name = "sales",
            joinColumns = @JoinColumn(name = "c_id"),
            inverseJoinColumns = @JoinColumn(name = "p_id"))
    private Set<Product> customerProducts = new HashSet<>();
}
spring-data spring-data-jpa spring-boot jpa hibernate
1 Answers

I would map these classes a bit differently:

@Entity
@Table(name="customer")
public class Customer {
    @Id
    @Column(name="c_id")
    private String customerId;
    
    @Column(name="customer_name")
    private String customerName;
    
    @OneToMany(mappedBy = "customer")
    private Set<Sale> customerSales = new HashSet<>();
}


@Entity
@Table(name="product")
public class Product {
    @Id
    @Column(name="p_id")
    private String productId;
    
    @Column(name="product_name")
    private String productName;
    
    @Column(name="price")
    private Double price;
}


@Entity
@Table(name="sales")
public class Sales {
    @EmbeddedId
    private SalesPK salesId;
    
    @MapsId("customerId") // maps customerId attribute of embedded id
    @ManyToOne
    Customer customer;
    
    @MapsId("productId") // maps productId attribute of embedded id
    @ManyToOne
    Product product;
    
    @Column(name="qty")
    private Long qty;

    // ... Setters & Getters
}


@Embeddable
public class SalesPK implements Serializable {
    @Column(name = "c_id")
    private String customerId;

    @Column(name = "p_id")
    private String productId;

    public SalesPK() {}

    public SalesPK(String customerId, String productId) {
        this.customerId = customerId;
        this.productId = productId;
    }
}
user_15663450
Related