HQL many to many query
Solution 1
If you need to retrieve products, you need to do a query that select
Product entity, not Category.
So:
return all the products by previosly selected category object with id
You need to do:
Query query = entityManager.createQuery("SELECT p FROM Product p
JOIN p.categories c
WHERE c.id = :idCategory");
query.setParameter("idCategory", category.getId());
You use LEFT JOIN
but this is not necessary in your case, because the unique condition of your query is find a category with a specific ID. This condition will ignore the LEFT
part of the JOIN
, forcing always a JOIN
.
Solution 2
I think there are some errors in your code:
You
SELECT c
which meanCategory
but you cast the result list toList<Product>
, should beSELECT c.products
Your
WHERE c.category = :category
clause is not correct because you don't have anycategory
attribute in your Category class, should beWHERE c.id = :id
andquery.setParameter("id", category.getId());
Hope that help.
user202822
Updated on June 04, 2022Comments
-
user202822 almost 2 years
I have two tables with many to many relations. products(id, description, price,image)----> products_category(idProducts, category _id)----> category(id, category_name).
Here is my enteties:
1. Products
@Entity @Table(name = "products") public class Products implements Serializable { @Id @GeneratedValue(strategy = GenerationType.AUTO) @Column(name = "idProducts") private long id; @Column(name = "description") private String description; @Column(name = "price") private String price; @Column(name = "image") private byte [] image; public Products() { } public Products(String description, String price, byte[] image) {} @ManyToMany @JoinTable( name = "category_products", joinColumns ={@JoinColumn (name = "Products_idProducts", referencedColumnName = "idProducts")}, inverseJoinColumns = {@JoinColumn(name = "category_id", referencedColumnName = "id")} ) List<Category> categories = new ArrayList<>(); @ManyToMany @JoinTable( name = "users_product", joinColumns ={@JoinColumn (name = "Products_idProducts", referencedColumnName = "idProducts")}, inverseJoinColumns = {@JoinColumn(name = "users_id", referencedColumnName = "id")} ) List<Users> usersList = new ArrayList<>();
2.Category
@Entity @Table(name = "category") public class Category { @Id @Column(name = "id") private long id; public Category() { } public Category(String category_name) { this.category_name = category_name; } @Column (name = "category_name") private String category_name; @ManyToMany(mappedBy = "categories") private List<Products> products = new ArrayList<>();
I'm try to write query for controller, which return all the products by previosly selected category object with id? i tried many query, but all throws exceptions.
public List<Products> list (Category category) { //category - object with needed id Query query; query = entityManager.createQuery("SELECT c FROM Category c left join c.categories WHERE c.category = :category", Products.class); query.setParameter("category", category); return (List<Products>) query.getResultList(); } java.lang.IllegalArgumentException: org.hibernate.QueryException: could not resolve property: categories of: classes.Category [SELECT c FROM classes.Category c join c.categories WHERE c.category = :category] org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1750) org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677) org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1683)