package examples.htmlkona;

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import java.util.*;
import weblogic.db.jdbc.*;
import weblogic.html.*;

/**
 * This servlet shows how you can use dbKona QueryDataSets to display
 * data on an HTML page.  A QueryDataSet holds the results of any SQL
 * query. The results are available as a collection of Records
 * accessible by an integer index.
 * <p>
 * You can create new records and modify existing records of a
 * QueryDataSet, but the additions and changes cannot be saved. If you
 * wish to save such changes, use a TableDataSet instead.
 * <p>
 * A Record contains a collection of attribute values that correspond to
 * one fetch of a query. The Record elements are accessible by either
 * integer position or string name.
 *
 * @author Copyright (c) 1996-98 by WebLogic, Inc. All Rights Reserved.
 * @author Copyright (c) 1999 by BEA WebXpress, Inc. All Rights Reserved.
 */
public class SimpleQueryDisplay extends HttpServlet {

  public synchronized void service(HttpServletRequest req, HttpServletResponse res) 
    throws IOException
  {
    Connection conn = null;

    try {
      res.setStatus(HttpServletResponse.SC_OK);
      res.setContentType("text/html");
      
      conn = defaults.login();

      SelectStmt sql = new SelectStmt(Enums.ORACLE);

      // The first argument is the attribute name, the second is the alias
      sql.field("empno", "Emp ID")
         .field("ename", "Emp Name") 
         .field("sal",   "Emp Salary") 
         .from("emp")
         .where("sal < 2000")
         .order("empno");   

      QueryDataSet qs = new QueryDataSet(conn, sql);
 
      // We could have used an SQL string instead of a SelectStmt
      // QueryDataSet qs = new QueryDataSet(db, "select empno, ename, sal from emp " + 
      //                                        "where sal < 2000 order by empno");

      qs.fetchRecords();

      // Use an htmlKona UnorderedList to display the results
      UnorderedList ul = new UnorderedList();

      String name   = "";
      String id     = "";
      String salstr = "";
      int sal = 0;
      for (int i = 0; i < qs.size(); i++) {
        Record rec = qs.getRecord(i);
        int tmp = rec.getValue("Emp Salary").asInt();
        ul.addElement(new ListItem("$" + tmp));
        if ( tmp > sal) {
          sal = tmp;
          name   = rec.getValue("Emp Name").asString();
          id     = rec.getValue("Emp ID").asString();
          salstr = rec.getValue("Emp Salary").asString();
        }
      }

      ServletPage hp = new ServletPage("Simple Query Display");
      hp.getBodyElement()
	.setAttribute(BodyElement.bgColor, HtmlColor.white);
      hp.getBody()
        .addElement(MarkupElement.HorizontalLine)
        .addElement(new HeadingElement("Query String: ", +2))
        .addElement(sql.toString())
        .addElement(MarkupElement.HorizontalLine)
        .addElement("I examined the values:")
        .addElement(ul)
        .addElement(MarkupElement.HorizontalLine)
        .addElement("The maximum salary of those employees with a salary < 2000 is:")
        .addElement(MarkupElement.Break)
        .addElement("Name: ")
        .addElement(new BoldElement(name))
        .addElement(MarkupElement.Break)
        .addElement("ID: ")
        .addElement(new BoldElement(id))
        .addElement(MarkupElement.Break)
        .addElement("Salary: ")
        .addElement(new BoldElement(salstr))
        .addElement(MarkupElement.HorizontalLine)
	.addElement("Copyright 1996-99 by BEA WebXpress, Inc. All Rights Reserved.");

      hp.output(res.getOutputStream());
    }
    catch (Exception e) {
      defaults.showException(e, res.getOutputStream());
    }
    
    // Always close the connection in a finally block.
    finally {
      try {conn.close();} catch (Exception e2) {;}
    }
  }
}