Mercurial > dive4elements > river

comparison artifacts/src/main/java/org/dive4elements/river/artifacts/model/minfo/QualityMeasurementFactory.java @ 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 2e11fc7f5d35
children 5e38e2924c07
comparison
equal deleted inserted replaced
8786:0beb34d55630 8787:5855f919cbd6
25 public class QualityMeasurementFactory { 25 public class QualityMeasurementFactory {
26 26
27 private static Logger log = Logger.getLogger(QualityMeasurementFactory.class); 27 private static Logger log = Logger.getLogger(QualityMeasurementFactory.class);
28 28
29 private static final String SQL_BED_MEASUREMENT = 29 private static final String SQL_BED_MEASUREMENT =
30 "SELECT st.km as km," + 30 "SELECT dat.km as km," +
31 " st.datum as datum," + 31 " dat.datum as datum," +
32 " sp.tiefevon as depth1," + 32 " sp.tiefevon as depth1," +
33 " sp.tiefebis as depth2," + 33 " sp.tiefebis as depth2," +
34 " sa.d10 as d10," + 34 " sa.d10 as d10," +
35 " sa.dm as dm," + 35 " sa.dm as dm," +
36 " sa.d16 as d16," + 36 " sa.d16 as d16," +
45 " sa.d80 as d80," + 45 " sa.d80 as d80," +
46 " sa.d84 as d84," + 46 " sa.d84 as d84," +
47 " sa.d90 as d90," + 47 " sa.d90 as d90," +
48 " sa.dmin as dmin," + 48 " sa.dmin as dmin," +
49 " sa.dmax as dmax " + 49 " sa.dmax as dmax " +
50 "FROM sohltest st " + 50 "FROM sohltest dat " +
51 " JOIN station sn ON sn.stationid = st.stationid " + 51 " JOIN station sn ON sn.stationid = dat.stationid " +
52 " JOIN gewaesser gw ON gw.gewaesserid = sn.gewaesserid " + 52 " JOIN gewaesser gw ON gw.gewaesserid = sn.gewaesserid " +
53 " JOIN sohlprobe sp ON sp.sohltestid = st.sohltestid " + 53 " JOIN sohlprobe sp ON sp.sohltestid = dat.sohltestid " +
54 " JOIN siebanalyse sa ON sa.sohlprobeid = sp.sohlprobeid " + 54 " JOIN siebanalyse sa ON sa.sohlprobeid = sp.sohlprobeid " +
55 "WHERE gw.name = :name AND " + 55 "WHERE gw.name = :name AND " +
56 " st.km IS NOT NULL AND " + 56 " dat.km IS NOT NULL AND " +
57 " sp.tiefevon IS NOT NULL AND " + 57 " sp.tiefevon IS NOT NULL AND " +
58 " sp.tiefebis IS NOT NULL AND " + // TODO: Test if char diameter ist null. 58 " sp.tiefebis IS NOT NULL AND " + // TODO: Test if char diameter ist null.
59 " st.km BETWEEN :from - 0.001 AND :to + 0.001 AND " + 59 " dat.km BETWEEN :from - 0.001 AND :to + 0.001 ";
60 " st.datum BETWEEN :start AND :end " +
61 "ORDER BY st.km";
62 60
63 private static final String SQL_BEDLOAD_MEASUREMENT = 61 private static final String SQL_BEDLOAD_MEASUREMENT =
64 "SELECT m.km as km," + 62 "SELECT dat.km as km," +
65 " m.datum as datum," + 63 " dat.datum as datum," +
66 " m.dm as dm," + 64 " dat.dm as dm," +
67 " m.d10 as d10," + 65 " dat.d10 as d10," +
68 " m.d16 as d16," + 66 " dat.d16 as d16," +
69 " m.d20 as d20," + 67 " dat.d20 as d20," +
70 " m.d25 as d25," + 68 " dat.d25 as d25," +
71 " m.d30 as d30," + 69 " dat.d30 as d30," +
72 " m.d40 as d40," + 70 " dat.d40 as d40," +
73 " m.d50 as d50," + 71 " dat.d50 as d50," +
74 " m.d60 as d60," + 72 " dat.d60 as d60," +
75 " m.d70 as d70," + 73 " dat.d70 as d70," +
76 " m.d75 as d75," + 74 " dat.d75 as d75," +
77 " m.d80 as d80," + 75 " dat.d80 as d80," +
78 " m.d84 as d84," + 76 " dat.d84 as d84," +
79 " m.d90 as d90," + 77 " dat.d90 as d90," +
80 " m.dmin as dmin," + 78 " dat.dmin as dmin," +
81 " m.dmax as dmax " + 79 " dat.dmax as dmax " +
82 "FROM messung m" + 80 "FROM messung dat" +
83 " JOIN station sn ON sn.stationid = m.stationid" + 81 " JOIN station sn ON sn.stationid = dat.stationid" +
84 " JOIN gewaesser gw ON gw.gewaesserid = sn.gewaesserid " + 82 " JOIN gewaesser gw ON gw.gewaesserid = sn.gewaesserid " +
85 "WHERE gw.name = :name AND " + 83 "WHERE gw.name = :name AND " +
86 " m.km IS NOT NULL AND " + 84 " dat.km IS NOT NULL AND " +
87 " m.d10 IS NOT NULL AND" + //TODO: Add all other char. diameter. 85 " dat.d10 IS NOT NULL AND" + //TODO: Add all other char. diameter.
88 " m.km BETWEEN :from - 0.001 AND :to + 0.001 AND" + 86 " dat.km BETWEEN :from - 0.001 AND :to + 0.001 ";
89 " m.datum BETWEEN :start AND :end " + 87
90 "ORDER BY m.km"; 88 private static final String SQL_WHERE_DATE =
89 "AND dat.datum BETWEEN :start AND :end ";
90
91 private static final String SQL_ORDER_BY = "ORDER BY dat.km";
92
91 93
92 /** Transform query result into objects, use INSTANCE singleton. */ 94 /** Transform query result into objects, use INSTANCE singleton. */
93 public static final class QualityMeasurementResultTransformer 95 public static final class QualityMeasurementResultTransformer
94 extends BasicTransformerAdapter { 96 extends BasicTransformerAdapter {
95 97
132 } // class BasicTransformerAdapter 134 } // class BasicTransformerAdapter
133 135
134 private QualityMeasurementFactory() { 136 private QualityMeasurementFactory() {
135 } 137 }
136 138
137 protected static QualityMeasurements load( 139 private static SQLQuery baseQuery(
138 Session session, 140 Session session,
139 String river, 141 String river,
140 double from, 142 double from,
141 double to, 143 double to,
142 Date start,
143 Date end,
144 String statement 144 String statement
145 ) { 145 ) {
146 SQLQuery query = session.createSQLQuery(statement) 146 SQLQuery query = session.createSQLQuery(statement)
147 .addScalar("km", StandardBasicTypes.DOUBLE) 147 .addScalar("km", StandardBasicTypes.DOUBLE)
148 .addScalar("datum", StandardBasicTypes.DATE) 148 .addScalar("datum", StandardBasicTypes.DATE)
161 .addScalar("d84", StandardBasicTypes.DOUBLE) 161 .addScalar("d84", StandardBasicTypes.DOUBLE)
162 .addScalar("d90", StandardBasicTypes.DOUBLE) 162 .addScalar("d90", StandardBasicTypes.DOUBLE)
163 .addScalar("dmin", StandardBasicTypes.DOUBLE) 163 .addScalar("dmin", StandardBasicTypes.DOUBLE)
164 .addScalar("dmax", StandardBasicTypes.DOUBLE); 164 .addScalar("dmax", StandardBasicTypes.DOUBLE);
165 165
166 if (statement.equals(SQL_BED_MEASUREMENT)) { 166 if (statement.startsWith(SQL_BED_MEASUREMENT)) {
167 query.addScalar("depth1", StandardBasicTypes.DOUBLE); 167 query.addScalar("depth1", StandardBasicTypes.DOUBLE);
168 query.addScalar("depth2", StandardBasicTypes.DOUBLE); 168 query.addScalar("depth2", StandardBasicTypes.DOUBLE);
169 } 169 }
170
170 String seddbRiver = RiverFactory.getRiver(river).nameForSeddb(); 171 String seddbRiver = RiverFactory.getRiver(river).nameForSeddb();
171 172
172 query.setString("name", seddbRiver); 173 query.setString("name", seddbRiver);
173 query.setDouble("from", from); 174 query.setDouble("from", from);
174 query.setDouble("to", to); 175 query.setDouble("to", to);
176
177 query.setResultTransformer(
178 QualityMeasurementResultTransformer.INSTANCE);
179
180 return query;
181 }
182
183 protected static QualityMeasurements load(
184 Session session,
185 String river,
186 double from,
187 double to,
188 String statement
189 ) {
190 SQLQuery query = baseQuery(session, river, from, to, statement);
191
192 return new QualityMeasurements(query.list());
193 }
194
195 protected static QualityMeasurements load(
196 Session session,
197 String river,
198 double from,
199 double to,
200 Date start,
201 Date end,
202 String statement
203 ) {
204 SQLQuery query = baseQuery(session, river, from, to, statement);
205
175 query.setDate("start", start); 206 query.setDate("start", start);
176 query.setDate("end", end); 207 query.setDate("end", end);
177 208
178 query.setResultTransformer(
179 QualityMeasurementResultTransformer.INSTANCE);
180
181 return new QualityMeasurements(query.list()); 209 return new QualityMeasurements(query.list());
182 } 210 }
183 211
184 /** Get all measurements. */ 212
185 public static QualityMeasurements getBedMeasurements( 213 public static QualityMeasurements getBedMeasurements(
186 String river, 214 String river,
187 double from, 215 double from,
188 double to, 216 double to
189 Date start, 217 ) {
190 Date end) { 218 Session session = SedDBSessionHolder.HOLDER.get();
191 Session session = SedDBSessionHolder.HOLDER.get(); 219 return load(session, river, from, to,
192 try { 220 SQL_BED_MEASUREMENT + SQL_ORDER_BY);
193 return load(session, river, from, to, start, end,
194 SQL_BED_MEASUREMENT);
195 }
196 finally {
197 //session.close();
198 }
199 } 221 }
200 222
201 public static QualityMeasurements getBedloadMeasurements( 223 public static QualityMeasurements getBedloadMeasurements(
224 String river,
225 double from,
226 double to
227 ) {
228 Session session = SedDBSessionHolder.HOLDER.get();
229 return load(session, river, from, to,
230 SQL_BEDLOAD_MEASUREMENT + SQL_ORDER_BY);
231 }
232
233 public static QualityMeasurements getBedMeasurements(
202 String river, 234 String river,
203 double from, 235 double from,
204 double to, 236 double to,
205 Date start, 237 Date start,
206 Date end 238 Date end
207 ) { 239 ) {
208 Session session = SedDBSessionHolder.HOLDER.get(); 240 Session session = SedDBSessionHolder.HOLDER.get();
209 try { 241 return load(session, river, from, to, start, end,
210 return load( 242 SQL_BED_MEASUREMENT + SQL_WHERE_DATE + SQL_ORDER_BY);
211 session, 243 }
212 river, 244
213 from, 245 public static QualityMeasurements getBedloadMeasurements(
214 to, 246 String river,
215 start, 247 double from,
216 end, 248 double to,
217 SQL_BEDLOAD_MEASUREMENT); 249 Date start,
218 } 250 Date end
219 finally { 251 ) {
220 //session.close(); 252 Session session = SedDBSessionHolder.HOLDER.get();
221 } 253 return load(
254 session,
255 river,
256 from,
257 to,
258 start,
259 end,
260 SQL_BEDLOAD_MEASUREMENT + SQL_WHERE_DATE + SQL_ORDER_BY);
222 } 261 }
223 } 262 }
224 // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 : 263 // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :
http://dive4elements.wald.intevation.org