Mapping a list with GROUP BY clause

joostschouten · **Posted:** Sun Mar 25, 2007 7:25 am

Hi,

I have mapped my User properly as well as a List of (financial) Transactions the user has made. This all works fine. Now however, I wish to not map every individual transaction the User has made, but just the sum ordered by the currency. So the result of the SQL:

SELECT currency, sum(amount) as amount from transactions GROUP BY currency;

Should be read in in my List of Transactions.

Is there a way and if so, how can I define this in my mapping file?

Thank you,
Joost Schouten

batmat · **Posted:** Sun Mar 25, 2007 11:18 am

I'm sorry, I'm not sure I understand your needs. But maybe the custom methods could correspond to what you want: http://www.hibernate.org/hib_docs/v3/re ... erysql-cud Or maybe the formula? : http://www.hibernate.org/hib_docs/v3/re ... n-property (at the end of this part)

joostschouten · **Posted:** Mon Mar 26, 2007 7:17 am

Dear Batmat,

Thank ytou for your reply. I think I'm looking for something like the formula or collection mapping's where clause. I tried the folowing, which doesn't work as the order by clause is injected in the where clause and not understood. Nor would this sum the amount like I would want to.

Code:

<set
        name="summedReservedCredit"
        lazy="true"
        inverse="true"
   cascade="none"
   where="GROUP BY receiver,currency"
    >
        <key>
            <column name="receiver" />
        </key>
        
        <one-to-many 
            class="com.jsportal.projectportal.portalBeans.FinancialTransaction"
        />
</set>

I looked into using named queries like:

Code:

<set
        name="summedCredit"
        lazy="true"
        inverse="true"
      cascade="none"
    >
      <key/>
      <one-to-many class="FinancialTransaction"/>
      <loader query-ref="summedCreditSQL"/>
   </set>
   
   <sql-query name="summedCreditSQL">
      <load-collection alias="sc" role="User.summedCredit"/>
      SELECT sc.* FROM transactions sc WHERE sc.receiver=:id AND sc.reservation=0 GROUP BY sc.receiver, sc.currency, sc.type
   </sql-query>

This does get some result, but still doesn't sum my amount. What other tools are there to acomplish my result. Is there a way to have hibernate simply populate my User bean with a resultset and have my bean handle it on setSummedCreditRS(ResultSet)?

Thank you,
Joost