Mercurial > dive4elements > river
changeset 4008:fea3d9210948
S(Q) relation: Totals are now fetched with separate SQL statement. Fixed join to GEWAESSER table.
author | Sascha L. Teichmann <teichmann@intevation.de> |
---|---|
date | Tue, 02 Oct 2012 00:00:30 +0200 |
parents | d4d272e56d3a |
children | 60d88ec49c3b |
files | flys-artifacts/ChangeLog flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/sq/MeasurementFactory.java |
diffstat | 2 files changed, 94 insertions(+), 34 deletions(-) [+] |
line wrap: on
line diff
--- a/flys-artifacts/ChangeLog Mon Oct 01 23:05:21 2012 +0200 +++ b/flys-artifacts/ChangeLog Tue Oct 02 00:00:30 2012 +0200 @@ -1,3 +1,9 @@ +2012-10-01 Sascha L. Teichmann <sascha.teichmann@intevation.de> + + * src/main/java/de/intevation/flys/artifacts/model/sq/MeasurementFactory.java: + Totals are now fetched with separate SQL statement. Fixed join to + GEWAESSER table. + 2012-10-01 Sascha L. Teichmann <sascha.teichmann@intevation.de> * src/main/java/de/intevation/flys/artifacts/model/sq/MeasurementFactory.java:
--- a/flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/sq/MeasurementFactory.java Mon Oct 01 23:05:21 2012 +0200 +++ b/flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/sq/MeasurementFactory.java Tue Oct 02 00:00:30 2012 +0200 @@ -4,18 +4,11 @@ import java.util.Calendar; import java.util.Collections; import java.util.Date; +import java.util.HashMap; import java.util.List; +import java.util.Map; import java.util.TreeMap; -import de.intevation.flys.artifacts.model.DateRange; - -import de.intevation.flys.backend.SedDBSessionHolder; - -import java.util.HashMap; -import java.util.Map; - -import org.apache.log4j.Logger; - import org.hibernate.SQLQuery; import org.hibernate.Session; @@ -23,13 +16,31 @@ import org.hibernate.type.StandardBasicTypes; +import de.intevation.flys.artifacts.model.DateRange; + +import de.intevation.flys.backend.SedDBSessionHolder; + public class MeasurementFactory { - private static final Logger log = - Logger.getLogger(MeasurementFactory.class); + public static final String SQL_TOTALS = + "SELECT " + + "m.Q_BPEGEL AS Q_BPEGEL,"+ + "m.TSCHWEB AS TSCHWEB," + + "m.TSAND AS TSAND" + + "FROM MESSUNG m " + + "JOIN STATION s ON m.STATIONID = s.STATIONID " + + "JOIN GEWAESSER r ON s.GEWAESSERID = r.GEWAESSERID " + + "WHERE " + + "r.NAME = :river_name " + + "AND m.Q_BPEGEL IS NOT NULL " + + "AND s.KM BETWEEN :location - 0.001 AND :location + 0.001 " + + "AND m.DATUM BETWEEN :from AND :to " + + "AND m.DATUM IS NOT NULL"; public static final String SQL_FACTIONS = - "SELECT m.datum AS DATUM," + + "SELECT " + + "m.datum AS DATUM," + + "m.Q_BPEGEL AS Q_BPEGEL,"+ "g.GLOTRECHTEID AS GLOTRECHTEID," + "gp.LFDNR AS LFDNR," + "g.UFERABST AS UFERABST," + @@ -62,11 +73,12 @@ "gs.RSIEB21 AS RSIEB21, gs.REST AS REST " + "FROM MESSUNG m " + "JOIN STATION s ON m.STATIONID = s.STATIONID " + + "JOIN GEWAESSER r ON s.GEWAESSERID = r.GEWAESSERID " + "JOIN glotrechte g ON m.MESSUNGID = g.MESSUNGID " + "JOIN gprobe gp ON g.GLOTRECHTEID = gp.GLOTRECHTEID " + "JOIN GSIEBUNG gs ON g.GLOTRECHTEID = gs.GLOTRECHTEID " + "WHERE " + - "g.NAME = :river_name " + + "r.NAME = :river_name " + "AND m.Q_BPEGEL IS NOT NULL " + "AND s.KM BETWEEN :location - 0.001 AND :location + 0.001 " + "AND m.DATUM BETWEEN :from AND :to " + @@ -84,7 +96,25 @@ "COALESCE(gs.RSIEB17, 0) + COALESCE(gs.RSIEB18, 0) +" + "COALESCE(gs.RSIEB19, 0) + COALESCE(gs.RSIEB20, 0) +" + "COALESCE(gs.RSIEB21, 0) + COALESCE(gs.REST, 0)) > 0 " + - "ORDER BY m.DATUM, g.UFERABST, g.GLOTRECHTEID, gp.LFDNR"; + "ORDER BY " + + "m.DATUM, g.UFERABST, g.GLOTRECHTEID, gp.LFDNR"; + + public static final BasicTransformerAdapter TOTALS_TRANSFORMER = + new BasicTransformerAdapter() { + private static final long serialVersionUID = 1L; + + @Override + public Object transformTuple(Object [] tuple, String [] aliases) { + Map<String, Object> map = new HashMap<String, Object>(); + for (int i = 0; i < tuple.length; ++i) { + Object value = tuple[i]; + if (value != null) { + map.put(aliases[i], value); + } + } + return new Measurement(map, Collections.<Sieve>emptyList()); + } + }; private static final int index(String s) { return Integer.parseInt(s.substring(s.length()-2))-1; @@ -92,6 +122,7 @@ public static final BasicTransformerAdapter FRACTIONS_TRANSFORMER = new BasicTransformerAdapter() { + private static final long serialVersionUID = 1L; @Override public Object transformTuple(Object [] tuple, String [] aliases) { @@ -134,18 +165,55 @@ private MeasurementFactory() { } - protected static Measurements load( + public static Measurements getMeasurements( + String river, + double location, + DateRange dateRange + ) { + Session session = SedDBSessionHolder.HOLDER.get(); + try { + List<Measurement> totals = loadTotals( + session, river, location, dateRange); + + List<Measurement> accumulated = loadFractions( + session, river, location, dateRange); + + return new Measurements(totals, accumulated); + } + finally { + session.close(); + } + } + + protected static List<Measurement> loadTotals( Session session, String river, double location, DateRange dateRange ) { - boolean debug = log.isDebugEnabled(); + SQLQuery query = session.createSQLQuery(SQL_TOTALS) + .addScalar("Q_BPEGEL", StandardBasicTypes.DOUBLE) + .addScalar("TSCHWEB", StandardBasicTypes.DOUBLE) + .addScalar("TSAND", StandardBasicTypes.DOUBLE); - if (debug) { - log.debug(SQL_FACTIONS); - } + query.setString("river_name", river); + query.setDouble("location", location); + query.setDate("from", dateRange.getFrom()); + query.setDate("to", dateRange.getTo()); + query.setResultTransformer(TOTALS_TRANSFORMER); + + @SuppressWarnings("unchecked") + List<Measurement> result = (List<Measurement>)query.list(); + return result; + } + + protected static List<Measurement> loadFractions( + Session session, + String river, + double location, + DateRange dateRange + ) { SQLQuery query = session.createSQLQuery(SQL_FACTIONS) .addScalar("Q_BPEGEL", StandardBasicTypes.DOUBLE) .addScalar("DATUM", StandardBasicTypes.DATE) @@ -240,7 +308,7 @@ m.adjustSieves(); } - return new Measurements(measuments, separateByDate(accumulated)); + return separateByDate(accumulated); } protected static List<Measurement> separateByDate(List<Measurement> measurements) { @@ -384,19 +452,5 @@ return new Measurement(data, accumulatedSieves); } - - public static Measurements getMeasurements( - String river, - double location, - DateRange dateRange - ) { - Session session = SedDBSessionHolder.HOLDER.get(); - try { - return load(session, river, location, dateRange); - } - finally { - session.close(); - } - } } // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf-8 :