JPA Pitfalls (8): Navigation of Relationships

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 results of navigational JPQL queries.

Navigating a relationship in a JPQL query is mapped to an inner join in the corresponding SQL query. As a result non-related entities are not part of the query result. The following JPQL query returns the related department entities for the employees matching the where clause. In fact, only employees having a department are taken into account. So the relationship navigation defines an extra condition for the WHERE clause of the corresponding SQL query. The query result does not include any NULL values representing that an employee does not have a department:

SELECT e.department FROM Employee WHERE ...

You should use an OUTER JOIN query, if you want employees without department taken into account:

SELECT d FROM Employee e LEFT OUTER JOIN e.department d 
WHERE ...

Recommendation: Use a OUTER JOIN query in  order to take null-valued relationships into account.

You can find an example of this pitfall and its solution in the class OuterJoinQueryExperiment 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.