June 3, 2012

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.

...

Please fill the form - I will response as fast as I can!