CODEWITHSUNDEEP
mysqlhibernatespring-bootjpa
Raymond Gitonga
Raymond Gitonga

Reputation: 171

Many to many Jpa Query Spring boot

I have three mysql tables with a many to many relationship and I am trying to make a Jpa Query on spring boot. The tables are

Product table has a many to many relationship with extra table, as a product can have many extras hence the need for product_extra table

Here is the query i would like to include in my project

SELECT extra.name 
FROM extra 
INNER JOIN product_extra ON extra_id = extra.id
WHERE product_id = ?;

Should i have like a @ManyToMany annotation and where should i have it

Upvotes: 0

Views: 834

Answers (1)

Ken Chan
Ken Chan

Reputation: 90447

Yes you should. Use @ManyToMany to map between Product and Extra.Make sure to use Set instead of List for the mapping in order to have better performance. It looks like:

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

    @ManyToMany(cascade = {CascadeType.PERSIST,CascadeType.MERGE})
    @JoinTable(name = "product_extra",
        joinColumns = @JoinColumn(name = "product_id"),
        inverseJoinColumns = @JoinColumn(name = "extra_id")
    )
    private Set<Extra> extras = new HashSet<>();

}

@Entity
@Table(name="extra")
public class Extra{

    @ManyToMany(mappedBy = "extras")
    private Set<Product> products = new HashSet<>();
}

After mapping them , you can then use JPQL to get a product by id together with its extra by : 

select p from Product p left join fetch p.extras where p.id = :productId;

Upvotes: 2

Related Questions