Are there Best Practices in Hibernate for using aggregate

kristian · **Posted:** Thu Mar 02, 2006 11:18 am

I'm using following HQL query to determine a INVOICE, the CUSTOMER,
the INVOICEITEMS and the ARTICLES:

Code:

(Invoice) getHibernateTemplate().getSessionFactory()
                                  .getCurrentSession()
                                  .createQuery("FROM Invoice i " +
                                                  "JOIN FETCH i.customer " +
                                                  "LEFT JOIN FETCH i.invoiceItems it " +
                                                  "LEFT JOIN FETCH it.article " +
                                                  "WHERE i.id = :iid")
                                  .setParameter("iid",id)
              .uniqueResult();

Everything works perfect and is absolutely elegant. But I also want to
have the total of my invoice (sum(invoiceitem.quantity*article.price)).

How do i get it?

There a two obvious solutions to this Problem:

a) use a second Statement with GROUP BY and SUM
or
b) iterate on the objects and calculate the total using JAVA

Are there more solutions available? Maybe by extending my mappings?

Thanks in advance
kris

Hibernate version:
3.1
Mapping documents:

Code:

<hibernate-mapping>
   <class name="com.poi.poa.cross.model.Article" table="ARTIKEL">
        <id name="id" column="ART_SYSNR">
            <generator class="increment"/>
        </id>
      <property name="name"          column="BEZEICHNUNG" />
      <property name="price"          column="PREIS" type="float" />
      <property name="description"   column="TST_BIGTEXT" /> 
   </class>
</hibernate-mapping>

<hibernate-mapping>
    <class name="com.poi.poa.cross.model.Customer" table="KUNDE" dynamic-insert="true">
        <id name="id" column="KUNDE_SYSNR">
            <generator class="increment" />
        </id>
       <many-to-one name="title" column="ANREDE_SYSNR"
                     class="com.poi.poa.cross.model.Title" />
         
        <property name="lastname"    column="NACHNAME" />
        <property name="firstname"    column="VORNAME" />
        <property name="street"      column="STRASSE" />
        <property name="zipcode"   column="PLZ" />
        <property name="city"      column="ORT" />
        <property name="birthday"   column="GEBURTSDATUM" />
        <property name="tel"      column="TELEFON" />
        <property name="sex"      column="GESCHLECHT" />
        <property name="role"      column="ROLLE" />
        <property name="lastchange" column="LETZTEAEND" />
        <property name="isdeleted"  column="LOESCHKZ" />
    </class>
</hibernate-mapping>

<hibernate-mapping>
   <class name="com.poi.poa.cross.model.Invoice" table="RECHNUNG">
      <id name="id" column="RECH_SYSNR">
         <generator class="increment" />
      </id>
       <many-to-one name="customer" column="KUNDE_SYSNR"  
                 class="com.poi.poa.cross.model.Customer" 
                 not-null="true" />   
   
       <bag name="invoiceItems" table="RECHNUNGSPOS" inverse="true">
           <key column="RECH_SYSNR"/>
           <one-to-many class="com.poi.poa.cross.model.InvoiceItem" />
       </bag>      
      
      <property name="isPaid" column="BEZAHLTKZ" />
   </class>
</hibernate-mapping>

<hibernate-mapping>
   <class name="com.poi.poa.cross.model.InvoiceItem" table="RECHNUNGSPOS">
      <id name="id" column="RECHPOS_SYSNR">
         <generator class="increment" />
      </id>
      
      <property name="quantity"    column="MENGE" />
      
      <many-to-one name="invoice_id" column="RECH_SYSNR"
                class="com.poi.poa.cross.model.Invoice" />

      <many-to-one name="article" column="ARTIKEL_SYSNR" 
                class="com.poi.poa.cross.model.Article" />
   </class>
</hibernate-mapping>

Name and version of the database you are using:
Oracle 10g

Code:

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

You have a few options. The most efficient would be to calculate it in business logic, after all the invoiceItems have been loaded: that way you wouldn't do it unless the relevant method was called.

Another option would be to use a formula element and a subselect, in your Invoice mapping. Some performance impact, but all in the DB and without any extra objects being created.

You could group by invoice to get your sum, but then your invoiceItems and article fetches would have to be selects, and that affects performance.

You could use Critieria projections to implement this last option, too.