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

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.