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("
").append(sql).append("
");
sb.append("");
for (int i=1; i<=columns; i++) {
sb.append("").append( meta.getColumnName(i)).append(" | ");
}
sb.append("
\n\n");
while(rs.next())
{
count++;
sb.append("");
for (int i=1; i<=columns; i++) {
String value = rs.getString(i);
if (value != null) {
value = value.replace("\n", "
");
}
sb.append("").append( value ).append(" | ");
}
sb.append("
\n");
}
sb.append("
");
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();
}
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("").append(query).append("
");
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 *, " +
"'Maps' AS maps, " +
"'Bane.dk Fjern' as banedk1, " +
"'Bane.dk stog' 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 = "All
All with links
Missing coords
Duplicate stations
Disabled stations
No address
Has aliases
update coords";
}
resp.setContentType("text/html");
resp.getWriter().println(res);
}
}