thoerup/traininfoservice/LoadStations.java

package dk.thoerup.traininfoservice;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import dk.thoerup.genericjavautils.HttpUtil;
import dk.thoerup.traininfoservice.db.DBConnection;

@Deprecated
@WebServlet(urlPatterns={"/LoadStations"})

public class LoadStations extends HttpServlet {
        private static final long serialVersionUID = 1L;

        public LoadStations() {
                super();
        }

        protected void insertOrUpdate(Connection conn, String type, String code, String name)  throws SQLException{
                String sql = "SELECT id FROM trainstations WHERE stationcode=?";
                PreparedStatement stmt = conn.prepareStatement(sql);
                stmt.setString(1, code);
                
                ResultSet rs = stmt.executeQuery();
                
                boolean stog = type.equalsIgnoreCase("S2");
                
                if (rs.next()) {
                        int id = rs.getInt(1);
                        stmt = conn.prepareStatement("UPDATE trainstations SET name=?, stog=?, updated=now() WHERE id=?");
                        stmt.setString(1, name);
                        stmt.setBoolean(2, stog);
                        stmt.setInt(3, id);
                        stmt.execute();
                } else {
                        stmt = conn.prepareStatement("INSERT INTO trainstations (name,stationcode,stog,updated) VALUES (?,?,?,now())");
                        stmt.setString(1, name);
                        stmt.setString(2, code);
                        stmt.setBoolean(3, stog);
                        stmt.execute();
                }
                
        }

        @Override
        protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
                String url = "http://rafiki.t-hoerup.dk/tog/stations.php";

                String stationStr = HttpUtil.getContentString(url, 5000, "ISO-8859-1");

                String stations[] = stationStr.split("\n");
                Connection conn = null; 
                try {
                        conn = DBConnection.getConnection();            
                        for (String stationLine : stations) {
                                String fields[] = stationLine.split(";");
                                
                                
                                insertOrUpdate(conn, fields[0], fields[1], fields[2]);
                        }
                } catch (Exception e) {
                        throw new ServletException(e);
                } finally {
                        try {
                                if (conn != null && !conn.isClosed())
                                        conn.close();
                        } catch (SQLException e) {}
                }                       
                PrintWriter out = resp.getWriter();
                out.print("ok");
        }

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