Hibernate aggregates + Spring MVC

Question

Hi,

I am using Hibernate with my Spring MVC project.

Lets say my model has 2 objects each linked to Oracle tables, respectively USERS (USERID, NAME) and USERMONEYDATA (CATEGORY, USERID, AMOUNT)

My users can add , edit and remove rows in USERMONEYDATA, that belongs to them of course. Now, I want to have a view that aggregates those data.

Using oracle, I made a simple view to get the total amount per user and category :

select  userid, category, sum(amount)
from USERS a inner join USERMONEYDATA b on a.USERID = b.USERID
group by userid, category

But what is the best way to use it? should I create a new MODEL object specifically for this view ?

Should I aggregate directly in Hibernate ? But if yes, how do I display the results if I dont have a specific POJO object to map it to ?

thanks

Answer 1

If User and UserMoneyData are mapped as Hibernate entities, it's a natural choice to run aggregate queries in Hibernate.

By default, HQL or JPQL SELECT clause with multiple paths produces tuples in the form of Object[]. Since this kind of data is read-only, you may display these arrays directly, without converting them to objects.

Another option is to create a POJO for representing results of the query and use a constructor syntax SELECT NEW MoneyReport(u.userId, d.category, SUM(d.amount)) .... These POJOs don't need to be entities.

See also:

• Chapter 15. HQL: The Hibernate Query Language