Hibernate complex query

Question

I am trying to execute a query against a MySQL database. The query is fairly complex it has 5 inner joins, including 1 join to itself and it returns 3 pieces of information from 2 different tables. We are using hibernate and till now I have used it for simple queries only. I have written the sql query and tested it too. I am wondering how to implement this using hibernate, can I execute plain sql statements with hibernate? If so what do I need, a separate hbm.xml? If I use hibernate and execute the plain sql query can I still utilize caching later on?

Answer 1

Yes, you can execute plain SQL queries with Hibernate.

No, you don't need a separate hbm.xml mapping file (unless you WANT to separate sql queries from the rest, in which case you can do so). You can map your named SQL query the same way you do with named HQL queries.

Whether you will be able to "utilize caching" depends on what exactly you understand by "caching" and how you're going to map your SQL query; it's impossible to answer without knowing more details.

All that said, you may not need to resort to SQL query; HQL is quite powerful and it may very well be possible (assuming appropriate mappings exist) to write your query as HQL. Can you post relevant mappings / schemas and your SQL query?

Answer 2

I tried using Native SQL as suggested. My query I want to execute is:

select distinct (cat.NAME), cat.UID_PK, catAttr.VALUE as Description
from TPRODUCT prod
inner join TCROSSSELLING cs    on cs.PRODUCTUID = prod.UID_PK
inner join TPRODUCTVARIATION prodVar     on cs.CSPRODUCTVARIATIONUID = prodVar.UID_PK
inner join TPRODUCTCATEGORY prodCat   on prodCat.PRODUCTUID = cs.PRODUCTUID
inner join TCATEGORY cat    on cat.UID_PK = prodCat.CATEGORYUID
inner join TCATEGORY cat2 on cat2.UID_PK = cat.PARENTCATEGORY
inner join TCATEGORYATTRIBUTE catAttr on catAttr.CATEGORYUID = cat.UID_PK
where prodVar.DISABLED = 0
and prodVar.DELETED = 0
and prod.DISABLED = 0
and prod.DELETED = 0
and prodCat.DISABLED = 0
and prodCat.DELETED = 0
and cat.DELETED = 0
and cat.HIDDENCATEGORY = 0
and prodVar.PRODUCTCODE =:DeviceSku
and cat2.NAME = 'Content Store'
and catAttr.ATTRIBUTEUID = 270;

To execute this query I did :

final Session session = HibernateUtil.currentSession();
List results = null;
results = session.createSQLQuery(getCompatibleCategoriesByDeviceSkuSQL).setParameter("DeviceSku", sku).list();

However, I am getting an error like:

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 137     
[select distinct (cat.NAME), cat.UID_PK, catAttr.VALUE as Description .....

It appears that the query with join in it is causing problems. Is my approach correct?

Answer 3

I strongly recommend criteria queries over HQL queries. They are much closer to your program code without sacrificing any expression power. They DO however depend on relations to be explicitly mapped, otherwise they get quite complicated.

To speed up development, set property hibernate.show_sql=true, and play with the system in the debugger, using the "reload modified class" and "drop stack frame" features of the IDE+jvm until the SQL emitted looks like the one you've posted.