Left join condition in CriteriaQuery
Hi everyone, I am trying to do this in CriteriaQuery, I have searched for so long but I cannot find anything, can someone help me?
SELECT b.name
FROM Empl a
LEFT OUTER JOIN Deplo b ON (a.id_depl = b.id_depl) AND b.id_place = 2;
I'm just trying to fulfill a condition in the left member of the join, I saw the ".on" function, but I don't know if this will work and how it works because I was trying to do something like this:
Join Table1, Table2j1 = root.join(Table1_.table2, JoinType.LEFT).on(cb.and(cb.equal(table2_.someid, someId)));
But this requires a boolean expression.
+3
source to share
1 answer
The proposal on
was submitted in JPA 2.1. Solution example:
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<String> cq = cb.createQuery(String.class);
Root<Empl> a = cq.from(Empl.class);
Join<Empl,Deplo> b = a.join("b", JoinType.LEFT); //left outer join
b.on(
cb.and(
cb.equal(b.get("id_place"), cb.parameter(Integer.class, "idPlace")),
cb.equal(b.get("id_depl"), a.get("id_depl"))
)
);
cq.select(b.<String>get("name"));
List<String> results = em.createQuery(cq)
.setParameter("idPlace", 2)
.getResultList();
+8
source to share