
package beans;

import java.sql.*;
import java.io.Serializable;
import java.util.StringTokenizer;

public class HtmlQueryBean implements Serializable {

  private String searchCondition = "";
  private String connStr = null;
  private String left = null;
  private String right = null;
  private String operator = null;
  private String newCondition = null;

  public String getResult() throws SQLException {
    return runQuery();
  }

  public void setSearchCondition(String searchCondition) {
    this.searchCondition = searchCondition;
  }
  
  public void setConnStr(String connStr) {
    this.connStr = connStr;
  }

  private String runQuery() {
    Connection conn = null;    
    Statement stmt = null;
    ResultSet rset = null;
    try {
      if (conn == null) {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      conn = DriverManager.getConnection(connStr,
					 "scott","tiger");
      }
        if ((searchCondition.equals("") || right.equalsIgnoreCase("null"))) {
          stmt = conn.createStatement();
          rset = stmt.executeQuery ("SELECT ename as \"Name\", " +
 	               "sal as \"Salary\"," +  
                       "TO_CHAR(hiredate, 'DD-MON-YYYY') as \"Date Hired\"" + 
                        "FROM scott.emp " + 
               (newCondition == null? "" : newCondition) + " order by ename"); 
        } else {
           PreparedStatement ps = 
             conn.prepareStatement("SELECT ename as \"Name\", " +
 	               "sal as \"Salary\"," +  
                       "TO_CHAR(hiredate, 'DD-MON-YYYY') as \"Date Hired\"" + 
                        "FROM scott.emp " + 
                        newCondition +" order by ename");   
           ps.setString(1, right);        
           rset = ps.executeQuery();
        }
      return  format(rset);
    } catch (SQLException e) { 
      return ("<P> SQL error: <PRE> " + e + " </PRE> </P>\n");
    }
    finally {
    try {
      if (rset!= null) rset.close();
      if (stmt!= null) stmt.close(); 
      if (conn!= null) conn.close();
      } catch (SQLException ignored) {}
    }
  } 

  
  public static String format(ResultSet rs) throws SQLException {
    StringBuffer sb = new StringBuffer();
    if (rs == null || !rs.next())
      sb.append("<P> No matching rows.<P>\n");
    else {
      sb.append("<TABLE BORDER>\n");
      ResultSetMetaData md = rs.getMetaData();
      int numCols = md.getColumnCount();
      for (int i=1; i<= numCols; i++) {
        sb.append("<TH><I>" + md.getColumnLabel(i) + "</I></TH>");
      }
      do {
        sb.append("<TR>\n");

        for (int i = 1; i <= numCols; i++) {
          sb.append("<TD>");
          Object obj = rs.getObject(i);
          if (obj != null)  sb.append(obj.toString());
          sb.append("</TD>");
        }
        sb.append("</TR>");
      } while (rs.next());
      sb.append("</TABLE>");
    }
    return sb.toString();
  }

  public boolean verifyCondition(String cond) {
      // for security we are only accepting conditions
      // in the form of value1 operator value2
      StringTokenizer st = new StringTokenizer(cond, " ");
      if (!((st.countTokens() == 3) || (st.countTokens() == 4))) return false;
      left = st.nextToken();      
      operator = st.nextToken();
      right = st.nextToken();
      if (st.countTokens() == 1 && operator.equalsIgnoreCase("is") 
                                && right.equalsIgnoreCase("not")) {
         operator = operator + " " + right;
         right = st.nextToken();
      }
      if (st.countTokens() == 1 && operator.equalsIgnoreCase("not") 
                                && right.equalsIgnoreCase("like")) {
         operator = operator + " " + right;
         right = st.nextToken();
      }
      if (right.equalsIgnoreCase("null") && (operator.equalsIgnoreCase("is") ||
           operator.equalsIgnoreCase("is not"))) { 
         newCondition = "WHERE " + left + " " + operator + " " + right;
         return true;
      } else 
        if (operator.equals("=") ||
          operator.equals("!=")  ||
          operator.equals(">")   ||
          operator.equals("<")   ||
          operator.equals("<=")  ||
          operator.equals(">=")  ||
          operator.equalsIgnoreCase("is") ||
          operator.equalsIgnoreCase("like") ||
          operator.equalsIgnoreCase("not like")) {
           newCondition = "WHERE " + left + " " + operator + 
             " ? ";
           return true;
      }
      return false;
    }
}

