/*	ExecuteSQL.java
*	@author: Charles Bell
*	@version: Feb 2, 2001
*/
import java.awt.*;
import java.awt.event.*;
import java.awt.image.*;
import java.sql.*;
import javax.swing.*;

/**	Provides the user with an easy to use graphical user interface for 
*	connecting to a relational database, entering sql commands, and displaying
*	the results.  A red and green light show database connection status. Red 
*	for disconnected, and green for connected. 
*/
public class ExecuteSQL implements ActionListener{

	String databasename = "DatabaseName";
	String username = "userName";
	String passwordstring = "password";
	boolean debug = false;//set to true to see debug messages.
	Connection connection;
	JFrame frame;
	JTextArea commandarea;
	HtmlFrame resultsdisplay;
	BufferedImage connectedimage;
	BufferedImage disconnectedimage;
	ImageIcon connected;
	ImageIcon disconnected;
	JLabel connectionstatuslabel;
	JButton connectionbutton;	

	/**	Instantiates an instance of this class, initiates the GUI, 
	*	and calls to connect to the database.
	*/
	public static void main(String[] args){

		ExecuteSQL execsql = new ExecuteSQL();
		if (args.length == 0) {
			execsql.databasename = "";
			execsql.username = "";
			execsql.passwordstring = "";
		}else if (args.length == 1) {
			execsql.databasename = args[0];
			execsql.username = "";
			execsql.passwordstring = "";
		}else if (args.length == 2) {
			execsql.databasename = args[0];
			execsql.username = args[1];
			execsql.passwordstring = "";	
		}else if (args.length == 3) {
			execsql.databasename = args[0];
			execsql.username = args[1];
			execsql.passwordstring = args[2];
		}else{
			execsql.println("Useage: java ExecSql");
			execsql.println("                     or");
			execsql.println("Useage: java ExecSql databasename");
			execsql.println("                     or");
			execsql.println("Useage: java ExecSql databasename username");
			execsql.println("                     or");
			execsql.println("Useage: java ExecSql databasename username passwordstring");
		}		
		execsql.init();
		execsql.connect();
	}

	/**	For setting the Database Name to be used.
	*/
	public void setDatabaseName(String s){
		this.databasename= s;
	}

	/**	For setting the username to be used.
	*/
	public void setUserName(String s){
		this.username = s;
	}

	/**	For setting the password to be used.
	*/
	public void setPassword(String s){
		this.passwordstring = s;
	}
	
	/**	Sets up the gui for actions. The user is provided with a text 
	*	area for entering sql commands. An HtmlFrame view is set up 
	*	for displaying the results of queries with html formatted text.
	*/
	public void init(){
		frame = new JFrame("ExecuteSQL");
		Dimension dimension  = Toolkit.getDefaultToolkit().getScreenSize();
		frame.setSize(dimension.width,dimension.height/2);
		frame.addWindowListener(new FrameListener());
		JPanel toppanel = new JPanel();
		JPanel middlepanel = new JPanel();
		JPanel bottompanel = new JPanel();
		JButton exitbutton = new JButton("Exit");
		connectionbutton = new JButton("Disconnect");
		JButton clearbutton = new JButton("Clear");
		JButton execbutton = new JButton("Execute");
		exitbutton.addActionListener(this);
		connectionbutton.addActionListener(this);
		clearbutton.addActionListener(this);
		execbutton.addActionListener(this);

		int imageType = BufferedImage.TYPE_INT_RGB;
		// Create an image buffer in which to paint on.
		connectedimage = new BufferedImage(20, 20,imageType);
		// Create graphics object associated with the image
		Graphics2D graphics2da = connectedimage.createGraphics();
		// Paint image on the graphics object
		graphics2da.setColor(Color.green);
		graphics2da.fillArc(5,5,10,10,0,360);
		graphics2da.dispose();
		disconnectedimage = new BufferedImage(20, 20,imageType);
		// Create graphics object associated with the image
		Graphics2D graphics2db = disconnectedimage.createGraphics();
		// Paint image on the graphics object
		graphics2db.setColor(Color.red);
		graphics2db.fillArc(5,5,10,10,0,360);
		graphics2db.dispose();
		connected = new ImageIcon(connectedimage);
		disconnected = new ImageIcon(disconnectedimage);

		connectionstatuslabel = new JLabel(disconnected);
		toppanel.add(connectionstatuslabel);
		toppanel.add(exitbutton);
		toppanel.add(connectionbutton);
		toppanel.add(clearbutton);
		toppanel.add(execbutton);

		commandarea = new JTextArea(12,65);
		middlepanel.add(commandarea);
		resultsdisplay = new HtmlFrame();

		frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		frame.getContentPane().add(toppanel,"North");
		frame.getContentPane().add(middlepanel,"Center");
		frame.show();
	}

	/**	Implements the ActionListener interface by providing response for
	*	the gui buttons.
	*/
	public void actionPerformed(ActionEvent actionevent){

		String commandstring = actionevent.getActionCommand();
		if (debug) println(commandstring);
		if (commandstring.compareTo("Exit") ==0) {
			closeDatabaseConnection();
			System.exit(0);
		}

		if (commandstring.compareTo("Disconnect") ==0) {
			closeDatabaseConnection();
			//swap connection button actions and text string
			connectionbutton.setActionCommand("Connect");
			connectionbutton.setText("Connect");
		}
		
		if (commandstring.compareTo("Connect") ==0) {
			connect();
			//swap connection button actions and text string
			connectionbutton.setActionCommand("Disconnect");
			connectionbutton.setText("Disconnect");
		}

		// The heart of the action is here.
		if (commandstring.compareTo("Execute") ==0) {	
			//make sure there is a connection before proceeding 
			//make sure there is a command string before proceeding		
			if ((connection != null)&&(commandarea.getText().length() > 0)){
				try{
					ResultSet rs = execsql(commandarea.getText().trim(),connection);
					if (rs != null){
						displayResults(rs);
						rs.close();
					}
				}catch (SQLException sqlexception){
					displaySqlExceptionInformation(sqlexception);
				}
			}
		}

		// clear the display screens
		if (commandstring.compareTo("Clear") ==0) {
			commandarea.setText("");
			resultsdisplay.show("");
		}

	}

	/**	Saves the programmer from typing out the whole line over and over again.
	*/
	public void println(String s){
		System.out.println(s);
	}

	/**	Processes the ResultSet object passed, and creates html string text
	*	for display by the HTMLFrame viewer.
	*/
	public void displayResults(ResultSet rs){
		if (rs == null) return;
		String displaystring = "";
		try{
			// use ResultSetMetaData to check column headings
            	ResultSetMetaData rsmd = rs.getMetaData ();
            	// Get the number of columns from the ResultSetMetaData 
            	int columns = rsmd.getColumnCount();
            	boolean more = rs.next();
			//more will be false if there is no return data to display
			if (more) {
				displaystring = "<html><body>\n" + "<table>\n";
				displaystring = displaystring + "<tr>\n";
				for (int j=1; j <= columns; j++) {
					displaystring = displaystring + "<td><b>" + rsmd.getColumnName(j) + "</b></td>";
				}
				displaystring = displaystring + "<tr>\n";
           	  		while (more){
					displaystring = displaystring + "<tr>\n";
					for (int i=1; i <= columns; i++) {
						displaystring = displaystring + "<td>" + rs.getString(i) + "</td>";
					}
					displaystring = displaystring + "<tr>\n";
					//more will change to false when the end of the result set is reached
             	     		more = rs.next(); 
				}
				displaystring = displaystring + "<table>\n" + "</h2></body></html>";
			}else{
				displaystring = "<html><body>SQL statement executed. No results to display.</h2></body></html>";
			}
			if (debug) println(displaystring);
			resultsdisplay.show(displaystring);
		}catch (SQLException sqlexception){
			displaySqlExceptionInformation(sqlexception);
		}
	}

	/**	Closes the database connection if it is open.
	*/
	public void closeDatabaseConnection(){
		if (connection != null){
			try{
				connection.close();
				connection = null;
				connectionstatuslabel.setIcon(disconnected);
			if (debug) System.out.println("Connection to MySQL database " + databasename + " closed.");
			}catch (SQLException sqlexception) {
				displaySqlExceptionInformation(sqlexception);
			}
		}
	}

	/**	Initiates actions to connect to the database after checking to ensure
	*	it is not already connected.
	*/
	public void connect(){
		if (connection == null){
			connection = getDatabaseConnection(databasename);
			if (debug) System.out.println("Connected to database " + databasename);
			connectionstatuslabel.setIcon(connected);
		}
	}

	/**	Establish a database connection.
	*/
	public Connection getDatabaseConnection(String databasename){
		if (debug) println("databasename = " + databasename);
		Connection databaseconnection = null;
		String databaseurl = "jdbc:odbc:" + databasename + "/?user=" 
			+ username + "&password=" + passwordstring;

            try {
			// Load the jdbc-mysql bridge driver
                  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
			//connect to the database using jdbc 
                  databaseconnection = DriverManager.getConnection(databaseurl);
			if (debug) println("Database connection established with " + databasename);
			//set for read only
			//databaseconnection.setReadOnly(true);
                  //Check for sql warnings associated with the connection
			checkAndDisplaySqlWarnings(databaseconnection.getWarnings());
			if (debug) displayConnectionInformation(databaseconnection);
		}catch (SQLException sqlexception) {
			displaySqlExceptionInformation(sqlexception);
            }catch (ClassNotFoundException cnfe){
			System.out.println("ClassNotFoundException: " + cnfe.getMessage());
			System.out.println("Couldn't load database driver.");
		}catch (Exception e) {
			System.out.println("ClassNotFoundException: " + e.getMessage());
			System.out.println("Couldn't load database driver.");
		}
		return databaseconnection;
	}

	/** Executes an SQL statement on a connected database and returns the ResultSet.
	*/
	public ResultSet execsql(String sqlstring, Connection databaseconnection){
		ResultSet resultset = null;

		if (debug) println("Executing SQL statement " + sqlstring);

            try {
			// Create a Statement object from the connection
                  Statement statement = databaseconnection.createStatement();
			if (statement.execute(sqlstring)) {
				resultset = statement.getResultSet();
			}else{
				if (debug) System.out.println("No results");
				resultsdisplay.show("<html><body>Executed SQL statement: " + sqlstring + "<br>No results to display.</h2></body></html>");
				
			}
		}catch (SQLException sqlexception) {
			displaySqlExceptionInformation(sqlexception);
            }
		return resultset;
	}

	
	/**	Displays the database connection information.
	*/
	public void displayConnectionInformation(Connection connection){
		try{
			// Get the DatabaseMetaData object and display connection data
			DatabaseMetaData metadata = connection.getMetaData ();
			System.out.println("Connected to " + metadata.getURL() + "\n");
			System.out.println("Driver: " + metadata.getDriverName() + "\n");
			System.out.println("Version: " + metadata.getDriverVersion() + "\n");
		}catch (SQLException sqlexception) {
			displaySqlExceptionInformation(sqlexception);
            }
	}

	/**	Generates SQL Exception information to display to the user and displays
	*	is to the ser so they can correct the problem or end the session.
	*/
	public void displaySqlExceptionInformation(SQLException sqlexception){
		String exceptionstring = "";
           	while (sqlexception != null) {
			exceptionstring = exceptionstring +
				"*************SQLException:*************************\n" +
				"*** SQLState: "   + sqlexception.getSQLState()  + "\n" +
				"*** Message: "    + sqlexception.getMessage()   + "\n" + 
				"*** Error Code: " + sqlexception.getErrorCode() + "\n" + 
				"***************************************************\n";
			//there could be multiple exception messages chained together
			sqlexception = sqlexception.getNextException();
		}
		println(exceptionstring);
		resultsdisplay.show("<html><body><b>" + exceptionstring + "</b></body></html>");
	}

	/** 	Checks for and displays SQL warnings.  
     	*/
     	public void checkAndDisplaySqlWarnings (SQLWarning sqlwarning) throws SQLException{
		if (hasSqlWarning(sqlwarning)){
			displaySqlWarning(sqlwarning);
            }
     	} 

	/** 	Checks if anSQL warning existed.  
     	*/
     	public boolean hasSqlWarning (SQLWarning sqlwarning) throws SQLException{
            boolean warningstatus = false;
		if (sqlwarning != null) {
			warningstatus = true;
            }
            return warningstatus;
     	} 


	/**	Displays SQL Warning messages. If there are multiple warning messages 
	*	they are chained together and are sorted out one by one.
	*/
	public void displaySqlWarning(SQLWarning sqlwarning)throws SQLException{
		String warningstring = "";
		while (sqlwarning != null) {
			warningstring = warningstring +
			"\n *** SQL Warning ***\n"  +
			"SQLState: " + sqlwarning.getSQLState() +
			"Message:  " + sqlwarning.getMessage() +
			"Vendor:   " + sqlwarning.getErrorCode() +
			"\n *** SQL Warning ***\n";
			sqlwarning = sqlwarning.getNextWarning ();
		}
		println(warningstring);
		resultsdisplay.show("<html><body><b>" + warningstring + "</b></body></html>");		
	}

	/**	Simple class to ensure the database connection closes 
	*	before exiting
	*/
	class FrameListener extends WindowAdapter{

		public void windowClosing(WindowEvent windowevent){
			closeDatabaseConnection();
			System.exit(0);
		}
	}

	/**	Simple class for showing sql result set data as html.
	*	All you have to do is instantiate it, and feed it html
	*	text strings to display.
	*/
	class HtmlFrame{
		JFrame jframe;
		JEditorPane jeditorpane;
		JScrollPane jscrollpane;

		/**	Constructor to set up a basic html viewer.
		*/
		HtmlFrame(){
			this.jeditorpane = new JEditorPane();
			this.jframe = new JFrame("SQL Results");
			this.jscrollpane = new JScrollPane(jeditorpane);
			this.jframe.setDefaultCloseOperation(WindowConstants.DISPOSE_ON_CLOSE);
			this.jframe.setContentPane(jscrollpane);
			Dimension d  = Toolkit.getDefaultToolkit().getScreenSize();
			d.width = d.width;
			d.height = d.height/2;
			this.jframe.setSize(d);
			this.jframe.setLocation(1,d.height);
			this.jframe.show();
		}

		/**	Takes a text string formatted in html for the jedito pane to display.
		*/
		public void show(String htmltext){
			jeditorpane.setContentType("text/html");
			jeditorpane.setText(htmltext);
		}
	}
}