traininfoservice/db/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 {


        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 {
                        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><tr>");
                        for (int i=1; i<=columns; i++) {
                                sb.append("<th>").append( meta.getColumnName(i)).append("</th>");
                        }
                        sb.append("</tr>");
                        while(rs.next())
                        {
                                sb.append("<tr>");
                                for (int i=1; i<=columns; i++) {
                                        sb.append("<td>").append(rs.getString(i)).append("</td>");
                                }
                                sb.append("</tr>");
                        }
                        sb.append("</table>");
                } 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 = "invalid dump parameter";
        
                if (dump.equals("all")) {
                        res = dumpResultset("SELECT * FROM trainstations");
                } else if (dump.equals("coords")) {
                        res = dumpResultset("SELECT * FROM trainstations WHERE latitude IS NULL OR longitude IS NULL");                 
                }
                resp.setContentType("text/html");
                resp.getWriter().println(res);
        }

}
1	torben	296	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
17			private static final long serialVersionUID = 1L;
18
19			String dumpResultset(String sql) throws ServletException {
20			StringBuilder sb = new StringBuilder();
21
22			Connection conn = null;
23			Statement stmt = null;
24			ResultSet rs = null;
25			try {
26			conn = DBConnection.getConnection();
27			stmt = conn.createStatement();
28
29			rs = stmt.executeQuery(sql);
30			ResultSetMetaData meta = rs.getMetaData();
31			int columns = meta.getColumnCount();
32
33			sb.append("<h2>").append(sql).append("</h2>");
34			sb.append("<table><tr>");
35			for (int i=1; i<=columns; i++) {
36			sb.append("<th>").append( meta.getColumnName(i)).append("</th>");
37			}
38			sb.append("</tr>");
39			while(rs.next())
40			{
41			sb.append("<tr>");
42			for (int i=1; i<=columns; i++) {
43			sb.append("<td>").append(rs.getString(i)).append("</td>");
44			}
45			sb.append("</tr>");
46			}
47			sb.append("</table>");
48			} catch (Exception e) {
49			throw new ServletException(e);
50			} finally {
51
52			try {
53			if (rs != null && !rs.isClosed())
54			rs.close();
55			} catch (Exception e) {}
56			try {
57			if (stmt != null && !stmt.isClosed())
58			stmt.close();
59			} catch (Exception e) {}
60
61			try {
62			if (conn != null && !conn.isClosed())
63			conn.close();
64			} catch (Exception e) {}
65			}
66
67			return sb.toString();
68			}
69
70			@Override
71			protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
72			String dump = req.getParameter("dump");
73
74			String res = "invalid dump parameter";
75
76			if (dump.equals("all")) {
77			res = dumpResultset("SELECT * FROM trainstations");
78			} else if (dump.equals("coords")) {
79			res = dumpResultset("SELECT * FROM trainstations WHERE latitude IS NULL OR longitude IS NULL");
80			}
81			resp.setContentType("text/html");
82			resp.getWriter().println(res);
83			}
84
85			}