/********************************************************************
 * Made by Daniel.Schneider@tecfa.unige.ch 1999 TECFA. 
 *MODIFIED 2001 fradeff@bcu.unil.ch
 * Fixed MySQL non-sense (importing classes) on 4/2000
 * This is Freeware
 *
 * Java Tutorial inspired JDBC Example adapted to MySQL
 * MM MySQL Driver Version -> http://www.worldserver.com/mm.mysql/
 * At TECFA (UNIX) :  source /local/env/java117.csh            
 *                    setenv CLASSPATH /local/java/classes/mm-jdbc/mysql.jar:.
 * Other Sites: install the Driver
 *
 * You need Swing !! (JDK 1.2 apppletview or customized NS or Java plugin)
 * The simplest thing is to put swing.jar in your Netscape/...java/classes/
 *
 * WARNING: This example might NOT work as is (access restrictions sometimes)
 *          If it does work, make sure that the WWW server is tecfa2, NOT tecfa
 *
 * This needs some clean up ... but I don't have time :)
 * - not fully tested yet, layout is different from I think it ought to be :)
 * - Text fields are not cut/pastable ... needs some additional swing stuff
 * - Some destroy code is lacking
 * - ... and more, this is my second Swing program =)
 *
 *******************************************************************/


/**
 * This is a demonstration JDBC applet.
 * It will let you query and update a COFFEE table
 */

import java.applet.Applet;
import javax.swing.*;
import javax.swing.table.*;
import java.awt.*;
import java.awt.event.*;
import java.util.Vector;
import java.sql.*;

public class MySqlUpdateSwingApplet extends JApplet implements ActionListener {

    
    // This is a hack to avoid an ugly error message.
    public MySqlUpdateSwingApplet() {
        getRootPane().putClientProperty("defeatSystemEventQueueCheck",
                                        Boolean.TRUE);
    }

    // Our 2 Input Widgets
    JTextField queryTextField;
    JTextField updateTextField;
    // Feedback text + Scroll Pane around
    JTextArea textArea;
    // The queryPanel
    JPanel queryPanel;
    QueryTableModel tableModel;
    // params from the applet
    String url = null;
    String user = null;
    String password = null;
    // Variables that hold data
    String [][] resultData;
    String [] resColNames;
    // NewLines are different on different machines !
    String newline = System.getProperty("line.separator");
    // the DataBase Connection
    Connection con;
    // The TopLevelContainer
    Container container;

    public void init () {

	// The Container
	container = getContentPane();
	
	// The update panel 

	JPanel updatePanel = new JPanel ();
	updateTextField = new JTextField
	    ("UPDATE voc SET REM = 'Hard' WHERE NESSAIS > 5",45);

        updatePanel.add(updateTextField, BorderLayout.NORTH);

	// The query panel (label + select field)
	queryPanel = new JPanel ();
	queryTextField = new JTextField("SELECT * FROM voc", 45);
        queryPanel.add(queryTextField, BorderLayout.NORTH );

	// In the heart a JTable with and instance of our tableModel
	tableModel = new QueryTableModel ();
	JTable table=new JTable (tableModel);
	table.setPreferredScrollableViewportSize(new Dimension(500, 200));
	JScrollPane queryScrollPane = new JScrollPane(table);
	queryPanel.add(queryScrollPane, BorderLayout.CENTER);

	// The tabbed Pane and the whole container
	JTabbedPane tabbedPane = new JTabbedPane ();
	tabbedPane.addTab("Update", null, updatePanel, "Simple Update Window");
	tabbedPane.addTab("Query", null, queryPanel, "Simple Query Window");
	container.add(tabbedPane, BorderLayout.CENTER);
	
	// Text in the bottom panel
        textArea = new JTextArea("HELLO",4,45);
        textArea.setEditable(false);
	JScrollPane textScrollPane = new JScrollPane(textArea);
        JPanel bottomPanel = new JPanel();
        bottomPanel.add(textScrollPane,BorderLayout.CENTER);
	container.add(bottomPanel, BorderLayout.SOUTH);

	//Add a Listener for the queryTextField
        queryTextField.addActionListener(this);
        updateTextField.addActionListener(this);
    }


    /**
       opens a connection to the db
    */


    public void start () {

	try {
	    Class.forName("org.gjt.mm.mysql.Driver");
	} catch(Exception ex) {
	    displayError("Can't find Database driver class: " + ex);
	    return;
	}

	url = getParameter("URL");
	user = getParameter("USER");
	// password = getParameter("PASSWORD");
	password = null;

	try {
	    con = (Connection) DriverManager.getConnection(url, user, password);
	    feedbackMessage("Connected to " + url + newline);
	} catch(SQLException ex) {
	    displayError("SQLException: " + ex);
	}
    }


    /**
       closes the connection to the db (when the applet is killed)
    */

    public void destroy () {
	try {
	    con.close();
	} catch(SQLException ex) {
	    displayError("SQLException: " + ex);
	}
    }

    /**
       sends a query to the db
    */

    public class QueryTableModel extends AbstractTableModel {
	// initalize because swing wants to paint the table
	int noCols = 0;
	int nRows = 0;
	String [] resColNames;
	Vector cachedData = new Vector ();

	private void setQuery(String queryString) {

	    try {
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(queryString);
		
		ResultSetMetaData rsMeta = rs.getMetaData();
		noCols = rsMeta.getColumnCount();
		stmt.close();

		// Create the Array that holds labels
		resColNames = new String [noCols];
		for (int c=1; c<=noCols; c++) {
		    resColNames[c-1]= rsMeta.getColumnLabel(c);
		}

		// "Copy" the data line by line into a Vector
		cachedData = new Vector ();
		while (rs.next()) {
		    // create a string array for each line and fill it
		    // For sophisticated treatement it would be an obj array
		    String [] rawLineData = new String[noCols];
		    for (int c=0; c<noCols; c++) {
			String el = rs.getString(c+1);
			rawLineData[c] = el;
		    }
		    cachedData.addElement(rawLineData);
		}
		// calculate the Size
		nRows = cachedData.size();

		// FIRE
		fireTableChanged (null);
		
	    } catch(SQLException ex) {
		displayError("SQLException: " + ex);
	    }
	}

	//
	// Methods we must implement for the Abstract Table Model
	//
	public int getColumnCount () {
	    return noCols; }

	public String getColumnName (int i) { 
	    return resColNames[i]; }

	public int getRowCount () { 
	    return nRows; ; }

	public Object getValueAt (int row, int col) {
	    return ((String []) cachedData.elementAt(row))[col];
	}

    }

    /**
       sends an update request to the db.
       The executeUpdate method returns the number of updated columns
    */

    private void update(String updateString) {

	try {
	    Statement stmt = (Statement) con.createStatement();
	    int updateRowCount = stmt.executeUpdate(updateString);
	    stmt.close();
	    if (updateRowCount > 0)
		feedbackMessage("Update successful: " 
				+ updateRowCount + " Rows updated.");
	    else
 		feedbackMessage
		    ("Update feedback: " 
		     + updateRowCount + " Rows updated." + newline + 
		     "Either NO update was needed, or you misspelled a value"
		     + newline + "You sent: \"" + updateString + "\""
		     );
	} catch(SQLException ex) {
	    displayError("SQLException: " + ex);
	}
    }


    /**
     * This method is used to print error messages
     */
    
    private void displayError(String mess) {
	textArea.setText("Mhh there was a problem, likely an illegal SQL expression:" + newline);
	textArea.append(mess);
	// updatePanel.repaint();
    }

    /**
     * This method is used for other general feedback
     */

    private void feedbackMessage(String mess) {
	textArea.setText("Hello:" + newline);
	textArea.append(mess);
	// updatePanel.repaint();
    }


    public void actionPerformed(ActionEvent evt) {
        String text;
	Object TheWidget = evt.getSource();
	// user hit return in query Text Field
	if (TheWidget == queryTextField) {
	    text = queryTextField.getText();
	    //activate the setQuery method of the tableModel
	    tableModel.setQuery (text);
	    queryTextField.selectAll();
	}
	// must have hit return in the other field
	else if (TheWidget == updateTextField) {
	    text = updateTextField.getText();
	    update (text);
	    updateTextField.selectAll();
	}
	else displayError("This should not happen: " + TheWidget);
    }

}