traininfoservice/db/DumpResultSet.java

package dk.thoerup.traininfoservice.db;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.Date;

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


@WebServlet(urlPatterns={"/DumpResultSet"})
public class DumpResultSet extends HttpServlet {

    public DumpResultSet() {
        super();
    }


        private static final long serialVersionUID = 1L;

        String dumpResultset(String sql) throws ServletException {
                StringBuilder sb = new StringBuilder();
                
                Connection conn = null;
                Statement stmt = null;
                ResultSet rs = null;
                
                try {
                        int count = 0;
                        
                        conn =  DBConnection.getConnection();
                        stmt = conn.createStatement();
                        
                        rs = stmt.executeQuery(sql);
                        ResultSetMetaData meta = rs.getMetaData();
                        int columns = meta.getColumnCount();
                        
                        sb.append("<h2>").append(sql).append("</h2>");
                        sb.append("<table border=1><tr>");
                        for (int i=1; i<=columns; i++) {
                                sb.append("<th>").append( meta.getColumnName(i)).append("</th>");
                        }
                        sb.append("</tr>\n\n");
                        while(rs.next())
                        {
                                count++;
                                
                                sb.append("<tr>");
                                for (int i=1; i<=columns; i++) {
                                        String value = rs.getString(i);
                                        if (value != null) {
                                                value = value.replace("\n", "<br>");
                                        }
                                        sb.append("<td>").append( value ).append("</td>");
                                }
                                sb.append("</tr>\n");
                        }
                        sb.append("</table>");
                        sb.append("Rowcount: ").append(count).append("<br>\n");
                        sb.append("Generated: ").append(new Date()).append("<br>\n");
                } catch (Exception e) { 
                        throw new ServletException(e);
                } finally {

                        try {
                                if (rs != null && !rs.isClosed())
                                        rs.close();
                        } catch (Exception e) {}
                        try {
                                if (stmt != null && !stmt.isClosed())
                                        stmt.close();
                        } catch (Exception e) {}
                        
                        try {
                                if (conn != null && !conn.isClosed())
                                        conn.close();
                        } catch (Exception e) {}
                }
                return sb.toString();
        }
        
        String dumpUpdate(String query) throws ServletException {
                StringBuilder sb = new StringBuilder();

                Connection conn = null;
                Statement stmt = null;
                try {
                        conn =  DBConnection.getConnection();
                        stmt = conn.createStatement();
                        stmt.execute(query);
                        int count = stmt.getUpdateCount();
                        
                        sb.append("<h2>").append(query).append("</h2>");
                        sb.append("Affected rows: ").append(count);
                } catch (Exception e) { 
                        throw new ServletException(e);
                } finally {
                        try {
                                if (stmt != null && !stmt.isClosed())
                                        stmt.close();
                        } catch (Exception e) {}
                        
                        try {
                                if (conn != null && !conn.isClosed())
                                        conn.close();
                        } catch (Exception e) {}
                }
                
                return sb.toString();
        }
        
        
        @Override
        protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
                String dump = req.getParameter("dump");
                
                String res = null;
        
                if (dump != null) {
                        if (dump.equals("all")) {
                                res = dumpResultset("SELECT * FROM trainstations ORDER BY id");
                        } else if (dump.equals("coords")) {
                                res = dumpResultset("SELECT * FROM trainstations WHERE latitude IS NULL OR longitude IS NULL ORDER BY id");                     
                        } else if (dump.equals("duplicate")) {
                                res = dumpResultset("SELECT name,count(*) FROM trainstations GROUP BY name HAVING count(*) > 1"); 
                        } else if (dump.equals("allfull")) {
                                res = dumpResultset("select *, " + 
                                                "'<a href=\"http://maps.google.dk/?q=' ||latitude || ',' || longitude || '\">Maps</a>' AS maps, " + 
                                                "'<a href=\"http://www.bane.dk/visStation.asp?ArtikelID=4275&W=FJRN&S=' || stationcode_fjrn  || '\">Bane.dk Fjern</a>' as banedk1, " + 
                                                "'<a href=\"http://www.bane.dk/visStation.asp?ArtikelID=4275&W=S2&S=' || stationcode_stog  || '\">Bane.dk stog</a>' as banedk2 " + 
                                                "FROM trainstations ORDER BY id");
                        } else if (dump.equals("disabled")) {
                                res = dumpResultset("SELECT * FROM trainstations WHERE enabled = false ORDER BY id");
                        } else if (dump.equals("noaddress")) {
                                res = dumpResultset("SELECT * FROM trainstations WHERE address IS NULL or address = '' ");
                        } else if (dump.equals("aliases")) {
                                res = dumpResultset("SELECT * FROM trainstations WHERE aliases IS NOT null");
                        } else if (dump.equals("updatecoords")) {
                                res = dumpUpdate("UPDATE trainstations SET earth_coord = ll_to_earth(latitude,longitude)");
                        } 
                }
                if (res == null) {
                        res = "<a href=DumpResultSet?dump=all>All</a><br>" +
                                        "<a href=DumpResultSet?dump=allfull>All with links</a><br>" +
                                        "<a href=DumpResultSet?dump=coords>Missing coords</a><br>" +
                                        "<a href=DumpResultSet?dump=duplicate>Duplicate stations</a><br>" +
                                        "<a href=DumpResultSet?dump=disabled>Disabled stations</a><br>" +
                                        "<a href=DumpResultSet?dump=noaddress>No address</a><br>" +
                                        "<a href=DumpResultSet?dump=aliases>Has aliases</a><br>" +
                                        "<a href=DumpResultSet?dump=updatecoords>update coords</a><br>";
                }
                
                resp.setContentType("text/html");
                resp.getWriter().println(res);
        }

}
1	torben	1255	package dk.thoerup.traininfoservice.db;
2	torben	296
3			import java.io.IOException;
4			import java.sql.Connection;
5			import java.sql.ResultSet;
6			import java.sql.ResultSetMetaData;
7			import java.sql.Statement;
8	torben	827	import java.util.Date;
9	torben	296
10			import javax.servlet.ServletException;
11	torben	958	import javax.servlet.annotation.WebServlet;
12	torben	296	import javax.servlet.http.HttpServlet;
13			import javax.servlet.http.HttpServletRequest;
14			import javax.servlet.http.HttpServletResponse;
15
16	torben	1255
17	torben	958	@WebServlet(urlPatterns={"/DumpResultSet"})
18	torben	296	public class DumpResultSet extends HttpServlet {
19
20	torben	298	public DumpResultSet() {
21			super();
22			}
23	torben	296
24	torben	298
25	torben	296	private static final long serialVersionUID = 1L;
26
27			String dumpResultset(String sql) throws ServletException {
28			StringBuilder sb = new StringBuilder();
29
30			Connection conn = null;
31			Statement stmt = null;
32			ResultSet rs = null;
33	torben	298
34	torben	296	try {
35	torben	304	int count = 0;
36
37	torben	296	conn = DBConnection.getConnection();
38			stmt = conn.createStatement();
39
40			rs = stmt.executeQuery(sql);
41			ResultSetMetaData meta = rs.getMetaData();
42			int columns = meta.getColumnCount();
43
44			sb.append("<h2>").append(sql).append("</h2>");
45	torben	298	sb.append("<table border=1><tr>");
46	torben	296	for (int i=1; i<=columns; i++) {
47			sb.append("<th>").append( meta.getColumnName(i)).append("</th>");
48			}
49	torben	304	sb.append("</tr>\n\n");
50	torben	296	while(rs.next())
51			{
52	torben	304	count++;
53
54	torben	296	sb.append("<tr>");
55			for (int i=1; i<=columns; i++) {
56	torben	494	String value = rs.getString(i);
57			if (value != null) {
58			value = value.replace("\n", "<br>");
59			}
60			sb.append("<td>").append( value ).append("</td>");
61	torben	296	}
62	torben	304	sb.append("</tr>\n");
63	torben	296	}
64			sb.append("</table>");
65	torben	827	sb.append("Rowcount: ").append(count).append("<br>\n");
66			sb.append("Generated: ").append(new Date()).append("<br>\n");
67	torben	296	} catch (Exception e) {
68			throw new ServletException(e);
69			} finally {
70
71			try {
72			if (rs != null && !rs.isClosed())
73			rs.close();
74			} catch (Exception e) {}
75			try {
76			if (stmt != null && !stmt.isClosed())
77			stmt.close();
78			} catch (Exception e) {}
79
80			try {
81			if (conn != null && !conn.isClosed())
82			conn.close();
83			} catch (Exception e) {}
84			}
85			return sb.toString();
86			}
87
88	torben	733	String dumpUpdate(String query) throws ServletException {
89			StringBuilder sb = new StringBuilder();
90
91			Connection conn = null;
92			Statement stmt = null;
93			try {
94			conn = DBConnection.getConnection();
95			stmt = conn.createStatement();
96			stmt.execute(query);
97			int count = stmt.getUpdateCount();
98
99			sb.append("<h2>").append(query).append("</h2>");
100			sb.append("Affected rows: ").append(count);
101			} catch (Exception e) {
102			throw new ServletException(e);
103			} finally {
104			try {
105			if (stmt != null && !stmt.isClosed())
106			stmt.close();
107			} catch (Exception e) {}
108
109			try {
110			if (conn != null && !conn.isClosed())
111			conn.close();
112			} catch (Exception e) {}
113			}
114
115			return sb.toString();
116			}
117
118
119
120	torben	296	@Override
121			protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
122			String dump = req.getParameter("dump");
123
124	torben	298	String res = null;
125	torben	296
126	torben	298	if (dump != null) {
127			if (dump.equals("all")) {
128	torben	304	res = dumpResultset("SELECT * FROM trainstations ORDER BY id");
129	torben	298	} else if (dump.equals("coords")) {
130	torben	304	res = dumpResultset("SELECT * FROM trainstations WHERE latitude IS NULL OR longitude IS NULL ORDER BY id");
131	torben	298	} else if (dump.equals("duplicate")) {
132			res = dumpResultset("SELECT name,count() FROM trainstations GROUP BY name HAVING count() > 1");
133	torben	304	} else if (dump.equals("allfull")) {
134			res = dumpResultset("select *, " +
135	torben	497	"'<a href=\"http://maps.google.dk/?q=' \|\|latitude \|\| ',' \|\| longitude \|\| '\">Maps</a>' AS maps, " +
136	torben	314	"'<a href=\"http://www.bane.dk/visStation.asp?ArtikelID=4275&W=FJRN&S=' \|\| stationcode_fjrn \|\| '\">Bane.dk Fjern</a>' as banedk1, " +
137			"'<a href=\"http://www.bane.dk/visStation.asp?ArtikelID=4275&W=S2&S=' \|\| stationcode_stog \|\| '\">Bane.dk stog</a>' as banedk2 " +
138			"FROM trainstations ORDER BY id");
139	torben	377	} else if (dump.equals("disabled")) {
140			res = dumpResultset("SELECT * FROM trainstations WHERE enabled = false ORDER BY id");
141	torben	492	} else if (dump.equals("noaddress")) {
142			res = dumpResultset("SELECT * FROM trainstations WHERE address IS NULL or address = '' ");
143	torben	715	} else if (dump.equals("aliases")) {
144			res = dumpResultset("SELECT * FROM trainstations WHERE aliases IS NOT null");
145	torben	733	} else if (dump.equals("updatecoords")) {
146			res = dumpUpdate("UPDATE trainstations SET earth_coord = ll_to_earth(latitude,longitude)");
147	torben	961	}
148	torben	296	}
149	torben	298	if (res == null) {
150	torben	813	res = "<a href=DumpResultSet?dump=all>All</a><br>" +
151			"<a href=DumpResultSet?dump=allfull>All with links</a><br>" +
152			"<a href=DumpResultSet?dump=coords>Missing coords</a><br>" +
153			"<a href=DumpResultSet?dump=duplicate>Duplicate stations</a><br>" +
154			"<a href=DumpResultSet?dump=disabled>Disabled stations</a><br>" +
155			"<a href=DumpResultSet?dump=noaddress>No address</a><br>" +
156			"<a href=DumpResultSet?dump=aliases>Has aliases</a><br>" +
157	torben	961	"<a href=DumpResultSet?dump=updatecoords>update coords</a><br>";
158	torben	298	}
159
160	torben	296	resp.setContentType("text/html");
161			resp.getWriter().println(res);
162			}
163
164			}