Hibernate version:3.30GA
I need to do something similar to the following HQL, but need to use the Criteria API because I'm doing complex query assembly on the fly:
"select p , p.field1 * p.field2 / p.field3 as calc1 from Person p order by calc1 desc "
I'm having trouble using the criteria API. For example when I do the following:
Code:
DetachedCriteria query= DetachedCriteria.forClass(Person.class, "p");
Projection proj= Projections.sqlProjection("{alias}.field1 * {alias}.field2 / {alias}.field3", new String[] {"calc1"}, new Type[] {Hibernate.DOUBLE});
query.setProjection(Projections.projectionList().add(Projections.alias(Projections.property("p"),"person")).add(proj));
query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
List<Map> results = query.getExecutableCriteria(sess).setMaxResults(10).list();
for(Map<String,Object> map: results){
Person result = (Person)map.get("person");
if(map.containsKey("calc1")){
foo.field= (Double)map.get("calc1");
}
I get a property exception stating that Hibernate cannot resolve "p". It is as if the alias "p" does not register or have context under a projection context like above.
if I remove the alias is produces the problem, such as :
Code:
query.setProjection(Projections.projectionList().add(Projections.property("p")).add(proj));
Note, if I remove the "p" property all together from the projection, the calculation an map binding works as expected.
I'm I on the right track here, or is another approach required? It is important that I perform the calculation in the DBMS because of the order by clause and the large set size. It seems that this would be a simple thing to accomplish, but there are no examples... desperate help is needed.
Thanks in advance.