JPQL Enhancements in JPA 2.1 and Java EE 7 – JOIN ON

Java EE 7 is around for a few years already, and provides several very useful and long-awaited features, like entity graphs and better support for stored procedures and results mapping. For an overview, have a look at Thorben Janssen’s blog post. The query capabilities were also enhanced, with 3 additional keywords. All of them are available in both JPQL and Criteria API:

  • ON keyword to specify conditions for JOINs
  • FUNCTION to call arbitrary database function
  • TREAT to downcast entities to their specific type

In this post, I’ll focus on the first of them – the ON keyword in JOINs.

JOIN ON

JOIN expressions in JPA are already a bit different from JOIN in standard SQL. It is possible to use JOIN only when a mapping between entities already exists, and is not always necessary due to lazy loading of related collections using implicit joins. Be careful with JPA JOIN, if you are a beginner to JPA, and read the documentation carefully.

Until JPA 2.1, it was only possible to filter final query results using conditions in WHERE clause. This is sufficient in most cases. But you may run to a limit at the point, when, using LEFT JOIN, you want to limit what is going to be joined from the other entity. With LEFT JOIN, you always get at least one row from the first entity, but sometimes you do not want to join any instances from the other entity, leaving the joined collection empty.

Apart from WITH keyword in Hibernate, there used to be no standard way of doing this in JPA. Since JPA 2.1, it is possible to add condition to joins with ON keyword, similar to SQL JOIN ON.

SELECT a FROM Person p LEFT JOIN p.addresses a ON a.city = p.cityCode language: SQL (Structured Query Language) (sql)

The above snippet will retrieve only those addresses, which have the same city as the person. The same can be achieved by moving the condition into WHERE, so we need a more complicated example with multiple joins to see the advantage:

SELECT c FROM Person p LEFT JOIN p.addresses a ON a.city = p.city LEFT JOIN a.country c ON c.region = 'Europe'Code language: SQL (Structured Query Language) (sql)

In the above example, we get only countries, where and address exists and their person has the same city. What is the difference to using WHERE? If we put both conditions from ON clauses into WHERE at the end, we would include all countries related to all address of a person, and not only to addresses with the same city. Clearly, the result could be bigger, when we apply the condition only at the end. The ON keyword makes possible to filter results after every join, leading to smaller result after each successive join.

However, one limitation still remains even when using JOIN with ON – entities can still be joined only when they are mapped together as related entities.

JOIN ON with multiple roots with Eclipselink and Hibernate

Eclipselink and Hibernate (since 5.1 as explained by Vlad Mihalcea here) provides an additional feature to standard ON keyword. It makes possible to relate unrelated entities in the ON condition, making it possible to JOIN an unrelated entity to other entities already in the query. Therefore, it does not require that fields are mapped as related. This is convenient especially if we need the join condition only for a single report and we don’t want to update our mappings. Also, integration tests, which generate database tables and constraints from the mappings, will not create foreign keys for unmapped relations if you do not want them for some reason (e.g. if there would be constraint violations in the test data).

Here is an example of extended usage of ON in Eclipselink and Hibernate (not included in JPA 2.1 standard). This one joins persons to a person which has the same city:

SELECT p FROM Person p LEFT JOIN Person p2 ON p2.city = p.cityCode language: SQL (Structured Query Language) (sql)

Leave a Reply

Your email address will not be published. Required fields are marked *

Captcha loading...