Hibernate JPA Change the implicit INNER JOIN with a LEFT JOIN with Criteria Builder and build a new Result bean: Only one LEFT JOIN Query is generated

Removed

Here my experience with the Generated Queries using my lovely Hibernate. I was stuck for about four days with one of the generated query, but finally I find the solution.

I was developing a code in order to get Persons from my tables using JPA (Hibernate implementation) and Criteria Builder . Performing the test, I have noticed that Hibernates returns only Persons which have a Phone number (as I have a foreign Key on a Contact table). Hibernate do by default an INNER JOIN, and not a LEFT JOIN.

SO to force Hibernate doing a LEFT JOIN with the criteria Builder (I was creating a dynamic query), I used this code:

Root root = query.from(Person.class);

Join phone = root.join("contact", JoinType.LEFT);
...
query.select(builder.construct(MyPerson.class, 
               root.get("contact").get("phone")

The generated Query where :

SELECT phone2.number, person1.name
FROM person person1
LEFT OUTER JOIN phones phone1 ON person1.contact_id = phone1.id
,phones phone2
WHERE 
person1.contact_id = phone2.id

So Hibernate IGNORES in fact my Left JOIN even if it put it in the query. It use the Phone2 and not Phone1 present in the Left Join.  So the rows with contact null, is not yet returned.

If I build my bean by giving the root only, it will generate many other requests.
query.select(builder.construct(MyPerson.class, root);

It was very hard to finally find the solution which consists of removing the Join and do the join while creating the new bean. So the code should be in fact like this:

Root root = query.from(Person.class);
query.select(builder.construct(MyPerson.class, 
   root.join("contact", JoinType.LEFT).get("phone"),...));

So finally the generated query is exactly what I wanted:

SELECT phone1.number, person1.name
FROM person person1
LEFT OUTER JOIN phones phone1 ON person1.contact_id = phone1.id