CODEWITHSUNDEEP
jpamultiple-join-rows
r.l.
r.l.

Reputation: 41

Problem with jpa criteriabuilder multiple joins

I have a table with 2 foreign keys. I need to create a query that aggregates results that use both foreign keys.

i.e. Table A "b_id" is a foreign key to Table B "id", Table A "c_id" is a foreign key to Table C "id" 123 is the variable id i want to query

I can successfully find 1 result where id = 123 that joins to Table B as follows ...

EntityManager em = getEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery();
Root<A> rt = cq.from(A.class);
List<Predicate> predList = new ArrayList<>();

Join<A, B> joinB = rt.join("b_id", JoinType.INNER);
Predicate p1 = cb.equal(joinB.get("id"),123);
predList.add(p1); 

cq.select(rt).where(predList.toArray(new Predicate[predList.size()]));
Query q = em.createQuery(cq);

... and I can successfully find 1 result where id = 123 that joins to Table C as follows ...

EntityManager em = getEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery();
Root<A> rt = cq.from(A.class);
List<Predicate> predList = new ArrayList<>();

Join<A, C> joinC = rt.join("c_id", JoinType.INNER);
Predicate p1 = cb.equal(joinC.get("id"),123);
predList.add(p1); 

cq.select(rt).where(predList.toArray(new Predicate[predList.size()]));
Query q = em.createQuery(cq);

So I thought to get both results in one query I need to "or" the 2 predicates as below but it doesn't work?

EntityManager em = getEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery();
Root<A> rt = cq.from(A.class);

List<Predicate> predList = new ArrayList<>();

Join<A, B> joinB = rt.join("b_id", JoinType.INNER);
Join<A, C> joinC = rt.join("c_id", JoinType.INNER);
Predicate p1 = cb.equal(joinB.get("id"),123);
Predicate p2 = cb.equal(joinC.get("id"),123);
predList.add(cb.or(p1, p2)); 

cq.select(rt).where(predList.toArray(new Predicate[predList.size()]));
Query q = em.createQuery(cq);

What am I doing wrong here please? thanks ...

The SQL generated by this query when I apply the change suggested by Twister is (with superfluous fields omitted)

SELECT t1.my_id FROM e t3, d t2, a t1, c t0 WHERE (((t1.my_id = 123) OR (t3.my_id = 123)) AND (((t0.id = t1.id) AND (t2.id = t0.id)) AND (t3.id = t2._id)))

Upvotes: 0

Views: 3438

Answers (1)

Twister
Twister

Reputation: 161

The function where(Predicate... restrictions) of CriteriaQuery uses operator 'AND' for all restrictions.

If you want to use "or" for 2 predicates, you can try this:

cq.select(rt).where(cb.or(predList.toArray(new Predicate[0])));

Upvotes: 0

Related Questions