Alternative to fetch join with "ON clause" in Hibernate

Question

Given is the following domain model in a RDBMS that is mapped with Hibernate into Java objects.

┌─────────┐            ┌─────────────┐
│ Project │ 1     0..n │ UserSetting │
├─────────┼------------┼─────────────┤
│  name   │            │   username  │
└─────────┘            │   favorite  │
                       └─────────────┘

Users can flag a project as favorite which is reflected by an associated (optional) UserSetting entry. If a user did not flag a project, there is no UserSetting entry in the database which implies that the project is no favorite. Think of UserSetting being "sparse".

Furthermore given: Entities should be fetched via JPA with Hibernate as provider.

Assume that the Project and the UserSetting table can be very large. Optimizing the database schema should not be subject to this question. Assume that efficient indexes exist.

For a given user I would like to efficiently fetch all projects including the favorite status for that particular user. Note the sort criterium!

My initial approach was to formulate the following JPQL query:

SELECT p, us FROM Project p
    LEFT JOIN FETCH UserSetting us
    WHERE us.username IS NULL OR us.username = :currentUser
    ORDER BY COALESCE(us.favorite, false) DESC, p.name ASC

This does not work if currentUser does not have a UserSetting but userB has. The UserSetting of userB will be joined but the project will not be listed due to the WHERE clause.

Fortunately, JPA 2.1 introduces JOINS with ON clause (Javadoc) which can be used to reformulate the query into:

SELECT p, us FROM Project p
    LEFT JOIN FETCH UserSetting us
    ON us.username IS NULL OR us.username = :currentUser
    ORDER BY COALESCE(us.favorite, false) DESC, p.name ASC

Unfortunately, Hibernate (4.3.6) does not support fetch joins with "ON clause" ("WITH clause" in Hibernate's lingus) and quits with the following exception:

org.hibernate.hql.internal.ast.QuerySyntaxException: with-clause not allowed on fetched associations; use filters

How could the query be reformulated in order to implement the functionality and fulfill my constraints:

• Fixed domain model
• Efficient query that scales well
• JPA + Hibernate
• Hibernate specific features should be avoided

Answer 1

More recent discovery is that the JPA 2.1 (JSR 338) specification defines the grammar without allowing "ON clauses" for fetch joins.

My workaround is to omit the object mapping feature of Hibernate and explicitly selecting the required fields of Project and UserSetting.

Answer 2

You can use the following to also retrieve Project without UserSettings:

SELECT p, us FROM Project p
LEFT JOIN FETCH UserSetting us
WHERE us is null OR us.username IS NULL OR us.username = :currentUser
ORDER BY COALESCE(us.favorite, false) DESC, p.name ASC

The

 us is null

part in the WHERE clause of the query does the trick.