# HG changeset patch # User Sascha L. Teichmann # Date 1349128830 -7200 # Node ID fea3d9210948d84d2b6161396ab6f794a7d0ab10 # Parent d4d272e56d3ad7a5f4369396a21c7909c9fe4983 S(Q) relation: Totals are now fetched with separate SQL statement. Fixed join to GEWAESSER table. diff -r d4d272e56d3a -r fea3d9210948 flys-artifacts/ChangeLog --- 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 + + * 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 * src/main/java/de/intevation/flys/artifacts/model/sq/MeasurementFactory.java: diff -r d4d272e56d3a -r fea3d9210948 flys-artifacts/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 map = new HashMap(); + 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.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 totals = loadTotals( + session, river, location, dateRange); + + List accumulated = loadFractions( + session, river, location, dateRange); + + return new Measurements(totals, accumulated); + } + finally { + session.close(); + } + } + + protected static List 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 result = (List)query.list(); + return result; + } + + protected static List 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 separateByDate(List 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 :