如何将对象分类到存储桶中以表达为有效的JPQL表达式? [英] How can sorting objects into buckets be formulated as valid JPQL expression?
问题描述
我想显示一个2D图，其中包含给定时间段内已处理对象的数量(在JPA中映射).此数量可以在30分钟的时间内扩展到大约3万个对象.
I would like to display a 2D-chart containing the amount of processed objects (mapped in JPA) in a given time span. This amount can be scale up to around 30k objects in an timespan of 30 minutes.
作为DBMS，我将PostgreSQL 9.4和JPA 2.0与Hibernate一起使用，版本为4.2.21.
As a DBMS I am using PostgreSQL 9.4 and JPA 2.0 with Hibernate in version 4.2.21.
此刻，我正在使用这段代码，以将金额添加到我的临时存储桶"中.
At the moment I am using this piece of code in order to add the amount into my temporal "buckets".
// Get all objects after a given start date
List<MyObject> myObjects= myJPADbService.getMyObjects(Date.from(startDate.atZone
(ZoneId.systemDefault()).toInstant()), Integer.MAX_VALUE);
for (MyObject information : myObjects) {
LocalDateTime pageDate = LocalDateTime.ofInstant(Instant.ofEpochMilli(information.getLastSeen().getTime()), ZoneId.systemDefault());
long duration;
if (temporalUnit == ChronoUnit.MINUTES) {
duration = Duration.between(startDate, pageDate).toMinutes();
} else if (temporalUnit == ChronoUnit.DAYS) {
duration = Duration.between(startDate, pageDate).toDays();
} else {
duration = Duration.between(startDate, pageDate).toHours();
}
pagesPerUnit[(int) (Math.abs(duration))]++;
}
但是，根据用户选择的时间跨度，这段代码可能效率很低.如果DBMS根据用户选择的时间范围来计算存储桶，则可能会更有效.
However depending on an user selected timespan, this piece of code might be very inefficient. It might be more efficient, if the DBMS computes the buckets according to an user selected timespan.
如何用正确的JPQL语法来表述?
How can this be formulated in proper JPQL syntax?
推荐答案
首先，您需要一种JPA方法来计算各个单元中startDate和pageDate之间的差异，这很难用公式表示因为它非常依赖数据库.最后，您将需要一些自定义函数或编写一个非常复杂的JPQL查询.
First of all, you need a JPA way of computing the difference between the startDate and pageDate in the respective unit, which is somewhat hard to formulate because it's very database dependent. In the end you will need some custom functions or write a very complicated JPQL query.
在PostgreSQL中计算两个日期之间的日期与date_part('day', t2 - t1)一样容易.对于小时，您已经需要date_part('day', t2 - t1) * 24 + date_part('hour', t2 - t1)和分钟date_part('day', t2 - t1) * 24 * 60 + date_part('hour', t2 - t1) * 60 + date_part('minute', t2 - t1).
Computing the days between two dates in PostgreSQL is as easy as doing date_part('day', t2 - t1). For the hours you already need date_part('day', t2 - t1) * 24 + date_part('hour', t2 - t1) and minutes date_part('day', t2 - t1) * 24 * 60 + date_part('hour', t2 - t1) * 60 + date_part('minute', t2 - t1).
为了在JPQL中使用这些数据库功能，可以使用FUNCTION语法，例如FUNCTION('date_part', 'day', :startDate - pageDate).
In order to use these database functions in JPQL, you can use the FUNCTION syntax like FUNCTION('date_part', 'day', :startDate - pageDate).
最后，您将按这样的表达式分组并按id进行计数，如下所示:
In the end, you will be grouping by such an expression and do a count by id, something like the following
SELECT COUNT(o.id)
FROM MyObject o
GROUP BY FUNCTION('date_part', 'day', :startDate - o.pageDate)
SELECT COUNT(o.id)
FROM MyObject o
GROUP BY FUNCTION('date_part', 'day', :startDate - o.pageDate)
这篇关于如何将对象分类到存储桶中以表达为有效的JPQL表达式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
