Reputation: 3067
PROBLEM: hibernate is ignoring JOIN FETCH I put in my query. My intention is to retrieve the product_item elements from a oneToMany relationship that has been set as lazy but this is not happening.
This is the query I am trying to run:
em.createQuery("SELECT DISTINCT e " +
"FROM ereturn e, " +
"user shipper " +
"JOIN FETCH product_item pi on pi.ereturn.id = e.id " +
"JOIN FETCH product_definition pd on pi.product.id = pd.id " +
"WHERE " +
"shipper.id = e.shipper.id " +
"AND e.scanDateTime IS NOT NULL " +
"AND e.status = 'RECEIVED'").getResultList()
this is the query execution plan:
Hibernate:
select
distinct ereturn0_.id as id1_1_,
ereturn0_.barcode as barcode2_1_,
ereturn0_.carrier as carrier27_1_,
ereturn0_.consignee as consign28_1_,
ereturn0_.consigneeFirstName as consigne3_1_,
ereturn0_.consigneeLastName as consigne4_1_,
ereturn0_.creationtime as creation5_1_,
ereturn0_.disabled as disabled6_1_,
ereturn0_.dispatchedDate as dispatch7_1_,
ereturn0_.failedReturnPOBoxPrivateBag as failedRe8_1_,
ereturn0_.globalCondition as globalCo9_1_,
ereturn0_.globalId as globalI10_1_,
ereturn0_.groupName as groupNa11_1_,
ereturn0_.invoice as invoice12_1_,
ereturn0_.notes as notes13_1_,
ereturn0_.pickupDateTime as pickupD14_1_,
ereturn0_.pickupDateTimeOffset as pickupD15_1_,
ereturn0_.pieces as pieces16_1_,
ereturn0_.processedByShipper as process17_1_,
ereturn0_.reasonToReturn as reasonT18_1_,
ereturn0_.returnAction as returnA19_1_,
ereturn0_.returnMethod as returnM20_1_,
ereturn0_.returned as returne21_1_,
ereturn0_.rma as rma22_1_,
ereturn0_.scanDateTime as scanDat23_1_,
ereturn0_.shipper as shipper29_1_,
ereturn0_.status as status24_1_,
ereturn0_.trackingNumber as trackin25_1_,
ereturn0_.weight as weight26_1_
from
ereturn ereturn0_ cross
join
user user1_
inner join
product_item productite2_
on (
productite2_.ereturn=ereturn0_.id
)
inner join
product_definition productdef3_
on (
productite2_.product=productdef3_.id
)
where
user1_.id=ereturn0_.shipper
and (
ereturn0_.scanDateTime is not null
)
and ereturn0_.status='RECEIVED'
The query gets executed but ereturn.productItems are not fetched hence json serialization complains.
And these are my classes:
public class Ereturn {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@ManyToOne
@JoinColumn(name = "shipper")
private User shipper = new User("", UserType.SHIPPER);
@ManyToOne
@JoinColumn(name = "consignee")
private User consignee;
@ManyToOne
@JoinColumn(name = "carrier")
private User carrier = new User("", UserType.CARRIER);
@JsonManagedReference(value="ereturn-parcel")
@OneToMany(mappedBy = "ereturn", fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)
private List<Parcel> parcels = new ArrayList<>();
@JsonManagedReference(value="ereturn-productItems")
@OneToMany(mappedBy = "ereturn", fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)
private List<ProductItem> productItems = new ArrayList<>();
...
}
public class ProductItem {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@ManyToOne
@JoinColumn(name = "product")
private ProductDefinition product;
@JsonBackReference(value="ereturn-productItems")
@ManyToOne
@JoinColumn(name = "ereturn")
private Ereturn ereturn;
...
}
public class ProductDefinition {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@ManyToOne
@JoinColumn(name = "shipper")
private User shipper;
@JsonIgnore
@OneToMany(mappedBy = "product", cascade = CascadeType.REFRESH)
private List<ProductItem> productItems = new ArrayList<>();
...
}
public class User implements SecurityContext {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@JsonIgnore
@OneToMany(mappedBy = "shipper", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
private Set<ProductDefinition> productDefinitions = new HashSet<>();
@JsonIgnore
@OneToMany(mappedBy = "shipper", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
private Set<Ereturn> ereturns = new HashSet<>(); // Client process ereturn to finish it
@JsonIgnore
@OneToMany(mappedBy = "shipper", cascade = CascadeType.ALL)
private Set<Contract> contracts = new HashSet<>();
private Boolean disabled = Boolean.FALSE;
...
}
QUESTION 1: Why the query is not fetching elements from product_item table?
QUESTION 2: the query above returns as many elements as rows returned from the database instead of grouping by ereturn. Why is that?
Upvotes: 1
Views: 377
Reputation: 19050
I believe that JOIN FETCH
with ON
conditions not work to fetch entities on Hibernate. I not found this kind of use on Hibernate documentation or examples on the Internet.
To make the FETCH
work, you need to use the relationship between the two entities and use the JOIN FETCH
without the ON conditions, like:
SELECT e FROM eReturn e
JOIN FETCH e.productItens
And remember to not use the fetched alias to apply WHERE
conditions.
Upvotes: 2