How to use <property formula= > ?

micho2001 · **Posted:** Thu Jan 15, 2004 4:33 pm

hi!

I have a many-to-one relationship (Proposal has many ProposalDetail)and it works great.

I would like compute for a given Proposal the sum of certain fields in its ProposalDetail.

I tried reading the hibernate doc but doesnt say much on formula properties... can someone help or give a suggestion?

P.D: My domain objects know nothing of hibernate, DAO's are the only ones that know anything about hibernate

Ive got this mappings

Code:

<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN" 
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
    <class
        name="com.cce.domain.proposal.Proposal"
        table="package_contractor"
        dynamic-update="false"
        dynamic-insert="false"
    >

        <id
            name="id"
            column="id"
            type="long"
            unsaved-value="0"
        >
            <generator class="native">
            </generator>
        </id>

        <set
            name="proposalItems"
            lazy="true"
            inverse="true"
            cascade="all-delete-orphan"
            sort="unsorted"
        >

              <key
                  column="package_contractor"
              />

              <one-to-many
                  class="com.cce.domain.proposal.ProposalItem"
              />
        </set>
        
        <property formula="????????" >
    </class>

</hibernate-mapping>

And this other class

Code:

<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN" 
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
    <class
        name="com.cce.domain.proposal.ProposalItem"
        table="proposaldetail"
        dynamic-update="false"
        dynamic-insert="false"
    >

        <id
            name="id"
            column="id"
            type="long"
            unsaved-value="0"
        >
            <generator class="native">
            </generator>
        </id>

      

        <property
            name="unitLabour"
            type="double"
            update="true"
            insert="true"
            column="unit_labor"
        />

        <property
            name="quantity"
            type="double"
            update="true"
            insert="true"
            column="quantity"
        />

        <property
            name="unit_cost"
            type="double"
            update="true"
            insert="true"
            column="unit_cost"
        />

        <many-to-one
            name="proposal"
            class="com.cce.domain.proposal.Proposal"
            cascade="none"
            outer-join="auto"
            update="true"
            insert="true"
            column="package_contractor"
        />


    </class>

</hibernate-mapping>

thanks anyone![/b]

micho2001 · **Posted:** Thu Jan 15, 2004 4:36 pm

My solution right now is to initialize the ProposalItem from a Proposal and then just iterate through all of them adding up the properties I need..

As you can see its not very elegant... would love some help!

michael · **Posted:** Thu Jan 15, 2004 4:46 pm

You can insert any plain SQL for the formula attribute, which will be included in the query to select the property. The easiest way to try it would be to enable SQL logging and see what SQL hibernate generates when you set something for the formula attribute.

drj · **Posted:** Fri Jan 16, 2004 1:15 am

Something like

Code:

java.util.List list = session.createQuery(
    "select sum (pi.unitLabour), sum(pi.quantity) from Proposal p " +
    join ProposalItem pi on p.package_contractor = pi.package_contractor " + 
    "where ....")
    .list();

You'll should get a single Array of objects back in the list.
Justin

micho2001 · **Posted:** Fri Jan 16, 2004 11:35 am

Thanks guys!

I solved it this way:

Code:

 <property name="total" formula="(select sum(proposaldetail.quantity*(proposaldetail.unit_cost+proposaldetail.unit_labor)) from proposaldetail where proposaldetail.package_contractor=id)" />

The only thing is that aparently I cant use table aliases, but that dont matter![/code]