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!

score 28 · Answer 1 · answered Jan 20 '11 at 05:58

28

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.

answered Jan 20 '11 at 05:58

Ashfak Balooch

1,879
4
18
30

1

Thanks. And what if you want the result to include entire Message class with all its property set? – Eliut Islas Feb 14 '18 at 20:15

score 19 · Accepted Answer · edited Sep 06 '16 at 10:03

19

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.

edited Sep 06 '16 at 10:03

Daniel Fath

16,453
7
47
82

answered Jan 19 '11 at 10:48

Nayan Wadekar

11,444
4
50
73

But are there any examples how to do it using Criteria API? – gennad Jan 19 '11 at 11:49
1

How the Criteria API builds dynamic queries and reduces run-time failures - http://www.ibm.com/developerworks/java/library/j-typesafejpa/ – Nayan Wadekar Jan 19 '11 at 12:27
1

This actually works. The only acceptable solution I found. – cen Dec 20 '16 at 08:08
1

But with this solution, you don't have an unique message from each msgFrom – ourobor93 Sep 02 '20 at 11:33

score 6 · Answer 3 · answered Mar 29 '16 at 06:04

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.

score 3 · Answer 4 · edited Sep 03 '11 at 19:58

3

Try this, it worked for me:

SELECT FROM YourTableName 
WHERE somecolumnName=condition 
GROUP BY yourDistinctColumnName

edited Sep 03 '11 at 19:58

APC

144,005
19
170
281

answered Sep 02 '11 at 21:05

Harz

75
2
7

it works and it can be more clear as SELECT FROM Object o WHERE o.propCondition = condition GROUP BY o.propDistinct – H. ELKINA Sep 17 '19 at 14:16

score 0 · Answer 5 · edited Jan 29 '16 at 09:04

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

score 0 · Answer 6 · answered Sep 17 '19 at 14:21

0

As @APC response

it works and it can be more clear as

SELECT FROM Object o 
WHERE o.propCondition = condition
GROUP BY o.propDistinct

answered Sep 17 '19 at 14:21

H. ELKINA

233
2
12

Marcin · Answer 7 · 2020-04-15T12:52:45.013

0

In case You want to get a whole Entity with DISTINCT you can use Projections.Entity

Criteria.SetProjection(
Projections.Distinct(Projections.Entity(typeof(YourEntityHere), "this")));

"this" means root entity.

edited Apr 15 '20 at 12:52

answered Apr 15 '20 at 12:46

Marcin

479
6
11

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

7 Answers7

Linked