CODEWITHSUNDEEP
javahibernateormgroovyhql
Dónal
Dónal

Reputation: 187399

Hibernate: find duplicates

Assume I have the following Groovy class (or the equivalent in Java)

class User  {
    Long id
    String name
}

I would like to write a Hibernate query (either HQL or Criteria) which returns all the users that have at least one other user with the same name.

Update

The following query has been suggested

select min(user.id), user.name
from User user
group by user.name
having count(user.name) > 1

However, there are a few problems with this:

Thanks, Don

Upvotes: 1

Views: 3585

Answers (3)

srinivas gowda
srinivas gowda

Reputation: 496

String hql = "select columnName form entityName";
Query query = session.createQuery(hql);
arrayList =   query.list();

int countOFRecords=Collections.frequency(arrayList , recordName)

Upvotes: 0

ralph
ralph

Reputation: 1017

I would assume that you are looking for an exact String match of the user name. Also you would want the primary key on id that way you could tell if the user has the same name but is actually a different user.

Did you set up a primary key on your User table? It sounds like you are getting duplicates because the string you are searching on is a primary key. Hibernate requires you to put primary keys on your tables/objects.

Upvotes: -1

Pascal Thivent
Pascal Thivent

Reputation: 570595

I'd try something like this:

select min(user.id), user.name
from User user
group by user.name
having count(user.name) > 1

Note that, according to the documentation, SQL functions and aggregate functions are allowed in the having and order by clauses if they are supported by the underlying database (i.e., not in MySQL).

EDIT: It should be possible to retrieve Users with an IN (I don't think the query performance will be very good though):

from User u
where u.name IN (
select user.name
from User user
group by user.name
having count(user.name) > 1)

Upvotes: 5

Related Questions