Mega Code Archive
Servlet Database ResultSet Display Helper
<![CDATA[
import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class MyServlet extends HttpServlet {
private Connection con = null;
public void init() throws ServletException {
try {
Class.forName("com.sybase.jdbc.SybDriver");
con = DriverManager.getConnection("jdbc:sybase:Tds:dbhost:7678", "user", "passwd");
}
catch (ClassNotFoundException e) {
throw new UnavailableException("Couldn't load database driver");
}
catch (SQLException e) {
throw new UnavailableException("Couldn't get db connection");
}
}
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
res.setContentType("text/html");
PrintWriter out = res.getWriter();
out.println("<HTML><HEAD><TITLE>Phonebook</TITLE></HEAD>");
out.println("<BODY>");
HtmlSQLResult result = new HtmlSQLResult("SELECT NAME, PHONE FROM EMPLOYEES", con);
out.println("<H2>Employees:</H2>");
out.println(result);
out.println("</BODY></HTML>");
}
public void destroy() {
try {
if (con != null) con.close();
}
catch (SQLException ignored) { }
}
}
class HtmlSQLResult {
private String sql;
private Connection con;
public HtmlSQLResult(String sql, Connection con) {
this.sql = sql;
this.con = con;
}
public String toString() { // can be called at most once
StringBuffer out = new StringBuffer();
// Uncomment the following line to display the SQL command at start of table
// out.append("Results of SQL Statement: " + sql + "<P>\n");
try {
Statement stmt = con.createStatement();
if (stmt.execute(sql)) {
// There's a ResultSet to be had
ResultSet rs = stmt.getResultSet();
out.append("<TABLE>\n");
ResultSetMetaData rsmd = rs.getMetaData();
int numcols = rsmd.getColumnCount();
// Title the table with the result set's column labels
out.append("<TR>");
for (int i = 1; i <= numcols; i++)
out.append("<TH>" + rsmd.getColumnLabel(i));
out.append("</TR>\n");
while(rs.next()) {
out.append("<TR>"); // start a new row
for(int i = 1; i <= numcols; i++) {
out.append("<TD>"); // start a new data element
Object obj = rs.getObject(i);
if (obj != null)
out.append(obj.toString());
else
out.append(" ");
}
out.append("</TR>\n");
}
// End the table
out.append("</TABLE>\n");
}
else {
// There's a count to be had
out.append("<B>Records Affected:</B> " + stmt.getUpdateCount());
}
}
catch (SQLException e) {
out.append("</TABLE><H1>ERROR:</H1> " + e.getMessage());
}
return out.toString();
}
}
<?xml version="1.0" encoding="iso-8859-1"?>
<!DOCTYPE web-app
PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
"http://java.sun.com/dtd/web-app_2_3.dtd">
<web-app>
<servlet><servlet-name>MyServletName</servlet-name>
<servlet-class>MyServlet</servlet-class>
</servlet>
<servlet-mapping><servlet-name>MyServletName</servlet-name>
<url-pattern>*.htm</url-pattern>
</servlet-mapping>
<context-param>
<param-name>javax.servlet.jsp.jstl.fmt.timeZone</param-name>
<param-value>US/Central</param-value>
</context-param>
<context-param>
<param-name>database-driver</param-name>
<param-value>org.gjt.mm.mysql.Driver</param-value>
</context-param>
<context-param>
<param-name>database-url</param-name>
<param-value>
jdbc:mysql://localhost/forum?user=forumuser</param-value>
</context-param>
</web-app>
]]>