JPA + Hibernate + Named Query + how to JOIN a subquery result

Question

Hi,

Can anybody help me in converting the following native query into a Named Query?

Native Query:

SELECT 
      usr1.user_id, urr1.role_id, usr2.user_id, urr2.role_id, usr1.school_id, 
      term.term_name, count(material.material_id) as "Total Book Count", 
      fpc.FOLLETT_PENDING_COUNT as "Follett Pending Count", 
      rrc.RESOLUTION_REQUIRED_COUNT as "Resolution Required Count" 
FROM  va_school sch 

JOIN va_user_school_rel usr1 on sch.school_id=usr1.school_id  
JOIN va_user_role_rel urr1 on usr1.user_id=urr1.user_id and urr1.role_id=1001 
JOIN va_user_school_rel usr2 on sch.school_id=usr2.school_id 
JOIN va_user_role_rel urr2 on usr2.user_id=urr2.user_id and urr2.role_id=1002 
JOIN va_term term on term.school_id = usr1.school_id 
JOIN va_class course on course.term_id = term.term_id 
JOIN va_material material on material.class_id = course.class_id

LEFT JOIN 
      (SELECT VA_CLASS.TERM_ID as "TERM_ID", COUNT(*) as "FOLLETT_PENDING_COUNT" 
       FROM VA_CLASS JOIN VA_MATERIAL ON VA_MATERIAL.CLASS_ID = VA_CLASS.CLASS_ID 
       WHERE VA_CLASS.reference_flag = 'A' AND trunc(VA_MATERIAL.FOLLETT_STATUS) = 0 
       GROUP BY VA_CLASS.TERM_ID) fpc on term.term_id = fpc.term_id 

LEFT JOIN 
      (SELECT VA_CLASS.TERM_ID as "TERM_ID", COUNT(*) as "RESOLUTION_REQUIRED_COUNT" 
       FROM VA_CLASS JOIN VA_MATERIAL ON VA_MATERIAL.CLASS_ID = VA_CLASS.CLASS_ID 
       WHERE VA_CLASS.reference_flag = 'A' AND trunc(VA_MATERIAL.FOLLETT_STATUS) = 1 
       GROUP BY VA_CLASS.TERM_ID) rrc on term.term_id = rrc.term_id 
WHERE course.reference_flag = 'A' 
GROUP BY usr1.user_id, urr1.role_id, usr2.user_id, urr2.role_id, usr1.school_id, term.term_name, fpc.FOLLETT_PENDING_COUNT, rrc.RESOLUTION_REQUIRED_COUNT 
ORDER BY usr1.school_id, term.term_name;

Thanks in advance.

• Srihari

Answer 1

I'd suggest move this to Stored Procedure and call Stored Procedure from JPA.

Answer 2

Based on your object Object model you should think about the aim of your query. Looking at your SQL statement is semantically a problem, if a HQL query should be formulated.

Nevertheless it is possible - and usual - to create theta-style queries even as named queries in Hibernate based applications.

The HQL query looks most time the same way like the native one, expcept that it makes use of the relationships of the object model. These relationships have a great influence on the way, the query might be defined. Instead of starting at a School entity. They might start at the User datasets.

SELECT ....
FROM User user1, User user2
JOIN User.school as school
...
WHERE school = user2.school

It is really more easy to follow to one relationships, therefore i typically remodel my queries in a way, that they tend to execute i that way.