4

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.

Dave
  • 15,639
  • 133
  • 442
  • 830

3 Answers3

5

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.

V G
  • 18,822
  • 6
  • 51
  • 89
  • For Solution 1: keep the field `codeUsers` private and don't add accessor for it, if you want to keep the same business model. It may be important, in order to avoid performance / memory issues. – jeromerg May 03 '14 at 13:36
1

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.

V G
  • 18,822
  • 6
  • 51
  • 89
  • Hi, Thanks but it's not an option to add a member field (in your answer, "codeUsers") to my User entity. – Dave Sep 17 '13 at 13:14
  • Changed the example so that no new field is needed AND making a RIGHT JOIN. – V G Sep 17 '13 at 13:43
  • 16
    Thanks for updating your answer. I tried your suggestion but got a "java.lang.UnsupportedOperationException: RIGHT JOIN not supported" exception on the JoinType.RIGHT line. Another restraint of this problem is that I have to use Hibernate. I'll try to upgrade the version, but I think this is all of Hibernate. – Dave Sep 17 '13 at 17:55
  • It's not supported since 2010: https://hibernate.atlassian.net/browse/JPA-2 – amra Oct 25 '21 at 13:35
-1

use CROSS JOIN

    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);
    criteria.select(codeUser);
    preds.add(builder.equal(codeUser.get("USER_ID"), user.get("ID")))

    …
    return criteria.where(builder.and(preds.toArray(new Predicate[preds.size()])));
user11800719
  • 1