1 |
package dk.thoerup.traininfoservice;
|
2 |
|
3 |
import java.io.IOException;
|
4 |
import java.io.PrintWriter;
|
5 |
import java.sql.Connection;
|
6 |
import java.sql.PreparedStatement;
|
7 |
import java.sql.ResultSet;
|
8 |
import java.sql.SQLException;
|
9 |
|
10 |
import javax.servlet.ServletException;
|
11 |
import javax.servlet.http.HttpServlet;
|
12 |
import javax.servlet.http.HttpServletRequest;
|
13 |
import javax.servlet.http.HttpServletResponse;
|
14 |
|
15 |
|
16 |
public class LoadStations extends HttpServlet {
|
17 |
private static final long serialVersionUID = 1L;
|
18 |
|
19 |
public LoadStations() {
|
20 |
super();
|
21 |
}
|
22 |
|
23 |
protected void insertOrUpdate(Connection conn, String type, String code, String name) throws SQLException{
|
24 |
String sql = "SELECT id FROM trainstations WHERE stationcode=?";
|
25 |
PreparedStatement stmt = conn.prepareStatement(sql);
|
26 |
stmt.setString(1, code);
|
27 |
|
28 |
ResultSet rs = stmt.executeQuery();
|
29 |
|
30 |
boolean stog = type.equalsIgnoreCase("S2");
|
31 |
|
32 |
if (rs.next()) {
|
33 |
int id = rs.getInt(1);
|
34 |
stmt = conn.prepareStatement("UPDATE trainstations SET name=?, stog=?, updated=now() WHERE id=?");
|
35 |
stmt.setString(1, name);
|
36 |
stmt.setBoolean(2, stog);
|
37 |
stmt.setInt(3, id);
|
38 |
stmt.execute();
|
39 |
} else {
|
40 |
stmt = conn.prepareStatement("INSERT INTO trainstations (name,stationcode,stog,updated) VALUES (?,?,?,now())");
|
41 |
stmt.setString(1, name);
|
42 |
stmt.setString(2, code);
|
43 |
stmt.setBoolean(3, stog);
|
44 |
stmt.execute();
|
45 |
}
|
46 |
|
47 |
}
|
48 |
|
49 |
@Override
|
50 |
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
|
51 |
String url = "http://rafiki.t-hoerup.dk/tog/stations.php";
|
52 |
|
53 |
String stationStr = DownloadUtil.getContentString(url, 5000, "ISO-8859-1");
|
54 |
|
55 |
String stations[] = stationStr.split("\n");
|
56 |
Connection conn = null;
|
57 |
try {
|
58 |
conn = DBConnection.getConnection();
|
59 |
for (String stationLine : stations) {
|
60 |
String fields[] = stationLine.split(";");
|
61 |
|
62 |
|
63 |
insertOrUpdate(conn, fields[0], fields[1], fields[2]);
|
64 |
}
|
65 |
} catch (Exception e) {
|
66 |
throw new ServletException(e);
|
67 |
} finally {
|
68 |
try {
|
69 |
if (conn != null && !conn.isClosed())
|
70 |
conn.close();
|
71 |
} catch (SQLException e) {}
|
72 |
}
|
73 |
PrintWriter out = resp.getWriter();
|
74 |
out.print("ok");
|
75 |
}
|
76 |
|
77 |
}
|