getting result set into DTO with native SQL Query in Hibernate
You could maybe use a result transformer. Quoting Hibernate 3.2: Transformers for HQL and SQL:
SQL Transformers
With native sql returning non-entity beans or Map's is often more useful instead of basic
Object[]
. With result transformers that is now possible.List resultWithAliasedBean = s.createSQLQuery( "SELECT st.name as studentName, co.description as courseDescription " + "FROM Enrolment e " + "INNER JOIN Student st on e.studentId=st.studentId " + "INNER JOIN Course co on e.courseCode=co.courseCode") .addScalar("studentName") .addScalar("courseDescription") .setResultTransformer( Transformers.aliasToBean(StudentDTO.class)) .list(); StudentDTO dto =(StudentDTO) resultWithAliasedBean.get(0);
Tip: the
addScalar()
calls were required on HSQLDB to make it match a property name since it returns column names in all uppercase (e.g. "STUDENTNAME"). This could also be solved with a custom transformer that search the property names instead of using exact match - maybe we should provide a fuzzyAliasToBean() method ;)
References
- Hibernate Reference Guide
- Hibernate's Blog
Reddy
Exception in thread "main" java.lang.NullPointerException at com.reddy.Reddy.main(Reddy.java:4)
Updated on March 02, 2020Comments
-
Reddy about 4 years
I have a query like below
select f.id, s.name, ss.name from first f left join second s on f.id = s.id left join second ss on f.sId = ss.id
If I could use HQL, I would have used HQL constructor syntax to directly populate DTO with the result set. But, since hibernate doesn't allow left join without having an association in place I have to use the Native SQL Query.
Currently I am looping through the result set in JDBC style and populating DTO objects. Is there any simpler way to achieve it?
-
Reddy over 13 yearsThanks a lot Pascal as always. :)
-
rohit about 8 years@PascalThivent How to do this if I want to fetch course description into an object inside StudentDTO (StudentDTO.CourseDTO.description)?
-
Sandro Wiggers over 6 yearsThis should be inside the DTO class or a separated class?
-
Cihangir almost 3 yearsResult transformer is deprecated.