Hibernate/JPA: How to force implicit joins to use LEFT OUTER JOINS

Question

There is a class Offer that has optional relationship to class Article. So that some offers article property holds a null value.

If i use the following statement, everything works fine. I got all offers, even those that have no article.

SELECT o FROM Offer o 
         LEFT OUTER JOIN o.article a 
         LEFT OUTER JOIN o.vendor v 
         WHERE v.number = '0212' OR a.nummer = '123456'

If i change the statement to:

SELECT o FROM Offer o 
         LEFT OUTER JOIN o.article a 
         LEFT OUTER JOIN o.vendor v 
         WHERE v.number = '0212' OR o.article.nummer = '123456'

I got only these offers having articles different from NULL. That is because the notation for implicit joins (o.article.nummer) forces an inner join.

Is there a possibility to force left outer joins to implicit joins (annotation driven or something else)? If there is a chance i could use a short form like this:

SELECT o FROM Offer o 
         WHERE v.number = '0212' OR o.article.nummer = '123456'

Answer 1

As far as I could dig, Hibernate does not offer a way to change the default implicit form of association joining for a HQL Query.

The best solutions I could find for myself were:

• Build the query with explicit join information;
• Use Criteria, which is probably best for dynamic query building.

Answer 2

First of all if you try to use o.article.nummer instead of a.nummer I believe it will put in an extra WHERE clause with an inner join. Thre is no way to explicitly say left joins. But you are specifying it yourself in the query anyway so just use the joined entity from the alias a.number = '23456'.

Since you know the field is nullable you cannot use = just as you cannot use = in SQL on nullable fields. Instead use COALESCE to convert NULL values to an empty string for this purpose:

SELECT o FROM Offer o 
    LEFT OUTER JOIN o.article a
    LEFT OUTER JOIN o.vendor v 
        WHERE v.number = '0212'
        OR COALESCE(a.nummer,'') = '123456'

Answer 3

I had a similiar problem: I had some sort of GeneralFacility-Table which contained a Column SpecificType. Not all facilities had this type and as the SpecificType was inner joined on the GeneralFacility-Table entries without a specific type fell under the table.

I solved the problem by putting

    @Fetch(FetchMode.SELECT)

next to the @ManyToOne-line in the model. The type now gets fetched in a separate query and if that does return nothing the results of the GeneralFacility-query are NOT discarded.

Answer 4

You can try putting @Fetch(FetchMode.JOIN) on the Article property. This is a Hibernate annotation, however.

import org.hibernate.annotations.Fetch;
import org.hibernate.annotations.FetchMode;

//...

@ManyToOne
@Fetch(FetchMode.JOIN)
Article article;