# HG changeset patch
# User Felix Wolfsteller <felix.wolfsteller@intevation.de>
# Date 1321969163 0
# Node ID 57b03b3ab15cd0738b5e03b225fbab00cfaadb24
# Parent  e0c39146d4697f641c17a687c19b86a21310cc7a
Optimize query to fix annotations fetching on oracle, by Sascha Teichmann.

flys-artifacts/trunk@3300 c6561f87-3c4e-4783-a992-168aeb5c3f6f

diff -r e0c39146d469 -r 57b03b3ab15c flys-artifacts/ChangeLog
--- a/flys-artifacts/ChangeLog	Thu Nov 17 11:38:16 2011 +0000
+++ b/flys-artifacts/ChangeLog	Tue Nov 22 13:39:23 2011 +0000
@@ -1,3 +1,9 @@
+2011-11-22  Felix Wolfsteller <felix.wolfsteller@intevation.de>
+
+	* src/main/java/de/intevation/flys/artifacts/model/AnnotationsFactory.java:
+	  Use different queries to avoid costy joins. Patch by Sascha
+	  Teichmann, minor typo-fix.
+
 2011-11-17  Felix Wolfsteller <felix.wolfsteller@intevation.de>
 
 	* doc/conf/artifacts/winfo.xml: Added other.wkms.interpol facet
diff -r e0c39146d469 -r 57b03b3ab15c flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/AnnotationsFactory.java
--- a/flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/AnnotationsFactory.java	Thu Nov 17 11:38:16 2011 +0000
+++ b/flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/AnnotationsFactory.java	Tue Nov 22 13:39:23 2011 +0000
@@ -4,6 +4,7 @@
 
 import java.util.List;
 import java.util.Iterator;
+import java.util.Collections;
 
 import de.intevation.flys.backend.SessionHolder;
 import de.intevation.flys.model.Annotation;
@@ -18,11 +19,6 @@
  */
 public class AnnotationsFactory {
 
-    public static List<Annotation> getAnnotations(River river) {
-        return getAnnotations(river.getName());
-    }
-
-
     /**
      * Get Annotations which do not have a "b" ("to")-value set.
      *
@@ -46,21 +42,16 @@
     }
 
 
-    public static List<Annotation> getAnnotations(String river) {
+    public static List<Annotation> getAnnotations(River river) {
         Session session = SessionHolder.HOLDER.get();
 
-        Query rangesQuery = session.createQuery(
-            "from Range where river.name=:name");
-        rangesQuery.setParameter("name", river);
-        List<Range> ranges = rangesQuery.list();
-
         Query query = session.createQuery(
-            "from Annotation where range in (:ranges) order by range.a");
-        query.setParameterList("ranges", ranges);
+            "from Annotation as an where an.range.river = :river" +
+            " order by an.range.a");
+        query.setParameter("river", river);
         return query.list();
     }
 
-
     /**
      * Get minimal "a" ("from") and maximal "b" ("to") value of annotations'
      * ranges of a river.
@@ -85,17 +76,25 @@
     }
 
 
-    public static Iterator<Annotation> getAnnotationsIterator(String river) {
+    public static Iterator<Annotation> getAnnotationsIterator(
+        String riverName
+    ) {
         Session session = SessionHolder.HOLDER.get();
 
-        Query rangesQuery = session.createQuery(
-            "from Range where river.name=:name");
-        rangesQuery.setParameter("name", river);
-        List<Range> ranges = rangesQuery.list();
+        Query riverQuery = session.createQuery(
+            "from River where name = :name");
+        riverQuery.setParameter("name", riverName);
+        List<River> rivers = riverQuery.list();
+        if (rivers.isEmpty()) {
+            List<Annotation> list = Collections.emptyList();
+            return list.iterator();
+        }
 
         Query query = session.createQuery(
-            "from Annotation where range in (:ranges) order by range.a");
-        query.setParameterList("ranges", ranges);
+            "from Annotation as an" +
+            " where an.range.river = :river order by an.range.a");
+        query.setParameter("river", rivers.get(0));
+
         return (Iterator<Annotation>)query.iterate();
     }
 }