Search code examples
hibernatehql

HQL: How to select all entities distinct by some column?



A simple question:
In this example I need to retrieve all objects, but these objects must have distinct msgFrom fields.
When I use

List<Message> list = getHibernateTemplate().find("select distinct m.msgFrom from Message m WHERE msgTo = ? AND msgCheck = 0", dinc);

I get next error: 

java.lang.ClassCastException: java.lang.Integer cannot be cast to com.example.model.Message

I suppose it's because Hibernate retrieves only one column, but I need an object, not column.
How can I do this?
I think that I can just scroll through a comma, i.e. 

List<Message> list = getHibernateTemplate().find("select distinct m.msgFrom, m.To, m.datetime, .......... from Message m WHERE msgTo = ? AND msgCheck = 0", dinc);

But what if I have more than 20 fields here? Is there an easy solution?

Thanks!

Solution

  • Below is the sample query :

    select e from Message e 
where e.msgFrom IN (select distinct m.msgFrom 
                      from Message m
                      WHERE m.msgTo = ? 
                      AND m.msgCheck = "0");

    Alternatively, you can also use Criteria API.