Following is my code Here I am using multiple lists to fetch data from database. On fetching data from hql query it is showing exception.
Pojo Class
public class BillDetails implements java.io.Serializable { private Long billNo; // other fields @LazyCollection(LazyCollectionOption.FALSE) private List<BillPaidDetails> billPaidDetailses = new ArrayList<BillPaidDetails>(); private Set productReplacements = new HashSet(0); @LazyCollection(LazyCollectionOption.FALSE) private List<BillProduct> billProductList = new ArrayList<BillProduct>(); //getter and setter }
hmb.xml file
<class name="iland.hbm.BillDetails" table="bill_details" catalog="retail_shop"> <id name="billNo" type="java.lang.Long"> <column name="bill_no" /> <generator class="identity" /> </id> <bag name="billProductList" table="bill_product" inverse="true" lazy="false" fetch="join"> <key> <column name="bill_no" not-null="true" /> </key> <one-to-many class="iland.hbm.BillProduct" /> </bag> <bag name="billPaidDetailses" table="bill_paid_details" inverse="true" lazy="false" fetch="select"> <key> <column name="bill_no" not-null="true" /> </key> <one-to-many class="iland.hbm.BillPaidDetails" /> </bag> <set name="productReplacements" table="product_replacement" inverse="true" lazy="false" fetch="join"> <key> <column name="bill_no" not-null="true" /> </key> <one-to-many class="iland.hbm.ProductReplacement" /> </set> </class>
Hql query
String hql = "select distinct bd,sum(bpds.amount) from BillDetails as bd " + "left join fetch bd.customerDetails as cd " + "left join fetch bd.billProductList as bpd " + "left join fetch bpd.product as pd " +"left join fetch bd.billPaidDetailses as bpds " + "where bd.billNo=:id " + "and bd.client.id=:cid ";
I am trying following query to fetch data from database but this is showing org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags
How to resolve this
Hibernate doesn't allow fetching more than one bag because that would generate a Cartesian product.
Now, you will find lots of answers, blog posts, videos, or other resources telling you to use a Set
instead of a List
for your collections.
That's terrible advice!
Using Sets
instead of Lists
will make the MultipleBagFetchException
go away, but the Cartesian Product will still be there.
Instead of using multiple JOIN FETCH
in a single JPQL or Criteria API query:
List<Post> posts = entityManager.createQuery(""" select p from Post p left join fetch p.comments left join fetch p.tags where p.id between :minId and :maxId """, Post.class) .setParameter("minId", 1L) .setParameter("maxId", 50L) .getResultList();
You can do the following trick:
List<Post> posts = entityManager.createQuery(""" select distinct p from Post p left join fetch p.comments where p.id between :minId and :maxId """, Post.class) .setParameter("minId", 1L) .setParameter("maxId", 50L) .setHint(QueryHints.PASS_DISTINCT_THROUGH, false) .getResultList(); posts = entityManager.createQuery(""" select distinct p from Post p left join fetch p.tags t where p in :posts """, Post.class) .setParameter("posts", posts) .setHint(QueryHints.PASS_DISTINCT_THROUGH, false) .getResultList();
As long as you fetch at most one collection using JOIN FETCH
, you will be fine. By using multiple queries, you will avoid the Cartesian Product since any other collection but the first one is fetched using a secondary query.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With