How to create a namedquery of manytomany entity?
Question by Khushbu Joshi
Brand
public class Brand implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "BrandID", nullable = false)
private Integer brandID;
@Basic(optional = false)
@Column(name = "BrandName", nullable = false, length = 100)
private String brandName;
@Basic(optional = false)
@Column(name = "Description", nullable = false, length = 1000)
private String description;
@Column(name = "Is_Visible")
private Boolean isVisible;
@JoinTable(name = "brandcategory", joinColumns = {
@JoinColumn(name = "BrandID", referencedColumnName = "BrandID")}, inverseJoinColumns = {
@JoinColumn(name = "CategoryID", referencedColumnName = "CategoryID")})
@ManyToMany(fetch = FetchType.EAGER)
private Collection<Category> categoryCollection;
@OneToMany(mappedBy = "brand", fetch = FetchType.EAGER)
private Collection<Product> productCollection;
I want to retrive the Brand IDs from table brandcategory whoes categoryID = :categoryID
how can i createnamed query for it in entity brand?
this does not work:
@NamedQuery(name = "Brand.getBrandListByCategory",
query = "SELECT b FROM Brand b WHERE b.brandID =
(SELECT bc.brandID
FROM b.brandctegory bc
WHERE bc.category.categoryID = :categoryID)")
Answer by JB Nizet
If I understand correctly, you want all the brands belonging to a category. Why don’t you simply make the association bidirectional. You could then just do:
Category category = em.find(Category.class, categoryId);
return category.getBrands();
If it’s unidirectional, then you’ll need a query, but it’s much simpler that the one you tried:
select b from Brand b inner join b.categoryCollection category
where category.id = :categoryId;
Your query doesn’t make sense: it uses a non-existing association (b.brandcategory). Remember that JPQL uses entities, their persistent fields and associations to other entities. And nothing else. Tables don’t exist in JPQL.
Answer by Starx
AFAIK, you cant go out of a entity boundary, when creating queries in entity class.
Instead use .createNativeQuery() method of the entity manager, to create complex and mixed queries.
