Using User-Defined Variables in NamedQuery in Hibernate

Question

I have task to load all products in the categories (load by name) and sub categories. Having working with MySQL, I`ve created SQL query:

select *
from product
where primaryCategoryID in (
select  categoryID
from    (select * from category) categories,
        (select pv := (select categoryID from category where name = 'Tools') as ID) initialisation
where   find_in_set(parentCategory, pv) > 0
and     pv := concat(pv, ',', categoryID)
union 
select categoryID from category where name = 'Tools'
)

As you can mentioned 'Tools' in a name of category. It works well But if you have any suggestion(s) - you are more than welcome. In my project I`m working with Hibernate, Named Query. And issue is with variable @pv. Now It is not compiled and throws exception as 'unexpected token...' Does anyone know how to work with variable in NamedQuery?

Answer 1

Hibernate does not support many SQL features. E.g. you use union in your query which is not supported. The same with variables.

Try to use Named Native Query (see example)

For annotation it could be like this (from example above)

@NamedNativeQueries({
    @NamedNativeQuery(
    name = "findStockByStockCodeNativeSQL",
    query = "select * from stock s where s.stock_code = :stockCode",
        resultClass = Stock.class
    )
})
@Entity
@Table(name = "stock", catalog = "mkyong")
public class Stock implements java.io.Serializable {

and place your query in the annotation