hql查询以获取数据和金额总和 [英] hql query to fetch data and sum of amount

问题描述

我正在编写查询以从表中获取数据

I am writing query to fetch data from tables

我的hql查询是

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

以上查询工作正常

我想编写查询以获取billPaidDetailses的所有金额字段的总和.

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

billPaidDetailses是BillDetails类中的列表.

我正在尝试执行以下查询，但无法正常工作

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

返回的错误是

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

推荐答案

引用

Hibernate当前还没有扩展分组的实体，因此您 如果cat的所有属性未聚合，则不能按cat分组. 您必须明确列出所有未聚合的属性.

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

为您提供HQL查询，例如:

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

与本机SQL查询相比，唯一的优势是Hibernate能够以分层结构重新组合实体.

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

这篇关于hql查询以获取数据和金额总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！