EmployeeList.java
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.util.*;
import java.sql.*;
/**
Demonstration of a database-enabled servlet.
In a *real* application, we'd separate the data from the
presentation using JSPs or something similar.
*/
public class EmployeeList extends HttpServlet
{
protected void doGet(HttpServletRequest req,
HttpServletResponse res)
throws ServletException, IOException
{
res.setContentType("text/html");
PrintWriter out = res.getWriter();
out.println("<HTML>\n<HEAD>\n" +
"<TITLE>\nEmployee list\n</TITLE>\n" +
"</HEAD>\n<BODY>\n\n<H2>ACE employee list</H2>\n\n");
makeList(out);
out.println("\n</BODY>\n</HTML>");
out.close();
}
public String getServletInfo()
{
return "Employee list";
}
private static void makeList(PrintWriter out)
{
String driver = "org.hsqldb.jdbcDriver";
String dsn = "jdbc:hsqldb:hsql://localhost:9001";
Connection conn;
Statement stat;
String query =
"SELECT EmpID,EmpName " +
"FROM Employee " +
"WHERE Active<>0 " +
"ORDER BY nLast";
try {
Class.forName(driver);
conn = DriverManager.getConnection(dsn,"sa","");
stat = conn.createStatement();
boolean hasResultSet = stat.execute(query);
if (hasResultSet) {
showResultSet(out,stat.getResultSet());
}
stat.close();
conn.close();
}
catch (SQLException ex) {
// Note the use of nested exceptions!
while (ex != null) {
ex.printStackTrace();
ex = ex.getNextException();
}
}
catch (ClassNotFoundException ex) {
ex.printStackTrace();
}
}
private static void showResultSet(
PrintWriter out,ResultSet result)
throws SQLException
{
ResultSetMetaData metaData = result.getMetaData();
int columnCount = metaData.getColumnCount();
out.println("<TABLE CELLPADDING=1 BORDER=1>");
out.print("<TR><TH ALIGN=\"LEFT\">");
for (int i=1; i<=columnCount; i++) {
if (i > 1)
out.print("</TH><TH ALIGN=\"LEFT\">");
out.print(metaData.getColumnLabel(i));
}
out.println("</TH></TR>");
while (result.next()) {
out.print("<TR><TD ALIGN=\"LEFT\">");
for (int i=1; i<=columnCount; i++) {
if (i > 1)
out.print("</TD><TD ALIGN=\"LEFT\">");
String s = result.getString(i);
if (result.wasNull())
out.print(" ");
else
out.print(s);
}
out.println("</TD></TR>");
}
out.println("</TABLE>");
result.close();
}
}