JPA + Hibernate + Native Query + custom list of dtos from the resultset

My native query below is working fine oracle sqlplus. But through JPA native query, giving following error:

[ERROR] org.hibernate.util.JDBCExceptionReporter - ORA-00923: FROM keyword not found where expected

Native Query

SELECT sch.school_name, term.term_name, count(material.MATERIAL_ID), sip.SIP_COUNT, ... FROM VA_SCHOOL sch  JOIN va_user_school_rel usr1 on sch.school_id=usr1.school_id  

JOIN va_user ur ON usr1.USER_ID= ur.USER_ID  
JOIN va_user_role_rel urr1 on usr1.user_id=urr1.user_id  
JOIN va_role rle ON urr1.role_id= rle.role_id  
JOIN va_user_school_rel usr2 on sch.school_id=usr2.school_id   
JOIN va_user ur1 ON usr2.USER_ID= ur1.USER_ID  
JOIN va_user_role_rel urr2 on usr2.user_id=urr2.user_id  
JOIN va_role rle1 ON urr2.role_id= rle1.role_id  
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 TERM_ID, COUNT(*) as SIP_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.SCHOOL_STATUS) = 0  GROUP BY VA_CLASS.TERM_ID) sip on term.term_id = sip.term_id

WHERE course.reference_flag = 'A' AND rle.role_name='ROLE_1' AND rle1.role_name='ROLE_2'  

GROUP BY sch.school_name, term.term_name, sip.SIP_COUNT
ORDER BY sch.school_name, term.term_name

JPA Native Query:

SELECT new MyDTO(sch.school_name, term.term_name, count(material.MATERIAL_ID), sip.SIP_COUNT, ...) FROM VA_SCHOOL sch  
JOIN va_user_school_rel usr1 on sch.school_id=usr1.school_id  
JOIN va_user ur ON usr1.USER_ID= ur.USER_ID  
JOIN va_user_role_rel urr1 on usr1.u开发者_开发技巧ser_id=urr1.user_id  
JOIN va_role rle ON urr1.role_id= rle.role_id  
JOIN va_user_school_rel usr2 on sch.school_id=usr2.school_id   
JOIN va_user ur1 ON usr2.USER_ID= ur1.USER_ID  
JOIN va_user_role_rel urr2 on usr2.user_id=urr2.user_id  
JOIN va_role rle1 ON urr2.role_id= rle1.role_id  
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 TERM_ID, COUNT(*) as SIP_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.SCHOOL_STATUS) = 0  GROUP BY VA_CLASS.TERM_ID) sip on term.term_id = sip.term_id  

WHERE course.reference_flag = 'A' AND rle.role_name=:p_roleName AND rle1.role_name=:p_roleName1  

GROUP BY sch.school_name, term.term_name, sip.SIP_COUNT 
ORDER BY sch.school_name, term.term_name

---

Take a look at the answer to this question. Verify that the validation query is something like SELECT 1 FROM DUAL;

---

JPA native query will not transform resultset into list of supplied type of DTO :(. We need to do explicitly.

Thanks,
Srihari

---

A JPA native query means just that, it's straight sql. The new MyDTO(...) is not SQL so the sql parser will reject it. You have to provide an entity that is mapped on the result set, most easily done by using a @SqlResultSetMapping and then referring to that mapping in your query. IMHO, using a @NamedNativeQuery like

@SqlResultSetMapping(
    name="yourResultSetMapping",
    entities = {
        @EntityResult(
            entityClass=YourResultSetEntity.class,
            fields = {
                @FieldResult(name = "<entity class field name", column = "result set column name"),
                 .
                 .
            }
        )
    }
 )

@NamedNativeQuery(
    name="getSchoolName",
    query = "<your very long query here",
    resultSetMapping = "yourResultMapping"
)

Beware name collision. If you have several columns in your result set with the same name JPA will not be able to resolve them and an exception will ensue. Use aliases in your result set to give each column unique names and use the alias name in the column part of the @FieldResult