Hibernate Newbie, help on queries that do some grouping

[Arvind Karuppasamy]

Hi,

I am new to using Hibernate, and in our project, we use Hibernate and Spring to do all the database stuff.

I have a requirement wherein I need to write a method that takes a few inputs and then queries a table to return the sum of a column and the max and min of another column for the given input query criteria.

Now I am confused as to how I can use ORM in this case, should I define a java class for that table and then just use the query without any grouping to fetch the objects of that class and then do a grouping, sum and min,max computations for the resulting object set using Java code, like iterating through the loop?

How best can I use Hibernate to achieve this, I am a newbie to ORM and all the other Objects in my project are all DAOs which operate on a single record.

[Paul Sturrock]

Now I am confused as to how I can use ORM in this case, should I define a java class for that table and then just use the query without any grouping to fetch the objects of that class and then do a grouping, sum and min,max computations for the resulting object set using Java code, like iterating through the loop?

Map the table, yes. But no sense in repeating what the database already does for you. HQL contains aggregate functions. Criteria based queries can also use things like the Projections class to do the same sort of stuff.

[Arvind Karuppasamy]

Is there a way to map this summary object also using Hibernate, so that I do not need to write the hql queries.

[Paul Sturrock]

A named query could be what you are after then. Another alternative would be to define the query as a view in your database and map that.

I'm not sure what this gives you over and above using HQL or Criteria though. You still have to write the query, you just write it in a different place. What is your concern with mapping the object as you normally would?

[Arvind Karuppasamy]

My problem is this, I have a summary object and another object that maps to a row in the table. Can I have a hql query that returns me the Summary object.
Can I map that somewhere?

I have not used HQL before, so I am confused as to what will HQL return, is it a resultset that I need to iterate and convert to my summary object, or is there a simpler way to obtain my summary object from the hql.

[gopal venu]

There are two ways :
1.Normal and clean approach :
Map your tables with POJO Classes .IF The class you want to query has association (one-to-many) or (many-to-one) and on that association you want to search and find min/max then go for Criterian framwork to search with EAGER FETCH MOde.

IF its the class you want to query use HQL

[gopal venu]

2ND Not so clean approach : Map your summary logic as View in Database and map that view(aka table ) to a POJO class

[Paul Sturrock]

I have not used HQL before, so I am confused as to what will HQL return, is it a resultset that I need to iterate and convert to my summary object, or is there a simpler way to obtain my summary object from the hql.

HQL aggregate functions return the types defined by JPA. So if you are using a sum() function for example it will be a Long.

There is no need to use HQL if you are unfamiliar with it (though it is very like SQL so most people take to it quite quickly). You could just use Criteria and Projections e.g.:

Session session = getSessionFactory().openSession();
Criteria crit = session.createCriteria(YourMappedObject.class);
crit.setProjection(Projections.sum("someProperty"));
List results = crit.list();
 
Long value = (Long)results.get(0);

[Arvind Karuppasamy]

I saw this code snippet in Manning Hibernate book.
This looks like what I want, I have just one query. In the below code, Bid is a class that is mapped to a database table using Hibernate and ItemBidSummary is just some class in my package, is that correct? or should ItemBidSummary also be mapped to something. Also, what does this code snippet return - a List of ItemBidSummary?

select new ItemBidSummary(
           bid.item.id, count(bid), avg(bid.amount)
           )
from Bid bid
where bid.item.successfulBid is null
group by bid.item.id