Java 在 Hibernate 中使用本机 SQL 查询将结果设置为 DTO

getting result set into DTO with native SQL Query in Hibernate

提问by Reddy

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 syntaxto 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.

如果我可以使用 HQL，我会使用HQL 构造函数语法直接用结果集填充 DTO。但是，由于休眠不允许在没有关联的情况下进行左连接，因此我必须使用本机 SQL 查询。

Currently I am looping through the result set in JDBC style and populating DTO objects. Is there any simpler way to achieve it?

目前我正在循环遍历 JDBC 样式的结果集并填充 DTO 对象。有没有更简单的方法来实现它？

采纳答案by Pascal Thivent

You could maybe use a result transformer. Quoting Hibernate 3.2: Transformers for HQL and SQL:

您也许可以使用结果转换器。引用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 ;)

SQL 转换器

使用本机 sql 返回非实体 bean 或 Map 通常比 basic 更有用Object[]。现在可以使用结果转换器。

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);

提示：addScalar()在 HSQLDB 上需要调用以使其匹配属性名称，因为它以全部大写形式返回列名称（例如“STUDENTNAME”）。这也可以通过搜索属性名称而不是使用精确匹配的自定义转换器来解决 - 也许我们应该提供一个 FuzzyAliasToBean() 方法;)

References

参考

• Hibernate Reference Guide
  • 16.1.5. Returning non-managed entities
• Hibernate's Blog
  • Hibernate 3.2: Transformers for HQL and SQL

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