// be sure your CLASSPATH includes ojdbc14.jar!

import java.awt.*;
import java.awt.event.*;
//import java.applet.*;
import javax.swing.*;
import java.sql.*;
import java.io.*;
import javax.swing.event.*;
import javax.swing.border.*;

public class Standalone extends JFrame {

	private boolean isStandalone = true;

	Connection conn;

	/*
	JTextField jTextField1 = new JTextField();
	JTextField jTextField2 = new JTextField();
	JTextField jTextField3 = new JTextField();
	JTextField jTextField4 = new JTextField();
	JTextField jTextField5 = new JTextField();
	/*

	JLabel jLabel1 = new JLabel();
	JLabel jLabel2 = new JLabel();
	JLabel jLabel3 = new JLabel();
	JLabel jLabel4 = new JLabel();
	JLabel jLabel5 = new JLabel();
	JLabel jLabel6 = new JLabel();
	JLabel jLabel7 = new JLabel();
	/* */

	JButton jButton1 = new JButton();
	JButton jButton2 = new JButton();
	JButton jButton3 = new JButton();
	JButton jButton4 = new JButton();
	JButton jButton5 = new JButton();
	JButton jButton6 = new JButton();

	LabeledText studid = new LabeledText("Student ID");
	LabeledText lname = new LabeledText("Last name");
	LabeledText fname = new LabeledText("First name");
	LabeledText startterm = new LabeledText("start term");
	LabeledText advisor = new LabeledText("Advisor");
	LabeledText major  = new LabeledText("Major");

	JTextArea theArea = new JTextArea();

	JPasswordField jPasswordField1 = new JPasswordField();

	/**
	 *  Labeled text has a label and a textbox, with getText/setText methods
	 */
	private class LabeledText {
		private JPanel joint;
		private JTextField theText;
		private JLabel theLabel;

		public JPanel panel() {return joint;}

		public LabeledText(String labelstr) {
			joint = new JPanel();
			theText = new JTextField("");
			theLabel = new JLabel();
			theLabel.setText(labelstr);
			joint.setLayout(new BorderLayout());
			joint.add(theLabel, BorderLayout.WEST);
			joint.add(theText, BorderLayout.CENTER);
		}

		public String getText() {
			return theText.getText();
		}

		public void setText(String t) {
			theText.setText(t);
		}

		public JTextField getTF() {
			return theText;
		}
	}


	//=======================================================

	//public String getParameter(String key, String def) {
	//	return isStandalone ? System.getProperty(key, def)
	//	   : (getParameter(key) != null ? getParameter(key) : def);
	//}

	public Standalone () {}

	public void init() {
		try {jbInit();}
		catch(Exception e) {e.printStackTrace();}
	}

	private void jbInit() throws Exception {

		JPanel contentPane = (JPanel)getContentPane();
		contentPane.setBorder(new EmptyBorder(6,10,10,10));

		contentPane.setLayout(new BoxLayout(contentPane, BoxLayout.Y_AXIS));
        setDefaultCloseOperation(EXIT_ON_CLOSE);  // pld: commented out



		JPanel buttonbar = new JPanel();
		buttonbar.setLayout(new GridLayout(0, 6));

		jButton1.setBackground(SystemColor.activeCaption);
		//jButton1.setBounds(new Rectangle(238, 39, 136, 21));
		jButton1.setFont(new java.awt.Font("Dialog", 1, 11));
		jButton1.setText("Get Rows");
		jButton1.addActionListener(
			new ActionListener() {
				public void actionPerformed(ActionEvent e) {
					jButton1_action(e);
				}
			}
		);

		jButton2.setBackground(SystemColor.activeCaption);
		//jButton2.setBounds(new Rectangle(238, 89, 136, 21));
		jButton2.setFont(new java.awt.Font("Dialog", 1, 11));
		jButton2.setText("Clear Text only");
		jButton2.addActionListener(
			new ActionListener() {
				public void actionPerformed(ActionEvent e) {
					jButton2_action(e);
				}
			}
		);

		jButton3.setBackground(SystemColor.activeCaption);
		//jButton3.setBounds(new Rectangle(28, 262, 132, 29));
		jButton3.setFont(new java.awt.Font("Dialog", 1, 11));
		jButton3.setText("Search ID");
		jButton3.addActionListener(
			new ActionListener() {
				public void actionPerformed(ActionEvent e) {
					jButton3_action(e);
				}
			}
		);

		jButton4.setBackground(SystemColor.activeCaption);
		//jButton4.setBounds(new Rectangle(238, 39, 136, 21));
		jButton4.setFont(new java.awt.Font("Dialog", 1, 11));
		jButton4.setText("Search LName");
		jButton4.addActionListener(
			new ActionListener() {
				public void actionPerformed(ActionEvent e) {
					jButton4_action(e);
				}
			}
		);

		jButton5.setBackground(SystemColor.activeCaption);
		//jButton5.setBounds(new Rectangle(238, 64, 136, 21));
		jButton5.setFont(new java.awt.Font("Dialog", 1, 11));
		jButton5.setText("new record");
		jButton5.addActionListener(
			new ActionListener() {
				public void actionPerformed(ActionEvent e) {
					jButton5_action(e);
				}
			}
		);

		jButton6.setBackground(SystemColor.activeCaption);
		//jButton6.setBounds(new Rectangle(238, 64, 136, 21));
		jButton6.setFont(new java.awt.Font("Dialog", 1, 11));
		jButton6.setText("srch by advisor");
		jButton6.addActionListener(
			new ActionListener() {
				public void actionPerformed(ActionEvent e) {
					jButton6_action(e);
				}
			}
		);


		buttonbar.add(jButton1);
		buttonbar.add(jButton2);
		buttonbar.add(jButton3);
		buttonbar.add(jButton4);
		buttonbar.add(jButton5);
		buttonbar.add(jButton6);


		add(buttonbar);


		add(studid.panel());
		add(lname.panel());
		add(fname.panel());

		/* */
		add(startterm.panel());
		add(advisor.panel());
		add(major.panel());

		/*
		JPanel triad = new JPanel();
		triad.setLayout(new GridLayout(0,3));
		triad.add(startterm.panel());
		triad.add(advisor.panel());
		triad.add(major.panel());

		add(triad);
		/* */
		//==================================================

		theArea.setRequestFocusEnabled(false);	// pld: ??
		theArea.setColumns(50);
		theArea.setRows(5);
		//theArea.setTabSize(8);
		//theArea.setBounds(new Rectangle(28, 117, 347, 134));

		JScrollPane thePane = new JScrollPane(theArea);

		add(thePane);

		conn = dbConnect("indo", "indo");
		if (conn == null) System.err.println("we are hosed");

		pack();
		Dimension d = Toolkit.getDefaultToolkit().getScreenSize();
		setLocation(d.width/2 - getWidth()/2, d.height/2 - getHeight()/2);
		setVisible(true);
	}

	//public String getAppletInfo() {return "Applet information";}

	//public String[][] getParameterInfo() {return null;}

	/*
	JComponent combine(JLabel jl, JTextField jt) {
		JPanel joint = new JPanel();
		joint.setLayout(new BorderLayout());
		joint.add(jl, BorderLayout.WEST);
		joint.add(jt, BorderLayout.CENTER);

		return joint;
	}
	/* */
	/**
	 *  Button 1 gets a dump of all the records, into theArea
	 */

	void jButton1_action(ActionEvent e)  {
		String query ="select studentid, last, first, startterm, facultyid, majorid from student";
		try {
			Statement stmt = conn.createStatement();
			ResultSet rset = stmt.executeQuery(query);

			String response = "studid\tlast\tfirst\tterm\tadvisor\tmajor\n";
			while (rset.next()) {
				response += (
					rset.getString(1) + "\t"
					+ rset.getString(2) + "\t"
					+ rset.getString(3) + "\t"
					+ rset.getString(4) + "\t"
					+ rset.getString(5) + "\t"
					+ rset.getString(6) + "\n"
				);
			}
			theArea.setText(response);
			stmt.close();
		} catch (SQLException se) {
			System.err.println("query failed: " + query);
		}
	}

	/**
	 *  dbConnect takes care of making the connection to the oracle server
	 */
	public Connection dbConnect(String user, String pass) {

		Connection conn;

		//try {
		//	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		//} catch(ClassNotFoundException ex) {
		//	System.err.println("cannot load driver");
		//	//System.exit(11);
		//}

		try {
			DriverManager.registerDriver(
				new oracle.jdbc.driver.OracleDriver()
			);
		} catch (SQLException se) {
			System.err.println("cannot load driver");
			return null;
		}

		String constring
		   = "jdbc:oracle:thin:" + user + "/" + pass + "@localhost:1521:xe";

		try {
			conn = DriverManager.getConnection(constring);
			conn.setAutoCommit(false);		// important (maybe)
		} catch (SQLException se) {
			System.err.println("could not connect: "+ constring);
			studid.setText("cound not connect");
			return null;
		}

		System.err.println("done with dbConnect");

		return conn;
	}

	/**
	 * button 2 causes all fields to be cleared
	 */
	void jButton2_action(ActionEvent e)  {
		clearfields(true);
	}

	void clearfields(boolean alsoArea) {
		studid.setText("");
		lname.setText("");
		fname.setText("");
		startterm.setText("");
		advisor.setText("");
		major.setText("");
		if (alsoArea) theArea.setText("");
	}

	/**
	 * search by contents of studId field
	 */
	void jButton3_action(ActionEvent e)  {
		String sid = studid.getText();
		System.out.println("student id is: " + sid);

		String query = "SELECT studentid, last, first, startterm, facultyid, "
			+ "majorid from STUDENT where studentid = '" + sid + "'";

		Statement stmt = null;

		try {
			stmt = conn.createStatement();
			ResultSet rset = stmt.executeQuery(query);

		if (!(rset.next())) {
			clearfields(false);
			studid.setText(sid);
			lname.setText("NOT FOUND");
			return;
		}
		sid = rset.getString(1);
		studid.setText(sid);		// in case of trim/leading-zeros change

		lname.setText(rset.getString(2));
		fname.setText(rset.getString(3));
		startterm.setText(rset.getString(4));
		advisor.setText(rset.getString(5));
		major.setText(rset.getString(6));

		stmt.close();

		} catch (SQLException se) {
			System.err.println("error in query: " + query);
			clearfields(false);
		}

	}

	/**
	 *  button4: searches by last name
	 */

	void jButton4_action(ActionEvent e)  {
		String ln = lname.getText().toLowerCase();
		System.out.println("last name is: " + ln);
		String query = "SELECT studentid, last, first, startterm, facultyid, majorid "
			+ "from STUDENT where lower(last) = '" + ln + "'";

		Statement stmt = null;

		try {
			stmt = conn.createStatement();
			ResultSet rset = stmt.executeQuery(query);

		if (!(rset.next())) {
			clearfields(false);
			studid.setText("NOT FOUND");
			lname.setText(ln);
			return;
		}

		studid.setText(rset.getString(1));
		lname.setText(rset.getString(2));
		fname.setText(rset.getString(3));
		startterm.setText(rset.getString(4));
		advisor.setText(rset.getString(5));
		major.setText(rset.getString(6));

		stmt.close();

		} catch (SQLException se) {
			System.err.println("error in query: " + query);
			clearfields(false);
		}
	}

	/**
	 * button 5: create new record
	 */
	void jButton5_action(ActionEvent e)  {
		String insertstr = "insert into student values " +
			 "(?,?,?,null, null, null, null, ?, null,  ?,  ?, null)";
			//id,l,f,st,   city, state, zip, term,bday,adv,maj, phone)

		String studidS = studid.getText().trim();
		String lnameS  = lname.getText().trim();
		String fnameS  = fname.getText().trim();
		String stermS  = startterm.getText().trim();
		String advisorS= advisor.getText().trim();
		String majorS  = major.getText().trim();

		// sanity checks

		if(studidS.length() == 0 || lnameS.length() == 0 || fnameS.length() == 0
			|| stermS.length() == 0 || advisorS.length() == 0 || majorS.length() == 0) {
			   System.err.println("field left blank");
			   return;
		}

		// end of sanity

		try{
			PreparedStatement pstmt = conn.prepareStatement(insertstr);

			pstmt.setString(1, studidS);
			pstmt.setString(2, lnameS);
			pstmt.setString(3, fnameS);
			pstmt.setString(4, stermS);
			/*
			pstmt.setString(5, advisorS);
			pstmt.setString(6, majorS);
			/* */
			pstmt.setInt(5, intval(advisorS));
			pstmt.setInt(6, intval(majorS));
			/* */

			pstmt.executeUpdate();
			conn.commit();
			pstmt.close();
		} catch (SQLException se) {
			System.err.println("update failed");
//			conn.rollback();
		}
	}


	/**
	 *  button6: searches by faculty advisor
	 */

	void jButton6_action(ActionEvent e)  {
		String advId = advisor.getText();
		System.out.println("Advisor ID is: " + advId);
		String bquery = "SELECT studentid, last, first, startterm, facultyid, majorid "
			+ "from STUDENT where facultyid = ";
		String query1 = bquery + advId;
		String query2 = bquery + "?";

		Statement stmt = null;
		PreparedStatement pstmt = null;

		try {
			stmt  = conn.createStatement();
			pstmt = conn.prepareStatement(query2);

			ResultSet rset;
			//rset = stmt.executeQuery(query1);
			System.out.println(query1);
			System.out.println(query2);
			pstmt.setInt(1, intval(advId));
			rset = pstmt.executeQuery();

/*
			if (!(rset.next())) {
				clearfields(true);
				studid.setText("NOT FOUND");
				advisor.setText(advId);
				return;
			}
/* */

			String response = "studid\tlast\tfirst\tterm\tadvisor\tmajor\n";
			while (rset.next()) {
				response += (
					rset.getString(1) + "\t"
					+ rset.getString(2) + "\t"
					+ rset.getString(3) + "\t"
					+ rset.getString(4) + "\t"
					+ rset.getString(5) + "\t"
					+ rset.getString(6) + "\n"
				);
				System.out.println(rset.getString(2));
			}
			theArea.setText(response);

			if (stmt != null)  stmt.close();
			if (pstmt != null) pstmt.close();

		} catch (SQLException se) {
			System.err.println("error in query: " + query1);
			clearfields(false);
		}
	}

	// there's got to be a better way to do this.
	public int intval(String s) {
		Integer i = new Integer(s);
		return i.intValue();
	}

	public static void main(String args[]) {
		Standalone s = new Standalone();
		s.init();
	}

}