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 to share
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 to share