// an applet that actually connects to redwood?!
//
// last modified: 12-5-00

import java.applet.Applet;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;

public class TryJDBCApplet1 extends Applet implements ActionListener
{
	TextField		askField, lNameField, jobField;
	Button			submit, clear;
	Connection		conn;
	PreparedStatement 	pStmt;

	public void init()
	{
		Label	askLabel, lNameLabel, jobLabel;
		String 	connString;
		
		// set up label and textfield to get
		// desired employee number
		askLabel = new Label("enter employee number:");
		add(askLabel);

		askField = new TextField(10);
		askField.addActionListener(this);
		add(askField);

		// set up labels and textfields
		// to show looked-up employee
		// information for that employee
		// number
		lNameLabel = new Label("last name:");
		jobLabel = new Label("job title:");
		lNameField = new TextField(20);
		jobField = new TextField(20);

		add(lNameLabel);
		add(lNameField);
		add(jobLabel);
		add(jobField);
	
		// now, add buttons that will submit the
		// query and clear the textfields when pushed
		submit = new Button("submit request");
		clear = new Button("clear all fields");
		submit.addActionListener(this);
		clear.addActionListener(this);
		add(submit);
		add(clear);

		// this COULD be multiple queries --- I might
		// as well used a PreparedStatement, for
		// possible efficiency benefits
		
		// load driver, set up connection
		try
		{
			DriverManager.registerDriver(
				new oracle.jdbc.driver.OracleDriver());
			System.out.println("after driver registration");

			// BEWARE!! this needs to be a SINGLE STRING!
			// (so, beware of pico's tendency, for example,
			// to "helpfully" break it up into multiple
			// lines!!)
			connString =
"jdbc:oracle:thin:@(description=(address_list=(address=(protocol=tcp)(host=www.humboldt.edu)(port=1610))(address=(protocol=tcp)(host=redwood.humboldt.edu)(port=1521)))(source_route=yes)(connect_data=(sid=student)))";

			conn = DriverManager.getConnection(connString, 
				"java", "java");
			System.out.println("after connecting");

			// set up prepared statement for query
			pStmt = conn.prepareStatement("select ename, job " 
					            + "from emp "
					            + "where empno = ?");
			System.out.println("have set up pStmt");
		}
		catch(Exception e)
		{
			System.out.println(e);
		}
	}

	// note!! This is another applet method that we have
	// not normally overridden, but that we WANT TO here!!
	// 
	// stop() is (in the words of the BlueJ Applet "default"
	// code comments) "Called by the browser or applet viewer 
	// to inform this Applet that it should stop its execution."
	//
	// "It is called when the Web page that contains this Applet 
	// has been replaced by another page, and also
	// just before the Applet is to be destroyed."
	//
	// so, guess why we NEED this here? To CLOSE the connection
	// to the database!!
	public void stop()
	{
		// close connection to database, close pStmt
		System.out.println("beginning stop()");
		try
		{
			if (conn != null)
			{
				conn.close();
			}
			if (pStmt != null)
			{
				pStmt.close();
			}
		}
		catch (Exception e)
		{
			System.out.println(e);
		}
		System.out.println("about to complete stop()");
	}

	// handle clear and submit button pushes, and entry of
	// a value into the empl_num textfield
	public void actionPerformed(ActionEvent ev)
	{
		ResultSet	rs;
	
		// yet another interesting predicate!
		if (ev.getSource() instanceof Button)
		{
			if (ev.getActionCommand().equals("clear all fields"))
			{
				// clear all textfields
				askField.setText("");
				lNameField.setText("");
				jobField.setText("");
			}
			else if (ev.getActionCommand().equals("submit request"))
			{
			    try
			    {
				// make sure that there is an employee
				// number in textfield!
				if (!(askField.getText().trim().equals("")))
				{
					// replace ? with employee num
					pStmt.setInt(1, 
						Integer.parseInt(
						askField.getText()));

					rs = pStmt.executeQuery();
					System.out.println("after query " +
						 "execution");
					if (rs.next())
					{
						lNameField.setText(
						    rs.getString("ename"));
						jobField.setText(
						    rs.getString("job"));
					}
					else
					{
						lNameField.setText("not found "
						    + "in database");
						jobField.setText("");
					}
				}
			    }
			    

			    catch(Exception e)
			    {
				System.out.println(e);
			    }
			}
		}	
						
		// otherwise, this is an employee number
		// entry --- clear other fields?
		else
		{
			lNameField.setText("");
			jobField.setText("");
		}
	}			
}