CODEWITHSUNDEEP
javahibernatejpaormhql
xrcwrn
xrcwrn

Reputation: 5327

hql query to fetch data and sum of amount

I am writing query to fetch data from tables

my hql query is

SELECT distinct bd 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
    WHERE bd.billNo=:id
    AND bd.client.id=:cid

Above query is working properly

I want to write query to fetch sum of all amount field of billPaidDetailses.

billPaidDetailses is a list in BillDetails class.

I am trying following query for that but it is not working

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 ";

The error returned is

org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch 
    multiple bags

Upvotes: 0

Views: 2713

Answers (1)

Vlad Mihalcea
Vlad Mihalcea

Reputation: 153780

Quoting Hibernate manual:

Hibernate also does not currently expand a grouped entity, so you cannot write group by cat if all properties of cat are non-aggregated. You have to list all non-aggregated properties explicitly.

It means you will have to add all properties for all joined entities:

  • BillDetails bd
  • bd.customerDetails cd
  • bd.billProductList bpd
  • bpd.product pd

giving you a HQL query like:

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
group by
    bd.id,
    bd.propertyA,
    bd.propertyB,
    cd.id,
    cd.propertyC,
    cd.propertyD,
    pd.id,
    pd.propertyE,
    pd.propertyF,
    bpds.id,
    bpds.propertyG,
    bpds.propertyH

The only advantage over a native SQL query is that Hibernate manages to regroup entities in a hierarchical structure.

Upvotes: 1

Related Questions