Hibernate: find duplicates

Question

Hi,

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:

• It doesn't actually return the User objects, just their id and name
• If there are 3 users with the same name, it will only return the id of one of them, whereas I want all 3
• It may not work on MySQL, which is the RDBMS I'm using.

Thanks, Don

Answer 1

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)

Answer 2

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.