在Hibernate中使用本地SQL查询将结果集填充到DTO中

Question

我有一个如下的查询

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

如果我能使用HQL，我会使用HQL构造函数语法直接将DTO与结果集填充。但是，由于Hibernate不允许在没有关联的情况下进行左连接，所以我必须使用本地SQL查询。目前我正在以JDBC样式循环遍历结果集并填充DTO对象。有更简单的方法吗？

- Reddy

Answer 1

你可以使用结果转换器。引用Hibernate 3.2: HQL和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 ;)

参考资料

• Hibernate参考指南
  • 16.1.5. 返回非托管实体
• Hibernate博客
  • Hibernate 3.2: HQL和SQL的变换器

- Pascal Thivent

---

一如既往，非常感谢你的帮助，Pascal。 :) - Reddy

@PascalThivent 如果我想将课程描述获取到StudentDTO对象内的CourseDTO.description中，应该如何操作？ - rohit

这应该放在DTO类内部还是单独一个类中？ - Sandro Wiggers

结果转换器已过时。 - Cihangir