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

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

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

revision 588 by torben, Mon Feb 8 19:12:15 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                  StationBean result;                  Statement stmt = null;
55                                    ResultSet res = null;
56                    StationEntry result;
57    
58                  try {                  try {
59                          conn = DBConnection.getConnection();                          conn = DBConnection.getConnection();
60                    
61                          Statement stmt = conn.createStatement();                          stmt = conn.createStatement();
62                          ResultSet res = stmt.executeQuery(SQL);                                  res = stmt.executeQuery(SQL);          
63                          res.next();                          res.next();
64                          result = convertSingleRow(res);                          result = convertSingleRow(res);
65                  } finally {                  } finally {
66                            if (res != null)
67                                    res.close();
68                            if (stmt != null)
69                                    stmt.close();
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          public List<StationBean> getByName(String name) throws SQLException {                  
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) )
123             *     create function rlike(text,text) returns bool as
124             *     'select $2 ilike $1' language sql strict immutable;
125             *     create operator ~~~ (procedure = rlike, leftarg = text, rightarg = text, commutator = ~~);
126             */
127            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 ? 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;
137                    ResultSet res = null;
138                  try {                  try {
139                          conn = DBConnection.getConnection();                          conn = DBConnection.getConnection();
140                          PreparedStatement stmt = conn.prepareStatement(SQL);                          stmt = conn.prepareStatement(SQL);
141                            
142                          stmt.setString(1, name + "%");                          stmt.setString(1, name + "%");
143                                                    stmt.setString(2, name + "%");
144                          ResultSet rs = stmt.executeQuery();  
145                          result = convertResultset(rs);                          res = stmt.executeQuery();
146                                                    result = convertResultset(res);
147    
148                  } finally {                  } finally {
149                          if (conn != null)                          if (res != null)
150                                    res.close();
151                            if (stmt != null)
152                                    stmt.close();
153                            if (conn!= null)
154                                  conn.close();                                  conn.close();
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 * FROM ( "+          // in denmark 0.4dg latitude ~ 44km, 0.75dg longitude ~ 47km
161                  "               SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address," +  
162                  "                     earth_distance( ll_to_earth(latitude,longitude), ll_to_earth(?,?))::int AS calcdist " +          // the ultra fast method  (and only slightly inaccurate as long as we only cover a limited geographically area)
163                  "               FROM trainstations " +          // is using an aproximation of the length of 1 latitude degree and 1 longitude degree and just use pythagoras to
164                  "               WHERE enabled = true " +          // calculate the distance:
165                  "       ) AS trainstations2 " +          //     sqrt( power(abs(latitude-?)*111320, 2) + power(abs(longitude-?)*63000,2) )::int as calcdist
166                  "ORDER BY calcdist ASC " +  
167                  "LIMIT 4 ";          public StationBean getByLocationWorker(double latitude, double longitude, boolean geolimit) throws SQLException {
168                  List<StationBean> result;                  
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;
183                    ResultSet res = null;
184                  try {                  try {
185                          conn = DBConnection.getConnection();                          conn = DBConnection.getConnection();
186                          PreparedStatement 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                          ResultSet rs = stmt.executeQuery();                          if (geolimit == true) {
190                          result = convertResultset(rs);                                  stmt.setDouble(3, latitude);
191                                                            stmt.setDouble(4, longitude);
192                            }
193                            res = stmt.executeQuery();
194                            result = convertResultset(res);
195                    
196                  } finally {                  } finally {
197                          if (conn != null)                          if (res != null)
198                                    res.close();
199                            if (stmt != null)
200                                    stmt.close();
201                            if (conn!= null)
202                                  conn.close();                                  conn.close();
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;  
                         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())
                                 Statement stmt = conn.createStatement();  
                                 ResultSet res = stmt.executeQuery(SQL);  
                                 result = convertResultset(res);  
                         } finally {  
                                 if (conn!= null)  
265                                          conn.close();                                          conn.close();
266                          }                          } catch (Exception e) {}
267                                            }
268                          return result;  
269                                            return station;
270           }          }
271            
272            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.588  
changed lines
  Added in v.1082
  ViewVC Help
Powered by ViewVC 1.1.20