jpa 2 CriteriaQuery do not discriminate child entities

Question

I have an entity which have a relation of one to many with another entity. say

1A -> 1.*B

i wrote this query.

CriteriaBuilder cb = super.getEntityManager().getCriteriaBuilder();
CriteriaQuery<A> select = cb.createQuery(A.class);
Root<A> from = select.from(A.class);
Join<A, B> joinB = from.<A, B>join("b");
select.select(from);
select.where( cb.between(joinB.<Date>get("date"), dateA, dateB) );

the problem: The query is returning all the child entities instead of only those which meets the between constrain.

so even if A has 3 child B and only two meets the criteria, the query always returns 3 B's.

checkin the hibernate's generated sql, i can see this.

select a from A where a_id = ? and b_date b.date between ? and ?

which is fine, all the entities A retrieved meets the criteria, but then a second query is thrown to get the B entities.

select * from b where aid = ?

which is wrong because the select should be

select * from b where aid = ? and b.date between ? and ?

i had this problem once, but my query did't use CriteriaBuilder, and it was solved by using JOIN FETCH, but with CriteriaQuery i have't been able to solve it.

score 0 · Accepted Answer · answered Feb 09 '15 at 21:04

Ok, the solution was easy (at the end) i just had to add a cast from fetch to join, this is the final query.

CriteriaBuilder cb = super.getEntityManager().getCriteriaBuilder();
CriteriaQuery<A> select = cb.createQuery(A.class);
Root<A> from = select.from(A.class);
Join<A, B> joinB =   (Join<A, B>)  from.<A, B>fetch("b");//CAST.
select.select(from);
select.where( cb.between(joinB.<Date>get("date"), dateA, dateB) );

jpa 2 CriteriaQuery do not discriminate child entities

1 Answers1