thoerup/traininfoservice/DumpResultSet.java

package dk.thoerup.traininfoservice;

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.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

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)");
                        } else if (dump.equals("trainstatistics")) {
                                res = dumpResultset("SELECT * FROM trainstatistics ORDER BY statisticsdate DESC");
                        } else if (dump.equals("trainstatistics_avg")) {
                                res = dumpResultset("SELECT  round(avg(location),1) as location, round(avg(name),1) as name, round(avg(favorites),1) as favorites, " +
                                                "round(avg(departure),1) as departure, round(avg(depcache),1) as depcache, round(avg(deperror),1) as deperror, " +
                                                "round(avg(timetable),1) as timetable, round(avg(timecache),1) as timecache, round(avg(timeerror),1) as timeerror " +
                                                "FROM trainstatistics");
                        }
                }
                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>" +
                                        "<a href=DumpResultSet?dump=trainstatistics>statistics</a><br>" +
                                        "<a href=DumpResultSet?dump=trainstatistics_avg>stats_avg</a><br>";
                }
                
                resp.setContentType("text/html");
                resp.getWriter().println(res);
        }

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