How can I extract variables total, min, max from hibernate SQL queries and assign them to java variables?
(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);
This post 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]);
}
}
Scalar queries return a List of Object arrays (Object[]) - or a single Object[] in your case.
It is however possible to return non-managed entities using a ResultTransformer. Quoting the Hibernate 3.2: Transformers for HQL and SQL blog post:
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 afuzzyAliasToBean()method ;)
