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

Question

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 1

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.

Answer 2

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.

Answer 3

Hibernate criteria is pretty easy to select distinct results. If you want single result to be returned in the projected result, you may want to use:

Criteria criteria = session.createCriteria(Message.class);
criteria.setProjection(Projections.distinct(Projections.property("msgFrom ")));
List<String> msgFromList = criteria.list();

If you want the result to include entire Message class with all its property set, you can use Hibernate result Transformer,

Criteria criteria = session.createCriteria(Message.class);
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
List<Message> messages = criteria.list();

But it filters on the basis of root entity.

Or

Criteria criteria = session.createCriteria(Message.class);

ProjectionList projection = Projections.projectionList();
projection.add(Projections.distinct(Projections.property("msgFrom")));
//Add as many columns as you want using Projection
projection.add(Projections.property("msgTo"));
criteria.setProjection(projection);

criteria.setResultTransformer(Transformers.aliasToBean(Message.class));
List<String> msgFromList = criteria.list();

As per your question first solution gives correct output.

Answer 4

Try this, it worked for me:

SELECT FROM YourTableName 
WHERE somecolumnName=condition 
GROUP BY yourDistinctColumnName