Best way to use hibernate for complex queries like top n per group

Question

I'm working now for a while on a reporting applications where I use hibernate to define my queries. However, more and more I get the feeling that for reporting use cases this is not the best approach.

1) The queries only result partial columns, and thus not typed objects (unless you cast all fields in java). 2) It is hard to express queries without going straight into sql or hql.

My current problem is that I want to get the top N per group, for example the last 5 days per element in a group, where on each day I display the amount of visitors.

The result should look like:

| RowName | 1-1-2009 | 2-1-2009 | 3-1-2009 | 4-1-2009 | 5-1-2009
| SomeName| 1        | 42       | 34       | 32       | 35

What is the best approach to transform the data which is stored per day per row to an output like this? Is it time to fall back on regular sql and work with untyped data?

I really want to use typed objects for my results but java makes my life pretty hard for that. Any suggestions are welcome!

Answer 1

Well I guess You will need to use hql but that's no so bad: the results of a query can be typed objects.

Answer 2

Using the Criteria API, you can do this:

Session session = ...;
Criteria criteria = session.createCriteria(MyClass.class);
criteria.setFirstResult(1);
criteria.setMaxResults(5);
... any other criteria ...
List topFive = criteria.list();

To do this in vanilla SQL (and to confirm that Hibernate is doing what you expect) check out this SO post:

Answer 3

Struggling with the same thing, can someone please post an example if they have one? A query or criteria implementation will do. Or a link to an example :)