Mercurial > dive4elements > river
changeset 8787:5855f919cbd6
Issue1854: Avoid assumptions about limits of SQL datetime.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Tue, 08 Dec 2015 20:15:45 +0100 (2015-12-08) |
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()) {