/[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 588 by torben, Mon Feb 8 19:12:15 2010 UTC revision 929 by torben, Sun Jun 27 17:07:27 2010 UTC
# Line 7  import java.sql.SQLException; Line 7  import java.sql.SQLException;
7  import java.sql.Statement;  import java.sql.Statement;
8  import java.util.ArrayList;  import java.util.ArrayList;
9  import java.util.List;  import java.util.List;
10    import java.util.logging.Logger;
11    
12  public class StationDAO {  public class StationDAO {
13            final static int LOCATION_LIMIT = 5;
14            static final Logger logger = Logger.getLogger(StationDAO.class.getName());
15            
16            
17          private StationBean convertSingleRow(ResultSet res) throws SQLException {          private StationBean convertSingleRow(ResultSet res) throws SQLException {
18                  StationBean station = new StationBean();                  StationBean station = new StationBean();
19                    
20                  station.setId( res.getInt(1) );                  station.setId( res.getInt(1) );
21                  station.setName( res.getString(2) );                  station.setName( res.getString(2) );
22                  station.setLatitude( res.getDouble(3) );                  station.setLatitude( res.getDouble(3) );
# Line 21  public class StationDAO { Line 26  public class StationDAO {
26                  station.setMetro( res.getString(7) );                  station.setMetro( res.getString(7) );
27                  station.setAddress( res.getString(8) );                  station.setAddress( res.getString(8) );
28                  station.setCalcdist( (int)res.getDouble(9) );                  station.setCalcdist( (int)res.getDouble(9) );
29                    
30                  return station;                  return station;
31          }          }
32            
33          private List<StationBean> convertResultset(ResultSet res) throws SQLException {          private List<StationBean> convertResultset(ResultSet res) throws SQLException {
34                  List<StationBean> stations = new ArrayList<StationBean>();                  List<StationBean> stations = new ArrayList<StationBean>();
35                  while (res.next()) {                  while (res.next()) {
36                          stations.add( convertSingleRow(res) );                          stations.add( convertSingleRow(res) );
37                  }                  }
38                  return stations;                  return stations;
39                    
40          }          }
41            
42            
43          public StationBean getById(int id) throws SQLException {          public StationBean getById(int id) throws SQLException {
44                  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 " +
45                        "FROM trainstations WHERE id=" + id + " AND enabled=true";                  "FROM trainstations WHERE id=" + id + " AND enabled=true";
46                    
47                  Connection conn = null;                  Connection conn = null;
48                    Statement stmt = null;
49                    ResultSet res = null;
50                  StationBean result;                  StationBean result;
51                    
52                  try {                  try {
53                          conn = DBConnection.getConnection();                          conn = DBConnection.getConnection();
54                    
55                          Statement stmt = conn.createStatement();                          stmt = conn.createStatement();
56                          ResultSet res = stmt.executeQuery(SQL);                                  res = stmt.executeQuery(SQL);          
57                          res.next();                          res.next();
58                          result = convertSingleRow(res);                          result = convertSingleRow(res);
59                  } finally {                  } finally {
60                            if (res != null)
61                                    res.close();
62                            if (stmt != null)
63                                    stmt.close();
64                          if (conn != null)                          if (conn != null)
65                                  conn.close();                                  conn.close();
66                  }                  }
67                    
68                  return result;                  return result;
69          }          }
70            
71                    /*
72             * 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) )
73             *     create function rlike(text,text) returns bool as
74             *     'select $2 ilike $1' language sql strict immutable;
75             *     create operator ~~~ (procedure = rlike, leftarg = text, rightarg = text, commutator = ~~);
76             */
77          public List<StationBean> getByName(String name) throws SQLException {          public List<StationBean> getByName(String name) throws SQLException {
78                  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 " +
79                  "FROM trainstations " +                  "FROM trainstations " +
80                  "WHERE name ILIKE ? AND enabled = true " +                  "WHERE (name ILIKE ? OR ? ~~~ ANY(aliases)) AND enabled = true " +
81                  "ORDER BY name ";                  "ORDER BY name ";
82    
83                    
84                  List<StationBean> result;                  List<StationBean> result;
85                  Connection conn = null;                  Connection conn = null;
86                    PreparedStatement stmt = null;
87                    ResultSet res = null;
88                  try {                  try {
89                          conn = DBConnection.getConnection();                          conn = DBConnection.getConnection();
90                          PreparedStatement stmt = conn.prepareStatement(SQL);                          stmt = conn.prepareStatement(SQL);
91                            
92                          stmt.setString(1, name + "%");                          stmt.setString(1, name + "%");
93                                                    stmt.setString(2, name + "%");
94                          ResultSet rs = stmt.executeQuery();  
95                          result = convertResultset(rs);                          res = stmt.executeQuery();
96                                                    result = convertResultset(res);
97    
98                  } finally {                  } finally {
99                          if (conn != null)                          if (res != null)
100                                    res.close();
101                            if (stmt != null)
102                                    stmt.close();
103                            if (conn!= null)
104                                  conn.close();                                  conn.close();
105                  }                  }
106                  return result;                  return result;
107          }          }
108            
109          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,
110                  String SQL = "SELECT * FROM ( "+          // in denmark 0.4dg latitude ~ 44km, 0.75dg longitude ~ 47km
111                  "               SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address," +  
112                  "                     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)
113                  "               FROM trainstations " +          // is using an aproximation of the length of 1 latitude degree and 1 longitude degree and just use pythagoras to
114                  "               WHERE enabled = true " +          // calculate the distance:
115                  "       ) AS trainstations2 " +          //     sqrt( power(abs(latitude-?)*111320, 2) + power(abs(longitude-?)*63000,2) )::int as calcdist
116                  "ORDER BY calcdist ASC " +  
117                  "LIMIT 4 ";          public List<StationBean> getByLocationWorker(double latitude, double longitude, boolean geolimit) throws SQLException {
118                    
119                    String limitExpression = (geolimit == true) ? "AND abs(latitude-?)<0.4 AND abs(longitude-?)<0.75 " : "";
120                    
121                    String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, " +
122                            "earth_distance( earth_coord, ll_to_earth(?,?))::int AS calcdist " +
123                            "FROM trainstations " +
124                            "WHERE enabled = true " + limitExpression +
125                            "ORDER BY calcdist ASC " +
126                            "LIMIT " + LOCATION_LIMIT;
127    
128    
129                    
130                  List<StationBean> result;                  List<StationBean> result;
131                  Connection conn = null;                  Connection conn = null;
132                    PreparedStatement stmt = null;
133                    ResultSet res = null;
134                  try {                  try {
135                          conn = DBConnection.getConnection();                          conn = DBConnection.getConnection();
136                          PreparedStatement stmt = conn.prepareStatement(SQL);                          stmt = conn.prepareStatement(SQL);
137                          stmt.setDouble(1, latitude);                          stmt.setDouble(1, latitude);
138                          stmt.setDouble(2, longitude);                          stmt.setDouble(2, longitude);
139                          ResultSet rs = stmt.executeQuery();                          if (geolimit == true) {
140                          result = convertResultset(rs);                                  stmt.setDouble(3, latitude);
141                                                            stmt.setDouble(4, longitude);
142                            }
143                            res = stmt.executeQuery();
144                            result = convertResultset(res);
145                    
146                  } finally {                  } finally {
147                          if (conn != null)                          if (res != null)
148                                    res.close();
149                            if (stmt != null)
150                                    stmt.close();
151                            if (conn!= null)
152                                  conn.close();                                  conn.close();
153                  }                  }
154                  return result;                  return result;
155          }          }
156                    
157           public List<StationBean> getByList(String list) throws SQLException {          public List<StationBean> getByLocation(double latitude, double longitude) throws SQLException {
158                          String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro, address,0.0 " +                  List<StationBean> result = getByLocationWorker(latitude, longitude, true);
159                          "FROM trainstations " +                  
160                          "WHERE id IN " + list + " AND enabled = true " +                  if (result.size() < LOCATION_LIMIT) { //failover
161                          "ORDER BY name ";                          logger.info("getByLocation failover: " +latitude + "," + longitude);
                           
                         Connection conn = null;  
                         List<StationBean> result;  
162                                                    
163                            result = getByLocationWorker(latitude, longitude, false);
164                    }
165                    
166                    return result;
167            }
168            
169            
170    
171            public List<StationBean> getByList(String list) throws SQLException {
172                    String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro, address,0.0 " +
173                    "FROM trainstations " +
174                    "WHERE id IN " + list + " AND enabled = true " +
175                    "ORDER BY name ";
176    
177                    Connection conn = null;
178                    Statement stmt = null;
179                    ResultSet res = null;
180                    List<StationBean> result;
181    
182                    try {
183                            conn = DBConnection.getConnection();
184                            stmt = conn.createStatement();
185                            res = stmt.executeQuery(SQL);
186                            result = convertResultset(res);
187                    } finally {
188                            if (res != null)
189                                    res.close();
190                            if (stmt != null)
191                                    stmt.close();
192                            if (conn!= null)
193                                    conn.close();
194                    }
195    
196                    return result;
197    
198            }
199            public static String getStationName(int stationID) {
200                    String station = "";
201    
202                    Connection conn = null;
203                    try {
204                            conn = DBConnection.getConnection();
205                            Statement stmt = conn.createStatement();
206                            ResultSet rs = stmt.executeQuery("SELECT name FROM trainstations WHERE id=" + stationID);
207                            if (rs.next()) {
208                                    station = rs.getString(1);
209                            }
210    
211                    } catch (Exception e) {
212                    } finally {
213                          try {                          try {
214                                  conn = DBConnection.getConnection();                                  if (conn != null && !conn.isClosed())
                                 Statement stmt = conn.createStatement();  
                                 ResultSet res = stmt.executeQuery(SQL);  
                                 result = convertResultset(res);  
                         } finally {  
                                 if (conn!= null)  
215                                          conn.close();                                          conn.close();
216                          }                          } catch (Exception e) {}
217                                            }
218                          return result;  
219                                            return station;
220           }          }
221            
222            public int getIdByName(String name) throws SQLException {
223                    String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0 " +
224                    "FROM trainstations " +
225                    "WHERE name = ?  AND enabled = true " +
226                    "LIMIT 1 ";
227    
228                    List<StationBean> result;
229                    Connection conn = null;
230                    PreparedStatement stmt = null;
231                    ResultSet res = null;
232                    try {
233                            conn = DBConnection.getConnection();
234                            stmt = conn.prepareStatement(SQL);
235    
236                            stmt.setString(1, name );
237    
238                            res = stmt.executeQuery();
239                            result = convertResultset(res);
240    
241                    } finally {
242                            if (res != null)
243                                    res.close();
244                            if (stmt != null)
245                                    stmt.close();
246                            if (conn!= null)
247                                    conn.close();
248                    }
249    
250                    if (result.size() == 1) {
251                            return result.get(0).getId();
252                    } else {
253                            return -1;
254                    }
255            }
256  }  }
Legend:
Removed from v.588  
changed lines
  Added in v.929
  ViewVC Help
Powered by ViewVC 1.1.20