package j3.oracle;
import java.sql.*;
import javax.swing.*;
import java.util.*;

/**
 * Please check the JDBC connection constants defined below.
 *
 * This class uses JDatabase Wizard generated files to  demonstrate
 * how to use such classes.
 *
 * Note: Our distribution of Oracle 8i drivers did not
 *  support update operations on the result sets.
 */

public class JDBCHandler
{
  StringBuffer myReport = new StringBuffer();
  Connection myConnection;

  /**
   * Generated Person Table access class instance
   */
  PersonSQL mySQL;
  /**
   * Generated Parm stored procedure class instance
   */
  SpParmSQL myParmSQL;

  /**
   * Constant used to connect to the Oracle database
   */
	private static final String	CLASS		= "oracle.jdbc.driver.OracleDriver";
  /**
   * Please insert your database URL below:
   */
  //private static final String	URL		= "jdbc:oracle:thin:@<Host Name>:1521:<ORCL>";
  private static final String	URL		= "jdbc:oracle:thin:@Daedalus:1521:Daedalus";
  /**
   * database user
   */
	private static final String	USER		= "J3_DEMO";
  /**
   * password
   */
	private static final String	PASSWORD	= "DEMO";

  /**
   * Constructor create JDBC access classes
   */
  public JDBCHandler()
  {
    mySQL = new PersonSQL();
    myParmSQL = new SpParmSQL();
  }

  /**
   * Perform JDBC operations using the generated classes.
   */
  public String go()
  {
    try
    {
      myReport.append("<H2>JDBC 2 Test suite</H2>\n");

      Class.forName(CLASS).newInstance();
      myReport.append("<P>Connecting... ");
      myConnection = DriverManager.getConnection(URL, USER, PASSWORD);
      myReport.append("connected.</P>");

      // Call non resultset methods on PersonSQL
      deleteAll();
      selectAll();
      insert();
      selectAll();
      updateOne();
      deleteOne();
      selectAll();

      // Call stored procedure SpParm2SQL
      spParm();

      // close statements, just to flush out anything left behind.
      closeStatements();

      myReport.append("<HR>\n");
      myReport.append("<H2>JDBC2 Specific Tests</H2>");

      // ---------------------------------------------------------------------
      // PersonSQL database table resultset method tests
      // ---------------------------------------------------------------------

      // PersonSQL resultset tests
      selectAll();
      displayResultSetInfo();
      //insertRow();
      absolute();
      lastTests();
      firstTests();
      //deleteRow();
      selectAll();
      cursorTests();
      selectAll();

      // close everything
      close();

    }
    catch (Exception ex)
    {
      wrapException(ex);
    }
    finally
    {
      return myReport.toString();
    }
  }

  /**
   * Test cursor movement methods on PersonSQL
   * @throws Exception
   */
  private void cursorTests() throws Exception
  {
    myReport.append("<H2>Cursor Tests</H2>");

    // move to 1st record
    myReport.append("<P>absolute(1)");

    PersonData data = mySQL.absolute(1);

    displayPersonData(data);
    myReport.append("done</P>\n");

    // update row
    /*********************************************************
    * Our distribution of Oracle 8i drivers did not
    *  Support update operations on the result sets.
    **********************************************************
    myReport.append("<P>updateRow()");

    data.setFirstName("cursor test");
    data.setLastName("cursor test");
    mySQL.updateRow(data);

    myReport.append("done</P>\n");
    */

    // refresh row
    /*
    myReport.append("<P>refreshRow()");
    data = mySQL.refreshRow();


    displayPersonData(data);
    myReport.append("done</P>\n");
        */

    // relative
    myReport.append("<P>relative(2)");
    data = mySQL.relative(2);

    displayPersonData(data);
    myReport.append("done</P>\n");

    // previous
    myReport.append("<P>previous()");
    data = mySQL.previous();

    displayPersonData(data);
    myReport.append("done</P>\n");
  }

  /**
   * Display all misc. info resultset provides.
   * @throws Exception
   */
  private void displayResultSetInfo() throws Exception
  {
    myReport.append("\n<H2>Display ResultSet Info</H2>");
    // get resutlset type
    myReport.append("<P>getType()...");
    int type = mySQL.getType();
    switch (type)
    {
      case java.sql.ResultSet.TYPE_FORWARD_ONLY:
        myReport.append(" java.sql.ResultSet.TYPE_FORWARD_ONLY ");
        break;
      case java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE:
        myReport.append(" java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE ");
        break;
      case java.sql.ResultSet.TYPE_SCROLL_SENSITIVE:
        myReport.append(" java.sql.ResultSet.TYPE_SCROLL_SENSITIVE ");
        break;
      default:
        myReport.append(" unknown resultset type " + type + ' ');
        break;
    }
    myReport.append("done</P>");

    // concurrency
    myReport.append("<P>getConcurrency()...");
    int conc = mySQL.getConcurrency();
    switch (conc)
    {
      case java.sql.ResultSet.CONCUR_READ_ONLY:
        myReport.append(" java.sql.ResultSet.CONCUR_READ_ONLY ");
        break;
      case java.sql.ResultSet.CONCUR_UPDATABLE:
        myReport.append(" java.sql.ResultSet.CONCUR_UPDATABLE ");
        break;
      default:
        myReport.append(" unknown concurrency constant " + conc + ' ');
        break;
    }
    myReport.append("done</p>");

    // fetch direction
    myReport.append("<P>getFetchDirection()...");
    int dir = mySQL.getFetchDirection();
    switch (dir)
    {
      case java.sql.ResultSet.FETCH_FORWARD:
        myReport.append(" java.sql.ResultSet.FETCH_FORWARD ");
        break;
      case java.sql.ResultSet.FETCH_REVERSE:
        myReport.append(" java.sql.ResultSet.FETCH_REVERSE ");
        break;
      case java.sql.ResultSet.FETCH_UNKNOWN:
        myReport.append(" java.sql.ResultSet.FETCH_UNKNOWN ");
        break;
      default:
        myReport.append(" unknown fetch direction constant " + dir + ' ');
        break;
    }
    myReport.append("done</p>");

    // fetch size
    myReport.append("<P>getFetchSize()...");
    int size = mySQL.getFetchSize();
    myReport.append("Size: " + size + " done</P>\n");

    // get current row number
    myReport.append("<P>getRow()...");
    int row = mySQL.getRow();
    myReport.append(row);
    myReport.append(" done</P>\n");
  }

  /**
   * Delete the current ResultSet row
   * @throws Exception
   */

  private void deleteRow() throws Exception
  {
    myReport.append("<P>deleteRow()...");
    mySQL.deleteRow();
    myReport.append("done</P>");

    myReport.append("<p>rowDeleted()...");
    boolean b = mySQL.rowDeleted();
    myReport.append(b);
    myReport.append(" done</P>");
  }

  private void firstTests() throws Exception
  {
    // move to after first
    myReport.append("<P>beforeFirst()...");
    mySQL.beforeFirst();
    myReport.append("done</P>");

    // does the driver support isAfterLast() ?
    myReport.append("<P>isBeforeFirst()...");
    boolean b = mySQL.isBeforeFirst();
    myReport.append(b);
    myReport.append(" done</P>");

    // move to last & display
    myReport.append("<P>first()...");
    PersonData data = mySQL.last();
    myReport.append("done, first row is:");
    displayPersonData(data);

    // does the driver support isLast() ?
    myReport.append("<P>isFirst()...");
    b = mySQL.isFirst();
    myReport.append(b);
    myReport.append(" done</P>");

  }
  private void lastTests() throws Exception
  {
    // move to after last
    myReport.append("<P>afterLast()...");
    mySQL.afterLast();
    myReport.append("done</P>");

    // does the driver support isAfterLast() ?
    myReport.append("<P>isAfterLast()...");
    boolean b = mySQL.isAfterLast();
    myReport.append(b);
    myReport.append(" done</P>");

    // move to last & display
    myReport.append("<P>last()...");
    PersonData data = mySQL.last();
    myReport.append("done, last row is:");
    displayPersonData(data);

    // does the driver support isLast() ?
    myReport.append("<P>isLast()...");
    b = mySQL.isLast();
    myReport.append(b);
    myReport.append(" done</P>");

  }

  /**
   * Move to 3rd record in recordset, and display its contents.
   * @throws Exception
   */
  private void absolute() throws Exception
  {
    myReport.append("<P>Absolute(3)...");

    // absolute:
    PersonData data = mySQL.absolute(3);

    displayPersonData(data);

    myReport.append("done</P>");
  }

  /**
   * Move to insert position, then insert a record using the resultset
   * @throws Exception
   */

  private void insertRow() throws Exception
  {
    myReport.append("<P>Insert Row...");

    // prepare data to insert
    PersonData data = new PersonData();
    data.setEmail("InsertRow Test");
    data.setFirstName("IR test");
    data.setLastName("IR Test");
    data.setTitle("A");

    // Move to insert position and insert
    mySQL.moveToInsertRow();
    mySQL.insertRow(data);

    myReport.append("done</P>");

    // Does driver support rowInserted() ?
    myReport.append("<P>rowInserted()...");
    boolean b = mySQL.rowInserted();
    myReport.append(b);
    myReport.append(" done</P>");
  }

  /**
   * Call the stored procedure which uses in/out parameters
   * @throws Exception
   */
  private void spParm() throws Exception
  {
    myReport.append("<P>spParm() Email0...");

    // set stored procedure parameter
    myParmSQL.setEmailIn("Email0");

    // call stored procedure
    myParmSQL.callSpParmSQL(myConnection);

    // get output parameters and return values
    myParmSQL.postCall();

    // display output parameter values
    myReport.append(myParmSQL.getEmailOut() + ' ' + myParmSQL.getFirstName() +
                    ' ' + myParmSQL.getLastName());

    myReport.append(" done</P>");
  }

  /**
   * Delete one record
   * @throws Exception
   */
  private void deleteOne() throws Exception
  {
    myReport.append("<P>Delete() record 3...");

    // call delete method
    mySQL.deleteByEmail(myConnection, "Email2");

    myReport.append("done</P>");
  }

  /**
   * Update one record
   * @throws Exception
   */
  private void updateOne() throws Exception
  {
    myReport.append("<P>Update() record 2...");

    // prepare data to update
    PersonData data = new PersonData();
    data.setFirstName("Updated First Name");
    data.setLastName("Updated Last Name");

    // update data
    mySQL.updateByEmail(myConnection, data, "Email1");

    myReport.append("done</P>");
  }

  /**
   * Insert six records
   * @throws Exception
   */
  private void insert() throws Exception
  {
    myReport.append("<P>Insert() X 6...");
    PersonData data = new PersonData();
    for (int count = 0 ; count < 6 ; count ++)
    {
      // prepare data to insert
      data.setEmail("Email" + count);
      data.setFirstName("FirstName" + count);
      data.setLastName("LastName"+count);

      // insert record
      mySQL.insert(myConnection, data);
    }
    myReport.append("done</P>");
  }

  /**
   * delete all records
   * @throws Exception
   */
  private void deleteAll() throws Exception
  {
    myReport.append("<P>DeleteAll()...");

    // call delete method
    mySQL.deleteAll(myConnection);

    myReport.append("done</P>");
  }

  /**
   * select all records and display the resultset contents
   * @throws Exception
   */
  private void selectAll() throws Exception
  {
    myReport.append("<P>Select all...");

    // call select
    mySQL.selectAll(myConnection);

    myReport.append("done</P>");

    // display resultset contents
    PersonData data = mySQL.next();
    if (data != null)
    {
      myReport.append("<table border = 2>");
      myReport.append("<TH>Last Name</TH<TH>First Name</TH><TH>Email</TH>");
      while (data != null)
      {
        displayPersonDataRow(data);

        data = mySQL.next();
      }
      myReport.append("</table>");
    }
  }

  /**
   * Close all open JDatabase Wizard classes (statements & resultsets)
   */
  private void closeStatements()
  {
    myReport.append("<P><B>Closing statements...</B>");
    mySQL.close();
    //myParmSQL.close();
    myReport.append("closed</P>");
  }

  /**
   * Close resultsets, statements and the jdbc connection
   */
  private void close()
  {
    myReport.append("<P><B>Closing down...</B>");
    closeStatements();
    try
    {
      if (myConnection != null)
      {
        myConnection.close();
      }
    }
    catch (Exception ex)
    {
    }
    myReport.append("done</P>");
  }

  // ---------------------------------------------------------------------------
  // Utility methods
  // ---------------------------------------------------------------------------

  /**
   * Captures the error message and call stack information from an exception and
   * displays it.
   * @param e the exception to capture
   */
  private void wrapException(Exception e)
  {
      if (e != null)
      {
        myReport.append("\n<PRE><B>Error running test</B>\n" + e.getMessage() + '\n');
        java.io.CharArrayWriter cw = new java.io.CharArrayWriter();
        java.io.PrintWriter pw = new java.io.PrintWriter(cw,true);
        e.printStackTrace(pw);
        myReport.append(cw.toString() + "</PRE>\n");
        e.printStackTrace();
      }
  }

  /**
   * Display ONE PersonData record as a HTML table row.
   * @param data data to display, may not be null.
   */
  private void displayPersonDataRow(PersonData data)
  {
      myReport.append("<tr>");
      myReport.append("<td>"+data.getLastName()+"</td>");
      myReport.append("<td>"+data.getFirstName()+"</td>");
      myReport.append("<td>"+data.getEmail()+"</td>");
      myReport.append("</tr>");
  }

  /**
   * Display passed PersonData in a HTML table
   * @param data
   */
  private void displayPersonData(PersonData data)
  {
    if (data != null)
    {
      myReport.append("\n<table border = 2>");
      myReport.append("<TH>Last Name</TH<TH>First Name</TH><TH>Email</TH>");

      displayPersonDataRow(data);

      myReport.append("</table>\n");
    }
  }
}