/[projects]/android/TrainInfoService/src/dk/thoerup/traininfoservice/StationDAO.java
ViewVC logotype

Diff of /android/TrainInfoService/src/dk/thoerup/traininfoservice/StationDAO.java

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

revision 734 by torben, Tue May 18 20:39:22 2010 UTC revision 1082 by torben, Tue Sep 21 05:55:31 2010 UTC
# Line 1  Line 1 
1  package dk.thoerup.traininfoservice;  package dk.thoerup.traininfoservice;
2    
3    import java.sql.Array;
4  import java.sql.Connection;  import java.sql.Connection;
5  import java.sql.PreparedStatement;  import java.sql.PreparedStatement;
6  import java.sql.ResultSet;  import java.sql.ResultSet;
7  import java.sql.SQLException;  import java.sql.SQLException;
8  import java.sql.Statement;  import java.sql.Statement;
9  import java.util.ArrayList;  import java.util.logging.Logger;
10  import java.util.List;  
11    import dk.thoerup.android.traininfo.common.StationBean;
12    import dk.thoerup.android.traininfo.common.StationBean.StationEntry;
13    
14  public class StationDAO {  public class StationDAO {
15          private StationBean convertSingleRow(ResultSet res) throws SQLException {          final static int LOCATION_LIMIT = 8;
16                  StationBean station = new StationBean();          static final Logger logger = Logger.getLogger(StationDAO.class.getName());
17                            
18            
19            private StationEntry convertSingleRow(ResultSet res) throws SQLException {
20                    StationEntry station = new StationEntry();
21    
22                  station.setId( res.getInt(1) );                  station.setId( res.getInt(1) );
23                  station.setName( res.getString(2) );                  station.setName( res.getString(2) );
24                  station.setLatitude( res.getDouble(3) );                  station.setLatitude( res.getDouble(3) );
# Line 22  public class StationDAO { Line 29  public class StationDAO {
29                  station.setAddress( res.getString(8) );                  station.setAddress( res.getString(8) );
30                  station.setCalcdist( (int)res.getDouble(9) );                  station.setCalcdist( (int)res.getDouble(9) );
31                                    
32                    station.setIsRegional( station.getRegional() != null );
33                    station.setIsStrain( station.getStrain() != null );
34                    station.setIsMetro( station.getMetro() != null );
35    
36                  return station;                  return station;
37          }          }
38            
39          private List<StationBean> convertResultset(ResultSet res) throws SQLException {          private StationBean convertResultset(ResultSet res) throws SQLException {
40                  List<StationBean> stations = new ArrayList<StationBean>();                  StationBean stations = new StationBean();
41                  while (res.next()) {                  while (res.next()) {
42                          stations.add( convertSingleRow(res) );                          stations.entries.add( convertSingleRow(res) );
43                  }                  }
44                  return stations;                  return stations;
45                    
46          }          }
47            
48            
49          public StationBean getById(int id) throws SQLException {          public StationEntry getById(int id) throws SQLException {
50                  String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro,address,0.0 " +                  String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro,address,0.0 " +
51                        "FROM trainstations WHERE id=" + id + " AND enabled=true";                  "FROM trainstations WHERE id=" + id + " AND enabled=true";
52                    
53                  Connection conn = null;                  Connection conn = null;
54                  Statement stmt = null;                  Statement stmt = null;
55                  ResultSet res = null;                  ResultSet res = null;
56                  StationBean result;                  StationEntry result;
57                    
58                  try {                  try {
59                          conn = DBConnection.getConnection();                          conn = DBConnection.getConnection();
60                    
61                          stmt = conn.createStatement();                          stmt = conn.createStatement();
62                          res = stmt.executeQuery(SQL);                                    res = stmt.executeQuery(SQL);          
63                          res.next();                          res.next();
# Line 59  public class StationDAO { Line 70  public class StationDAO {
70                          if (conn != null)                          if (conn != null)
71                                  conn.close();                                  conn.close();
72                  }                  }
73                    
74                  return result;                  return result;
75          }          }
76                    
77            public StationBean dumpAll() throws SQLException {
78                    
79                    String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro,address,0.0,aliases " +
80                                    "FROM trainstations WHERE enabled = true ORDER BY id";
81                    
82                    Connection conn = null;
83                    Statement stmt = null;
84                    ResultSet res = null;          
85    
86                    
87                    try {
88                            conn = DBConnection.getConnection();
89    
90                            stmt = conn.createStatement();
91                            res = stmt.executeQuery(SQL);          
92                                                    
93                            // Does mostly the same as convertResultset()
94                            StationBean stations = new StationBean();
95                            while (res.next()) {
96                                    StationEntry entry = convertSingleRow(res);
97                                    
98                                    Array arr = res.getArray(10);
99                                    if (arr != null) {
100                                            String[] aliases = (String[]) arr.getArray();
101                                            entry.setAliases(aliases);
102                                    }
103                                    
104                                    stations.entries.add( entry );
105                                    
106                            }
107                            return stations;
108                            
109    
110                    } finally {
111                            if (res != null)
112                                    res.close();
113                            if (stmt != null)
114                                    stmt.close();
115                            if (conn != null)
116                                    conn.close();
117                    }
118                    
119            }
120    
121          /*          /*
122           * 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) )           * 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) )
123           *     create function rlike(text,text) returns bool as           *     create function rlike(text,text) returns bool as
124           *     'select $2 ilike $1' language sql strict immutable;           *     'select $2 ilike $1' language sql strict immutable;
125       *     create operator ~~~ (procedure = rlike, leftarg = text, rightarg = text, commutator = ~~);           *     create operator ~~~ (procedure = rlike, leftarg = text, rightarg = text, commutator = ~~);
126           */           */
127          public List<StationBean> getByName(String name) throws SQLException {          public StationBean getByName(String name) throws SQLException {
128                  String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0 " +                  String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0 " +
129                  "FROM trainstations " +                  "FROM trainstations " +
130                  "WHERE (name ILIKE ? OR ? ~~~ ANY(aliases)) AND enabled = true " +                  "WHERE (name ILIKE ? OR ? ~~~ ANY(aliases)) AND enabled = true " +
131                  "ORDER BY name ";                  "ORDER BY name ";
132    
133                    
134                  List<StationBean> result;                  StationBean result;
135                  Connection conn = null;                  Connection conn = null;
136                  PreparedStatement stmt = null;                  PreparedStatement stmt = null;
137                  ResultSet res = null;                  ResultSet res = null;
138                  try {                  try {
139                          conn = DBConnection.getConnection();                          conn = DBConnection.getConnection();
140                          stmt = conn.prepareStatement(SQL);                          stmt = conn.prepareStatement(SQL);
141                            
142                          stmt.setString(1, name + "%");                          stmt.setString(1, name + "%");
143                          stmt.setString(2, name + "%");                          stmt.setString(2, name + "%");
144                            
145                          res = stmt.executeQuery();                          res = stmt.executeQuery();
146                          result = convertResultset(res);                          result = convertResultset(res);
147                            
148                  } finally {                  } finally {
149                          if (res != null)                          if (res != null)
150                                  res.close();                                  res.close();
# Line 100  public class StationDAO { Line 155  public class StationDAO {
155                  }                  }
156                  return result;                  return result;
157          }          }
158            
159          public List<StationBean> getByLocation(double latitude, double longitude) throws SQLException {          //the "hack" with max 0.4 degrees latitude and 0.75 degrees longitude is only valid since we only service danish trains,
160                  String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address," +          // in denmark 0.4dg latitude ~ 44km, 0.75dg longitude ~ 47km
161                                           "       earth_distance( earth_coord, ll_to_earth(?,?))::int AS calcdist " +  
162                                           "FROM trainstations " +          // the ultra fast method  (and only slightly inaccurate as long as we only cover a limited geographically area)
163                                           "WHERE enabled = true " +          // is using an aproximation of the length of 1 latitude degree and 1 longitude degree and just use pythagoras to
164                                           "ORDER BY calcdist ASC " +          // calculate the distance:
165                                           "LIMIT 4 ";          //     sqrt( power(abs(latitude-?)*111320, 2) + power(abs(longitude-?)*63000,2) )::int as calcdist
166                  List<StationBean> result;  
167            public StationBean getByLocationWorker(double latitude, double longitude, boolean geolimit) throws SQLException {
168                    
169                    String limitExpression = (geolimit == true) ? "AND abs(latitude-?)<0.4 AND abs(longitude-?)<0.75 " : "";
170                    
171                    String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, " +
172                            "earth_distance( earth_coord, ll_to_earth(?,?))::int AS calcdist " +
173                            "FROM trainstations " +
174                            "WHERE enabled = true " + limitExpression +
175                            "ORDER BY calcdist ASC " +
176                            "LIMIT " + LOCATION_LIMIT;
177    
178    
179                    
180                    StationBean result;
181                  Connection conn = null;                  Connection conn = null;
182                  PreparedStatement stmt = null;                  PreparedStatement stmt = null;
183                  ResultSet res = null;                  ResultSet res = null;
# Line 117  public class StationDAO { Line 186  public class StationDAO {
186                          stmt = conn.prepareStatement(SQL);                          stmt = conn.prepareStatement(SQL);
187                          stmt.setDouble(1, latitude);                          stmt.setDouble(1, latitude);
188                          stmt.setDouble(2, longitude);                          stmt.setDouble(2, longitude);
189                            if (geolimit == true) {
190                                    stmt.setDouble(3, latitude);
191                                    stmt.setDouble(4, longitude);
192                            }
193                          res = stmt.executeQuery();                          res = stmt.executeQuery();
194                          result = convertResultset(res);                          result = convertResultset(res);
195                                            
196                  } finally {                  } finally {
197                          if (res != null)                          if (res != null)
198                                  res.close();                                  res.close();
# Line 130  public class StationDAO { Line 203  public class StationDAO {
203                  }                  }
204                  return result;                  return result;
205          }          }
206                    
207           public List<StationBean> getByList(String list) throws SQLException {          public StationBean getByLocation(double latitude, double longitude) throws SQLException {
208                          String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro, address,0.0 " +                  StationBean result = getByLocationWorker(latitude, longitude, true);
209                          "FROM trainstations " +                  
210                          "WHERE id IN " + list + " AND enabled = true " +                  if (result.entries.size() < LOCATION_LIMIT) { //failover
211                          "ORDER BY name ";                          logger.info("getByLocation failover: " +latitude + "," + longitude);
                           
                         Connection conn = null;  
                         Statement stmt = null;  
                         ResultSet res = null;  
                         List<StationBean> result;  
212                                                    
213                            result = getByLocationWorker(latitude, longitude, false);
214                    }
215                    
216                    return result;
217            }
218            
219            
220    
221            public StationBean getByList(String list) throws SQLException {
222                    String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro, address,0.0 " +
223                    "FROM trainstations " +
224                    "WHERE id IN " + list + " AND enabled = true " +
225                    "ORDER BY name ";
226    
227                    Connection conn = null;
228                    Statement stmt = null;
229                    ResultSet res = null;
230                    StationBean result;
231    
232                    try {
233                            conn = DBConnection.getConnection();
234                            stmt = conn.createStatement();
235                            res = stmt.executeQuery(SQL);
236                            result = convertResultset(res);
237                    } finally {
238                            if (res != null)
239                                    res.close();
240                            if (stmt != null)
241                                    stmt.close();
242                            if (conn!= null)
243                                    conn.close();
244                    }
245    
246                    return result;
247    
248            }
249            public static String getStationName(int stationID) {
250                    String station = "";
251    
252                    Connection conn = null;
253                    try {
254                            conn = DBConnection.getConnection();
255                            Statement stmt = conn.createStatement();
256                            ResultSet rs = stmt.executeQuery("SELECT name FROM trainstations WHERE id=" + stationID);
257                            if (rs.next()) {
258                                    station = rs.getString(1);
259                            }
260    
261                    } catch (Exception e) {
262                    } finally {
263                          try {                          try {
264                                  conn = DBConnection.getConnection();                                  if (conn != null && !conn.isClosed())
                                 stmt = conn.createStatement();  
                                 res = stmt.executeQuery(SQL);  
                                 result = convertResultset(res);  
                         } finally {  
                                 if (res != null)  
                                         res.close();  
                                 if (stmt != null)  
                                         stmt.close();  
                                 if (conn!= null)  
265                                          conn.close();                                          conn.close();
266                          }                          } catch (Exception e) {}
267                                            }
268                          return result;  
269                                            return station;
270           }          }
          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) {}  
                  }  
271    
272                   return station;          public int getIdByName(String name) throws SQLException {
273           }                  String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0 " +
274                    "FROM trainstations " +
275                    "WHERE name = ?  AND enabled = true " +
276                    "LIMIT 1 ";
277    
278                    StationBean result;
279                    Connection conn = null;
280                    PreparedStatement stmt = null;
281                    ResultSet res = null;
282                    try {
283                            conn = DBConnection.getConnection();
284                            stmt = conn.prepareStatement(SQL);
285    
286                            stmt.setString(1, name );
287    
288                            res = stmt.executeQuery();
289                            result = convertResultset(res);
290    
291                    } finally {
292                            if (res != null)
293                                    res.close();
294                            if (stmt != null)
295                                    stmt.close();
296                            if (conn!= null)
297                                    conn.close();
298                    }
299    
300                    if (result.entries.size() == 1) {
301                            return result.entries.get(0).getId();
302                    } else {
303                            return -1;
304                    }
305            }
306  }  }
Legend:
Removed from v.734  
changed lines
  Added in v.1082
  ViewVC Help
Powered by ViewVC 1.1.20