HQL Problem: Where mit Collection (m:n Beziehung)

jhjp · **Joined:** Mon Oct 12, 2009 10:32 am **Posts:** 5

Hallo,

ich steh grad aufm Schlauch. Bestimmt bin ich nicht der erste, der vor folgender Problematik steht, aber ich find einfach nix (weder Google, noch Foren-Suche) :-(

Gegeben (alles vereinfacht):
Ein Datensatz hat eine unterschiedliche Anzahl an Attributen. Jedes Attribut hat einen Wert und ist von einer bestimmten Einheit, die einen Namen hat.
Z.B. Datensatz 1 besitzt zwei Attribute mit Attribut 1: 5 (value) Sulfur, und Attribut 2: 100 (value) Water

Folgende Klassen habe darauf basierend erstellt:
Dataset: enthält eine Liste mit Attributen
Attribute: enthält einen value und eine Unit
Unit: enthält einen Namen

Folgendes Datenbankschema wurde daraufhin erstellt:
Dataset: Dataset_ID
Attribute: Dataset_ID, Unit_ID, value
Unit: Unit_ID, name

Jetzt möchte ich gerne folgende Abfragen durchführen:
Gib mir alle Datensätze, bei denen Sulfur>5 ODER Water>100

Hierfür verwende ich folgendes HQL Statement:

Code:

select distinct d from Dataset as d
join d.attributes a
where (a.unit.name = 'Sulfur' and a.double_value > 5)
or (a.unit.name = 'Water' and a.double_value > 100)

Das funktioniert :-)

Ich möchte aber auch gerne folgende Abfrage durchführen:
Gib mir alle Datensätze, bei denen Sulfur>5 UND Water>100

Code:

select distinct d from Dataset as d
join d.attributes a
where (a.unit.name = 'Sulfur' and a.double_value > 5)
and (a.unit.name = 'Water' and a.double_value > 100)

Dieses Statement gibt jedoch eine leere Menge zurück. Wahrscheinlich weil die Klammern nicht beachtet werden?
Denn name = Sulfur and value > 5 and name = Water and value > 100 wäre dasselbe wie name = Sulfur and value > 100 and name = Water and value > 5 (wenn man keine Klammern beachtet) -> hierfür gibts natürlich keine Datensätze in der Datenbank.

Hat mir jemand einen Tipp (das würde mir völlig ausreichen :-))

Froestel · **Posted:** Mon Oct 12, 2009 5:13 pm

Hi jhjp,

jhjp wrote:

Hallo,

ich steh grad aufm Schlauch.

Scheint mir so.

jhjp wrote:

Ich möchte aber auch gerne folgende Abfrage durchführen:
Gib mir alle Datensätze, bei denen Sulfur>5 UND Water>100

Code:

select distinct d from Dataset as d
join d.attributes a
where (a.unit.name = 'Sulfur' and a.double_value > 5)
and (a.unit.name = 'Water' and a.double_value > 100)

Dieses Statement gibt jedoch eine leere Menge zurück. Wahrscheinlich weil die Klammern nicht beachtet werden?
Denn name = Sulfur and value > 5 and name = Water and value > 100 wäre dasselbe wie name = Sulfur and value > 100 and name = Water and value > 5 (wenn man keine Klammern beachtet) -> hierfür gibts natürlich keine Datensätze in der Datenbank.

Du hast recht, die Klammern werden nicht beachtet, unterliegst aber vermutlich einem Denkfehler: Die Klammern sind nämlich völlig überflüssig! Warum? Etwas Boole'sche Mathematik...:
Alle deine and-Verknüpfungen binden gleich stark, deshalb brauchst du die Klammern nicht (stell's dir einfach als "+" vor). Dementsprechend müssen also alle Teilausdrücke deiner where clause gleichzeitig wahr sein für jede Zeile deiner Ergebnismenge. Dass das nicht möglich ist, liegt daran, dass a.unit.name nicht gleichzeitig 'Sulfur' und 'Water' sein kann. Das nennt man Kontradiktion, d.h. es ist immer falsch, weswegen deine Ergebnismenge immer leer bleibt.

Wahrscheinlich musst du, um zur Lösung zu kommen, entweder mit zwei Teilqueries arbeiten und die dann per UNION zusammenmixen oder du guckst dir mal an wie ein CASE-WHEN-Konstrukt in SQL funktioniert.

CU
Froestel

jhjp · **Joined:** Mon Oct 12, 2009 10:32 am **Posts:** 5

Erst mal vielen Dank für deine Antwort!!!
Habe mal etwas recherchiert:

Union wird von HQL nicht unterstützt (http://opensource.atlassian.com/project ... e/HHH-1050)

CASE WHEN sollte eigentlich in HQL unterstützt werden (laut Doku), jedoch kann es mit dem case Befehl nichts anfangen

Ein Ausweichen auf SQL wird schwierig, da es portabel sein sollte (MySQL, ORACLE) und auch das Zusammenbauen des Objekts wird schwierig, da Dataset jede Menge anderer Objekte hinter sich herzieht. Und auch die Abfrage könnte komplexer werden: (Sulfur>5 and Water>100) or (Density=100 and Viscosity>=3)

Gibt es denn nicht einfach ein AND, das man irgendwie höherwertiger stellen kann... :-(
Die einzige Möglichkeit, die mir jetzt noch einfällt, wäre, die AND Teile in einzelne Teilqueries aufzusplitten und die resultierenden Listen mit addAll zusammenführen.

Wahrscheinlich ist das Datenbankschema einfach nicht für solche eine Abfrage geeignet, aber wie müsste es denn dann aussehen, so dass die oben genannte Abfrage möglichst einfach durchführbar ist und es trotzdem noch erweiterbar ist für zusätzliche Units bleibt?

jhjp · **Joined:** Mon Oct 12, 2009 10:32 am **Posts:** 5

Ok, nach 5 Stunden bin ich nun darauf gekommen, dass man das auch ganz einfach so machen kann:

Code:

select distinct d 
from Dataset as d  
join d.attributes a0  
join d.attributes a1  
join d.attributes a2  
where (d.country.name = 'Germany' ) and (d.survey.name = 'name' ) and 
(((a0.unit.name='Sulfur' and a0.double_value>5)   and   (a1.unit.name='Water' and a1.double_value>100))
 or ((a2.unit.name='Price' and a2.double_value<2)))

Das generierte SQL

Code:

select distinct
  dataset0_.DATASET_ID as DATASET1_4_,
  dataset0_.SURVEY_ID as SURVEY2_4_,
  dataset0_.COUNTRY_ID as COUNTRY3_4_ 
 from
  DATASETS dataset0_ 
 inner join
  DATASET_UNITS attributes1_ 
   on dataset0_.DATASET_ID=attributes1_.DATASET_ID 
 inner join
  DATASET_UNITS attributes2_ 
   on dataset0_.DATASET_ID=attributes2_.DATASET_ID 
 inner join
  DATASET_UNITS attributes3_ 
   on dataset0_.DATASET_ID=attributes3_.DATASET_ID,
  COUNTRIES country4_,
  SURVEYS survey5_,
  UNITS unit6_,
  UNITS unit7_,
  UNITS unit8_ 
 where
  dataset0_.COUNTRY_ID=country4_.COUNTRY_ID 
  and dataset0_.SURVEY_ID=survey5_.SURVEY_ID 
  and attributes1_.UNIT_ID=unit6_.UNIT_ID 
  and attributes2_.UNIT_ID=unit7_.UNIT_ID 
  and attributes3_.UNIT_ID=unit8_.UNIT_ID 
  and country4_.name='Germany' 
  and survey5_.name='name' 
  and (
   unit6_.name='Sulfur' 
   and attributes1_.double_value>5 
   and unit7_.name='Water' 
   and attributes2_.double_value>100 
   or unit8_.name='Price' 
   and attributes3_.double_value<2
  )

Leider benötigt diese Abfrage 30sec auf einem MySQL Server. Kann man da noch etwas optimieren?

Froestel · **Posted:** Tue Oct 13, 2009 12:33 pm

Hi jhjp,

das ist zwar eine Lösung, aber sicherlich keine schöne. Schön nicht, weil sie nicht generisch ist, also du die Abfarge jedesmal anpassen musst, wenn du ein weiteres Attribut zusätzlich willst.

Mit jedem JOIN wächst die Anzahl der Datensätze, die deine DB Engine durchsuchen muss. Damit hast du jetzt mind. |d| x |a| x |a| x |a|, wobei die beiden "|"-Symbole um die Buchstaben die Kardinailtät meint.

Wirklich Performance wirst du wahrscheinlich nur gewinnen, wenn du simple Einzelabfragen machst, das Ergebnis jeweils in ein java.util.HashSet speicherst und checkst, welche Objekte in allen Sets vorkommen. HashSet (oder auch Hashtable oder HashMap) deswegen, weil die rasend schnell sind. Ist nur so ne Idee (und auch nicht wirklich schön...)

CU
Froestel

jhjp · **Joined:** Mon Oct 12, 2009 10:32 am **Posts:** 5

Naja, die Abfrage ist schon generisch. Das HQL wird dynamisch generiert. Habe hierfür eine sehr einfache Query Sprache entwickelt. Der Benutzer gibt später lediglich ("Sulfur">5 and "Water">100) ein und das HQL Statement wird dann daraus erstellt.

Die Performance ist eigentlich nicht direkt mein Problem (die Abfrage kann ruhig ein paar Minuten benötigen). Wenn ich nämlich ein komlexeres Statement auf diese Art generiere, dann reagiert die lokale MySQL Instanz nicht mehr. Besser gesagt, das gesamte Windows XP steht kurz vor dem einfrieren (Maus Zeiger lässt sich kaum mehr bewegen). So ein Verhalten hatte ich zuletzt mit Windows 98. Wahrscheinlich liegt das am MySQL Server... hoffentlich verträgt die spätere Oracle DB die Abfrage besser.

Viele Grüße

mmerder · **Posted:** Tue Nov 03, 2009 6:56 am

Interessantes Problem...

Deine DB hauts natürlich um, weil du zig joins reingebaut hast, somit erstmal die ungefilterte Tupelmenge exponentiell wächst.
Ich würde mal einen Ansatz mit Subselects ausprobieren:

Code:

select d from Dataset d 
where d.id in (select a1.dataset.id from Attribute a1 where a1.unit.name = 'Sulfur' and a1.double_value > 5)
and d.id in (select a2.dataset.id from Attribute a2 where a2.unit.name = 'Water' and a2.double_value > 100)

Für jedes weitere Kriterium machst du dann einfach ein weiteres Subselect. Denke, das ist performanter. Du musst nur evtl. deine Beziehung Dataset --> Attribute bidirektional machen, damit es geht.

Um die Performanz noch mal wesentlich(!) zu erhöhen, leg dir Indizes über die Spalten der Attribut-Tabelle.

Poste mal bitte, ob meine Tips etwas gebracht haben, wie gesagt, interessantes Problem.