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

hibernate hql
gennad picture gennad · Jan 19, 2011 · Viewed 96.7k times · Source



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!

Answer

Ashfak Balooch picture Ashfak Balooch · Jan 20, 2011

You can also use Criteria and Projection together :

Criteria criteria = session.createCriteria( MyEntity.class );
criteria.setProjection( Projections.distinct( Projections.property( "id" ) ) );

Hope it help someone.

Related questions

How do you do a limit query in JPQL or HQL?

In Hibernate 3, is there a way to do the equivalent of the following MySQL limit in HQL? select * from a_table order by a_table_column desc limit 0, 20; I don't want to use setMaxResults if possible. This definitely was possible …

Read More
How do you create a Distinct query in HQL

Is there a way to create a Distinct query in HQL. Either by using the "distinct" keyword or some other method. I am not sure if distinct is a valid keywork for HQL, but I am looking for the HQL …

Read More
What is the difference between JOIN and JOIN FETCH when using JPA and Hibernate

Please help me understand where to use a regular JOIN and where a JOIN FETCH. For example, if we have these two queries FROM Employee emp JOIN emp.department dep and FROM Employee emp JOIN FETCH emp.department dep Is …

Read More