How can I do a JOIN FETCH in criteria api

Hibernate ORM
1

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

2

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

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

3

Dear @vlad,

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

thank you very much

4

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();
5

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

6

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();
1 Like