Hibernate Criteria unique result (column other than key)

Question

I'm currently using a Hibernate Criteria to return a list of results (filtering on various column values), but several of the objects have the same value for one of the attributes and I only need one "representative" for each.

For example: I am returned a list of 3 vehicle objects, with id/primary keys of 123, 456, 789, and manufacturer codes of: 111, 111, 222. I'd like to only have returned a list with two objects: 123/111, 789/222.

Is there a way to add a restriction to a Hibernate Criteria in order to return only unique (or first) result of the manufacturer code in this example? Please let me know if any additional info/clarification is needed.

Thanks for any suggestions!

http://stackoverflow.com/questions/8491796/hibernate-group-by-criteria-object above link is a good answer for this. — Ketan Bhavsar, Mar 12 '12 at 09:45

Puspendu Banerjee · Answer 1 · 2010-11-22T17:54:26.623

1

If you are using JPA with Hibernate the you can use something like below:

Query q = em.createQuery("select cat from DomesticCat cat");
q.setMaxResults(1);
List cats = q.getResultList(); //return any one cat from matched rows

Now, just get the 1st Item from the list. BTW, take care of NullPointerException and check the size of list before doing any further operation.

Else, if you are using Hibernate without JPA. You can go for the following one [uniqueResult()]:

Query query =session.createQuery("select ao from AccountOwner ao ");
AccountOwner ao=query.uniqueResult();

edited Nov 22 '10 at 17:54

answered Nov 22 '10 at 17:48

Puspendu Banerjee

2,631
16
19

I understand what you're saying, but this will return only 1 record. I'd like a list of all records, but limited to only one result per "manufacturer" from my example. – Ryan P. Nov 22 '10 at 18:06
1

then, do not set the MaxResult/ do not go for UniqueResult, rather modify the query to add a 'group by' clause for 'manufacturer'. – Puspendu Banerjee Nov 22 '10 at 18:13
How i know if i'm using with JPA (i'm using at the moment hibernate 4.2)? – Diogo Alves Feb 26 '15 at 22:02
@Diogo EntityManager.getDeletate() to find a provider: em = emf.createEntityManager(); Object delegate = em.getDelegate(); if (delegate instanceof org.eclipse.persistence.internal.jpa.EntityManagerImpl) System.out.println("Delegate: EclipseLink"); else if (delegate instanceof org.hibernate.Session) System.out.println("Delegate: Hibernate"); else if (delegate instanceof org.apache.openjpa.persistence.EntityManagerImpl) System.out.println("Delegate: OpenJPA"); else System.out.println("Delegate: " + delegate); – Puspendu Banerjee Mar 02 '15 at 23:12

score 1 · Accepted Answer · answered Nov 22 '10 at 18:31

1

The simpliest approach is to implement an appropriate ResultTransformer and apply it to your query.

If you really want it to be performed at the database side, you need some tricky query, such as this (in HQL/JPQL for brevity):

SELECT v FROM Vehicle v 
WHERE v.id = 
    (SELECT MIN(sv.id) FROM Vehicle sv WHERE sv.manufacturerCode = v.manufacturerCode)

answered Nov 22 '10 at 18:31

axtavt

239,438
41
511
482

This is the solution I wound up using. I was hoping I could make it happen on the DB-side, but this works well for the small-ish (less than 10k) results typically retrieved. Thanks! – Ryan P. Nov 23 '10 at 14:53

Hibernate Criteria unique result (column other than key)

2 Answers2