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

Im using JPA 2.0, Hibernate 4.1.0.Final and MySQL 5.5.37. I have the following two objects ...

@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 msg_read field in my Message object and I'm not sure how to specify the "AND" part of the left outer join in the CriteriaBuilder.

+3


source to share


2 answers


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 solves your request.

+2


source


If there is no explicit relationship between the two objects (no bi-directional relationship) then the JOIN condition is placed in the WHERE clause.

The JPQL approach might look like this:

SELECT DISTINCT m 
FROM Message m, MessageReadDate mr 
WHERE m.id = mr.message.id AND mr.recipient.name = 'USER1'

      



hence the criteria API might look like this:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Message> cq = cb.createQuery(Message.class);
Root<Message> m = cq.from(Message.class);
Root<MessageReadDate> mr = cq.from(MessageReadDate.class);

Predicate p1 = cb.equal(mr.get("recipient").get("name"), cb.literal("USER1"));
Predicate p2 = cb.equal(mr.get("message").get("id"), m.get("id"));
Predicate criteria = cb.conjunction();
criteria = cb.and(criteria, p1);
criteria = cb.and(criteria, p2);

cq.where(criteria);
cq.select(m).distinct(true);

List<Message> messages = em.createQuery(cq).getResultList();

      

+1


source







All Articles