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'

Easter

Answer 1

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;

Misconception

I don't know how this should solve the problem. AFAIK Fetchmode only specifies how to fetch the data to initialize the object in memory, not how to do joins in a query. – Metabolize

Answer 2

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.

Legume

The OP didn't say that there is anything dynamic. Did I miss something? – Metabolize

The '0212' and '123456' usage suggest the query changes at run time. One of the solutions I found for the "use of the right SQL join" was using the Criteria API instead. And it feels more natural. – Kingdon

Criteria API is usually more complicated to use and less powerful as HQL. If only values change, just put parameters into the query. This is not really "dynamic", it's parametrized. – Metabolize

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.

Voigt

I guess this only applies when you fetch the entity e.g. using session.get. When you need the property in an HQL query, it doesn't have an influence as far as I know. – Metabolize

Answer 4

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'

Misconception