Mercurial > dive4elements > river

changeset 8787:5855f919cbd6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Issue1854: Avoid assumptions about limits of SQL datetime.
author Tom Gottfried <tom@intevation.de>
date Tue, 08 Dec 2015 20:15:45 +0100
parents 0beb34d55630
children b32c1927f90d
files artifacts/src/main/java/org/dive4elements/river/artifacts/model/minfo/QualityMeasurementFactory.java artifacts/src/main/java/org/dive4elements/river/artifacts/states/minfo/BedQualityDistanceSelect.java
diffstat 2 files changed, 103 insertions(+), 70 deletions(-) [+]
line wrap: on
line diff
--- a/artifacts/src/main/java/org/dive4elements/river/artifacts/model/minfo/QualityMeasurementFactory.java	Tue Dec 08 19:16:03 2015 +0100
+++ b/artifacts/src/main/java/org/dive4elements/river/artifacts/model/minfo/QualityMeasurementFactory.java	Tue Dec 08 20:15:45 2015 +0100
@@ -27,8 +27,8 @@
     private static Logger log = Logger.getLogger(QualityMeasurementFactory.class);
 
     private static final String SQL_BED_MEASUREMENT =
-        "SELECT st.km       as km," +
-        "       st.datum    as datum," +
+        "SELECT dat.km      as km," +
+        "       dat.datum   as datum," +
         "       sp.tiefevon as depth1," +
         "       sp.tiefebis as depth2," +
         "       sa.d10      as d10," +
@@ -47,47 +47,49 @@
         "       sa.d90      as d90," +
         "       sa.dmin     as dmin," +
         "       sa.dmax     as dmax " +
-        "FROM sohltest st " +
-        "    JOIN station sn ON sn.stationid = st.stationid " +
+        "FROM sohltest dat " +
+        "    JOIN station sn ON sn.stationid = dat.stationid " +
         "    JOIN gewaesser gw ON gw.gewaesserid = sn.gewaesserid " +
-        "    JOIN sohlprobe sp ON sp.sohltestid = st.sohltestid " +
+        "    JOIN sohlprobe sp ON sp.sohltestid = dat.sohltestid " +
         "    JOIN siebanalyse sa ON sa.sohlprobeid = sp.sohlprobeid " +
         "WHERE gw.name = :name AND " +
-        "      st.km IS NOT NULL AND " +
+        "      dat.km IS NOT NULL AND " +
         "      sp.tiefevon IS NOT NULL AND " +
         "      sp.tiefebis IS NOT NULL AND " + // TODO: Test if char diameter ist null.
-        "      st.km BETWEEN :from - 0.001 AND :to + 0.001 AND " +
-        "      st.datum BETWEEN :start AND :end " +
-        "ORDER BY st.km";
+        "      dat.km BETWEEN :from - 0.001 AND :to + 0.001 ";
 
     private static final String SQL_BEDLOAD_MEASUREMENT =
-        "SELECT m.km    as km," +
-        "       m.datum as datum," +
-        "       m.dm    as dm," +
-        "       m.d10   as d10," +
-        "       m.d16   as d16," +
-        "       m.d20   as d20," +
-        "       m.d25   as d25," +
-        "       m.d30   as d30," +
-        "       m.d40   as d40," +
-        "       m.d50   as d50," +
-        "       m.d60   as d60," +
-        "       m.d70   as d70," +
-        "       m.d75   as d75," +
-        "       m.d80   as d80," +
-        "       m.d84   as d84," +
-        "       m.d90   as d90," +
-        "       m.dmin  as dmin," +
-        "       m.dmax  as dmax " +
-        "FROM messung m" +
-        "    JOIN station sn ON sn.stationid = m.stationid" +
+        "SELECT dat.km    as km," +
+        "       dat.datum as datum," +
+        "       dat.dm    as dm," +
+        "       dat.d10   as d10," +
+        "       dat.d16   as d16," +
+        "       dat.d20   as d20," +
+        "       dat.d25   as d25," +
+        "       dat.d30   as d30," +
+        "       dat.d40   as d40," +
+        "       dat.d50   as d50," +
+        "       dat.d60   as d60," +
+        "       dat.d70   as d70," +
+        "       dat.d75   as d75," +
+        "       dat.d80   as d80," +
+        "       dat.d84   as d84," +
+        "       dat.d90   as d90," +
+        "       dat.dmin  as dmin," +
+        "       dat.dmax  as dmax " +
+        "FROM messung dat" +
+        "    JOIN station sn ON sn.stationid = dat.stationid" +
         "    JOIN gewaesser gw ON gw.gewaesserid = sn.gewaesserid " +
         "WHERE gw.name = :name AND " +
-        "      m.km IS NOT NULL AND " +
-        "      m.d10 IS NOT NULL AND" + //TODO: Add all other char. diameter.
-        "      m.km BETWEEN :from - 0.001 AND :to + 0.001 AND" +
-        "      m.datum BETWEEN :start AND :end " +
-        "ORDER BY m.km";
+        "      dat.km IS NOT NULL AND " +
+        "      dat.d10 IS NOT NULL AND" + //TODO: Add all other char. diameter.
+        "      dat.km BETWEEN :from - 0.001 AND :to + 0.001 ";
+
+    private static final String SQL_WHERE_DATE =
+        "AND dat.datum BETWEEN :start AND :end ";
+
+    private static final String SQL_ORDER_BY = "ORDER BY dat.km";
+
 
     /** Transform query result into objects, use INSTANCE singleton. */
     public static final class QualityMeasurementResultTransformer
@@ -134,13 +136,11 @@
     private QualityMeasurementFactory() {
     }
 
-    protected static QualityMeasurements load(
+    private static SQLQuery baseQuery(
         Session session,
         String river,
         double from,
         double to,
-        Date start,
-        Date end,
         String statement
     ) {
         SQLQuery query = session.createSQLQuery(statement)
@@ -163,39 +163,83 @@
             .addScalar("dmin", StandardBasicTypes.DOUBLE)
             .addScalar("dmax", StandardBasicTypes.DOUBLE);
 
-        if (statement.equals(SQL_BED_MEASUREMENT)) {
+        if (statement.startsWith(SQL_BED_MEASUREMENT)) {
             query.addScalar("depth1", StandardBasicTypes.DOUBLE);
             query.addScalar("depth2", StandardBasicTypes.DOUBLE);
         }
+
         String seddbRiver = RiverFactory.getRiver(river).nameForSeddb();
 
         query.setString("name", seddbRiver);
         query.setDouble("from", from);
         query.setDouble("to", to);
-        query.setDate("start", start);
-        query.setDate("end", end);
 
         query.setResultTransformer(
             QualityMeasurementResultTransformer.INSTANCE);
 
+        return query;
+    }
+
+    protected static QualityMeasurements load(
+        Session session,
+        String river,
+        double from,
+        double to,
+        String statement
+    ) {
+        SQLQuery query = baseQuery(session, river, from, to, statement);
+
         return new QualityMeasurements(query.list());
     }
 
-    /** Get all measurements. */
+    protected static QualityMeasurements load(
+        Session session,
+        String river,
+        double from,
+        double to,
+        Date start,
+        Date end,
+        String statement
+    ) {
+        SQLQuery query = baseQuery(session, river, from, to, statement);
+
+        query.setDate("start", start);
+        query.setDate("end", end);
+
+        return new QualityMeasurements(query.list());
+    }
+
+
+    public static QualityMeasurements getBedMeasurements(
+        String river,
+        double from,
+        double to
+    ) {
+        Session session = SedDBSessionHolder.HOLDER.get();
+        return load(session, river, from, to,
+            SQL_BED_MEASUREMENT + SQL_ORDER_BY);
+    }
+
+    public static QualityMeasurements getBedloadMeasurements(
+        String river,
+        double from,
+        double to
+    ) {
+        Session session = SedDBSessionHolder.HOLDER.get();
+        return load(session, river, from, to,
+            SQL_BEDLOAD_MEASUREMENT + SQL_ORDER_BY);
+    }
+
     public static QualityMeasurements getBedMeasurements(
         String river,
         double from,
         double to,
         Date start,
-        Date end) {
+        Date end
+    ) {
         Session session = SedDBSessionHolder.HOLDER.get();
-        try {
-            return load(session, river, from, to, start, end,
-            SQL_BED_MEASUREMENT);
-        }
-        finally {
-            //session.close();
-        }
+        return load(session, river, from, to, start, end,
+            SQL_BED_MEASUREMENT + SQL_WHERE_DATE + SQL_ORDER_BY);
     }
 
     public static QualityMeasurements getBedloadMeasurements(
@@ -206,19 +250,14 @@
         Date end
     ) {
         Session session = SedDBSessionHolder.HOLDER.get();
-        try {
-            return load(
-                session,
-                river,
-                from,
-                to,
-                start,
-                end,
-                SQL_BEDLOAD_MEASUREMENT);
-        }
-        finally {
-            //session.close();
-        }
+        return load(
+            session,
+            river,
+            from,
+            to,
+            start,
+            end,
+            SQL_BEDLOAD_MEASUREMENT + SQL_WHERE_DATE + SQL_ORDER_BY);
     }
 }
 // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :
--- a/artifacts/src/main/java/org/dive4elements/river/artifacts/states/minfo/BedQualityDistanceSelect.java	Tue Dec 08 19:16:03 2015 +0100
+++ b/artifacts/src/main/java/org/dive4elements/river/artifacts/states/minfo/BedQualityDistanceSelect.java	Tue Dec 08 20:15:45 2015 +0100
@@ -58,19 +58,13 @@
         List<Double> bKms = QualityMeasurementFactory.getBedMeasurements(
             river,
             minMax[0],
-            minMax[1],
-            new Date(Long.MIN_VALUE/100000),
-            new Date(Long.MAX_VALUE/1000)
-             // values expected to fit in SQL Date, though large enough
+            minMax[1]
         ).getKms();
 
         List<Double> blKms = QualityMeasurementFactory.getBedloadMeasurements(
             river,
             minMax[0],
-            minMax[1],
-            new Date(Long.MIN_VALUE/100000),
-            new Date(Long.MAX_VALUE/1000)
-             // values expected to fit in SQL Date, though large enough
+            minMax[1]
         ).getKms();
 
         if (bKms.isEmpty() || blKms.isEmpty()) {
http://dive4elements.wald.intevation.org