-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 
Author Message
 Post subject: Criteria API clause on join/fetch table not working
PostPosted: Mon Jan 01, 2018 3:21 am 
Newbie

Joined: Mon Jan 01, 2018 3:13 am
Posts: 3
Hi,

I need to make a dynamic query based on the query below so I decided to use criteria API:

Code:
@NamedQuery(name = "Ereturn.fetchBatch", query = "SELECT DISTINCT(e) FROM ereturn e " +
                    "JOIN FETCH e.productItems pi " +
                    "JOIN FETCH pi.product " +
                    "JOIN FETCH e.consignee " +
                    "WHERE " +
                    "e.status = 'RECEIVED' AND " +
                    "e.carrier.id = :carrierId AND " +
                    "e.shipper.id = :shipperId AND " +
                    "pi.returnAction = :returnAction")


This is the best I can get using Criteria API but the issue is that it ignores the filtering by "returnAction" field returning productItems with any returnAction value.

Code:
CriteriaBuilder builder = em.getCriteriaBuilder();

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

    Root<Ereturn> er = criteria.from(Ereturn.class);
    //        Root<ProductItem> pi = criteria.from(ProductItem.class);

    if (carrier.getId() != null) er.fetch("carrier");
    if (shipper.getId() != null) er.fetch("shipper");
    er.fetch("consignee");
    er.fetch("productItems");
    Join<Ereturn, ProductItem> prodItemJoin = er.join("productItems");

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

    predicates.add(builder.equal( er.get( "status" ), ReturnStatus.RECEIVED));
    //        predicates.add(builder.equal(pi.get("ereturn"), er.get("id")));

    if (returnAction != null && !returnAction.equals("")) {
    //            predicates.add(builder.equal(pi.get("returnAction"), ReturnAction.valueOf(returnAction)));
        predicates.add(builder.equal(prodItemJoin.get("returnAction"), ReturnAction.valueOf(returnAction)));
    }

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

    criteria.select( er ).distinct(true);

    //        prodItemJoin.on(builder.and(predicates.toArray(new Predicate[predicates.size()])));

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


The query executes but the predicate below is not taking effect:

Code:
predicates.add(builder.equal(prodItemJoin.get("returnAction"), ReturnAction.valueOf(returnAction)));


This the query executed by hibernate:

Code:
Hibernate:
select
    distinct ereturn0_.id as id1_1_0_,
    user2_.id as id1_7_1_,
    user3_.id as id1_7_2_,
    user4_.id as id1_7_3_,
    productite5_.id as id1_6_4_,
    ereturn0_.barcode as barcode2_1_0_,
    ereturn0_.carrier as carrier27_1_0_,
    ereturn0_.consignee as consign28_1_0_,
    ereturn0_.consigneeFirstName as consigne3_1_0_,
    ereturn0_.consigneeLastName as consigne4_1_0_,
    ereturn0_.creationtime as creation5_1_0_,
    ereturn0_.disabled as disabled6_1_0_,
    ereturn0_.dispatchedDate as dispatch7_1_0_,
    ereturn0_.failedReturnPOBoxPrivateBag as failedRe8_1_0_,
    ereturn0_.globalCondition as globalCo9_1_0_,
    ereturn0_.globalId as globalI10_1_0_,
    ereturn0_.groupName as groupNa11_1_0_,
    ereturn0_.invoice as invoice12_1_0_,
    ereturn0_.notes as notes13_1_0_,
    ereturn0_.pickupDateTime as pickupD14_1_0_,
    ereturn0_.pickupDateTimeOffset as pickupD15_1_0_,
    ereturn0_.pieces as pieces16_1_0_,
    ereturn0_.processedByShipper as process17_1_0_,
    ereturn0_.reasonToReturn as reasonT18_1_0_,
    ereturn0_.returnAction as returnA19_1_0_,
    ereturn0_.returnMethod as returnM20_1_0_,
    ereturn0_.returned as returne21_1_0_,
    ereturn0_.rma as rma22_1_0_,
    ereturn0_.scanDateTime as scanDat23_1_0_,
    ereturn0_.shipper as shipper29_1_0_,
    ereturn0_.status as status24_1_0_,
    ereturn0_.trackingNumber as trackin25_1_0_,
    ereturn0_.weight as weight26_1_0_,
    user2_.accName as accName2_7_1_,
    user2_.accNum as accNum3_7_1_,
    user2_.address as address4_7_1_,
    user2_.bankName as bankName5_7_1_,
    user2_.branchName as branchNa6_7_1_,
    user2_.bsb as bsb7_7_1_,
    user2_.city as city8_7_1_,
    user2_.contactNumber as contactN9_7_1_,
    user2_.country as country10_7_1_,
    user2_.disabled as disable11_7_1_,
    user2_.email as email12_7_1_,
    user2_.firstName as firstNa13_7_1_,
    user2_.importEnabled as importE14_7_1_,
    user2_.labelApiUrl as labelAp15_7_1_,
    user2_.lastName as lastNam16_7_1_,
    user2_.password as passwor17_7_1_,
    user2_.pickupApiUrl as pickupA18_7_1_,
    user2_.pobox as pobox19_7_1_,
    user2_.postalcode as postalc20_7_1_,
    user2_.secondContactNumber as secondC21_7_1_,
    user2_.state as state22_7_1_,
    user2_.type as type23_7_1_,
    user2_.web as web24_7_1_,
    user3_.accName as accName2_7_2_,
    user3_.accNum as accNum3_7_2_,
    user3_.address as address4_7_2_,
    user3_.bankName as bankName5_7_2_,
    user3_.branchName as branchNa6_7_2_,
    user3_.bsb as bsb7_7_2_,
    user3_.city as city8_7_2_,
    user3_.contactNumber as contactN9_7_2_,
    user3_.country as country10_7_2_,
    user3_.disabled as disable11_7_2_,
    user3_.email as email12_7_2_,
    user3_.firstName as firstNa13_7_2_,
    user3_.importEnabled as importE14_7_2_,
    user3_.labelApiUrl as labelAp15_7_2_,
    user3_.lastName as lastNam16_7_2_,
    user3_.password as passwor17_7_2_,
    user3_.pickupApiUrl as pickupA18_7_2_,
    user3_.pobox as pobox19_7_2_,
    user3_.postalcode as postalc20_7_2_,
    user3_.secondContactNumber as secondC21_7_2_,
    user3_.state as state22_7_2_,
    user3_.type as type23_7_2_,
    user3_.web as web24_7_2_,
    user4_.accName as accName2_7_3_,
    user4_.accNum as accNum3_7_3_,
    user4_.address as address4_7_3_,
    user4_.bankName as bankName5_7_3_,
    user4_.branchName as branchNa6_7_3_,
    user4_.bsb as bsb7_7_3_,
    user4_.city as city8_7_3_,
    user4_.contactNumber as contactN9_7_3_,
    user4_.country as country10_7_3_,
    user4_.disabled as disable11_7_3_,
    user4_.email as email12_7_3_,
    user4_.firstName as firstNa13_7_3_,
    user4_.importEnabled as importE14_7_3_,
    user4_.labelApiUrl as labelAp15_7_3_,
    user4_.lastName as lastNam16_7_3_,
    user4_.password as passwor17_7_3_,
    user4_.pickupApiUrl as pickupA18_7_3_,
    user4_.pobox as pobox19_7_3_,
    user4_.postalcode as postalc20_7_3_,
    user4_.secondContactNumber as secondC21_7_3_,
    user4_.state as state22_7_3_,
    user4_.type as type23_7_3_,
    user4_.web as web24_7_3_,
    productite5_.disabled as disabled2_6_4_,
    productite5_.ereturn as ereturn18_6_4_,
    productite5_.expirationDate as expirati3_6_4_,
    productite5_.feedback as feedback4_6_4_,
    productite5_.forward as forward5_6_4_,
    productite5_.groupName as groupNam6_6_4_,
    productite5_.location as location7_6_4_,
    productite5_.lotNumber as lotNumbe8_6_4_,
    productite5_.lotReceivedDate as lotRecei9_6_4_,
    productite5_.lotStatus as lotStat10_6_4_,
    productite5_.notes as notes11_6_4_,
    productite5_.price as price12_6_4_,
    productite5_.processed as process13_6_4_,
    productite5_.product as product19_6_4_,
    productite5_.quantity as quantit14_6_4_,
    productite5_.quarantine as quarant15_6_4_,
    productite5_.reserve as reserve16_6_4_,
    productite5_.returnAction as returnA17_6_4_,
    productite5_.ereturn as ereturn18_6_0__,
    productite5_.id as id1_6_0__
from
    ereturn ereturn0_
inner join
    product_item productite1_
        on ereturn0_.id=productite1_.ereturn
inner join
    user user2_
        on ereturn0_.carrier=user2_.id
inner join
    user user3_
        on ereturn0_.shipper=user3_.id
inner join
    user user4_
        on ereturn0_.consignee=user4_.id
inner join
    product_item productite5_
        on ereturn0_.id=productite5_.ereturn
where
    ereturn0_.status=?
    and productite1_.returnAction=?  <<=== THE CLAUSE LOOKS RIGHT TO ME


This is the relationship from Ereturn to ProductItem:

Code:
public class Ereturn {
    ...
    @JsonManagedReference(value="ereturn-productItems")
    @OneToMany(mappedBy = "ereturn", fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)
    private List<ProductItem> productItems = new ArrayList<>();
    ...
}


And same from ProductItem to Ereturn:

Code:
public class ProductItem {
   ...
   @JsonBackReference(value="ereturn-productItems")
   @ManyToOne
   @JoinColumn(name = "ereturn")
   ...
}


PROBLEM: criteria API is not filtering and returning ereturn.productItems with any value in field returnAction.

QUESTION: How can I make my criteria API query to filter by productItem.returnAnction field?

Thank you very much


Top
 Profile  
 
 Post subject: Re: Criteria API clause on join/fetch table not working
PostPosted: Mon Jan 01, 2018 2:05 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1638
Location: Romania
You can't filter what you JOIN FETCH because collections need to be fetched entirely. It's by design to work like that.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.