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. However, I’d like add a more detailed summary about features in the JPA query language. 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 addition. I will add the other 2 in next posts.
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 run to a limit, when using LEFT JOIN, and 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 final 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.
Example of using JOIN ON in JPA
SELECT a FROM Person p LEFT JOIN p.addresses a ON a.city = p.city
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:
Example of using JOIN ON in JPA
SELECT c FROM Person p LEFT JOIN p.addresses a ON a.city = p.city LEFT JOIN a.country c ON c.region = 'Europe'
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 in Eclipselink
Eclipselink provides an additional feature to standard JPA 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 marked as related, even if we need the join condition only for a single report and we don’t want to update our mappings. Also tests, which generate database tables and constraints from the mappings, will not do not want it for some reason (e.g. if there would be constraint violations in vast array of test data).
Here is an example of extended usage of ON in Eclipselink (not included in JPA 2.1 standard). This one joins persons with the equal name of the city:
JOIN ON with multiple root entities
SELECT p FROM Person p LEFT JOIN Person p2 ON p2.city = p.city
| Reference: | JPQL Enhancements in JPA 2.1 and Java EE 7 (part 1 – JOIN ON) from our JCG partner Ondrej Mihalyi at the .Lost in Coding blog. |
