New object with HQL

Question

Trying to create an object from an HQL query, but just can't figure out what i'm doing wrong.

Query:

String query = "SELECT product.code, SUM(product.price), COUNT(product.code) from Product AS product GROUP BY product.code" 

(or should I use new MyCustomList(product.code, SUM(... , even though it's not mapped?) Now I want to cast this returned list into a similar object:

class MyCustomList{   public String code;   public BigDecimal price;   public int total;    // Constructor   public MyCustomList(String code, String price, int total){ //... 

Retrieving the data:

// This throws ClassCastException     List<MyCustomList> list = MyClass.find(query).fetch(); 

Using Play framework

Answer 1

I think that the section 15.6. The select clause covers what you're trying to achieve:

15.6. The select clause

...

Queries can return multiple objects and/or properties as an array of type Object[]:

select mother, offspr, mate.name from DomesticCat as mother     inner join mother.mate as mate     left outer join mother.kittens as offspr 

Or as a List:

select new list(mother, offspr, mate.name) from DomesticCat as mother     inner join mother.mate as mate     left outer join mother.kittens as offspr 

Or - assuming that the class Family has an appropriate constructor - as an actual typesafe Java object:

select new Family(mother, mate, offspr) from DomesticCat as mother     join mother.mate as mate     left join mother.kittens as offspr 

In your case, you probably want:

SELECT new MyCustomList(product.code, SUM(product.price), COUNT(product.code)) from Product AS product GROUP BY product.code 

Where MyCustomList is not necessarily a mapped entity.

Answer 2

I know that this is an old post, but you can also use for HQL:

Query query = session.createQuery("SELECT code AS code FROM Product");  

or this for SQL:

Query query = session.createSQLQuery("SELECT code AS code FROM Product"); 

with:

query.setResultTransformer(Transformers.aliasToBean(MyCustomList.class));