MySQL query to Hibernate Query

Question

Can anyone give me the equivalent Hibernate query for the MySQL query given below. I am not trying it from past few day but no success.

This is MySql query

  SELECT * FROM product WHERE category_id IN (SELECT id FROM  category WHERE parent_category_id IN (SELECT id FROM category WHERE parent_category_id=53));

The Hibernate query which i had written is.

public List<Product> findBy2ndLevel(String categoryName) {

    Query query = null;
    StringBuilder hql = new StringBuilder();
    try {
        hql.append("from Product product where product.category.id in");
        hql.append("(select id from Category category where category.parentCategory.id in");
        hql.append("(select id from Category category where category.parentCategory.id=:category_id))");
        query = sessionFactory.getCurrentSession().createQuery(hql.toString());
        query.setParameter("category_id",Integer.parseInt(categoryName));
    } catch (HibernateException e) {
        e.printStackTrace();
    }
    return  query.list();
}

It is not working though. Give anyone correct or give me the equivalent HQ.

Answer 1

I'm assuming that the entity Product has 1 category and a variable for this (called category), and that a category has 1 parent category and a variable for this (called parent).

But, something like:

select p
from Product p join p.category c join c.parent pc
where pc.id = :categoryId

Furthermore, I don't really get your variable use, you link the id of category with categoryName, and include a variable orgaId which is never really used for anything.. Also, your code will produce a NullPointerException if the creation of the query fails.