The standard way to avoid both n + 1 and Cartesian product issues while fetching with JPA
When someone has an object with fields that the collections are part of, you want to get the data with the least number of requests possible and use as little memory as possible. The first issue is addressed by "join fetch" in JPQL queries (N + 1 solution). However, "join fetch" (as can be easily seen from checking the corresponding SQL query) causes the problem of the Cartesian product: each "row" corresponding to the entity fields without cardinality is present in the returned result set with cardinality N_1 x N_2 x .. x N_m , where N_1 is the multiplicity of the first set, N_2 is the multiplicity of the second, and N_m is the multiplicity of the mth collection, assuming that the object has m fields that are collections.
Hibernate solves this problem with FetchMode.SUBSELECT (which, if I'm not mistaken, makes m + 1 queries, each of which doesn't return redundant data). What is the standard way to solve this problem in JPA (it seems to me that I cannot mix, at least in this case, JPA annotations with Hibernate tags)?
source to share
a safe way is to replace collections with queries, especially if the expected size is large enough to cause a performance issue:
-
You are removing the bidirectional side of @OneToMany leaving only the owned side of @ManyToOne
-
You select the parent object (Country for example) and then just run queries like:
select c from City c where c.country = :country select c from County c where c.country = :country select count(p), c.name from People p join p.country group by c.name
If this sounds radical, you can check out Christian Bauer (author of the famous Java Persistence with Hibernate opinion) on the subject.
source to share
When trying with
entityManager.find(PoDetail.class, poNumber)
you will have all lists declared in objects with @OneToMany
initialized with cartesian product no times, which will have duplicates as well. Of course, you can eliminate these duplicates using Set
, but Set does not preserve the order of insertion of data and when we try to display in the view we have scrambled strings.
I solved it using:
NamedQueries with parameters to avoid fetching the Cartesian product of collections.
This way, your browsing data will be the same as with constant data insertion order.
Here's some sample code:
Parent class Entity: (It has more list fields, I mention it here)
@Entity
@Table(name="PO_DETAILS")
@NamedQuery(name="PoDetail.findByPoNumber", query="SELECT p FROM PoDetail p where p.poNumber=:poNumber")
public class PoDetail implements Serializable {
@Id
@Column(name="PO_NUMBER", unique=true, nullable=false, length=30)
private String poNumber;
@Column(name="ACTION_TAKEN", length=2000)
private String actionTaken;
.....
//bi-directional one-to-many association to PcrDetail
@OneToMany(mappedBy="poDetail", cascade={CascadeType.ALL}, fetch=FetchType.EAGER, orphanRemoval=true)
private List<PcrDetail> pcrDetails;
Child class:
@Entity
@Table(name="PCR_DETAILS")
public class PcrDetail implements Serializable {
@Id
@Column(name="PCR_NUMBER", unique=true, nullable=false, length=30)
private String pcrNumber;
@Column(name="CONTRACT_ID", length=30)
private String contractId;
.....
//bi-directional many-to-one association to PoDetail
@ManyToOne(cascade={CascadeType.ALL})
@JoinColumn(name="PARENT_PO_NUMBER", insertable=false, updatable=false)
private PoDetail poDetail;
JPA DAO class:
public PoBean getPoDetails(PoBean poBean) {
PoDetail poDetail = poBean.getPoDetail();
String poNumber = poDetail.getPoNumber();
entityManagerFactory = JpaUtil.getEntityManagerFactory();
entityManager = entityManagerFactory.createEntityManager();
try {
try {
poDetail = (PoDetail) entityManager
.createNamedQuery("PoDetail.findByPoNumber")
.setParameter("poNumber", poNumber).getSingleResult();
} catch (NoResultException nre) {
poBean.setStatusCode(PopVO.ERROR);
poBean.setErrorMessage("No PO details foun with PO Number : "
+ poNumber);
}
return poBean;
} catch (Exception e) {
e.printStackTrace();
poBean.setStatusCode(PopVO.ERROR);
poBean.setErrorMessage(e.getMessage());
return poBean;
} finally {
entityManager.close();
}
}
source to share