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 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++) {
                                        sb.append("<td>").append(rs.getString(i)).append("</td>");
                                }
                                sb.append("</tr>\n");
                        }
                        sb.append("</table>");
                        sb.append("Rowcount: ").append(count);
                } 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();
        }
        
        @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.com/?q=' ||latitude || ',' || longitude || '\">Maps</a>' AS maps, " + 
                                                "CASE WHEN stog = false THEN '<a href=\"http://www.bane.dk/visStation.asp?ArtikelID=4275&W=FJRN&S=' || stationcode  || '\">Bane.dk</a>' " + 
                                                "ELSE '<a href=\"http://www.bane.dk/visStation.asp?ArtikelID=4275&W=S2&S=' || stationcode  || '\">Bane.dk</a>' " + 
                                                "END as banedk FROM trainstations ORDER BY id");
                        }
                }
                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>";
                }
                
                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
9	import javax.servlet.ServletException;
10	import javax.servlet.http.HttpServlet;
11	import javax.servlet.http.HttpServletRequest;
12	import javax.servlet.http.HttpServletResponse;
13
14	public class DumpResultSet extends HttpServlet {
15
16	public DumpResultSet() {
17	super();
18	}
19
20
21	private static final long serialVersionUID = 1L;
22
23	String dumpResultset(String sql) throws ServletException {
24	StringBuilder sb = new StringBuilder();
25
26	Connection conn = null;
27	Statement stmt = null;
28	ResultSet rs = null;
29
30	try {
31	int count = 0;
32
33	conn = DBConnection.getConnection();
34	stmt = conn.createStatement();
35
36	rs = stmt.executeQuery(sql);
37	ResultSetMetaData meta = rs.getMetaData();
38	int columns = meta.getColumnCount();
39
40	sb.append("<h2>").append(sql).append("</h2>");
41	sb.append("<table border=1><tr>");
42	for (int i=1; i<=columns; i++) {
43	sb.append("<th>").append( meta.getColumnName(i)).append("</th>");
44	}
45	sb.append("</tr>\n\n");
46	while(rs.next())
47	{
48	count++;
49
50	sb.append("<tr>");
51	for (int i=1; i<=columns; i++) {
52	sb.append("<td>").append(rs.getString(i)).append("</td>");
53	}
54	sb.append("</tr>\n");
55	}
56	sb.append("</table>");
57	sb.append("Rowcount: ").append(count);
58	} catch (Exception e) {
59	throw new ServletException(e);
60	} finally {
61
62	try {
63	if (rs != null && !rs.isClosed())
64	rs.close();
65	} catch (Exception e) {}
66	try {
67	if (stmt != null && !stmt.isClosed())
68	stmt.close();
69	} catch (Exception e) {}
70
71	try {
72	if (conn != null && !conn.isClosed())
73	conn.close();
74	} catch (Exception e) {}
75	}
76
77	return sb.toString();
78	}
79
80	@Override
81	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
82	String dump = req.getParameter("dump");
83
84	String res = null;
85
86	if (dump != null) {
87	if (dump.equals("all")) {
88	res = dumpResultset("SELECT * FROM trainstations ORDER BY id");
89	} else if (dump.equals("coords")) {
90	res = dumpResultset("SELECT * FROM trainstations WHERE latitude IS NULL OR longitude IS NULL ORDER BY id");
91	} else if (dump.equals("duplicate")) {
92	res = dumpResultset("SELECT name,count() FROM trainstations GROUP BY name HAVING count() > 1");
93	} else if (dump.equals("allfull")) {
94	res = dumpResultset("select *, " +
95	"'<a href=\"http://maps.google.com/?q=' \|\|latitude \|\| ',' \|\| longitude \|\| '\">Maps</a>' AS maps, " +
96	"CASE WHEN stog = false THEN '<a href=\"http://www.bane.dk/visStation.asp?ArtikelID=4275&W=FJRN&S=' \|\| stationcode \|\| '\">Bane.dk</a>' " +
97	"ELSE '<a href=\"http://www.bane.dk/visStation.asp?ArtikelID=4275&W=S2&S=' \|\| stationcode \|\| '\">Bane.dk</a>' " +
98	"END as banedk FROM trainstations ORDER BY id");
99	}
100	}
101	if (res == null) {
102	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>";
103	}
104
105	resp.setContentType("text/html");
106	resp.getWriter().println(res);
107	}
108
109	}