--- android/TrainInfoService/src/dk/thoerup/traininfoservice/StationDAO.java 2010/06/11 14:05:24 835 +++ android/TrainInfoService/src/dk/thoerup/traininfoservice/StationDAO.java 2010/06/11 17:12:29 836 @@ -11,7 +11,7 @@ public class StationDAO { private StationBean convertSingleRow(ResultSet res) throws SQLException { StationBean station = new StationBean(); - + station.setId( res.getInt(1) ); station.setName( res.getString(2) ); station.setLatitude( res.getDouble(3) ); @@ -21,32 +21,32 @@ station.setMetro( res.getString(7) ); station.setAddress( res.getString(8) ); station.setCalcdist( (int)res.getDouble(9) ); - + return station; } - + private List convertResultset(ResultSet res) throws SQLException { List stations = new ArrayList(); while (res.next()) { stations.add( convertSingleRow(res) ); } return stations; - + } - - + + public StationBean 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"; - + "FROM trainstations WHERE id=" + id + " AND enabled=true"; + Connection conn = null; Statement stmt = null; ResultSet res = null; StationBean result; - + try { conn = DBConnection.getConnection(); - + stmt = conn.createStatement(); res = stmt.executeQuery(SQL); res.next(); @@ -59,15 +59,15 @@ if (conn != null) conn.close(); } - + return result; } - + /* * 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) ) * create function rlike(text,text) returns bool as * 'select $2 ilike $1' language sql strict immutable; - * create operator ~~~ (procedure = rlike, leftarg = text, rightarg = text, commutator = ~~); + * create operator ~~~ (procedure = rlike, leftarg = text, rightarg = text, commutator = ~~); */ public List getByName(String name) throws SQLException { String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0 " + @@ -75,7 +75,7 @@ "WHERE (name ILIKE ? OR ? ~~~ ANY(aliases)) AND enabled = true " + "ORDER BY name "; - + List result; Connection conn = null; PreparedStatement stmt = null; @@ -83,13 +83,13 @@ 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(); @@ -100,22 +100,22 @@ } return result; } - + //the "hack" with max 1.5 degrees latitude and 2.5 degrees longitude is only valid since we only service danish trains // in denmark 1.5dg latitude ~ 165km, 2.5dg longitude ~ 155km - + // the ultra fast method (and only slightly inaccurate as long as we only cover a limited geographically area) // is using an aproximation of the length of 1 latitude degree and 1 longitude degree and just use pythagoras to // calculate the distance: - // sqrt( power(abs(latitude-?)*111320, 2) + power(abs(longitude-?)*63000,2) )::int as calcdist - + // sqrt( power(abs(latitude-?)*111320, 2) + power(abs(longitude-?)*63000,2) )::int as calcdist + public List getByLocation(double latitude, double longitude) throws SQLException { String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address," + - " earth_distance( earth_coord, ll_to_earth(?,?))::int AS calcdist " + - "FROM trainstations " + - "WHERE enabled = true AND abs(latitude-?)<1.5 AND abs(longitude-?)<2.5 " + - "ORDER BY calcdist ASC " + - "LIMIT 4 "; + " earth_distance( earth_coord, ll_to_earth(?,?))::int AS calcdist " + + "FROM trainstations " + + "WHERE enabled = true AND abs(latitude-?)<1.5 AND abs(longitude-?)<2.5 " + + "ORDER BY calcdist ASC " + + "LIMIT 4 "; List result; Connection conn = null; PreparedStatement stmt = null; @@ -129,7 +129,7 @@ stmt.setDouble(4, longitude); res = stmt.executeQuery(); result = convertResultset(res); - + } finally { if (res != null) res.close(); @@ -140,55 +140,92 @@ } return result; } - - public List getByList(String list) throws SQLException { - String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro, address,0.0 " + - "FROM trainstations " + - "WHERE id IN " + list + " AND enabled = true " + - "ORDER BY name "; - - Connection conn = null; - Statement stmt = null; - ResultSet res = null; - List result; - + + public List getByList(String list) throws SQLException { + String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro, address,0.0 " + + "FROM trainstations " + + "WHERE id IN " + list + " AND enabled = true " + + "ORDER BY name "; + + Connection conn = null; + Statement stmt = null; + ResultSet res = null; + List result; + + try { + conn = DBConnection.getConnection(); + stmt = conn.createStatement(); + res = stmt.executeQuery(SQL); + result = convertResultset(res); + } finally { + if (res != null) + res.close(); + if (stmt != null) + stmt.close(); + if (conn!= null) + conn.close(); + } + + return result; + + } + public static String getStationName(int stationID) { + String station = ""; + + Connection conn = null; + try { + conn = DBConnection.getConnection(); + Statement stmt = conn.createStatement(); + ResultSet rs = stmt.executeQuery("SELECT name FROM trainstations WHERE id=" + stationID); + if (rs.next()) { + station = rs.getString(1); + } + + } catch (Exception e) { + } finally { try { - conn = DBConnection.getConnection(); - stmt = conn.createStatement(); - res = stmt.executeQuery(SQL); - result = convertResultset(res); - } finally { - if (res != null) - res.close(); - if (stmt != null) - stmt.close(); - if (conn!= null) + if (conn != null && !conn.isClosed()) conn.close(); - } - - return result; - - } - public static String getStationName(int stationID) { - String station = ""; - - Connection conn = null; - try { - conn = DBConnection.getConnection(); - Statement stmt = conn.createStatement(); - ResultSet rs = stmt.executeQuery("SELECT name FROM trainstations WHERE id=" + stationID); - if (rs.next()) { - station = rs.getString(1); - } - - } catch (Exception e) { - } finally { - try { - if (conn != null && !conn.isClosed()) - conn.close(); - } catch (Exception e) {} - } + } catch (Exception e) {} + } - return station; - } + return station; + } + + public int getBySpecificName(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 "; + + System.out.println(" getBySpecificName() "); + + List 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(); + } + + if (result.size() == 1) { + return result.get(0).getId(); + } else { + return -1; + } + } }