java 休眠 SQLquery 提取变量

hibernate SQLquery extract variable

提问by Nish

How can I extract variables total, min, max from hibernate SQL queries and assign them to java variables?

如何从休眠 SQL 查询中提取变量 total、min、max 并将它们分配给 java 变量？

(select count(*) as total, min(price) as min, max(price) as max from product).addScalar("total", Hibernate.INTEGER).addScalar("min", Hibernate.INTEGER).addScalar("max", Hibernate.INTEGER);

回答by thelost

Thispost should help you.

这篇文章应该对你有所帮助。

Later edit:

后期编辑：

String sQuery = "select min(myEntity.x), max(myEntity.y) from MyEntity myEntity";
Query hQuery = session.createQuery(sQuery);
List result = hQuery.list();

Iterator iterator = result.iterator();

while (iterator.hasNext()) {
    Object[] row = (Object[])iterator.next();
    for (int col = 0; col < row.length; col++) {
        System.out.println(row[col]);
    }
}

回答by Pascal Thivent

Scalar queries return a Listof Objectarrays (Object[]) - or a single Object[]in your case.

标量查询返回List的Object阵列（Object[]） -或者一个Object[]你的情况。

It is however possible to return non-managed entitiesusing a ResultTransformer. Quoting the Hibernate 3.2: Transformers for HQL and SQLblog post:

然而，有可能返回非托管实体使用ResultTransformer。引用Hibernate 3.2: Transformers for 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()方法;)

See also

也可以看看

• 16.1.5. Returning non-managed entities

• 16.1.5. 返回非托管实体