HQL: How to select all entities distinct by some c

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

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 2

Try this, it worked for me:

SELECT FROM YourTableName 
WHERE somecolumnName=condition 
GROUP BY yourDistinctColumnName

Answer 3

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 4

I have got a answer for Hibernate Query Language to use Distinct fields. You can use SELECT DISTINCT(TO_CITY) FROM FLIGHT_ROUTE. If you use SQL query, it return String List. You can't use it return value by Entity Class. So the Answer to solve that type of Problem is use HQL with SQL.

"FROM FLIGHT_ROUTE F WHERE F.ROUTE_ID IN (SELECT SF.ROUTE_ID FROM FLIGHT_ROUTE SF GROUP BY SF.TO_CITY)";

From SQL query statement it got DISTINCT ROUTE_ID and input as a List. And IN query filter the distinct TO_CITY from IN (List).

Return type is Entity Bean type. So you can it in AJAX such as AutoComplement.

May all be OK

Answer 5

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.