getting result set into DTO with native SQL Query in Hibernate

by

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

More Related Contents:

  1. How to fix the Hibernate “object references an unsaved transient instance – save the transient instance before flushing” error
  2. Hibernate Annotations – Which is better, field or property access?
  3. How to map calculated properties with JPA and Hibernate
  4. How to print a query string with parameter values when using Hibernate
  5. Bypass GeneratedValue in Hibernate (merge data not in db?)
  6. When and why JPA entities should implement the Serializable interface?
  7. How should equals and hashcode be implemented when using JPA and Hibernate
  8. I found JPA, or alike, don’t encourage DAO pattern
  9. Getting Database connection in pure JPA setup
  10. Hibernate – @ElementCollection – Strange delete/insert behavior
  11. session.connection() deprecated on Hibernate?
  12. Location of hibernate.cfg.xml in project?
  13. Hibernate/JPA – annotating bean methods vs fields [duplicate]
  14. Postgresql UUID supported by Hibernate?
  15. how to configure hibernate config file for sql server
  16. Hibernate, iBatis, Java EE or other Java ORM tool
  17. How to set a default entity property value with Hibernate
  18. How can I use hibernate with MS Access?
  19. mappedBy reference an unknown target entity property
  20. Hibernate use of PostgreSQL sequence does not affect sequence table
  21. Primitive or wrapper for hibernate primary keys
  22. Hibernate Named Query Order By parameter
  23. Is it possible to dynamically define column names in Hibernate / JPA?
  24. How to use Hibernate @Any-related annotations?
  25. How do you map a “Map” in hibernate using annotations?
  26. How to turn off hbm2ddl?
  27. Hibernate triggering constraint violations using orphanRemoval
  28. Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]
  29. Hibernate @PostLoad never gets invoked
  30. HQL recursion, how do I do this?

Leave a Comment