将hibernate用于复杂查询的最佳方法，例如每组前N个

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.

我现在正在报告应用程序上工作一段时间,我使用hibernate来定义我的查询。但是,越来越多的人认为,对于报告用例,这不是最好的方法。

The queries only result partial columns, and thus not typed objects (unless you cast all fields in java).

查询只会生成部分列,因此不会生成类型化对象(除非您在java中转换所有字段)。

It is hard to express queries without going straight into sql or hql.

如果不直接进入sql或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.

我目前的问题是我希望每组获得前N个,例如组中每个元素的最后5天,每天我显示访问量。

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?

将每行每行存储的数据转换为这样的输出的最佳方法是什么?是时候回到常规的SQL并使用无类型的数据吗?

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

我真的想为我的结果使用类型化的对象,但是java让我的生活变得非常困难。欢迎任何建议!

1 个解决方案

#1

Using the Criteria API, you can do this:

使用Criteria API,您可以执行以下操作:

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:

要在vanilla SQL中执行此操作(并确认Hibernate正在执行您期望的操作),请查看此SO帖子:

#1