2

I'm trying to make a method in CrudRepository that will be able to give me duplicates in my table. I want to find a Person which have the same name and on the same day's birthday. For that I think it is easier, I want to find first all same names and then I want to filter names with the same birthday. Wished Method is a Specification but also queries are possible.

Is there a way in JPA?

Full Table:

ID     Name     Birthday 
1      Jean     11.10.2019     
2      Jones    10.10.2019   
3      Jean     11.10.2019 
4      Jean     12.10.2019

First wished result, filter with same names: 

ID     Name     Birthday 
1      Jean     11.10.2019
3      Jean     11.10.2019 
4      Jean     12.10.2019

The second wished result is: 

ID     Name     Birthday 
1      Jean     11.10.2019     
3      Jean     11.10.2019

Because Name "Jean" has on the same day birthday.

Can anybody help?

user182410
  • 151
  • 2
  • 8
  • check out this post: https://stackoverflow.com/questions/10324107/show-all-duplicated-rows/10324140 you can add a native query to your repository method – mckszcz Jan 23 '20 at 10:41
  • nope, that will give me name and birthday twice. `ID Name Birthday Name Birthday` – user182410 Jan 23 '20 at 10:50

2 Answers2

3

You can use native query like:

SELECT Name, Birthday, COUNT(*)
FROM Users
GROUP BY Name, Birthday
HAVING COUNT(*) > 1

This can be used together with JOIN to get the desired result:

SELECT u.ID, u.Name, u.Birthday FROM Users u
JOIN (SELECT Name, Birthday, COUNT(*) FROM Users GROUP BY Name, Birthday HAVING COUNT(*) > 1) u2 
ON u.Name=u2.Name AND u.Birthday=u2.Birthday

And the solution without INNER JOIN:

SELECT u.ID, u.Name, u.Birthday
FROM Users u, (SELECT Name, Birthday, COUNT(*) FROM Users GROUP BY Name, Birthday HAVING COUNT(*) > 1) u2 
WHERE u.Name = u2.Name AND u.Birthday = u2.Birthday
Axifive
  • 1,159
  • 2
  • 19
  • 31
  • it adds a row 'unkown'´ for count which i dont have in my entity. Also there is a group by and thats not what i'm searching for as you can see above. I want to list all results. But Thank you for trying ! Your result is `ID Name Birthday unknown 1 Jean 11.10.2019 2` – user182410 Jan 23 '20 at 10:22
  • It is possible with join query: `SELECT u.Name, u.Birthday FROM Users u JOIN (SELECT Name, Birthday, COUNT(*) FROM Users GROUP BY Name, Birthday HAVING COUNT(*) > 1) u2 ON u.Name=u2.Name AND u.Birthday=u2.Birthday` – Axifive Jan 23 '20 at 10:39
  • 1
    Ok. if you don't need joined fields: `SELECT u.ID, u.Name, u.Birthday FROM Users u, (SELECT Name, Birthday, COUNT(*) FROM Users GROUP BY Name, Birthday HAVING COUNT(*) > 1) u2 WHERE u.Name = u2.Name AND u.Birthday = u2.Birthday` – Axifive Jan 23 '20 at 12:15
  • Axifive gave you alle the information needed to solve your problem, although he does not give you the solution. Just select the correct columns from your table so it fits your entity. – LostKatana Jan 24 '20 at 08:14
0

Use Spring JPA Criteria

See example at https://spring.io/blog/2011/04/26/advanced-spring-data-jpa-specifications-and-querydsl/

https://www.baeldung.com/spring-data-criteria-queries

Because you did not show your entity, therefore, I cannot implementing coding more explicitly.

Vy Do
  • 46,709
  • 59
  • 215
  • 313