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

buildercriteriajpajpa-2.0left-join

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.

Best Answer

You can do something like this.

final Root<Message> messageRoot = criteriaQuery.from(Message.class);
Join<Message, MessageReadDate> join1 = messageRoot .join("joinColumnName", JoinType.LEFT);

Predicate predicate = criteriaBuilder.equal(MessageReadDate.<String> get("recipient"), recepientValue;
criteria.add(predicate);
criteriaQuery.where(predicate);
criteriaQuery.distinct(true);

I hope this will resolve your query.

Related Topic