[HQL] Dynamische Spalten erzeugen

[turmaline]

Hallo Leute,

ist es möglich mit HQL eine bzw. mehrere dynamische Spalten erzeugen und auf sie in den Ergebnissen einer Abfrage zugreifen?

Gruß,madlena

 

[SlaterB]

was ist eine 'dynamische Spalte'? vielleicht hilft ein Beispiel, in SQL oder wie auch immer du es dir im Idealfall vorstellst

 

[turmaline]

ok, ich habe eine Tabelle Feedback, die mit der Tabelle Comment über eine Mapping-Tabelle (feedbackId, commentId) Feedback_Comment mit OneToMany verbunden ist.

Fedback -> Comment (1:m)

Comment ist seinerseits mit der Tabelle Attachment mit OneToOne verbunden. Ein Comment kann einen Attachment haben, muss aber nicht.

Nun möchte ich eine Abfrage schreiben:

1) New Feedback Abfrage:

Gib mir [max] Feedbacks von [start], die noch nicht synchroniziert sind (f.synchronised = 0), sortiere diese [order] nach [sort]:

virtuelles Feld: hasAttachment ist 0, wenn dieser feedback keinen Attachment hat; 1, wenn dieser Feedback einen oder mehrere Attachments hat: also dieser F. hat einen oder mehrere Kommentare die einen Attachment haben.


2) Sync feedbacks Abfrage:

Gib mir [max] Feedbacks von [start], die bereits synchronisiert sind (f.synchronised = 0) und vom Benutzer nicht gelöscht sind (f.removedByUser), sortiere diese [order] nach [sort]:

virtuele Felder: hasAttachment, hasNotReadComment (dieser F. hat einen oder mehrere Kommentare, die noch nicht gelesen wurden: comment.read = 0)

Absteigende Sortierung nach hasNotReadComment: je mehr ungelesener, vom editor erzeugten Kommentare ein Feedback hat desto höher ist seine Position in der Liste.


Dann wenn ich diese Abfragen habe möchte ich auf die "viertuellen Spalten" hasAttachment, hasNotReadComment auf meinem *.gsp zugreifen. Naja wenn ich eine Abfrage an die Domain-Tabelle stelle (eine namedQuery über entityManager) bekomme ich ja eine List<Feedback> . Wenn ich jetzt wüsste wie ich diese Abfragen inHQL stellen soll, wüsste ich nicht wie ich danach in meinem *.gsp (in dem ich die feedback-listen anzeige) auf die Felder hasAttachment, hasNotReadComment zugreifen soll :shock: - ich muss es auf jeden Fall anzeigen können, ob ein F. einen/mehrere Att hat, bzw einen/mehrere ungelesene Kommentare.

???:L

Kein Wunder wenn Du mich nicht verstanden hast..

 

[SlaterB]

allgemein kann man in SQL oder auch HQL gewiss Auswertungen vornehmen:
[c]select name, count(*) from x group by name[/c]
da wäre das zweite schon eine 'dynamische Spalte'

wie kompliziert man da werden kann hängt glaube ich im wesentlichen von den SQL-Möglichkeiten der darunterliegenden Datenbank ab,
für Oracle gibt es z.B. ein DECODE
[c]select name, DECODE(count(attachements), 0, 0, 1) from x group by name[/c]
liefert entweder 0 oder 1 für alle anderen Anzahl attachements, wobei du jetzt noch nicht gesagt hast, was attachements sind,
eine andere Tabelle, direkt verknüpft oder per eigenen Join usw..,
(allerdings will ich auch nicht unbedingt alle Details klären )

ob eine solche Spalte in einer einzelnen Query nun einen Namen braucht..
wenn man das ganze mehrmals benötigt will man das sicher nicht in verschiedene Querys kopieren,
die einzige Alternative die ich nennen kann, wäre dann eine View in der DB, über eigenes Mapping in Hibernate eingebunden,

soweit mein Wissensstand dazu

 

[turmaline]

danke für Deine Antwort.

Frage1: ich habe zwei ferscheidenen Datenbanken: MySql und Oracle. Muss ich den "Dialekt" der jeweiligen Datenbank in der HQL-Anfrage berücksichtigen.

Frage2: ich lasse mir die Ergebnisse über die Schnittstelle liefern (so ist die Anfroderung). SOmit habe ich also eine Methode:

public List<?> getResults(String author, String sort, String order, int start, int max) ;

Die Anfrage wird an die Tabelle F. gestellt, Ergebnisse enthalten aber auch generierte, benannte Spalten, auf die ich dann auch zugreifen muss.
Von welchem Typ sollen die Ergebnisse sein?

Gruß,
madlena

 

[SlaterB]

1) ja, sofern du eben nicht SQL-Ausdrücke findest, die beide Datenbanken akzeptieren,
alles was über die bekannten Grundelemente a la SELECT, FROM, GROUP BY, HAVING, sowie vielleicht Funktionen wie COUNT, MIN, MAX, AVG, IS NULL, IN, || (String-Konkatenation) hinausgeht, ist meiner bisherigen Erfahrung nach Grauzone, datenbankabhängig,

SQL an sich ist schon schwer genug, der Übergang von HQL nach SQL ist eine weitere Komplexität hier,
vieles läßt Hibernate durch, manches findet es nicht so gut, hat z.B. Probleme den Typ der Spalte herauszufinden, was vielleicht mit deiner zweiten Frage zu tun hat,
ich schreibe teilsweise in meine Querys [c]SELECT 1.0*DECODE(...[/c] damit Hibernate kapiert dass ein BigDecimal zurückkommen soll..,

bemerkenswert fand ich vor wenigen Jahren, dass bei irgendeinem Versionssprung [c]SELECT count(*)[/c] auf einmal vom Typ Long war statt vorher Integer, mehr als 2 Milliarden Einträge in einer Tabelle hatte ich aber noch nicht, naja vielleicht bei Joins irgendwann mal..,

nach wie vor scheitert mein Hibernate daran, die zumindest in Oracle vorhandene Funktion MEDIAN durchzureichen, genau wie MIN, MAX, AVG,
dafür muss ich extra eine SQL-Query verwenden statt HQL, das läßt sich auf anderen Wege nicht lösen (außer alle Daten einzeln zu laden)

solche Probleme sind vorhanden, was du bei deiner 2. Frage im Besonderen wissen willst erkenne ich nicht,
kann ich vielleicht auch nicht beantworten

 

[turmaline]

ooook,

dann bastele ich meine Anfrage und stelle meine 2. Frage etw. konkreter. Die ANfrage schreibe ich gerade in SQL und sie ist schon ziemlich komliziert.

 

[turmaline]

ich hab jetzt mein bestes gegeben und habe folgende Anfragen in SQL zusammengebsatelt:

1) Die Anfrage die alle Felder der Tabelle Feedback liefert + eine generierte Spalte namens attCount, in der soweit ein Wert vorhanden ist die Anzahl der angehängten Kommentare, welche gleichzeitig Attachments haben, steht:

SELECT f.*, xx.attCount FROM Feedback as f LEFT JOIN
(SELECT fcc.ID, COUNT(*) as attCount
FROM 
(SELECT * FROM Feedback as f LEFT JOIN Mapping as fc
ON f.ID = fc.feedbackId) as fcc LEFT JOIN Comment as cc ON fcc.commentId = cc.id
where cc.attachmentId is not null
GROUP BY fcc.ID) as xx ON f.ID = xx.ID
WHERE f.author = 'author'
ORDER BY xx.attCount desc

2) Die Anfrage die alle Felder der Tabelle Feedback liefert + eine generierte Spalte namens notReadCount, in der soweit ein Wert vorhanden ist die Anzahl der angehängten Kommentare, welche noch nicht gelesen sind steht:

SELECT f.*, xy.notReadCount FROM
Feedback as f LEFT JOIN
(SELECT fcc.id, count(*) as notReadCount FROM (SELECT * FROM Feedback as f LEFT JOIN Mapping as fc
ON f.ID = fc.feedbackId) as fcc LEFT JOIN Comment as cc ON fcc.commentId = cc.id
WHERE cc.isRead = 0
GROUP BY fcc.id) as xy ON f.id = xy.id
WHERE f.author = 'author'
ORDER BY xy.notReadCount desc

Für meinen Wissensstand sind die Anfragen schon komliziert genug.

Frage1 Wie führe ich sie jetzt zusammen dass ich f.*, xx.attCount, xy.notReadCount habe?

Frage2 was soll ich tun damit die Anfrage auf beiden Datenbankentypen ausführbar ist

Frage3 Von welchem Typ sind meine Ergebnisse, wenn ich sie mir über eine Schnittstelle leifern lasse?

List<ArrayList<?>>

 

[SlaterB]

1) ich stelle mich nicht als SQL-Experte dar, mein Vorschlag wäre

SELECT f.*, xx.attCount, xy.notReadCount FROM  Feedback AS f, 
(Sub-Query mit Feedback f2 und Join) AS xx,  
(Sub-Query mit Feedback f3 und Join) AS xy,  
where f.id = xx.id and f.id = xy.id

hier tritt wieder ein gravierendes Hibernate-Problem auf: derartige Subqueries/ dynamischen Tabellen im FROM sind glaube ich in HQL nicht erlaubt

bisher sieht die Query für mich allgemeingültig für alle Datenbanken aus,
ansonsten kann ich persönlich da nicht mehr sagen, als einfach SQL-Bausteine manuell auszutauschen

zu Frage 3 könnte ich noch was sagen, aber warum eigentlich,
das kannst du alles ausprobieren

 

[turmaline]

mhm.. ich verstehe das nicht was passiert mit meinem LEFT JOIN in der ersten Zeile der Anfragen. Ich brauche ja ihn auch.

 

[SlaterB]

in [c](Sub-Query mit Feedback f2 und JOIN)[/c]
kannst du alles SQL der Welt einbauen, deine komplette Query reinkopieren notfalls,
ausprobieren ausprobieren ausprobieren, oder im Internet nachlesen

 

[turmaline]

ok das funktioniert jetzt, danke. Probiere nun die Anfrage über die Schnittstelle aus.

 

[turmaline]

Kann man bei solchen komplizierten Anfragen auch LIMIT verwenden?

SELECT f.*, xx.attCount, xy.notReadCount FROM  Feedback AS f, 
(Sub-Query mit Feedback f2 und JOIN) AS xx,  
(Sub-Query mit Feedback f3 und JOIN) AS xy,  
WHERE f.id = xx.id AND f.id = xy.id
ORDER by xx.attCount desc
LIMIT 0, 5

Wenn ich so eine Anfrage ausprobiere, bekomme ich Incorrect syntax

 

[turmaline]

oh oder geht das nicht datenbankunabhängig?

 

[SlaterB]

ob LIMIT grundsätzlich geht (du sagt nicht mal ob HQL-Java dir den Fehler gibt oder SQL im Datenbank-Tool)
am besten an einfacher Query prüfen:
[c]select * from x LIMIT 0, 5 [/c]

datenbankspezifisch ist das gewiss