package examples.intro;
/*
 * @author Copyright (c) 1999-2000. BEA Systems, Inc. 
 * All rights reserved.
 */

import java.io.*;
import java.sql.*;
import java.util.Hashtable;
import javax.naming.*;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.sql.DataSource;
import weblogic.common.*;


/**
 * This example servlet takes an SQL query from a form and 
 * executes it using a connection borrowed from a connection 
 * pool in WebLogic Server.
 *
 * The name of the connection pool is specified in an initArg 
 * for the servlet in the <tt>weblogic.properties</tt> file. 
 */

public class SqlServlet extends HttpServlet 
{
  String connPool;

  /**
   * The init method performs any tasks that need to be done when a
   * servlet first starts up.  For this servlet, there is one task,
   * which is to look up the name of the DataSource to use for SQL
   * queries. The DataSource is set in the servlet's initArgs
   * property in the <tt>weblogic.properties</tt> file.  
   */
  public void init(ServletConfig config)
    throws ServletException
  {
    super.init(config);
    connPool = getInitParameter("dataSource");
    if (connPool == null) {
      System.err.println("This servlet requires a " + 
                         " dataSource initArg." );
    }
  }
  
  /**
   * This method handles a POST request from a client. It 
   * gets the SqlStatement parameter from the HttpServletRequest 
   * object, executes the SQL using a connection from the connection 
   * pool, and generates an HTML page with the query results.  
   */
  public void doPost(HttpServletRequest req, 
                     HttpServletResponse res)
    throws ServletException, IOException
  {
    // first, set the "content type" header of the response
    res.setContentType("text/html");
    
    //Get the response's PrintWriter to return text to the client.
    PrintWriter pw = res.getWriter();
    pw.println("<html><head><title>" +
               "SQL Query Results</title></head>");
    pw.println("<body bgcolor=#ffffff><font face=\"Helvetica\">");
    pw.println("<h1><font color=\"#FF0000\">");
    pw.println("SQL Query Results</font></h1>");
    pw.println("<hr><p>");
    
    try {
      String theQuery = req.getParameterValues("sqlStatement")[0];
      pw.println("<p><i>Query:</i> " + theQuery + "<p>");
      
      doQuery(theQuery, pw); // all the JDBC is in this method
      pw.println("<p><hr>");
      putForm(req, pw, theQuery);           // ask for another SQL statement
      pw.println("<p><hr></body></html>");
    }
    catch (Exception e) {
      pw.println("<p><hr>A problem occurred:");
      pw.println("<pre>" + e.getMessage() + "</pre>");
    }
    finally {
      pw.close();
    }
  }
  
  /**
   * The doGet() method is called when a Servlet is first
   * requested. This implement prints a query form to get 
   * the query from the browser. Once a client has this
   * form, all of their subsequent calls to this servlet
   * should be through the doPost() method.
   */
  public void doGet(HttpServletRequest req, 
                    HttpServletResponse res)
    throws ServletException, IOException
  {
    res.setContentType("text/html");
    PrintWriter pw = res.getWriter();
    pw.println("<html><head><title>SQL Query Servlet");
    pw.println("</title></head>");
    pw.println("<body bgcolor=#ffffff><font face=\"Helvetica\">");
    pw.println("<h1><font color=\"#FF0000\">");
    pw.println("SQL Query Servlet</font></h1>");
    pw.println("<hr><p>");
    putForm(req, pw, "");
    pw.println("</body></html>");
    pw.close();
  }


  /**
   * This method executes the SQL query.
   */
  public void doQuery(String theQuery, PrintWriter pw)
    throws Exception
  {
    // set up the JDBC connection
    Context ctx = null;
    Hashtable ht = new Hashtable();
    Connection conn = null;
    ht.put(Context.INITIAL_CONTEXT_FACTORY,
           "weblogic.jndi.WLInitialContextFactory");
    try {
      ctx = new InitialContext(ht);
      javax.sql.DataSource ds = 
        (javax.sql.DataSource) ctx.lookup("demoDataSource");
      conn = ds.getConnection();
      conn.setAutoCommit(true);
      Statement s = conn.createStatement();
      ResultSet rs = null;
      s.execute(theQuery);

      // loop through the result sets
      while (true) {
        rs = s.getResultSet();
        int rowcount = s.getUpdateCount();
        if (rowcount != -1) {
          pw.println("Update count = " + rowcount);
        }
        if (rs == null && rowcount == -1)
          break;
        if (rs != null)
          // call another method to construct a table
          // from the ResultSet
          printResults(pw, rs);
        s.getMoreResults();
      }
    }
    catch (NamingException ne) {
      pw.print("<p><hr>JNDI Exception: ");
      pw.print("<pre>" + ne.getMessage() + "</pre>");
    }
    catch (SQLException se) {
          pw.print("<p><hr>SQL Exception: ");
          pw.println("<pre>" + se.getMessage() + "</pre>");
    }
    finally {
      try { conn.close(); } catch (Exception e) {};
      try { ctx.close(); } catch (Exception e) {};
    }
  }


  /** This method is called from doPost to format the
   * query results as an HTML table. 
   */
  static void printResults(PrintWriter pw, ResultSet rs)
    throws IOException, SQLException
  {
    int columns = 0;
    ResultSetMetaData md = null;

    pw.println("<table border=1 bgcolor=\"#FFFFCC\" " + 
               "width=\"100%\">");
    md = rs.getMetaData();
    columns = md.getColumnCount();
    pw.println("<tr>");
    for (int i = 0 ; i < columns; i++ ) {
      pw.print("  <th align=left>");
      String label = md.getColumnLabel(i+1);
      pw.print( label == null?"<br>" : label ) ;
      pw.println("</th>");
    }
    pw.println("</tr>");
    while (rs.next()) {
      pw.println("<tr>");
      for (int i = 0; i < columns; i++) {
        Object val = rs.getObject(i+1);
        if (val == null)
          pw.println("  <td><br></td>");
        else
          pw.println("  <td>" + val.toString().trim() 
                     + "</td>");
      }
      pw.println("</tr>");
    }
    pw.println("</table>");
    rs.close();
  }
  
  
  /**
   * putForm() writes an HTML form to the response to collect
   * the sqlStatement parameter. 
   */
  public void putForm(HttpServletRequest req, 
                      PrintWriter pw, String query)
    throws IOException 
  {
    
    pw.println("<form action=\"" + 
               req.getRequestURI() + 
               "\" method=post>");
    pw.println("Enter an SQL query:");
    pw.println("<center><p>");
    pw.println("<textarea name=\"sqlStatement\" " + 
               "rows=\"8\" cols=\"64\" wrap=hard>" + 
               query + "</textarea>");
    pw.println("<p><input type=\"submit\" " + 
               "name=\"sendSql\" value=\"Send SQL\">");
    pw.println("</center>");
    pw.println("</form>");
  }
}

  
  








