Kleiner Erfahrungsbericht von dieser Woche, den ich mal für die Nachwelt (Google^^) festhalten wollte.

Ich habe eine Enitätsklasse Foo mit JPA Annotationen versehen, also @Enitity, @Table, @NamedQueries, @Column und @OneToOne zu einer anderen Klasse Bar. Als Persistenzprovider kommt EclipseLink zum Einsatz, dahinter liegt eine PostgreSQL DB.

Foo hat ein paar Feldvariablen (@Columns), von denen ich jeweils Summen errechne. Das ganze soll gruppiert werden sowohl nach einem Objekt aus Bar als auch nach verschiedenen Zeiteinheiten (Stunde, Tag, Woche bis Jahr halt). Das ganze wird auf einen Zeitraum limitert, der mit Calendar-Objekten abgehandelt wird. Der @NamedQuery sieht für ersteres z.B. so aus:

SELECT y.groupVar, NEW com.chrissyx.test.FooSums(SUM(x.var1), SUM(x.var2), SUM(x.var3)) FROM Foo x JOIN x.bar y WHERE x.myDate BETWEEN :fromDate AND :toDate GROUP BY y.groupVar

FooSums ist eine Hilfsklasse, die alle gebildeten Summen beherbergt und direkt in JPQL erstellt wird mittels NEW und den vollständigen Klassenpfad. x ist der Alias für Foo und y der Alias für Bar. Das WHERE grenzt mittels BETWEEN den Zeitraum ein auf Basis des hinterlegten Datums im Feld myDate.

Als Rückgabe der Persistenzfunktion getResultList() gibt es eine Collection<Object[]>, d.h. eine Liste mit Objektarrays bei denen unter Index 0 das Objekt GroupVar aus der Klasse Bar ist und unter Index 1 eine Instanz von FooSums mit den gebildeten Summen. Durch die kann man itertieren und z.B. in eine Map<GroupVar, FooSums> verpacken. Das war noch der einfache Teil.

Wie kann man nun nach einem Datum gruppieren? Funktionen wie DAY() oder MONTH() aus MySQL gibt es bei JPQL nicht. Eine Möglichkeit wäre daher EXTRACT:

SELECT EXTRACT(DAY FROM x.myDate) days, NEW com.chrissyx.test.FooSums(SUM(x.var1), SUM(x.var2), SUM(x.var3)) FROM Foo x WHERE x.myDate BETWEEN :fromDate AND :toDate GROUP BY days

Auch wenn in der Doku EXTRACT(DAY, x.myDate) steht, ist obige Notation korrekt. Ein JOIN ist nicht mehr nötig. In der Collection<Object[]> gibt's nun unter Index 0 ein Double (nicht Calendar!) mit dem Tag, z.B. 15,0 für den 15. Tag eines Monats. Gleiches gilt für Jahre (2012,0) oder andere Daten. Index 1 hat nachwievor das gewohnte FooSums.

So weit, so gut, aber es gibt ein Problem: Was ist, wenn der Zeitraum über mehrere Monate geht und man öfters den 15. drin hat? Also 15.10. und 15.11.? Dann würde man trotzem nur eine 15,0 zurück bekommen, bei der aber die Summen aller 15. Tage enthalten sind. Doof! Wie kann man nun trennen nach beiden Daten? Dafür müssen die Angaben "über" den Tag, also Monat und notfalls auch Jahre mit dabei sein - am besten mittels Calendar. TRUNC gibt's zwar irgendwie bei EclipseLink, aber das ist leider keine Aggregatfunktion wie SUM. Auch Ansätze mittels OPERATOR('Trunc', x.myDate, 'day') scheitern daran. PostgreSQL selber bringt eine date_trunc() Funktion mit, die man per JPQLs SQL() einbinden kann. Das ist leider nicht mehr DB unabhängig und sähe so aus:

SELECT SQL('date_trunc(?, ?)', 'day', x.myDate) days, ...

Netter Ansatz, aber funktionert aus den gleichen Gründen ebenso wenig. Ohne Aggregatsfunktion geht es in diesem Fall nicht. Was kann man also tun? Die Lösung sieht letztendlich so aus:

SELECT MIN(x.myDate), NEW com.chrissyx.test.FooSums(SUM(x.var1), SUM(x.var2), SUM(x.var3)), EXTRACT(DAY FROM x.myDate) days, EXTRACT(MONTH FROM x.myDate) months, EXTRACT(YEAR FROM x.myDate) years FROM Foo x WHERE x.myDate BETWEEN :fromDate AND :toDate GROUP BY days, months, years

Das MIN(x.myDate) holt das jeweils kleinste Datum eines Tages, allerdings nicht so allgemein, so dass z.B. nur Jahr, Monat und Tag vorhanden sind, aber keine Stunden, Minuten und den ganzen Rest halt. Die ganzen EXTRACTs interessieren eigentlich gar nicht, sind aber notwenig zum Gruppieren. Collection<Object[]> hat daher nun ganze fünf Einträge:

• 0: Ein Timestamp des kleinsten Datums
  
• 1: Die aufsummierten Werte mittels FooSums
  
• 2: Tag als Double
  
• 3: Monat als Double
  
• 4: Jahr als Double
  

Timestamp stammt von Date ab, was in einem Calendar mittels setTime() gesetzt werden kann. Hier müsste man um ganz sauber zu arbeiten eine Instanz erst reseten und dann nur die Werte setzen, die wirklich von Interesse sind - Tag, Monat und Jahr. Dann kann man daraus eine Map<Calendar, FooSums> bauen und hat alles flexibel und hübsch aufbereitet zur Verfügung stehen.

Geht das noch besser oder viel geschickter? Antworten, wer mehr weiss!