How can I do a JOIN FETCH in criteria api

Dear hibernate community,

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)?

thank you very much

You didn’t tell if there is something not working with your example.

Did you try that example and it didn’t work?

Dear @vlad,

I have updated my question, I hope it is more clear now

thank you very much

Just remove the productItemJoin and use the productItemFetch instead.

CriteriaBuilder builder = em.getCriteriaBuilder();

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

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

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

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

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

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

I changed code to predicates.add(builder.equal( productItemFetch.get( "status" ), "RECEIVED")); but compiler complains:

The method get(String) is undefined for the type Fetch<Ereturn,ProductItem>

I also tried casting from Fetch to Join like Join<Ereturn, ProductItem> productItemFetch = (Join<Ereturn, ProductItem>)er.fetch("productItems", JoinType.LEFT); but

Cannot cast from Fetch to Join

I am using Hibernate 5.2.17.Final

Just cast the productItemFetch to a Join and then use the productItemJoin to get the attribute.

CriteriaBuilder builder = em.getCriteriaBuilder();

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

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

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();
2 Likes