29

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

KLE
  • 22,211
  • 4
  • 51
  • 60
Indrek
  • 5,946
  • 4
  • 26
  • 27

2 Answers2

52

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.

Pascal Thivent
  • 535,937
  • 127
  • 1,027
  • 1,106
  • I tried the new MyCustomList quite a few times, but if I remember correctly (not at that machine atm) it gave me the ClassNotFound excepton, though the classes are in the same package under Play! models. – Indrek Oct 27 '10 at 06:24
  • 13
    @Indrek: Use the fully qualified name: `select new com.acme.MyCustomList(...) from ...` – Pascal Thivent Oct 27 '10 at 06:29
  • @PascalThivent could you please look at my problem [How to write HQL JOIN query for multiple table's selected Columns using Constructor In The Select Clause](https://stackoverflow.com/q/47726605/3425489) **thank you** – Shantaram Tupe Dec 09 '17 at 09:50
1

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));
Jason Glez
  • 624
  • 1
  • 10
  • 14