How do I perform an outer join with a JPA CriteriaBuilder query?

Question

I'm using JPA 2.0, Hibernate 4.1.0.Final, and MySQL 5.5.27. I want to construct a JPA query that will return two entities per row and I want to perform a right outer join. The two entities are:

@Entity
@Table(name = "user",
    uniqueConstraints = { @UniqueConstraint(columnNames = { "USER_NAME" }) }
)
public class User implements Comparable<User>, Serializable
{
    ...
    @Column(name = "first_name")
    @NotNull
    /* the first name of the User */
    private String firstName;

and

@Entity
@Table(name="code_user",
    uniqueConstraints = { 
        @UniqueConstraint(columnNames = { "CODE_ID", "USER_ID" }) }
)
public class CodeUser 
{

    @Id
    @NotNull
    @GeneratedValue(generator = "uuid-strategy")
    @Column(name = "ID")
    private String id;

    @ManyToOne
    @JoinColumn(name = "CODE_ID", nullable = false, updatable = true)
    private Code code;

    @ManyToOne
    @JoinColumn(name = "USER_ID", nullable = false, updatable = true)
    private User user;

Here's my JPA so far, but the problem is that an inner join is being performed, and only one entity per row (the User object) is returned ...

    final CriteriaBuilder builder = m_entityManager.getCriteriaBuilder();
    final CriteriaQuery<User> criteria = builder.createQuery(User.class);
    final Root<User> user = criteria.from(User.class);

    final Root<CodeUser> codeUser = criteria.from(CodeUser.class);
    final Join<CodeUser, User> joinUser = codeUser.join(CodeUser_.user);
    criteria.select(joinUser);
    …
    return criteria.where(builder.and(preds.toArray(new Predicate[preds.size()])));

How do I perform an outer join so that all Users are returned and any matching CodeUser records are also returned? It's not an option to add extra member fields to my User entity.

Solution 1

You have to check whether Hibernate supports the Right Join, because the JPA provider is not required to support the RIGHT JOIN according to the documentation:

Right outer joins and right outer fetch joins are not required to be supported in Java Persistence 2.0. Applications that use RIGHT join types will not be portable.

So, So, if you find out that no hibernate version does, the only solutions that I see would be:

Solution 1. To add the LAZY field to the User entity

@OneToMany(mappedBy="TODO", fetch=FetchType.LAZY)
private Collection<CodeUser> codeUsers;

and then navigate from the User to CodeUsers with a LEFT JOIN.

Solution 2. Make two different queries: in the first fetch all the Users, and in the second fetch the corresponding CodeUsers.

Solution 2

You can do the following in order to make a RIGHT JOIN:

Join<CodeUser, User> joinUser = rootCodeUser.join(CodeUser_.user, JoinType.RIGHT);
//now you can add your conditions on the CodeUser

Also, in order to select more entities try doing this:

CriteriaQuery<Object[]> criteria = builder.createQuery( Object[].class );
Root<CodeUser> codeUserRoot = criteria.from( CodeUser.class );
Join<CodeUser, User> joinUser = rootUser.join(CodeUser_.user, JoinType.RIGHT);

criteria.select( builder.array( joinUser, codeUserRoot ) );
//now you can add you conditions

PS: One problem that I detected in your code is that it takes two roots, instead of one single.