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