HQL query for Many to Many Explict relationship

Question

I am trying to write hql for the below models.

Product.java

@Entity public class Product implements Serializable {

    private static final long serialVersionUID = -3532377236419382983L;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int productId;

    @NotEmpty(message = "The product name must not be empty")
    private String productName;

    private String productCategory;

    private String productDescription;

    @ManyToMany
    @JsonIgnore     @JoinTable(name="PRODUCT_SUBCATEGORY", 
                joinColumns={@JoinColumn(name="productId")}, 
                inverseJoinColumns={@JoinColumn(name="subCategoryId")})
    private Set<SubCategory> subCategory = new HashSet<SubCategory>(); //getter setter

SubCategory.java

@Entity
public class SubCategory implements Serializable {


    private static final long serialVersionUID = 7750738516036520962L;

    @Expose(serialize = true, deserialize = true)
    @Id
    @GeneratedValue
    private Integer subCategoryId;

    @Expose(serialize = true, deserialize = true)
    @NotEmpty(message = "The subcategory name must not be empty")
    @Size(min = 3, max = 20, message = "Minimum 3 to 20 characters allowed")
    private String subCategoryName;

    @Expose(serialize = false, deserialize = false)
    @ManyToOne
    @JoinColumn(name="categoryId")
    private Category category;
//getter setter

I would like to query base on subcategory table's subcategoryname to get the product data through PRODUCT_SUBCATEGORY table. Sample query like as below.

select * from product where productid in(
select psc.productid from
subcategory sc
inner join product_subcategory psc
on sc.subcategoryid=psc.subcategoryid
where sc.subcategoryname like 'men ware%');

How do I achieve the above sql in hql.

Thank you.

Answer 1

select distinct p from Product p 
join p.subCategory sc 
where sc.subCategoryName like :name