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("
").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).append("
\n");
sb.append("Generated: ").append(new Date()).append("
\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("").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)");
} else if (dump.equals("trainstatistics")) {
res = dumpResultset("SELECT statisticsdate, location+name+favorites AS station, location,name,favorites,departure,depcache,deperror,timetable,timecache,timeerror " +
"FROM trainstatistics ORDER BY statisticsdate DESC");
} else if (dump.equals("trainstatistics_avg")) {
res = dumpResultset("SELECT count(*) AS count, round(avg(location+name+favorites),1) AS station, 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 = "All
" +
"All with links
" +
"Missing coords
" +
"Duplicate stations
" +
"Disabled stations
" +
"No address
" +
"Has aliases
" +
"update coords
" +
"statistics
" +
"stats_avg
";
}
resp.setContentType("text/html");
resp.getWriter().println(res);
}
}