CODEWITHSUNDEEP
jpaspring-data-jpa
Antony Hutchison
Antony Hutchison

Reputation: 2991

How to find duplicate rows in JPA

Is there a way to find duplicate entries in a data set using JPA?

| id | text |
-------------
| 1  | foo  |
| 2  | bar  |
| 3  | foo  |

I want to have only entries 1 & 3 in my set. I can't make it unique on this field.

—

DISTINCT would give me rows 1 & 2.

If it’s a query, a join with the same table? I’m not sure how that would work. I couldn’t get group by to function.

Upvotes: 0

Views: 3064

Answers (2)

Tasos P.
Tasos P.

Reputation: 4114

You can apply common practice from SQL to JPQL with the following query:

SELECT e FROM Entity e WHERE e.text IN (SELECT text FROM Entity d GROUP BY text HAVING COUNT(*)>1.

A sub-query is required so you'd need an index on text column for it to be efficient.

Upvotes: 1

Andrew Preizner
Andrew Preizner

Reputation: 264

Edited I believe you can use the following syntax without inner query:

SELECT id, text, COUNT(*) FROM entity GROUP BY text HAVING COUNT(*) > 1

Upvotes: 2

Related Questions