JPA Pitfalls (6): Query Result with Duplicates

This blog article is part of a “series of blog articles” about common pitfalls using JPA and ways to avoid them. In this article, we describe unexpected duplicates in the result of a JPQL query.

A JPQL query navigating a multi-valued relationship might return the same entity multiple times. The following query will return the same Department entity instance as often as there are employee entities in the department matching the where clause of the JPQL query.

SELECT d FROM Department d JOIN d.employees e 
WHERE e.salary > 1000.0d

In order to avoid duplicates in the query result you should use the keyword DISTINCT in the SELECT Clause of the JPQL query:

SELECT DISTINCT d FROM Department d JOIN d.employees e 
WHERE e.salary > 1000.0d

Recommendation: Use the DISTINCT keyword in the JPQL query to avoid duplicates.

You can find an example of this pitfall and its solution in the class DuplicatesInQueryResultExperiment in this project: https://github.com/akquinet/jpapitfalls.git.

Leave a Reply