--- android/TrainInfoService/src/dk/thoerup/traininfoservice/StationDAO.java 2010/09/21 05:55:31 1082 +++ android/TrainInfoService/src/dk/thoerup/traininfoservice/db/StationDAO.java 2011/06/10 15:05:24 1516 @@ -1,4 +1,4 @@ -package dk.thoerup.traininfoservice; +package dk.thoerup.traininfoservice.db; import java.sql.Array; import java.sql.Connection; @@ -9,9 +9,22 @@ import java.util.logging.Logger; import dk.thoerup.android.traininfo.common.StationBean; -import dk.thoerup.android.traininfo.common.StationBean.StationEntry; +import dk.thoerup.android.traininfo.common.StationEntry; public class StationDAO { + + private interface StatementParamSetter { + public void setParams(PreparedStatement stmt) throws SQLException ; + } + private class NullSetter implements StatementParamSetter { + @Override + public void setParams(PreparedStatement stmt) throws SQLException {} + } + + public static class NostationException extends Exception { + private static final long serialVersionUID = 1L; + } + final static int LOCATION_LIMIT = 8; static final Logger logger = Logger.getLogger(StationDAO.class.getName()); @@ -45,78 +58,46 @@ } - - public StationEntry getById(int id) throws SQLException { - String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro,address,0.0 " + - "FROM trainstations WHERE id=" + id + " AND enabled=true"; - + private StationBean fetchStations(String SQL, StatementParamSetter setter) throws SQLException { + StationBean stations; Connection conn = null; - Statement stmt = null; + PreparedStatement stmt = null; ResultSet res = null; - StationEntry result; - try { conn = DBConnection.getConnection(); + stmt = conn.prepareStatement(SQL); + + setter.setParams(stmt); + + res = stmt.executeQuery(); + stations = convertResultset(res); - stmt = conn.createStatement(); - res = stmt.executeQuery(SQL); - res.next(); - result = convertSingleRow(res); } finally { if (res != null) res.close(); if (stmt != null) stmt.close(); - if (conn != null) + if (conn!= null) conn.close(); } - - return result; - } - - public StationBean dumpAll() throws SQLException { - - String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro,address,0.0,aliases " + - "FROM trainstations WHERE enabled = true ORDER BY id"; - - Connection conn = null; - Statement stmt = null; - ResultSet res = null; - + return stations; - try { - conn = DBConnection.getConnection(); + } - stmt = conn.createStatement(); - res = stmt.executeQuery(SQL); - - // Does mostly the same as convertResultset() - StationBean stations = new StationBean(); - while (res.next()) { - StationEntry entry = convertSingleRow(res); - - Array arr = res.getArray(10); - if (arr != null) { - String[] aliases = (String[]) arr.getArray(); - entry.setAliases(aliases); - } - - stations.entries.add( entry ); + public StationEntry getById(int id) throws SQLException,NostationException { + String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro,address,0.0 " + + "FROM trainstations WHERE id=" + id + " AND enabled=true"; - } - return stations; - - - } finally { - if (res != null) - res.close(); - if (stmt != null) - stmt.close(); - if (conn != null) - conn.close(); - } + StationBean stations = fetchStations(SQL, new NullSetter() ); + if (stations.entries.size() > 0 ) { + return stations.entries.get(0); + } else { + throw new NostationException(); + } } + + /* * this code requires theses statements are run on database in order to do ILIKE searches against aliases (which is defines as array of varchar(64) ) @@ -124,39 +105,77 @@ * 'select $2 ilike $1' language sql strict immutable; * create operator ~~~ (procedure = rlike, leftarg = text, rightarg = text, commutator = ~~); */ - public StationBean getByName(String name) throws SQLException { + public StationBean getByNameNormal(final String name) throws SQLException { String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0 " + "FROM trainstations " + "WHERE (name ILIKE ? OR ? ~~~ ANY(aliases)) AND enabled = true " + "ORDER BY name "; + class NameSetter implements StatementParamSetter { + @Override + public void setParams(PreparedStatement stmt) throws SQLException { + stmt.setString(1, name + "%"); + stmt.setString(2, name + "%"); + } + } + + return fetchStations(SQL, new NameSetter() ); + } + + + public StationBean getByNameFuzzy(final String name) throws SQLException { + String SQL = "SELECT * FROM (" + + " SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0, " + + " levenshtein(lower(name),lower(?) ) as leven " + + " FROM trainstations " + + " WHERE enabled = true ) as lev2 " + + "WHERE (leven <= 3) " + + "ORDER BY leven " + + "LIMIT 1"; + + class NameSetter implements StatementParamSetter { + @Override + public void setParams(PreparedStatement stmt) throws SQLException { + stmt.setString(1, name ); + } + } + + StationBean stations = fetchStations(SQL, new NameSetter() ); + stations.fuzzystrmatch = true; + return stations; + } + + private String removeSuffix(String str, String suffix) { + if (str.endsWith(suffix)) { + return str.substring(0, str.length() - suffix.length() ); + } else { + return str; + } + } + + public StationBean getByName(String name) throws SQLException { + name = removeSuffix(name, " st."); + name = removeSuffix(name, " st"); + name = removeSuffix(name, " station"); + + StationBean stations = getByNameNormal(name); + + if (stations.entries.size() == 0) { + stations = getByNameFuzzy(name); - StationBean result; - Connection conn = null; - PreparedStatement stmt = null; - ResultSet res = null; - try { - conn = DBConnection.getConnection(); - stmt = conn.prepareStatement(SQL); - - stmt.setString(1, name + "%"); - stmt.setString(2, name + "%"); - - res = stmt.executeQuery(); - result = convertResultset(res); - - } finally { - if (res != null) - res.close(); - if (stmt != null) - stmt.close(); - if (conn!= null) - conn.close(); + logger.info("getByName failover: " + name + "(" + (stations.entries.size() >0) + ")" ); } - return result; + return stations; } + + - //the "hack" with max 0.4 degrees latitude and 0.75 degrees longitude is only valid since we only service danish trains, + //Latitude (horizonal), longitude(vertical) so + // 1 degree latitude is ~ 111320 meters, since the distance between the horizonal lines is always the same + // 1 degree longitude is ~111320 meters at equator but gets shorter as we get closer to the poles. + // so 1 degree longitude is 64.5 km at denmarks southern point (gedser=54.55,11.95) + // and is 59.4km at northern point (skagen = 57.75,10.65) + // The "hack" with max 0.4 degrees latitude and 0.75 degrees longitude is only valid since we only service danish trains, // in denmark 0.4dg latitude ~ 44km, 0.75dg longitude ~ 47km // the ultra fast method (and only slightly inaccurate as long as we only cover a limited geographically area) @@ -164,7 +183,7 @@ // calculate the distance: // sqrt( power(abs(latitude-?)*111320, 2) + power(abs(longitude-?)*63000,2) )::int as calcdist - public StationBean getByLocationWorker(double latitude, double longitude, boolean geolimit) throws SQLException { + public StationBean getByLocationWorker(final double latitude, final double longitude, final boolean geolimit) throws SQLException { String limitExpression = (geolimit == true) ? "AND abs(latitude-?)<0.4 AND abs(longitude-?)<0.75 " : ""; @@ -175,33 +194,20 @@ "ORDER BY calcdist ASC " + "LIMIT " + LOCATION_LIMIT; - - - StationBean result; - Connection conn = null; - PreparedStatement stmt = null; - ResultSet res = null; - try { - conn = DBConnection.getConnection(); - stmt = conn.prepareStatement(SQL); - stmt.setDouble(1, latitude); - stmt.setDouble(2, longitude); - if (geolimit == true) { - stmt.setDouble(3, latitude); - stmt.setDouble(4, longitude); - } - res = stmt.executeQuery(); - result = convertResultset(res); - } finally { - if (res != null) - res.close(); - if (stmt != null) - stmt.close(); - if (conn!= null) - conn.close(); + class LatlongSetter implements StatementParamSetter { + @Override + public void setParams(PreparedStatement stmt) throws SQLException { + stmt.setDouble(1, latitude); + stmt.setDouble(2, longitude); + if (geolimit == true) { + stmt.setDouble(3, latitude); + stmt.setDouble(4, longitude); + } + } } - return result; + + return fetchStations(SQL, new LatlongSetter() ); } public StationBean getByLocation(double latitude, double longitude) throws SQLException { @@ -223,29 +229,81 @@ "FROM trainstations " + "WHERE id IN " + list + " AND enabled = true " + "ORDER BY name "; + + return fetchStations(SQL, new NullSetter() ); + } + + + public StationEntry getSimpleByName(final String name) throws SQLException { + String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0 " + + "FROM trainstations " + + "WHERE name = ? AND enabled = true " + + "LIMIT 1 "; + + class NameSetter implements StatementParamSetter { + @Override + public void setParams(PreparedStatement stmt) throws SQLException { + stmt.setString(1, name ); + } + } + + StationBean stations = fetchStations(SQL, new NameSetter() ); + + if (stations.entries.size() == 1) { + return stations.entries.get(0); + } else { + return null; + } + } + + //used to create full dump in order to populate Google Appengine DB + @Deprecated + public StationBean dumpAll() throws SQLException { + + String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro,address,0.0,aliases " + + "FROM trainstations WHERE enabled = true ORDER BY id"; + Connection conn = null; Statement stmt = null; - ResultSet res = null; - StationBean result; + ResultSet res = null; + try { conn = DBConnection.getConnection(); + stmt = conn.createStatement(); - res = stmt.executeQuery(SQL); - result = convertResultset(res); + res = stmt.executeQuery(SQL); + + // Does mostly the same as convertResultset() + StationBean stations = new StationBean(); + while (res.next()) { + StationEntry entry = convertSingleRow(res); + + Array arr = res.getArray(10); + if (arr != null) { + String[] aliases = (String[]) arr.getArray(); + entry.setAliases(aliases); + } + + stations.entries.add( entry ); + + } + return stations; + + } finally { if (res != null) res.close(); if (stmt != null) stmt.close(); - if (conn!= null) + if (conn != null) conn.close(); } - - return result; - + } + + @Deprecated public static String getStationName(int stationID) { String station = ""; @@ -268,37 +326,27 @@ return station; } - - public int getIdByName(String name) throws SQLException { + + + + @Deprecated + public int getIdByName(final String name) throws SQLException { String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0 " + "FROM trainstations " + "WHERE name = ? AND enabled = true " + "LIMIT 1 "; - StationBean result; - Connection conn = null; - PreparedStatement stmt = null; - ResultSet res = null; - try { - conn = DBConnection.getConnection(); - stmt = conn.prepareStatement(SQL); - - stmt.setString(1, name ); - - res = stmt.executeQuery(); - result = convertResultset(res); - - } finally { - if (res != null) - res.close(); - if (stmt != null) - stmt.close(); - if (conn!= null) - conn.close(); + class NameSetter implements StatementParamSetter { + @Override + public void setParams(PreparedStatement stmt) throws SQLException { + stmt.setString(1, name ); + } } - - if (result.entries.size() == 1) { - return result.entries.get(0).getId(); + + StationBean stations = fetchStations(SQL, new NameSetter() ); + + if (stations.entries.size() == 1) { + return stations.entries.get(0).getId(); } else { return -1; }