How do I use CriteriaBuilder to write a left outer join when relationship goes the other way?

Question

I’m using JPA 2.0, Hibernate 4.1.0.Final and MySQL 5.5.37. I have the following two entities …

@Entity
@Table(name = "msg")
public class Message
{

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

    @Column(name = "MESSAGE", columnDefinition="LONGTEXT")
    private String message;

and

@Entity
@Table(name = "msg_read", uniqueConstraints = { @UniqueConstraint(columnNames = { "MESSAGE_ID", "RECIPIENT" }) })
public class MessageReadDate
{

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

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

    @ManyToOne
    @JoinColumn(name = "MESSAGE_ID", nullable = false, updatable = true)
    private Message message;

    @Column(name = "READ_DATE")
    private java.util.Date readDate;

Using CriteriaBuilder, how would I write this

SELECT DISTINCT m.*
FROM msg AS m
LEFT JOIN msg_read AS mr
        ON mr.message_id = m.id AND mr.recipient = 'USER1'

? My problem is that there is no field “msg_read” in my Message entity, and I’m not sure how to specify the “AND” part of the left outer join in CriteriaBuilder.

Answer 1

you can't do this in JPA 2.0 (in criteria queries). you either have to do it via JPQL, or upgrade to JPA 2.2. in JPA 2.2 they introduced the .on() operator that allows for arbitrary outer joins.

Answer 2

Entity 'Message' doesn't have any reference to MessageReadDate. Therefore it's would be better to use start 'MessageReadDate' as a root and then go to the 'Message'.

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Message> query = cb.createQuery(Message.class);
Root<MessageReadDate> root = query.from(MessageReadDate.class);
final Join<MessageReadDate, Message> msg = root.join("message");

query.select(root);
query.where(cb.equal(root.get("recipient"),
                              "USER1"));
List<Message> = em.createQuery(query)
                  .getResultList();