'How to perform left join on two sql queries using criteria API
I have two tables and their classes as follows:
@Entity
@Table(name="Master")
public class Master {
@Column(name = "master_col_one")
private String masterColumnOne;
@Column(name = "master_col_two")
private String masterColumnTwo;
// Other fields and so on
@NotNull
@JoinColumn(name = "slave_col_three", nullable = false)
@ManyToOne(optional = false)
private Slave slaveData;
}
@Entity
@Table(name="slave")
public class Slave {
@Column(name = "slave_col_one")
private String slaveColumnOne;
@Column(name = "slave_col_two")
private String slaveColumnTwo;
// Other fields
}
My native SQL query is like this:
SELECT * FROM master m LEFT JOIN
(SELECT DISTINCT slave_col_one, slave_col_two FROM slave) s ON m.master_col_one = s.slave_col_one
AND m.master_col_two = s.slave_col_two
I need to build a criteria query for the above native SQL query.
I have built this much query so far:
final CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
final CriteriaQuery<Master> criteriaQuery = criteriaBuilder
.createQuery(Master.class);
final Root<Master> root = criteriaQuery.from(Master.class);
Join<Master, Slave> join = root.join(Master_.slaveData);
I think my join is not correct as it's not fetching distinct selective columns from slave table as per requirements, I searched the internet a lot but couldn't come up with a solution.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
