CODEWITHSUNDEEP
hibernatehql
gennad
gennad

Reputation: 5565

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!

Upvotes: 23

Views: 106827

Answers (7)

Marcin
Marcin

Reputation: 589

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.

Upvotes: 0

H. ELKINA
H. ELKINA

Reputation: 243

As @APC response

it works and it can be more clear as

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

Upvotes: 0

Nayan Wadekar
Nayan Wadekar

Reputation: 11602

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.

Upvotes: 19

Kamal Singh
Kamal Singh

Reputation: 1000

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.

Upvotes: 6

San Lin Naing
San Lin Naing

Reputation: 127

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

Upvotes: 0

Harz
Harz

Reputation: 75

Try this, it worked for me:

SELECT FROM YourTableName 
WHERE somecolumnName=condition 
GROUP BY yourDistinctColumnName

Upvotes: 3

Ashfak Balooch
Ashfak Balooch

Reputation: 1879

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.

Upvotes: 28

Related Questions