how to do a JOIN FETCH in jpa criteria

Question

I am trying to translate the query below to criteria api.

SELECT er from ereturn er JOIN FETCH product_item pi ON pi.ereturn_id = er.id WHERE pi.status = "RECEIVED"

To something like this:

CriteriaBuilder builder = em.getCriteriaBuilder();

CriteriaQuery<Ereturn> criteria = builder.createQuery( Ereturn.class );

Root<Ereturn> er = criteria.from(Ereturn.class);
Join<Ereturn, ProductItem> productItemJoin = er.join("productItems", JoinType.LEFT);
Fetch<Ereturn, ProductItem> productItemFetch = er.fetch("productItems", JoinType.LEFT);

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

predicates.add(builder.equal( productItemJoin.get( "status" ), "RECEIVED"));

criteria.where(
        builder.and(predicates.toArray(new Predicate[predicates.size()]))
);

List<Ereturn> ers = em.createQuery( criteria )
    .getResultList();

The problem is that hibernate generates this query:

select
ereturn0_.id as ...
...
productite6_.id as ...
...
from
ereturn ereturn0_ 
join
product_item productite1_ 
on ereturn0_.id = productite1_.ereturn 
join
product_item productite6_ 
on ereturn0_.id = productite6_.ereturn
where
productite1_.status='RECEIVED';

QUESTION: how could I tell hibernate to generate this query with only 1 join while fetching fields from both tables (ereturn and productItem)?

Umm... by not calling both `er.join` and `er.fetch`? Just use `productItemFetch` in your predicate and remove `productItemJoin` altogether — crizzis, May 27 '18 at 16:11
@crizzis I tried that but compiler complains... The method get(String) is undefined for the type Fetch — masber, May 27 '18 at 16:34
Try [this solution](https://stackoverflow.com/questions/17306655/using-the-jpa-criteria-api-can-you-do-a-fetch-join-that-results-in-only-one-joi) — crizzis, May 27 '18 at 16:44
Apparently, the result of `fetch` does not implement `Join` any longer. As another workaround, you could try casting it to a `Path`, or using another fetch: `productItemFetch.fetch("status")`, for the predicate. If neither of the above work, consider providing the query with a load graph, as suggested in another answer to the question I linked to — crizzis, May 27 '18 at 17:02
I think if you're doing a select based on the a criteria of the join then you don't need the fetch since the join has to be fetched in order to test the selection criteria. Just get rid of the Fetch. — K.Nicholas, May 27 '18 at 23:36
Possible duplicate of [JPA 2 Criteria Fetch Path Navigation](https://stackoverflow.com/questions/4511368/jpa-2-criteria-fetch-path-navigation) — K.Nicholas, May 28 '18 at 20:02

K.Nicholas · Accepted Answer · 2018-05-28T16:47:47.370

Indeed that is an issue, but your answer shows two additional tables and isn't the correct answer. Try this:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Ereturn> q = cb.createQuery(Ereturn.class);
Root<Ereturn> r = q.from(Ereturn.class);
r.fetch("productItem", JoinType.LEFT);
// here join with the root instead of the fetch
// casting the fetch to the join could cause portability problems
// plus, not nice
q.where(cb.equal(r.get("productItem").get("status"), "received"));
Ereturn ereturn= em.createQuery(q).getSingleResult();

which gives

select ereturn0_.id as id1_0_0_, productite1_.id as id1_1_1_, ereturn0_.parentEreturn_id as parentCo2_0_0_, ereturn0_.productItem_id as productI3_0_0_, productite1_.status as status2_1_1_ 
from Ereturn ereturn0_ 
left outer join ProductItem productite1_ on ereturn0_.productItem_id=productite1_.id 
where productite1_.status=?

See JPA 2 Criteria Fetch Path Navigation a little further down in the answers.

masber · Answer 2 · 2018-05-29T02:08:09.297

7

Apparently casting is the way to go however we need to take into consideration that .fetch()/.join() are generic methods so the casting is little bit different

Option 1

Fetch<Ereturn, ProductItem> productItemFetch = er.fetch("productItems", JoinType.LEFT);
Join<Ereturn, ProductItem> productItemJoin = (Join<Ereturn, ProductItem>) productItemFetch;

Option 2

Join<Ereturn, ProductItem> productItemJoin = (Join<Ereturn, ProductItem>) er.<Ereturn, ProductItem>fetch("productItems", JoinType.LEFT)

And now the query is correct to what I was looking for:

select
...
from
    ereturn ereturn0_ 
left outer join
    product_item productite5_ 
        on ereturn0_.id=productite5_.ereturn 
where
    and productite5_.status=?

edited May 29 '18 at 02:08

answered May 27 '18 at 17:30

masber

2,875
7
29
49

I think if you're doing a select based on the a criteria of the join then you don't need the fetch since the join has to be fetched in order to test the selection criteria. Just get rid of the Fetch. It doesn't seem to be adding anything to the query anyway. – K.Nicholas May 27 '18 at 23:35
4

@K.Nicholas no, missing the fetch means I won't get those records hence will end up in a N+1 problem. I already tested it. Could you please remove your down vote please? – masber May 28 '18 at 12:51
See new answer. I had to look into it a bit more. Your answer works but I think there is a better answer. – K.Nicholas May 28 '18 at 16:54

how to do a JOIN FETCH in jpa criteria

2 Answers2

Linked