Mercurial > dive4elements > river
comparison flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/sq/MeasurementFactory.java @ 3928:d3e2080d3ada
S(Q) relation. Extract more values from database.
flys-artifacts/trunk@5622 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Thu, 27 Sep 2012 16:31:21 +0000 |
parents | e1d3f6bccf2b |
children | f37323e86d59 |
comparison
equal
deleted
inserted
replaced
3927:208a891c21b5 | 3928:d3e2080d3ada |
---|---|
1 package de.intevation.flys.artifacts.model.sq; | 1 package de.intevation.flys.artifacts.model.sq; |
2 | |
3 import java.util.List; | |
2 | 4 |
3 import de.intevation.flys.artifacts.model.DateRange; | 5 import de.intevation.flys.artifacts.model.DateRange; |
4 | 6 |
5 import de.intevation.flys.backend.SedDBSessionHolder; | 7 import de.intevation.flys.backend.SedDBSessionHolder; |
6 | 8 |
19 public class MeasurementFactory | 21 public class MeasurementFactory |
20 { | 22 { |
21 private static final Logger log = | 23 private static final Logger log = |
22 Logger.getLogger(MeasurementFactory.class); | 24 Logger.getLogger(MeasurementFactory.class); |
23 | 25 |
24 public static final String [] GSIEBSATZ = { | |
25 "SIEB01", "SIEB02", "SIEB03", "SIEB04", | |
26 "SIEB05", "SIEB06", "SIEB07", "SIEB08", | |
27 "SIEB09", "SIEB10", "SIEB11", "SIEB13", | |
28 "SIEB13", "SIEB14", "SIEB15", "SIEB16", | |
29 "SIEB17", "SIEB18", "SIEB19", "SIEB20", | |
30 "SIEB21" | |
31 }; | |
32 | |
33 public static final String [] SSIEBUNG = { | |
34 "RSIEB01", "RSIEB02", "RSIEB03", "RSIEB04", | |
35 "RSIEB05", "RSIEB06", "RSIEB07", "RSIEB08", | |
36 "RSIEB09", "RSIEB10", "RSIEB11", "RSIEB13", | |
37 "RSIEB13", "RSIEB14", "RSIEB15", "RSIEB16", | |
38 "RSIEB17", "RSIEB18", "RSIEB19", "RSIEB20", | |
39 "RSIEB21", "REST" | |
40 }; | |
41 | |
42 public static final String SQL_MEASSURE = | 26 public static final String SQL_MEASSURE = |
43 ("SELECT " + | 27 "SELECT m.datum AS DATUM," + |
44 "m.TSAND AS TSAND," + | 28 "g.UFERABST AS UFERABST," + |
45 "m.TSCHWEB AS TSCHWEB," + | 29 "g.UFERABLINKS AS UFERABLINKS," + |
46 "m.CSCHWEB AS CSCHWEB," + | 30 "m.tschweb AS TSCHWEB," + |
47 "m.Q_BPEGEL AS Q_BPEGEL," + | 31 "m.TSAND AS TSAND," + |
48 "m.TGESCHIEBE AS TGESCHIEBE " + | 32 "gp.MESSDAUER AS MESSDAUER," + |
49 // ", %GSIEBSATZ% " + | 33 "gp.MENGE AS MENGE," + |
50 // "%SSIEBUNG% " + | 34 "gp.GTRIEB AS GTRIEB," + |
51 "FROM messung m " + | 35 "gp.LFDNR AS LFDNR," + |
52 "JOIN station s ON m.stationid = s.stationid " + | 36 "m.TGESCHIEBE AS TGESCHIEBE," + |
53 "JOIN gewaesser g ON s.gewaesserid = g.gewaesserid " + | 37 "gs.RSIEB01 AS RSIEB01," + |
54 // "LEFT JOIN GSIEBSATZ gs ON m.gsiebsatzid = gs.gsiebsatzid " + | 38 "gs.RSIEB02 AS RSIEB02," + |
55 // "LEFT JOIN SSIEBUNG ss ON gs.gsiebsatzid = ss.gsiebsatzid " + | 39 "gs.RSIEB03 AS RSIEB03," + |
40 "gs.RSIEB04 AS RSIEB04," + | |
41 "gs.RSIEB05 AS RSIEB05," + | |
42 "gs.RSIEB06 AS RSIEB06," + | |
43 "gs.RSIEB07 AS RSIEB07," + | |
44 "gs.RSIEB08 AS RSIEB08," + | |
45 "gs.RSIEB09 AS RSIEB09," + | |
46 "gs.RSIEB10 AS RSIEB10," + | |
47 "gs.RSIEB11 AS RSIEB11," + | |
48 "gs.RSIEB12 AS RSIEB12," + | |
49 "gs.RSIEB13 AS RSIEB13," + | |
50 "gs.RSIEB14 AS RSIEB14," + | |
51 "gs.RSIEB15 AS RSIEB15," + | |
52 "gs.RSIEB16 AS RSIEB16," + | |
53 "gs.RSIEB17 AS RSIEB17," + | |
54 "gs.RSIEB18 AS RSIEB18," + | |
55 "gs.RSIEB19 AS RSIEB19," + | |
56 "gs.RSIEB20 AS RSIEB20," + | |
57 "gs.RSIEB21 AS RSIEB21," + | |
58 "gs.REST AS REST," + | |
59 "g.GLOTRECHTEID AS GLOTRECHTEID " + | |
60 "FROM MESSUNG m " + | |
61 "JOIN STATION s ON m.STATIONID = s.STATIONID " + | |
62 "JOIN glotrechte g ON m.MESSUNGID = g.MESSUNGID " + | |
63 "JOIN gprobe gp ON g.GLOTRECHTEID = gp.GLOTRECHTEID " + | |
64 "JOIN GSIEBUNG gs ON g.GLOTRECHTEID = gs.GLOTRECHTEID " + | |
56 "WHERE " + | 65 "WHERE " + |
57 "g.name = :river_name " + | 66 "g.NAME = :river_name " + |
58 "AND m.Q_BPEGEL IS NOT NULL " + | 67 "AND m.Q_BPEGEL IS NOT NULL " + |
59 "AND s.km BETWEEN :location - 0.001 AND :location + 0.001 " + | 68 "AND s.KM BETWEEN :location - 0.001 AND :location + 0.001 " + |
60 "AND m.datum BETWEEN :from AND :to "); | 69 "AND m.DATUM BETWEEN :from AND :to " + |
61 //.replace("%GSIEBSATZ%", projection("gs", GSIEBSATZ)); | 70 "AND m.TGESCHIEBE IS NOT NULL " + |
62 //.replace("%SSIEBUNG%", projection("ss", SSIEBUNG)); | 71 "ORDER BY m.DATUM"; |
63 | |
64 private static final String projection( | |
65 String prefix, | |
66 String [] columnNames | |
67 ) { | |
68 StringBuilder sb = new StringBuilder(); | |
69 for (int i = 0; i < columnNames.length; ++i) { | |
70 if (i > 0) { | |
71 sb.append(','); | |
72 } | |
73 sb.append(prefix) | |
74 .append('.') | |
75 .append(columnNames[i]) | |
76 .append(" AS ") | |
77 .append(columnNames[i]); | |
78 } | |
79 return sb.toString(); | |
80 } | |
81 | 72 |
82 public static final class MeasurementResultTransformer | 73 public static final class MeasurementResultTransformer |
83 extends BasicTransformerAdapter | 74 extends BasicTransformerAdapter |
84 { | 75 { |
85 public static MeasurementResultTransformer INSTANCE = | 76 public static MeasurementResultTransformer INSTANCE = |
114 if (debug) { | 105 if (debug) { |
115 log.debug(SQL_MEASSURE); | 106 log.debug(SQL_MEASSURE); |
116 } | 107 } |
117 | 108 |
118 SQLQuery query = session.createSQLQuery(SQL_MEASSURE) | 109 SQLQuery query = session.createSQLQuery(SQL_MEASSURE) |
119 .addScalar("TSAND", StandardBasicTypes.DOUBLE) | 110 .addScalar("TSAND", StandardBasicTypes.DOUBLE) |
120 .addScalar("TSCHWEB", StandardBasicTypes.DOUBLE) | 111 .addScalar("TSCHWEB", StandardBasicTypes.DOUBLE) |
121 .addScalar("CSCHWEB", StandardBasicTypes.DOUBLE) | 112 .addScalar("CSCHWEB", StandardBasicTypes.DOUBLE) |
122 .addScalar("Q_BPEGEL", StandardBasicTypes.DOUBLE); | 113 .addScalar("Q_BPEGEL", StandardBasicTypes.DOUBLE) |
123 | 114 .addScalar("DATUM", StandardBasicTypes.DATE) |
124 /* | 115 .addScalar("UFERABST", StandardBasicTypes.DOUBLE) |
125 for (String siebsatz: GSIEBSATZ) { | 116 .addScalar("UFERABLINKS", StandardBasicTypes.DOUBLE) |
126 query.addScalar(siebsatz, StandardBasicTypes.DOUBLE); | 117 .addScalar("TSCHWEB", StandardBasicTypes.DOUBLE) |
127 } | 118 .addScalar("TSAND", StandardBasicTypes.DOUBLE) |
128 | 119 .addScalar("MESSDAUER", StandardBasicTypes.DOUBLE) |
129 for (String siebung: SSIEBUNG) { | 120 .addScalar("MENGE", StandardBasicTypes.DOUBLE) |
130 query.addScalar(siebung, StandardBasicTypes.DOUBLE); | 121 .addScalar("GTRIEB", StandardBasicTypes.DOUBLE) |
131 } | 122 .addScalar("LFDNR", StandardBasicTypes.DOUBLE) |
132 */ | 123 .addScalar("TGESCHIEBE", StandardBasicTypes.DOUBLE) |
124 .addScalar("RSIEB01", StandardBasicTypes.DOUBLE) | |
125 .addScalar("RSIEB02", StandardBasicTypes.DOUBLE) | |
126 .addScalar("RSIEB03", StandardBasicTypes.DOUBLE) | |
127 .addScalar("RSIEB04", StandardBasicTypes.DOUBLE) | |
128 .addScalar("RSIEB05", StandardBasicTypes.DOUBLE) | |
129 .addScalar("RSIEB06", StandardBasicTypes.DOUBLE) | |
130 .addScalar("RSIEB07", StandardBasicTypes.DOUBLE) | |
131 .addScalar("RSIEB08", StandardBasicTypes.DOUBLE) | |
132 .addScalar("RSIEB09", StandardBasicTypes.DOUBLE) | |
133 .addScalar("RSIEB10", StandardBasicTypes.DOUBLE) | |
134 .addScalar("RSIEB11", StandardBasicTypes.DOUBLE) | |
135 .addScalar("RSIEB12", StandardBasicTypes.DOUBLE) | |
136 .addScalar("RSIEB13", StandardBasicTypes.DOUBLE) | |
137 .addScalar("RSIEB14", StandardBasicTypes.DOUBLE) | |
138 .addScalar("RSIEB15", StandardBasicTypes.DOUBLE) | |
139 .addScalar("RSIEB16", StandardBasicTypes.DOUBLE) | |
140 .addScalar("RSIEB17", StandardBasicTypes.DOUBLE) | |
141 .addScalar("RSIEB18", StandardBasicTypes.DOUBLE) | |
142 .addScalar("RSIEB19", StandardBasicTypes.DOUBLE) | |
143 .addScalar("RSIEB20", StandardBasicTypes.DOUBLE) | |
144 .addScalar("RSIEB21", StandardBasicTypes.DOUBLE) | |
145 .addScalar("REST", StandardBasicTypes.DOUBLE) | |
146 .addScalar("GLOTRECHTEID", StandardBasicTypes.DOUBLE); | |
133 | 147 |
134 query.setString("river_name", river); | 148 query.setString("river_name", river); |
135 query.setDouble("location", location); | 149 query.setDouble("location", location); |
136 query.setDate("from", dateRange.getFrom()); | 150 query.setDate("from", dateRange.getFrom()); |
137 query.setDate("to", dateRange.getTo()); | 151 query.setDate("to", dateRange.getTo()); |
138 | 152 |
139 query.setResultTransformer(MeasurementResultTransformer.INSTANCE); | 153 query.setResultTransformer(MeasurementResultTransformer.INSTANCE); |
140 | 154 |
141 return new Measurements(query.list()); | 155 @SuppressWarnings("unchecked") |
156 List<Measurement> measuments = (List<Measurement>)query.list(); | |
157 | |
158 for (int i = 0, N = measuments.size(); i < N; ++i) { | |
159 Measurement m = measuments.get(i); | |
160 Measurement p = i > 0 ? measuments.get(i-1) : null; | |
161 Measurement n = i < N-1 ? measuments.get(i+1) : null; | |
162 m.setPrev(p); | |
163 m.setNext(n); | |
164 } | |
165 | |
166 return new Measurements(measuments); | |
142 } | 167 } |
143 | 168 |
144 public static Measurements getMeasurements( | 169 public static Measurements getMeasurements( |
145 String river, | 170 String river, |
146 double location, | 171 double location, |